how to set up mySQL 8.0 J/ Connector drive in Mathematica?

Question

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

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. — Sjoerd Smit, Sep 28 '18 at 11:04
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 — user34018, Sep 28 '18 at 11:43
Wolfram has it's own class path for java binaries.See http://reference.wolfram.com/language/JLink/ref/JavaClassPath.html to check how it's set in WL. — Sjoerd Smit, Sep 28 '18 at 12:58
I did this but still not results conn = OpenSQLConnection[ JDBC["com.mysql.cj.jdbc.MysqlDataSource", "jdbc:mysql"], "Name" -> "NewDatabase", "Username" -> "root", "Password" -> "1234"] — user34018, Oct 01 '18 at 01:06
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? http://reference.wolfram.com/language/DatabaseLink/tutorial/Overview.html — Sjoerd Smit, Oct 01 '18 at 08:37
Fedora 30, Mathematica 11.3, MySQL 8.x, latest Oracle Java Connector downloaded for MySQL 8. I have never solved this issue either. In my case, Mathematica segfaults when I try to use the Java Connector and I have to kill the application as the CPU is stuck at 100%. — cjpembo, Sep 22 '19 at 20:38

score 5 · Answer 1 · answered Sep 30 '19 at 17:00

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]

dvand · Answer 2 · 2019-10-27T21:47:33.320

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]

Works fine for me without the @. – cjpembo Nov 01 '19 at 14:27 — cjpembo, Nov 01 '19 at 14:27

score 2 · Answer 3 · answered Jan 21 '20 at 15:07

Obtained the up-to-date mysql-connector-java-8.0.19.jar from

https://dev.mysql.com/downloads/windows/installer/8.0.html

Copied to $InstallationDirectory > SystemFiles > Links > DatabaseLink > Java

and syntax as before:

conn = OpenSQLConnection[JDBC["mysql", "newserver/database"],
  "Username" -> "chris", "Password" -> "masterpassword"

how to set up mySQL 8.0 J/ Connector drive in Mathematica?

3 Answers3