0002068: Broken MySQL connections returned by mysql_connect()

Viewing Issue Simple Details [ Jump to Notes ]

[ View Advanced ] [ Issue History ] [ Print ]

Category

Severity

Reproducibility

Date Submitted

Last Update

0002068

[Quercus]

major

always

10-12-07 17:43

10-16-07 10:26

Reporter

koreth

View Status

public

Assigned To

ferg

Priority

normal

Resolution

fixed

Status

closed

Product Version

3.1.3

Summary

0002068: Broken MySQL connections returned by mysql_connect()

Description

When Resin/Quercus loses its connection to a MySQL server, it continues to make the dead connection available to Quercus code for subsequent queries, even if the code asks for a new connection. This breaks application code that checks for query failures and attempts to retry.

To test, set up a MySQL instance with a 10-second idle timeout. Create a user "testuser" with password "testpass" and create a table "test_table" in a database "testdb" (or modify the code to do some other query). Then try the attached code.

Vanilla PHP will say

got initial row
query failed
got initial row

Quercus will say

got initial row
/Users/sgrimm/resin/git-trunk/webapps/facebook/test.php:17: Warning: '1' of type 'BooleanValue' is an unexpected argument, expected MysqliResult [mysql_fetch_row]
row fetch failed
/Users/sgrimm/resin/git-trunk/webapps/facebook/test.php:6: Warning: Connection.close() has already been called. Invalid operation in this state. [mysql_select_db]
/Users/sgrimm/resin/git-trunk/webapps/facebook/test.php:17: Warning: '1' of type 'BooleanValue' is an unexpected argument, expected MysqliResult [mysql_fetch_row]
row fetch failed

In other words, PHP code can't recover from a failed database connection, because it will keep getting the same connection back when it asks for a fresh one. The fact that errors are output to the user is a problem too, but I expect there's a config option somewhere to turn that off.

The lack of connection recovery is the 0000001 issue I'm running into as I try to get our code base up and running under Quercus.

Additional Information

I have a hack to sort of work around the problem. In Mysqli.select_db(), I add the following at the top of the "try" block:

      if (! ping()) {
        close(getEnv());
        System.err.println("Reconnecting to " + getHost());
        connectInternal(getEnv(), getHost(), getUserName(), getPassword(), dbname, getPort(), null, 0, null, null);
      }

Seems to help, but of course it doesn't actually address the bug I described, just papers over the result a bit. (The workaround is only somewhat effective.)

Attached Files

test.php [^] (752 bytes) 10-12-07 17:43

Relationships

Notes
(0002341) koreth 10-12-07 18:05	I'm using the 5.1.5 MySQL JDBC driver, the latest stable one from the MySQL site.

(0002348) ferg 10-16-07 10:26	This should be resolved with the 0001108 fix. The <database-default> can set the max-idle-time (which is the main issue), and also enable <ping> to check that the connection is still valid when it's taken from the pool. (A <ping> without a <ping-table> will check Connection.isClosed() without adding extra database load.)

Issue History
Date Modified	Username	Field	Change
10-12-07 17:43	koreth	New Issue
10-12-07 17:43	koreth	File Added: test.php
10-12-07 18:05	koreth	Note Added: 0002341
10-16-07 10:26	ferg	Note Added: 0002348
10-16-07 10:26	ferg	Assigned To	=> ferg
10-16-07 10:26	ferg	Status	new => closed
10-16-07 10:26	ferg	Resolution	open => fixed
10-16-07 10:26	ferg	Fixed in Version	=> 3.1.4

Mantis 1.0.0rc3[^]

34 total queries executed.
29 unique queries executed.