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

[David M. <dav...@fi...>]

Thanks for the patches Chris,

The totalRows() patch will work with some DB's but not with ones that
don't know how many rows are going to come back from the query ahead of
time.  But, I think I know how to fix it for all db's.

I'll test out the autoreconnect patch.  It may need some #ifdefs for
older platforms, but otherwise it looks good!

Dave
dav...@fi...

On Mon, 2007-02-12 at 21:50 +0000, Chris Coyle wrote:
> 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
> 
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job easier.
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>