2
$\begingroup$

I have upgraded my MySQL database to 8.0. I downloaded its java connector, and moved mysql-connector-java-8.0.12.jar to :

/Applications/Mathematica.app/Contents/SystemFiles/Links/DatabaseLink/Java/

Set CLASSPATH to make sure it can be found

I went to Mathematica and tried to create a new connector:

 com.mysql.cj.jdbc.Driver

and for the protocol I used:

  jdbc:mysql://

Saved it.

Created a new connection to my database, but when I tried to connect I got Error: JDBC::classnotfound

by the way I did the following:

 conn = OpenSQLConnection[
     JDBC["com.mysql.cj.jdbc.Driver", "jdbc:mysql://"], 
     "Name" -> "MyDatabase", "Username" -> "root", 
     "Password" -> "secret"]

Now I get the following Error

 JDBC::error: Access denied for user 'root'@'localhost' (using password: YES)

Any help will be most appreciated

$\endgroup$
  • $\begingroup$ Is there a reason you put the driver in that directory? The times I needed to set up SQL connections, I've always used $UserBaseDirectory/Applications/Oracle/ with success. I put the jar into $UserBaseDirectory/Applications/Oracle/Java and the JDBCDriver configuration .m file into $UserBaseDirectory/Applications/Oracle/DatabaseResources. $\endgroup$ – Sjoerd Smit Sep 28 '18 at 11:04
  • $\begingroup$ Looking at that directory, I found out that it already have the jar for version 5 of MySQL, so it made sense to put it there. But I think my problem has to do with changing my CLASSPATH, do you know how I can reset it so I can access mySQL? Now I cannot even access it through MySQLWorkbench $\endgroup$ – user34018 Sep 28 '18 at 11:43
  • $\begingroup$ Wolfram has it's own class path for java binaries.See reference.wolfram.com/language/JLink/ref/JavaClassPath.html to check how it's set in WL. $\endgroup$ – Sjoerd Smit Sep 28 '18 at 12:58
  • $\begingroup$ I did this but still not results conn = OpenSQLConnection[ JDBC["com.mysql.cj.jdbc.MysqlDataSource", "jdbc:mysql"], "Name" -> "NewDatabase", "Username" -> "root", "Password" -> "1234"] $\endgroup$ – user34018 Oct 1 '18 at 1:06
  • $\begingroup$ In that case I don't think I can help you much further, since all I really know is what's in the documentation. Are you sure you didn't overlook anything in the user guide? reference.wolfram.com/language/DatabaseLink/tutorial/… $\endgroup$ – Sjoerd Smit Oct 1 '18 at 8:37
4
$\begingroup$

I have a working MySQL 8.x configuration that I'd like to pass along.

First, there are a few important MySQL configuration options we need to be aware of.

A default MySQL 8.x server configuration will accept, but not force, an SSL client connection. So unless your MySQL 5.x or 8.x server configuration has been altered to force an SSL connection, no additional parameters are required to connect.

More info here: MySQL 8.x SSL Connections

MySQL 8.x has updated the default behavior of DATETIME and TIMESTAMP. I was able to maintain compatibility with my database by including the "zeroDateTimeBehavior=convertToNull" connection option. YMMV. More info here: Datetime and Timestamp Changes

MySQL 8.x requires that you have a timezone configured (not sure about 5.x). I chose to define a timezone in my connection parameters rather than in my server config ("serverTimezone=UTC")

Second: where to place an updated MySQL connector jar file? Essentially, it does not matter where you place the updated jar file, as long as you include the file in your ClassPath. In my example I left the 8.x connector jar file in the directory the installer chose. I just passed the information along using "AddToClassPath". You could also place the updated connector jar file in any folder already included in Mathematica's default ClassPath, as found using "JavaClassPath[]".

Third: change "com.mysql.jdbc.Driver" to "com.mysql.cj.jdbc.Driver" as the MySQL 8.x connector has been updated and packaged slightly different than the 5.x versions.

The following connection works for me:

Needs["JLink`"];
AddToClassPath[FileNameJoin[{"/usr/share/java/mysql-connector-java.jar"}]];
JavaClassPath[]
Needs["DatabaseLink`"];
ShowJavaConsole[];
conn = OpenSQLConnection[JDBC["com.mysql.cj.jdbc.Driver","jdbc:mysql://localhost:3306/test?serverTimezone=UTC&zeroDateTimeBehavior=convertToNull"],"Username" -> "user","Password" -> "abcdefg", "ReadOnly" -> True]
$\endgroup$
0
$\begingroup$
conn = 
  OpenSQLConnection[
    JDBC[
      "com.mysql.cj.jdbc.Driver",
      "jdbc:mysql://localhost:3306/test?serverTimezone=UTC&zeroDateTimeBehavior=convertToNull"],
    "Username" -> "user", "Password" -> "abcdefg", "ReadOnly" -> True]

Above is incorrect because there is a required '@' before 'localhost'


Following is correct because the required '@' before 'localhost' has been added:

conn = 
  OpenSQLConnection[
    JDBC[
      "com.mysql.cj.jdbc.Driver",
      "jdbc:mysql://@localhost:3306/test?serverTimezone=UTC&zeroDateTimeBehavior=convertToNull"],
    "Username" -> "user","Password" -> "abcdefg", "ReadOnly" -> True]
$\endgroup$
  • $\begingroup$ Works fine for me without the @. $\endgroup$ – cjpembo Nov 1 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.