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: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 5.0.24-standard | OS: | Linux |
Assigned to: | CPU Architecture: | Any | |
Tags: | qc |
[12 Jun 2007 21:25]
Dave Pullin
[12 Jun 2007 21:26]
Dave Pullin
correct category is Connector/J not Connector/ODBC
[15 Jun 2007 12:17]
Tonci Grgin
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."
[17 Jun 2007 16:33]
Dave Pullin
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.
[17 Jun 2007 20:27]
Tonci Grgin
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.
[17 Jun 2007 20:30]
Tonci Grgin
Dave, maybe you'd like to change synopsis now.
[18 Jun 2007 4:00]
Valeriy Kravchuk
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.
[18 Jun 2007 4:59]
Dave Pullin
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.
[18 Jun 2007 6:22]
Sveta Smirnova
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"