[Sqlrelay-discussion] Router Connection only Returns 10 Rows && MySQL infini-loop

[Chris C. <cc...@gm...>]

This is in reference to the bugs mentioned with the router overlay
where it only returns 10 rows (see:
http://sourceforge.net/mailarchive/message.php?msg_id=37528685)  and
MySQL connections looping on reLogIn() infinitely when the host-end
closes the connection (see:
http://sourceforge.net/mailarchive/forum.php?thread_id=26504786&forum_id=43840).

Setup:
 - sqlrelay-0.3.9 (from CVS) on Gentoo
 - dbhost1 - Gentoo running MySQL-5.0.22-MAX
 - dbhost2 - Debian (Sarge) running MySQL-5.0.22-MAX

::Insufficient Result Set with "router" dbtype::
For this problem, the suggested patch of looking to
cur->endOfResultSet() still did not return the entire set (seemed to
return anywhere from 2-10 fewer rows than it should have) so i dug
around and found cur->totalRows();

--- routerconnection.C.orig     2007-02-09 18:22:18.000000000 +0000
+++ routerconnection.C  2007-02-12 20:21:19.000000000 +0000
@@ -708,7 +708,12 @@
 }

 bool routercursor::fetchRow() {
-       if (nextrow<cur->rowCount()) {
+       //if (nextrow<cur->rowCount()) {
+       //if (!cur->endOfResultSet()) {
+       if (nextrow < cur->totalRows()) {
                nextrow++;
                return true;
        }

With this patch I no longer have this issue, but as the documentation
for the API states, not all databases support this function, but it
works for MySQL.

::MySQL connection loops infinitely::
For our specific purposes we have an aggressive wait_timeout with
mysql (10 seconds) due to some broken clients that would spawn
hundreds of connections without properly closing them.

This can be fixed by enabling auto_reconnect:
--- /root/sqlrel-mon/sqlrelay/src/connections/mysql/mysqlconnection.C
 2006-12-13 03:00:28.000000000 +0000
+++ ./mysqlconnection.C 2007-02-12 21:35:45.000000000 +0000
@@ -95,6 +95,10 @@
                logOut();
                return false;
        }
+
+       // Enable autoreconnect in the C api
+       mysql_options(&mysql,MYSQL_OPT_RECONNECT,"1");
+
 #ifdef MYSQL_SELECT_DB
        if (mysql_select_db(&mysql,dbval)) {
                if (printerrors) {

Auto_reconnect is transparent to the client API so the "Server Has
Gone Away!" message never makes it back to handleError().  NOTE: This
may not be compatible with older versions of MySQL (3.x-4.x).

Hopefully this helps some other people who would like to use sqlRelay
but ran into the same walls I did.

Kudos to Dave Muse for a great project,

 - ccoyle