# MySQL Connection Problem

I have a problem with the configuration of the J-Connector plug-in with a MySQL database. Most of the time all works well, but sometimes the connection remains blocked for 2 hours and then it gives this error:

JDBC::error: "Communications link failure\n\nThe last packet successfully
received from the server was 7,200,110 milliseconds ago.  The last packet
sent successfully to the server was 7,200,047 milliseconds ago."


Note: I'm executing the query right after the connection is set, without waiting. It seems that the query is fetching results (with Mathematica running), but instead it remains blocked for 2 hours and then it fails.

Edit: I have noticed that the connection is correctly estabilished, then during the execution of the query it magically disappears (I have seen this checking the SHOW PROCESSLIST with another connection to database), but the JDBC driver didn't know anything about this and then after 2 hours it gives the error.

It looks like it has a 2 hours timeout (7,200,000 milliseconds) which I didn't set, my question is: how can I set it to a more reasonable time (like 1 minute)? So I can catch the error and re-execute the query without waiting for a long time.

Or, anyway, do you have any other solution to handle MySQL connection problems?

-

In the past, years ago, I have had problems with the MySQL connection that were solved following Wolfram's tech support advice of updating the java connector to its latest version. This can be done by:

2. Install it at $InstallationDirectory->SystemFiles->Links->DatabaseLink->Java My Mathematica 8 has version 5.1.13 installed and the latest available now at mysql.com is 5.1.18 You could try this, and save the original connector in a separate location just in case you need to revert. BTW, Which platform and mysql version are you using? After reading your comment I realized that what you need to do is to wrap your connection code inside a TimeConstrained command. In the past I have used this code to connect reliably to my database: Needs["DatabaseLink"]; CloseSQLConnection[conn]; TimeConstrained[ conn = OpenSQLConnection[JDBC["mysql","localhost:3306/my_database_name"], "Username"->"login", "Password"->"secret"], 5, CloseSQLConnection[conn] ];  - I have your same version, 5.1.13 installed, and I can see the 5.1.18 available on MySQL site! I'll try it but I don't think this is the problem, the database where I'm going to connect to is very busy so sometimes it remains blocked insted of replying, I don't think this is a driver problem, but only an overload problem that I want to catch and solve! – Francesco Feb 2 '12 at 15:42 This is a good idea but unfortunately this is not the solution. I tried to check the AbsoluteTiming of TimeConstrained[SQLExecute[CONNECTION, "a query that take more than 5 seconds to run"], 5] and this does NOT stop after 5 seconds, but it waits to the query to be completed and then reply $Aborted instead of the results 'cause of the TimeConstrained instruction. In other words, TimeConstrained doesn't work well if it's argument is a MySQL's function. –  Francesco Feb 3 '12 at 9:40

You appear to be facing a problem related to the configuration of MySQL itself. Have you take a look at the MySQL server configurations?

It appears to be the value of the wait_timeout property which depending on the installer used defaults to something between 2 and 8 hours.

Normally, the server configuration file is called my.cfg and it is located in the folder contained in the environment variable MYSQL_HOME.

Do you have other applications in the same machine as Mathematica that connect to this MySQL instance?

-
Hi Ailton and thanks for your reply. I didn't check the MySQL server configuration, but I don't think the wait_timeout property is the problem, because that variable is defined as "The number of seconds the server waits for activity on a noninteractive connection before closing it.", and my connection shouldn't be "noninteractive". That variable would be useful if the problem is: - I open the connection - I wait 2 hours - I make the query and MySQL answer "connection is closed" But this is not the case :( I'll add some details to my question! –  Francesco Feb 2 '12 at 12:44
Hello Francesco, I am still suspecting that it is a MySQL problem or, at most, a JDBC driver configuration problem. Are you using some form of connection pooling at the server? One possibility is that your connection is made idle at pooling server in such a way that the client side is unaware of it and keeps itself trying to use an already dead connection. What do you think about enabling the trace facility on this JDBC connection? –  Ailton Andrade de Oliveira Feb 3 '12 at 19:11
I would also suggest for you to set both wait_timeout and interactive_timeout to about 60 seconds. –  Ailton Andrade de Oliveira Feb 3 '12 at 19:19
I'm agree with you. I think it's a JDBC Driver Configuration problem. I don't use any connection pooling system, and unfortunately I can't change the database configuration (like setting wait_timeout and so on...), I think we should check dev.mysql.com/doc/refman/5.0/en/… and find the right option! –  Francesco Feb 6 '12 at 9:35
I think that we should start with more details about the platform and version of your MySQL server and your Connector/J client. Have you tried to use some SQL client based on JDBC to issue a SHOW VARIABLES and even test your queries outside of Mathematica? I would try the free SQuirreL SQL Client. –  Ailton Andrade de Oliveira Feb 6 '12 at 20:09

Here is database connection file to one of my databases. The file can be found on Linux systems in directory:

/home/username/.Mathematica/DatabaseResources/mydatabse.m


If you can't find mydatabase.m or DatabaseResources directory, try creating them (I assume that path should be similar on Windows?).
Here is a config file:

SQLConnection[JDBC["MySQL(Connector/J)", "servername:3306/dbname"],
"Catalog" -> Automatic, "Description" -> None, "Name" -> "dbname",
"RelativePath" -> False, "TransactionIsolationLevel" -> "ReadUncommitted",


3306 is a port number at which mysql server listens to connections. Make sure that you can connect from your host to mysql on a server, check that, before trying to correct the problem in Mathematica.

-
Thanks enedene for your reply but my problem is not the "normal" configuration of my connection (like username, password, port, and so on...) because I can succesfully connect and get results from my queries most of the times. The problem happens only sometimes when my connection remains blocked for hours without giving any results. –  Francesco Feb 3 '12 at 9:20

Check the following Linux parameters.

cat /proc/sys/net/ipv4/tcp_keepalive_time
cat /proc/sys/net/ipv4/tcp_keepalive_intvl
cat /proc/sys/net/ipv4/tcp_keepalive_probes
`
-