Mantis - Quercus
Viewing Issue Advanced Details
2068 major always 10-12-07 17:43 10-16-07 10:26
closed 3.1.3  
none 3.1.4  
0002068: Broken MySQL connections returned by mysql_connect()
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.
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()) {
        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.)
 test.php [^] (752 bytes) 10-12-07 17:43

10-12-07 18:05   
I'm using the 5.1.5 MySQL JDBC driver, the latest stable one from the MySQL site.
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.)