1
$\begingroup$

I'm trying to connect to a MySQL database. I've seen quite a few different methods (some of which are conflicting and seem to be outdated) of connecting but haven't managed to get any to work so far.

The following code gives an error message:

Needs["DatabaseLink`"]

OpenSQLConnection[
 JDBC["MySQL(Connector/J)", "ip/database"], 
 "Version" -> 3.1, "Catalog" -> Automatic, "Description" -> None, 
 "autoReconnect" -> True, "Password" -> "xxxx", 
  "Properties" -> {"useSSL" -> "False"}, "ReadOnly" -> Automatic, 
 "RelativePath" -> False,
   "TransactionIsolationLevel" -> Automatic, 
  "UseConnectionPool" -> Automatic, "Username" -> "root", 
  "Timeout" -> 1000]

(* JDBC::error: Unable to load authentication plugin 'caching_sha2_password' *)

Any help/suggestions with this would be much appreciate, thanks in advance!

Improve this question
$\endgroup$
10
  • $\begingroup$ This code seems okay and it also works for me, connecting to my local MySQL server. What problem do you have? Is there an error message? Since you are using an IP which is not publicly accessible, you most likely have not configured your firewall properly. Can you connect to the server with some other tools? Is this server on your local machine? $\endgroup$
    – Domen
    Commented Oct 26, 2022 at 10:59
  • $\begingroup$ @Domen i get the error message: JDBC::error: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. $\endgroup$
    – wmv
    Commented Oct 26, 2022 at 11:03
  • $\begingroup$ Great, I get the same error when trying to connect to your server. I haven't been able to connect to your server with any other tools either, so the problem – as I have already stated – is not in Mathematica code but in the configuration of your server. If you answer to my other questions in the previous comment, we might be able to help you (although Mathematica SE is not really the right place for this kind of problems :)). $\endgroup$
    – Domen
    Commented Oct 26, 2022 at 11:11
  • 1
    $\begingroup$ If the server is on your local machine, then you shouldn't use your public IP, but localhost or 127.0.0.1 instead. That is: OpenSQLConnection[JDBC["MySQL(Connector/J)", "127.0.0.1/database"], ...]] Please let us know if this works. $\endgroup$
    – Domen
    Commented Oct 26, 2022 at 12:46
  • 1
    $\begingroup$ It seems like Mathematica uses an older version of JDBCDriver that doesn't support some default configuration of newer MySQL servers. Please read this answer to change settings on server. $\endgroup$
    – Domen
    Commented Oct 26, 2022 at 13:10

1 Answer 1

Reset to default
2
$\begingroup$

MySQL provides two authentication plugins for user passwords: sha256_password and caching_sha2_password. In MySQL 8.0, caching_sha2_password is the default authentication plugin rather than mysql_native_password. However, the JDBC driver that is used in Mathematica to connect to a server implemented caching_sha2_password in version 5.1.46, as described in changelog for MySQL Connector/J.

However, even in the latest Mathematica version 13.1.0, the included JDBC driver seems to be outdated (5.1.44).

Needs["DatabaseLink`"]
JDBCDrivers["MySQL(Connector/J)"]

(* JDBCDriver["Name" -> "MySQL(Connector/J)", 
 "Driver" -> "com.mysql.jdbc.Driver", 
 "Protocol" -> {"jdbc:mysql://", "jdbc:mysql:loadbalance://"}, 
 "Version" -> 3.1, 
 "Description" -> "MySQL using Connector/J - Version 5.1.44", 
 "Location" -> "..."] *)

Solution A

Manually download the newest JDBC driver. Then add it to Mathematica as described in this answer.

Solution B

Change the authentication method in your MySQL server as described in this answer.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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