Re: [Sqlrelay-discussion] Unknown prepared statement handler?

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

It sounds like something between SQL Relay and the DB is disconnecting
the 2 of them from each other if they've been idle.  A common culprit is
a Cisco PIX firewall.  Is there a Cisco PIX firewall between SQL Relay
and the DB?
http://sqlrelay.sourceforge.net/sqlrelay/faq.html#firewallhang

Aside from that, I can't think of anything that could cause that to
happen.

Dave Muse
dav...@fi...

On Tue, 2009-04-14 at 12:17 -0400, ni...@sk... wrote:
> Some more diagnostic stuff that may help:
> 
> sqlr-status outout
> ------------------
> 
> sqlr-status -id mysqltest -config <absolute path
> root>/sqlrelay-0.40/config_file/conf.xml
> 
> Warning: using default connectionid.
> 
>   Open   Server Connections:  55
>   Opened Server Connections:  3400
> 
>   Open   Client Connections:  0
>   Opened Client Connections:  22107625
> 
>   Open   Server Cursors:      1375
>   Opened Server Cursors:      85000
> 
>   Times  New Cursor Used:     0
>   Times  Cursor Reused:       22619095
> 
>   Total  Queries:             22619090
>   Total  Errors:              0
> 
>   Forked Listeners:           0
> 
> Number of actual open DB connections
> ------------------------------------
> 
> I noticed that the number of open database connections had dropped to
> zero, as shown by the MySQL command 'show processlist'.
> 
> 
> Thanks,
> 
> 
> Nick
> 
> 
> 
> > Hi Folks,
> >
> > I've recently been evaluating SqlRelay and have been impressed by what
> > it's been able to do for my problem. Thanks for making it available!
> >
> > What's going on?
> > ----------------
> >
> > Before deploying it, I ran a soak test to investigate it's ability to
> > handle sustained load. The actual soak test went very well, but I did
> > encounter one problem in this situation:
> >
> >   * SqlRelay is configured to have between 50 and 100 client connections
> >   * SqlRelay starts up fine
> >   * Load test runs successfully and completes OK
> >   * SqlRelay ramps down the number of open client connections to 0
> >   * A number of hours pass...
> >   * Subsequent accesses to SqlRelay fail with the following error message:
> >
> > Unknown prepared statement handler (322816) given to mysql_stmt_close
> >
> > This appears to be repeatable, and only occurs after the SqlRelay process
> > has been idle for some time.
> >
> > Has anyone experienced this, and did you manage to fix it?
> >
> >
> > Thanks in advance for your help,
> >
> >
> > Nick
> >
> >
> > ============
> > System setup
> > ============
> >
> > Source and OS versions
> > ----------------------
> >
> >   Sql Relay version : 0.40, built from source
> >   Rudiments version : 0.32, built from source
> >
> >   SqlRelay host OS version : Redhat Enterprise Linux 4
> >
> >   Database : MySQL enterprise 5.0.48-enterprise-gpl-log
> >   Database host OS version : Redhat linux 5
> >
> >   Perl version : 5.8.8, 64 bit compile
> >
> > Config file setup
> > -----------------
> >
> >   <?xml version="1.0"?>
> >   <!DOCTYPE instances SYSTEM "sqlrelay.dtd">
> >   <instances>
> >
> >   <!--
> >   This is some junk to make SQLrelay parse its XML correctly.
> >   Without this, one part of the system doesn't start up properly.
> >
> >   It isn't actually used.
> >   -->
> >
> >   <instance id="dummy" port="12009" socket="/tmp/sybase-dummy.socket"
> >   dbase="freetds" connections="5" maxconnections="20" maxqueuelength="5"
> >   growby="1" ttl="60" endofsession="commit" sessiontimeout="600"
> >   runasuser="studma" runasgroup="users" cursors="10" authtier="listener"
> >   handoff="pass" deniedips="" allowedips="" debug="none"
> >   maxquerysize="65536" maxstringbindvaluelength="4000"
> >   maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1"
> >   listenertimeout="0" reloginatstart="false">
> >   <users>
> >   <user user="user" password="pw"/>
> >   </users>
> >   <connections>
> >   <connection connectionid="sybase-ba1"
> >   string="user=sa;password=;server=SYBTEST;db=ba31;charset=iso_1;"
> >   metric="1" behindloadbalancer="no"/>
> >   </connections>
> >   </instance>
> >
> >   <!-- This is the configuration which I actually use -->
> >
> >   <instance id="mysqltest" port="9000" socket="/tmp/mysqltest.socket"
> >    dbase="mysql" connections="50" maxconnections="100" maxqueuelength="0"
> >    growby="5" ttl="60" endofsession="commit" sessiontimeout="600"
> >    runasuser="npavey" runasgroup="design" cursors="25" authtier="listener"
> >    handoff="pass" idleclientimeout="10" maxlisteners="100"
> >    listenertimeout="120">
> >
> >    <users>
> >       <user user="mysqltest" password="mysqltest"/>
> >    </users>
> >
> >    <connections>
> >       <connection connectionid="mysqltest" string="user=redbase_ad
> > password=nezjJGDG;db=trs_benchmarking;host=
> > bd-midir005;port=3306" metric="1"/>
> >    </connections>
> >
> >    </instance>
> >
> >    </instances>
> >
> >
> > Command line used
> > -----------------
> >
> >   sqlr-start -id mysqltest -config <absolute path
> > root>/sqlrelay-0.40/config_file/conf.xml
> >
> >
> > client side code being issued
> > ------------------------------
> >
> > use SQLRelay::Connection;
> > use SQLRelay::Cursor;
> >
> > my $sqlr_con =
> > SQLRelay::Connection->new($host,$port,"",$user,$password,0,1);
> > my $sqlr_cur = SQLRelay::Cursor->new($sqlr_con);
> >
> > my $sql = "SELECT MIN(test_id) FROM test_table";
> > my $start_tid = select_scalar($sqlr_cur, $dat_file, $sql);
> >
> >
> > sub select_scalar {
> >   my ($sqlr_cur, $dat_file, $sql) = @_;
> >
> >   my $limit = 5;
> >   my $count = 0;
> >
> >   for ($count; $count < $limit; $count++) {
> >     my $rtn = $sqlr_cur->sendQuery($sql);
> >
> >     if ($rtn == 0) {
> >       my $err_msg = $sqlr_cur->errorMessage();
> >
> >       # These errors are generally caused by too much
> >       # traffic at the SQLrelay. They should be recoverable
> >       # provided the traffic doesn't stay high for very long periods.
> >       SWITCH : {
> >
> > 	if ($err_msg =~ /Too many listeners/ ) {
> > 	  print "\nEncountered 'too many listeners' error - backing off and
> > retrying\n";
> > 	  last SWITCH;
> > 	}
> >
> > 	if ($err_msg =~ /Failed to authenticate/ ) {
> > 	  print "\nEncountered 'failed to authenticate' error - backing off and
> > retrying\n";
> > 	  last SWITCH;
> > 	}
> >
> > 	# If we didn't match the error, then throw a fatal error.
> > 	write_cx_error($sqlr_cur->errorMessage(), $dat_file);
> >
> >       } # end of SWITCH
> >
> >       my $delay = int (rand (15) );
> >
> >       print "  current retry : $count\n";
> >       print "  retry limit : $limit\n";
> >       print "  backoff length : $delay\n\n";
> >
> >       sleep $delay;
> >
> >     } else {
> >
> >       # We got a success code, so we don't need to repeat the query
> >       # drop out of the FOR loop
> >       last;
> >     }
> >
> >   } # end of FOR loop
> >
> >   if ($count == $limit) {
> >     my $msg = "Retry limit reached.\n";
> >     $msg .= "$sql\n";
> >
> >     die "$msg\n";
> >   }
> >
> >   my $data_val = $sqlr_cur->getField(0,0);
> >
> >   return $data_val;
> > }
> >
> > Investigation so far
> > --------------------
> >
> > I did check whether the system is still running. I checked the contents of
> > the 'pids' directory and things appear to be OK. The listener, scalar,
> > cachemanager and connection processes are still running.
> >
> > I also ran 'strace' on the listener when I ran the SQL which caused the
> > problem:
> >
> >   strace -p 5788
> >   Process 5788 attached - interrupt to quit
> >   select(8, [3 4 5 6 7], NULL, NULL, NULL) = 1 (in [6])
> >   accept(6, {sa_family=AF_INET, sin_port=htons(59954),
> >     sin_addr=inet_addr("165.204.20.21")}, [3668744855036624912]) = 58
> >   setsockopt(58, SOL_TCP, TCP_NODELAY, [4294967297], 4) = 0
> >   semop(2031623, 0x4, 5340896)            = 0
> >   clone(child_stack=0,
> >      flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD,
> >      child_tidptr=0x2a95c438b0) = 31717
> >   close(58)                               = 0
> >   select(8, [3 4 5 6 7], NULL, NULL, NULL) = ? ERESTARTNOHAND (To be
> > restarted)
> >   --- SIGCHLD (Child exited) @ 0 (0) ---
> >   wait4(-1, NULL, WNOHANG, NULL)          = 31717
> >   wait4(-1, NULL, WNOHANG, NULL)          = -1 ECHILD (No child processes)
> >   rt_sigreturn(0xffffffffffffffff)        = -1 EINTR (Interrupted system
> > call)
> >   select(8, [3 4 5 6 7], NULL, NULL, NULL <unfinished ...>
> >
> > And I also ran strace on the scaler:
> >
> >   strace -p 6877
> >   Process 6877 attached - interrupt to quit
> >   semop(2031623, 0x8, 5313712)            = 0
> >   semop(2031623, 0x8, 5313488)            = 0
> >   semop(2031623, 0x8, 5313552)            = 0
> >   semop(2031623, 0x8, 5313904)            = 0
> >   semop(2031623, 0x8, 5313712 <unfinished ...>
> >
> >
> >
> >
> >
> > ------------------------------------------------------------------------------
> > This SF.net email is sponsored by:
> > High Quality Requirements in a Collaborative Environment.
> > Download a free trial of Rational Requirements Composer Now!
> > http://p.sf.net/sfu/www-ibm-com
> > _______________________________________________
> > Sqlrelay-discussion mailing list
> > Sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> >
> 
> 
> 
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by:
> High Quality Requirements in a Collaborative Environment.
> Download a free trial of Rational Requirements Composer Now!
> http://p.sf.net/sfu/www-ibm-com
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 
> __________________________________________________
> D O T E A S Y - "Join the web hosting revolution!"
>              http://www.doteasy.com

__________________________________________________
D O T E A S Y - "Join the web hosting revolution!"
             http://www.doteasy.com