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

Answer 1

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]

Answer 2

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]

Answer 3

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"