MySQL Bugs: #29061: Connection timeout inspite of 'autoReconnect=true'

Bug #29061	Connection timeout inspite of 'autoReconnect=true'
Submitted:	12 Jun 2007 21:25	Modified:	18 Jun 2007 6:22
Reporter:	Dave Pullin (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.24-standard	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
I suspect a jdbc error but I cant provide a simple reproducible example.

I am issuing an UPDATE that takes a long time (eg 7 hours). It fails with an com.mysql.jdbc.CommunicationsException
with message

The last communications with the server was 28817 seconds ago, which 
 is longer than the server  configured value of 'wait_timeout'. You should consider either expiring and/or testing  connection validity before use in your application, increasing the server configured 
values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

The problem is that I *do* check the validity of the connection before doing the update, and I **do use 'autoReconnect=true'.

I know that the jdbc driver, particularly V5 which I recently upgraded too, does a lot a chatter to server (select warnings() for example). 

I suspect that my long update timed-out the connection,
but that the after-the-update-chatter used the connection without checking its validity,
and without noticing the 'autoReconnect=true'. 

Can you confirm that this is true or not true?

How to repeat:

IN java open mysql connection with 'autoReconnect=true' 

issue an update (INSERT ... for huge and complicated table)

(But I dont really expect you to try it!).

correct category is Connector/J not Connector/ODBC

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Explanation:
Hi Dave and thanks for your report. "(But I dont really expect you to try it!)." of course we'll try but you need to provide us with as much info as possible:
 - MySQL server version, host os and version
 - JDK/JRE used
 - my.cnf / my.ini file
 - Small but complete test case exhibiting the error. Would be good to add SQL script with tables and data which I can import as "issue an update (INSERT ... for huge and complicated table)" is different for everybody.

What is the output of "select @@wait_timeout;" on your machine? It is better to increase server timeout than to use autoReconnect:
"Should the driver try to re-establish stale and/or
dead connections? If enabled the driver will throw
an exception for a queries issued on a stale or dead
connection, which belong to the current transaction,
but will attempt reconnect before the next
query issued on the connection in a new transaction.
The use of this feature is not recommended,
because it has side effects related to session state
and data consistency when applications
don'thandle SQLExceptions properly, and is only
designed to be used when you are unable to configure
your application to handle SQLExceptions
resulting from dead andstale connections properly.
Alternatively, investigate setting the MySQL server
variable "wait_timeout"to some high value
rather than the default of 8 hours."

I know you need to reproduce the problem, and I'd give you a method to do so if it didn't take a billion row database and 8 hours to make the bug happen!

However, with your hint, REDUCING the wait_timeout to 20 secs makes it fail in 20 secs. But it's specific to this particular UPDATE statement (A different update using small tables does that takes morfe than 20 seconds does not fail after 20 seconds with a timeout.)

But now I can see that is is NOT a jdbc bug. I ran the same Update in the Mysql console and the console hung. Nothing came back after the ";<enter>".
Running "SHOW PROCESSLIST;" in another console session showed that the UPDATE was not running, so it looks like the server is hanging prre-processing the Update statement.

I have the category to Server and the version to the server version 5.0.24-standard.

For reference the update is

insert ignore into transient.A select  s.* from working.B    join C as s  using(akey);

When C is a table with 283M rows, the mysql console hangs on this update.
If I replace it with  table D created LIKE C, and inserted into D select * from C limit 1000, everything works fine.

Anyway, unless and until I can isolate the bug to a small table there's probably nothing you can do to help .... I was hoping this was an "obvious" jdbc bug, but not such luck.

Dave, that's the point of asking so much info, to exclude obvious things... Thanks for narrowing the problem down to server, I'll deassign now and inform my colleagues of this report. In any case, they'll ask mainly the same info so proceed with attaching requested files, especially configuration and error.log if there is one available.

Dave, maybe you'd like to change synopsis now.

As we suspect a server bug here, please, check with a newer server version, 5.0.41/5.0.42, and inform about the results. In case of the same problem, please, send SHOW PROCESSLIST results while UPDATE is hanging.

I found an "error 5" on my 283M row table, in the .err file, I ran CHECK TABLE - it said "corrupted". REPAIR table fixed it, and the Update ran OK.

Which I guess means there is no "real" bug, except that the diagnosis of an I/O error by the server timing out doesn't seem too great! 

I have the synopsis as it was as that might help anyone in the same position I was. Thanks Guys.

Thank you for the feedback.

> Which I guess means there is no "real" bug

I'll change status of the report to "Not a Bug"