Bug 5107 - LIGO: globus-rls-server does not have automatic reconnection to DBMS
: LIGO: globus-rls-server does not have automatic reconnection to DBMS
Status: RESOLVED FIXED
: Replica Location
Roadmap
: development
: PC Linux
: P3 normal
: ---
Assigned To:
:
:
:
:
  Show dependency treegraph
 
Reported: 2007-03-16 16:52 by
Modified: 2008-05-28 19:19 (History)


Attachments


Note

You need to log in before you can comment on or make changes to this bug.


Description From 2007-03-16 16:52:19
In newer versions of MySQL (post version 3, I believe), the globus-rls-server
disconnects from the MySQL database after a short amount of time. This time can
be changed with configuration options to the MySQL server itself, but it cannot
make RLS automatically reconnect. Therefore, after whatever amount of time
specified of no MySQL activity, the RLS server needs to be restarted to
reattach itself to MySQL.

An example error message (4.1 of RLS with 5.0.22 of MySQL and 2.2.11 of
unixODBC):
globus_rls_client: DB error: [MySQL][ODBC 3.51
Driver][mysqld-5.0.22-standard]MySQL server has gone away

The MySQL documentation says this about the error:
'You have encountered a timeout on the server side and the automatic
reconnection in the client is disabled (the reconnect flag in the MYSQL
structure is equal to 0).'

It doesn't make sense to make large timeouts globally for all MySQL clients.
Instead, a service like RLS which is bound to the database backend should
always be able to reattach itself to the database if it has lost its
connection.

As a side note, I corresponded with some RLS developers in August 2006 about
this but never saw anything further about this issue.
------- Comment #1 From 2007-05-23 17:36:53 -------
I have recreated the bug with:
globus-rls-server 4.2
MySQL 5.0.27
MySQL Connector/ODBC 3.51.15

One interesting finding is that the old ODBC driver, MyODBC 3.51.06, seems to
have an auto-reconnect feature enabled by default. When using that driver, the
odbc driver reconnects and the RLS does not experience this bug.
------- Comment #2 From 2007-05-23 17:43:11 -------
MySQL has an auto-reconnect option available for MySQL Connector/ODBC driver's
starting with version 3.51.13.

See:
http://dev.mysql.com/doc/refman/5.0/en/myodbc-configuration-connection-parameters.html

option = 4194304

"Enables auto-reconnection functionality. You should not use this option with
transactions, since a auto reconnection during a incomplete transaction may
cause corruption. Note that an auto-reconnected connection will not inherit the
same settings and environment as the original. This option was enabled in
Connector/ODBC 3.5.13."

I have tested it with 3.51.15 with an odbc.ini that looks like this:

[lrc1000_mysql_3_51_15]
Driver      =
/nfs/v5/mypath/odbc/Drivers/mysql-connector-odbc-3.51.15-linux-x86-32bit/lib/libmyodbc3.so
Database    = lrc1000
SERVER      = myhost.isi.edu
PORT        = 3306
option      = 4194304

Using this option the RLS server (rather, the odbc driver) reconnects to the
MySQL server.
------- Comment #3 From 2007-05-23 21:38:07 -------
Comment #c2 discusses a partial solution based on the MySQL ODBC driver
auto-reconnect feature. Alternatively, it may be desirable to implement a
solution within the RLS server that uses the ODBC interface alone (as opposed
to relying on the configuration of the ODBC driver).

Using only the ODBC interface, one convenient solution would be to check the
connection attribute SQL_ATTR_CONNECTION_DEAD using the method
SQLGetConnectAttr. If a check on the connection dead attribute returned true,
then RLS would close/free the connection resources and reconnect.

Unfortunately, the attribute (SQL_ATTR_CONNECTION_DEAD) describe above is
available in the ODBC 3.5 standard specification, which is not supported by the
open source (permissively licensed) ODBC interface that RLS depends on --
libiodbc. Currently, libiodbc is ODBC 3.0 compliant.

A less ideal alternative may be to attempt a simple, dummy query to test
whether the connection is alive.
------- Comment #4 From 2007-05-24 16:34:03 -------
In the interest of finding a solution for PostgreSQL (as well as MySQL) users,
I have recreated the bug with:
globus-rls-server 4.2
postgres 7.4.6
psqlODBC 08.01.0200
------- Comment #5 From 2007-11-09 13:33:41 -------
Just noticed that there was a bug in MySQL drivers from >=3.51.10 to <3.51.20
that prevents dead connections from being properly identified. Full details are
here:

http://bugs.mysql.com/bug.php?id=14639

So for the SQL_ATTR_CONNECTION_DEAD attribute to respond correctly MySQL users
will need to use 3.51.20+ drivers.
------- Comment #6 From 2007-11-09 20:54:13 -------
The following patch (actually one for 4.0 branch and one for trunk) enable RLS
server to reconnect to a db.

4.0 branch: http://www.isi.edu/~schuler/bug5107/bug5107.40branch.diff
trunk: http://www.isi.edu/~schuler/bug5107/bug5107.trunk.diff

Both are committed to CVS.

Details
-------
The RLS now uses the SQLGetConnectionAttr call to check the
SQL_ATTR_CONNECTION_DEAD attribute. It appears that we can expect recent
versions of unixODBC, iodbc, MySQL and SQLite drivers to support it.

On each new request, the RLS checks the connection dead attribute. If the
driver indicates that the connection is dead, the RLS closes the connection,
frees it, and then attempts to open a new database connection. If it fails to
open, that is an indication that the db is down, and the RLS returns a
GLOBUS_RLS_DBERROR to the client.

Tested
------
It has been tested with:
globus-rls-server (trunk)
globus-rls-server (globus_4_0_branch)

libiodbc-3.51.2

MySQL ODBC 3.51.06
MySQL ODBC 3.51.15
MySQL ODBC 3.51.21

SQLite ODBC 0.74

MySQL Notes
-----------
On MySQL drivers 3.51.20+ support the connection attribute properly. For
instance:
  -- 3.51.06 does not support it but automatically reconnects by default
  -- 3.51.15 does not support it and does not automatically reconnect by
default
  -- 3.51.21 does support it and does not automatically reconnect by default

As noted earlier, MySQL has an option that tells the drivers to (such as
3.51.15) to automatically reconnect. MySQL users should upgrade their drivers
to a supporting version or set the auto-reconnect option.
------- Comment #7 From 2007-11-09 21:34:28 -------
A GPT package of the updated globus_rls_server:
http://www.isi.edu/~schuler/bug5107/globus_rls_server-4.4.tar.gz

I believe this will be added to the GT Advisories page soon.
------- Comment #8 From 2007-11-09 21:52:46 -------
The package is now available on the GT Advisories page.