MySQL Connection Problem

7

2

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?

Francesco

Posted 2012-02-02T10:35:18.737

Reputation: 571

Answers

5

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:

  1. Download the connector from MySQL web page: http://www.mysql.com/downloads/connector/j/
  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]
];

Gustavo Delfino

Posted 2012-02-02T10:35:18.737

Reputation: 6 847

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 – 2012-02-02T15:42:06.233

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 – 2012-02-03T09:40:20.960

2

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?

Ailton Andrade de Oliveira

Posted 2012-02-02T10:35:18.737

Reputation: 411

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 – 2012-02-02T12:44:40.240

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 – 2012-02-03T19:11:12.693

I would also suggest for you to set both wait_timeout and interactive_timeout to about 60 seconds. – Ailton Andrade de Oliveira – 2012-02-03T19:19:44.290

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 http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html and find the right option!

– Francesco – 2012-02-06T09:35:06.583

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 – 2012-02-06T20:09:58.493

I added some details to the first post with new discoveries about my problem. It seems like the query get killed, the problem is that the JDBC driver didn't notice this instantly, but instead it wait for 2 hours without packet received and then it gives the error. I think we should find this kind of timeout of the JDBC driver and set it differently. – Francesco – 2012-02-07T09:34:40.720

Anyway I'm using MySQL Workbench, which variable should I check? My queries on that program anyway works well without crashing :( – Francesco – 2012-02-07T09:37:55.240

Are there any firewalls or routers between the machine with Mathematica and the MySQL server? It could be something breaking the connection at the network level. This can happen if the query demands too much time to deliver results. If this is the case, maybe, setting tcpKeepAlive can help. – Ailton Andrade de Oliveira – 2012-02-07T10:13:59.427

Good idea Ailton. I have investigated and something (that is not Mathematica or MySQL) close the connection after 18/20 seconds, I checked the configuration and tcpKeepAlive should be on by default, do you have any idea why it seems it doesn't work? – Francesco – 2012-02-07T12:39:42.207

I think that now we are delving into the realm of network problem solving. Have you tried a traceroute command to see how the server is reached from your client? Could you make a test putting the client with Mathematica closer to the MySQL server in terms of network topology? – Ailton Andrade de Oliveira – 2012-02-08T00:23:32.807

Uhm... no, I can't change the network topology, the network stuff is now dealing with the problem, now we have understood that it's not Mathematica. Thanks for your help Ailton, there's nothing we can do anymore :) – Francesco – 2012-02-09T08:34:39.050

Well, now that we found out that this not a issue with Mathematica, I think that a moderator should take a look at this question. I don't know if it should be kept here or go to another Stack Exchange website or if it should be tagged in a special way. Anyway, it was an interesting discussion. Good luck! – Ailton Andrade de Oliveira – 2012-02-09T16:29:44.697

Thank you ;) The problem is now solved by network administrators :) ...thanks again! – Francesco – 2012-02-15T10:14:49.200

1

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", 
"Password" -> "yourpassword", "Properties" -> {}, "ReadOnly" -> False, 
"RelativePath" -> False, "TransactionIsolationLevel" -> "ReadUncommitted", 
"UseConnectionPool" -> False, "Username" -> "dbusername", "Version" -> 2.]

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.

enedene

Posted 2012-02-02T10:35:18.737

Reputation: 213

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 – 2012-02-03T09:20:09.923

1

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

sachin dholakia

Posted 2012-02-02T10:35:18.737

Reputation: 11