sqlrelay-discussion Mailing List for SQL Relay (Page 20)
Brought to you by:
mused
Menu
▾
▴
sqlrelay-discussion — Discussion of topics related to SQL Relay
You can subscribe to this list here.
| 2005 |
Jan
|
Feb
(20) |
Mar
(27) |
Apr
(17) |
May
(32) |
Jun
(45) |
Jul
(49) |
Aug
(68) |
Sep
(44) |
Oct
(29) |
Nov
(64) |
Dec
(25) |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2006 |
Jan
(61) |
Feb
(22) |
Mar
(25) |
Apr
(31) |
May
(18) |
Jun
(28) |
Jul
(19) |
Aug
(16) |
Sep
(8) |
Oct
(17) |
Nov
(32) |
Dec
(4) |
| 2007 |
Jan
(20) |
Feb
(25) |
Mar
(5) |
Apr
(12) |
May
(11) |
Jun
(18) |
Jul
(16) |
Aug
(22) |
Sep
(37) |
Oct
(20) |
Nov
(11) |
Dec
(2) |
| 2008 |
Jan
(11) |
Feb
(33) |
Mar
(12) |
Apr
(18) |
May
(22) |
Jun
(31) |
Jul
(23) |
Aug
(6) |
Sep
|
Oct
(10) |
Nov
(22) |
Dec
|
| 2009 |
Jan
(12) |
Feb
(8) |
Mar
(11) |
Apr
(20) |
May
(18) |
Jun
(7) |
Jul
(27) |
Aug
(2) |
Sep
(10) |
Oct
(5) |
Nov
(2) |
Dec
(1) |
| 2010 |
Jan
(11) |
Feb
(18) |
Mar
(10) |
Apr
(28) |
May
(28) |
Jun
|
Jul
(27) |
Aug
(9) |
Sep
(21) |
Oct
(2) |
Nov
(2) |
Dec
(11) |
| 2011 |
Jan
|
Feb
(2) |
Mar
(4) |
Apr
(2) |
May
(2) |
Jun
(44) |
Jul
(9) |
Aug
(2) |
Sep
(12) |
Oct
(7) |
Nov
(11) |
Dec
(7) |
| 2012 |
Jan
(5) |
Feb
|
Mar
(9) |
Apr
(9) |
May
(12) |
Jun
|
Jul
(13) |
Aug
(3) |
Sep
(3) |
Oct
(1) |
Nov
(1) |
Dec
(10) |
| 2013 |
Jan
(21) |
Feb
(3) |
Mar
(4) |
Apr
|
May
(3) |
Jun
(2) |
Jul
(3) |
Aug
(3) |
Sep
(3) |
Oct
|
Nov
|
Dec
(4) |
| 2014 |
Jan
(7) |
Feb
|
Mar
(1) |
Apr
|
May
(2) |
Jun
|
Jul
(4) |
Aug
(2) |
Sep
|
Oct
(1) |
Nov
|
Dec
|
| 2016 |
Jan
|
Feb
|
Mar
(1) |
Apr
|
May
(1) |
Jun
|
Jul
|
Aug
|
Sep
|
Oct
|
Nov
|
Dec
|
| 2017 |
Jan
|
Feb
(1) |
Mar
|
Apr
|
May
|
Jun
|
Jul
|
Aug
|
Sep
|
Oct
|
Nov
|
Dec
|
| 2018 |
Jan
|
Feb
|
Mar
|
Apr
|
May
|
Jun
(1) |
Jul
|
Aug
|
Sep
|
Oct
|
Nov
|
Dec
|
| 2021 |
Jan
|
Feb
|
Mar
|
Apr
(3) |
May
|
Jun
|
Jul
|
Aug
|
Sep
|
Oct
|
Nov
|
Dec
|
| 2022 |
Jan
|
Feb
|
Mar
|
Apr
|
May
|
Jun
(1) |
Jul
|
Aug
|
Sep
|
Oct
|
Nov
|
Dec
|
|
From: Unai R. <me...@u-...> - 2009-04-30 09:02:44
|
Dear All, We are currently developing an application using C# and SQL Server. The developers are using Microsoft Visual Studio 2005 as IDE. I have been trying to find out whether it would be possible to use SQLRelay within our system. It seems it is not, but I would like to make sure. >From my understanding, SQLRelay supports a number of programming languages and database abstraction layers, but I do not think C# is included. I thought of using the C++ API on our C# code as a possible approach, but I am a bit concerned about stability and performance with this approach. Does anyone have figured out a way of doing this? Thank you so much, unai |
|
From: Daniel S. <sql...@bo...> - 2009-04-29 17:31:35
|
All, I'm having an issue with sqlrelay 0.41 (I have not tried any earlier versions). When starting up sqlrelay, I get OCIEnvCreate() failed. I've tried the following to remedy the situation: 1. Installed with 10g libraries from local Oracle DB install (64Bit) 2. Installed with 10g instantclient libraries for 64bit 3. Installed with 10g instantclient libraries for 32bit 4. Tried removing code from oracle8connection.C to roll back to OCIInitialize instead of OCIEnvCreate, with similar results 5. Enabled debug to see what's going on. See output from my tests below. Any insight would be appreciated. Thanks, Daniel Smith [ ?0 dc1testlab112 root 3 !1509 &0 12:01:59 ] /opt/firstworks # sqlr-start -id oracletest Starting listener: sqlr-listener -id oracletest -config /opt/firstworks/etc/sqlrelay.conf -debug Debugging to: /opt/firstworks/var/sqlrelay/debug/sqlr-listener.8825 Starting 3 connections to oracletest : sqlr-connection-oracle8 -id oracletest -connectionid oracletest -config /opt/firstworks/etc/sqlrelay.conf -debug Debugging to: /opt/firstworks/var/sqlrelay/debug/sqlr-connection.8861 OCIEnvCreate() failed. error: Couldn't log into database. sqlr-connection-oracle8 failed to start. Thanks to MP3.com for sponsoring: Clustered/Replicated database support. Perl API. Thanks to FeedLounge for sponsoring: Query routing and filtering. [ ?1 dc1testlab112 root 3 !1510 &0 12:02:07 ] /opt/firstworks # cat var/sqlrelay/debug/sqlr-listener.8825 04/29/2009 12:02:06 CDT listener [8825] : creating shared memory and semaphores 04/29/2009 12:02:06 CDT listener [8825] : id filename: 04/29/2009 12:02:06 CDT listener [8825] : /opt/firstworks/var/sqlrelay/tmp/ipc/oracletest 04/29/2009 12:02:06 CDT listener [8825] : creating shared memory... 04/29/2009 12:02:06 CDT listener [8825] : creating semaphores... |
|
From: David M. <dav...@fi...> - 2009-04-29 15:46:29
|
I have a vague memory of fixing a bug like that with sybase a few
versions ago. I can't find it in the changelog though. You might want
to try a newer version of SQL Relay, it may just be fixed.
If the problem still exists in the current version, let me know and I'll
fix it.
Dave
dav...@fi...
On Fri, 2009-04-03 at 11:19 -0400, Alexandre Forget wrote:
> Hi,
>
> My problem is with the connection between sqlrelay and sybase, it is
> not automatically reconnected.
> To test this case I kill the connection in sybase with a command in sqlrsh:
>
> > select * from master..sysprocesses;
> > kill spid
>
> After this command I can no longer ping the database
> > ping;
> database is down
>
> This is the debug info I get from the sqlr-connection
> =====================
> done getting command
> ping
> unlinking /var/cache/sqlrelay/tmp/ipc/example-sybase1
> relogging in...
> closing cursors...
> 0
> 1
> 2
> 3
> 4
> done closing cursors...
> trying...
> initializing cursors...
> 0
> 1
> 2
> 3
> 4
> done initializing cursors
> done relogging in
> creating /var/cache/sqlrelay/tmp/ipc/example-sybase1
> getting command...
> =======================
>
> If I restart sqlrsh the database stay down if I am connected to the
> same sqlr-connection process. To me, it seem sqlrelay try to reconnect
> to the db but fail to get a working connexion
>
>
> If I restart sqlrelay width "sqlr-stop ;sqlr-start -id example" then
> all the connections are working.
>
> versions:
>
> sqlrelay: 0.39
> freetds: 0.82
> sybase: 11.0.3
>
> thanks
>
__________________________________________________
D O T E A S Y - "Join the web hosting revolution!"
http://www.doteasy.com
|
|
From: David M. <dav...@fi...> - 2009-04-29 15:43:02
|
Hello Nick, I'm not aware of a 100 character limit for paths. It's possible that somewhere up inside of glibc there is. Is it the socket that the listener is listening on that's giving you trouble? The default temp directory is /usr/local/firstworks/var/sqlrelay/tmp, but you can override the /usr/local/firstworks/var part of it using the -localstatedir option of sqlr-start. For example: sqlr-start -id myinstance -localstatedir /home/myinstance will cause the various temp files to be created under /home/myinstance/sqlrelay/tmp I don't remember whether the sqlrelay/tmp part will be created or whether you have to create it yourself though :) Dave dav...@fi... On Thu, 2009-04-16 at 17:58 -0400, ni...@sk... wrote: > Hi Dave, > > Thanks for the info. I haven't managed to run the tests yet, but I'll let > you know what I find. > > I've got another couple of questions... > > Possible string length issue? > ----------------------------- > > I know it seems unlikely, but there isn't a 100 character limit for paths > is there? > > I'm seeing the listener fail to start up when the path to the > .../tmp/sockets directory exceeds 99 chars (presumably there's a null > terminator). > > Unfortunately the error has scrolled off my screen, but it was along the > lines of: > > 'could not listen on unix socket : trscxpool-handoff' > > followed by a gripe about a double free operation from glibc. > > NB - 'trscxpool' is the ID from the configuration file. > > When I looked into the tmp/sockets directory I saw the file was present, > but truncated so it looked like this: > > trscxpool-= > > instead of : > > trscxpool-handoff= > > I then moved to a shorter path and recompiled and things went fine. The > file was created and the tool worked just fine. > > I've taken a look through the code and I can't see an obvious string > length issue (it's great to see that you've put a lot of effort into > avoiding the coredump magnet of string.h :-) ) but I might be missing > something. > > Parameterizing the /tmp directory > --------------------------------- > > I can see in the code that it appears to be possible to parameterize where > the /tmp directory lives. > > How would I do that, assuming that I'm using the sqlr-start utility? > > > Thanks again for your help, > > > > Nick > > > > > > SQL Relay does have a list of error codes, where if a query returns that > > error, then SQL Relay says "oh, the connection must be down". Then it > > reconnects and runs the query again. > > > > Every now and then, when a new version of the database comes out, there > > are new error conditions that SQL Relay isn't aware of. This could be > > one of those. > > > > If you can figure out exactly how to reproduce it, I can fix it and get > > you a patch. > > > > Dave > > > > On Thu, 2009-04-16 at 11:09 -0400, ni...@sk... wrote: > >> Hi Folks, > >> > >> I have an idea what the problem may be... The database engine itself is > >> disconnecting the connections to SqlRelay. > >> > >> MySQL has a connection life timeout (I think it's 4 hours, by default). > >> Once that time expires the connections are killed from the server side. > >> > >> Do you know if SqlRelay knows about behaviour and tries to re-animate > >> connections? > >> > >> I'll try to do a few tests and see if I can reproduce the issue by > >> tweaking the timeout on the server side. > >> > >> Thanks, > >> > >> > >> Nick > >> > >> > >> > >> > We had this problem (although different error message as we were > >> talking > >> > to DB2) and the Cisco FWSM timing out TCP sessions was the culprit. I > >> > think we set something up on the OS to twiddle the TCP keepalives so > >> that > >> > the FWSM saw enough traffic to keep the things going. If this sounds > >> like > >> > what might be causing your problem I can get the firewall and OS folks > >> to > >> > give me the details of exactly what they did... > >> > > >> > -----Original Message----- > >> > From: David Muse [mailto:dav...@fi...] > >> > Sent: Tuesday, April 14, 2009 1:06 PM > >> > To: Discussion of topics related to SQL Relay > >> > Subject: Re: [Sqlrelay-discussion] Unknown prepared statement handler? > >> > > >> > 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... > >> > > >> > > >> > ------------------------------------------------------------------------------ > >> > 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 > >> > > >> > >> > >> > >> ------------------------------------------------------------------------------ > >> Stay on top of everything new and different, both inside and > >> around Java (TM) technology - register by April 22, and save > >> $200 on the JavaOne (SM) conference, June 2-5, 2009, San Francisco. > >> 300 plus technical and hands-on sessions. Register today. > >> Use priority code J9JMT32. http://p.sf.net/sfu/p > >> _______________________________________________ > >> 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 > > > > ------------------------------------------------------------------------------ > > Stay on top of everything new and different, both inside and > > around Java (TM) technology - register by April 22, and save > > $200 on the JavaOne (SM) conference, June 2-5, 2009, San Francisco. > > 300 plus technical and hands-on sessions. Register today. > > Use priority code J9JMT32. http://p.sf.net/sfu/p > > _______________________________________________ > > Sqlrelay-discussion mailing list > > Sql...@li... > > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion > > > > > > ------------------------------------------------------------------------------ > Stay on top of everything new and different, both inside and > around Java (TM) technology - register by April 22, and save > $200 on the JavaOne (SM) conference, June 2-5, 2009, San Francisco. > 300 plus technical and hands-on sessions. Register today. > Use priority code J9JMT32. http://p.sf.net/sfu/p > _______________________________________________ > 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 |
|
From: David M. <dav...@fi...> - 2009-04-29 14:09:37
|
Hi Renat, I never figured out an exact cause for this. I've seen it before in my life, but I can't remember where. I know that it occurs if you're out of memory, or if there's an odd mismatch of libraries, usually if code is compiled against libstdc++ version X but then at runtime gets linked against libstdc++ version X-1. I'd look through the gcc toolchain on your solaris machine and see what libstdc++ it came with. You may find that there's a different version of libstdc++ in /usr/lib, /lib, /usr/local/lib, etc. In that case, the code would be getting compiled against the one up inside of the toolchain, but linked against a different one. I think I've also seen it where more than 1 version of SQL Relay or Rudiments is installed and the code gets compiled against 1 version but then linked against another at runtime. Check to see if you have more than 1 version of either of those on the system as well. Dave dav...@fi... On Fri, 2009-04-24 at 17:22 +0400, Renat Sabitov wrote: > Chris пишет: > > David Muse wrote: > >> Weird. I think that error usually means that a C++ "new" operator > >> failed. I think it usually happens if you compile against 1 version of > >> a library but then run against a different, incompatible version. > > > > Could it be a mix of tools causing it? gcc for compiling, gnu make, but > > the solaris ld tool. > > Have you solved this problem? We've got this error on solaris but > haven't fix it yet. > > -- > Renat Sabitov e-mail: sr...@st... > Stack Soft jid: sr...@ja... > > ------------------------------------------------------------------------------ > Crystal Reports - New Free Runtime and 30 Day Trial > Check out the new simplified licensign option that enables unlimited > royalty-free distribution of the report engine for externally facing > server and web deployment. > http://p.sf.net/sfu/businessobjects > _______________________________________________ > Sqlrelay-discussion mailing list > Sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion > & ) n,)bxm ך'( __________________________________________________ D O T E A S Y - "Join the web hosting revolution!" http://www.doteasy.com |
|
From: David M. <dav...@fi...> - 2009-04-29 14:01:14
|
Send me your sqlrelay.conf file and the sqlrsh command that you're using and I should be able to figure out what's going on. Dave dav...@fi... On Mon, 2009-04-27 at 22:03 +0200, Christoph Klein wrote: > Hello all, > > I've just installed the 0.41 version of SQLRelay and am not able to > get it up and running. I'm trying to connect to an Oracle 10g db with > no luck. I always get the same error : "Authentication error". It > doesn't matter if I connect via DBI or with sqlrsh. > > The debug messages say : > > database-based authentication failed: invalid user/password > > I don't think that this error message means the Oracle db right, I > would expect an ORA error then... > > I'm lost because the username and password are correct, everything's > set up and running. I can connect to oracle via sqlplus and there > shouldn't be any problems. > > I hope anyone of you is able to help me out here... > > Cheers, > > Chris > > ------------------------------------------------------------------------------ > Crystal Reports - New Free Runtime and 30 Day Trial > Check out the new simplified licensign option that enables unlimited > royalty-free distribution of the report engine for externally facing > server and web deployment. > http://p.sf.net/sfu/businessobjects > _______________________________________________ > 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 |
|
From: Christoph K. <chr...@ai...> - 2009-04-27 20:25:24
|
Hello all, I've just installed the 0.41 version of SQLRelay and am not able to get it up and running. I'm trying to connect to an Oracle 10g db with no luck. I always get the same error : "Authentication error". It doesn't matter if I connect via DBI or with sqlrsh. The debug messages say : database-based authentication failed: invalid user/password I don't think that this error message means the Oracle db right, I would expect an ORA error then... I'm lost because the username and password are correct, everything's set up and running. I can connect to oracle via sqlplus and there shouldn't be any problems. I hope anyone of you is able to help me out here... Cheers, Chris |
|
From: Luis M. C. C. <lmc...@ho...> - 2009-04-24 15:51:46
|
I install rudiments-0.32 and sqlrelay-0.39.4 in a Fedora system. I use a C++ program to connect to SQL Relay and SQL Relay to connect to MySql. When I run the queries everything works fine for a while. However after several minutes and hundreds of successful queries the C++ program waits indefinitely when retrieving data from the database. The SQL listener and SQL Connections process are running well, because I check it with the "ps -A | grep sqlr" command But what I found interesting is the log info, because this is what I see in the sqlr.connection.46542 logfile. (the same sequence is repeated over and over again, meaning that the connection is in an infinite loop. trying... initializing cursors... 0 1 2 3 4 done initializing cursors done relogging in creating /usr/local/firstworks/var/sqlrelay/tmp/ipc/jtrekdb-db processing query... preparing/executing... commit or rollback check... done with commit or rollback check processing query failed done processing query handling error... returning error... done returning error database is down... unlinking /usr/local/firstworks/var/sqlrelay/tmp/ipc/jtrekdb-db relogging in... closing cursors... 0 1 2 3 4 done closing cursors... trying... initializing cursors... Also I found that the same error was already found in https://sourceforge.net/mailarchive/forum.php?thread_name=461289CD.1070304%40linfield.edu&forum_name=sqlrelay-discussion I reproduce the text here because I don't know if the link will work. Can I get in contact with this people ?? I'm afraid that I'm definitely a newbie using sqlrelay, so I'm not sure what information to provide. I built and installed sqlrelay on a RedHat AS3 system running apache, php and perl. The database I want to provide connection pooling for is a mysql database running on a different machine. I can start sqlrelay using the linux service command (service sqlrelay start) and all is well for a while. Database accesses work both in php and perl. Then suddenly (and I don't know what causes the "suddenly"), both php and perl apps hang while trying to retrieve data from the database. Sqlrelay has not stopped running and so I do a restart (service sqlrelay restart), and everything is working again. Any ideas how to figure out the cause of the hanging on the database access? This is the connection string from the sqlrelay.conf file: <connections> <connection connectionid="db1" string="user=dbuser;password=secret;db=webapps;host=dbhost.linfield.edu;port=3306;fakebinds=no;" metric="1" behindloadbalancer="1"/> </connections> Thanks, Rob -- Rob Tanner UNIX Services Manager Linfield College, McMinnville OR and here is the reply.... Sounds like the sqlrelay server-side components are crashing or hanging. Whenever it hangs up, take a look and see if any sqlr-connection-mysql processes are running. If not, then they may have crashed. If so, then they may have hung. You can turn on debug in the sqlrelay.conf file and take a look at the logs. Recently, I updated SQL Relay to use the mysql statement API rather than the standard mysql API. There could be a bug with that. The debug logs would be really helpful in figuring out what's going on. Dave david.muse@fi... _________________________________________________________________ Get 5 GB of storage with Windows Live Hotmail. http://windowslive.com/Explore/Hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_5gb_112008 |
|
From: Renat S. <sr...@st...> - 2009-04-24 13:22:53
|
Chris пишет: > David Muse wrote: >> Weird. I think that error usually means that a C++ "new" operator >> failed. I think it usually happens if you compile against 1 version of >> a library but then run against a different, incompatible version. > > Could it be a mix of tools causing it? gcc for compiling, gnu make, but > the solaris ld tool. Have you solved this problem? We've got this error on solaris but haven't fix it yet. -- Renat Sabitov e-mail: sr...@st... Stack Soft jid: sr...@ja... |
|
From: <ni...@sk...> - 2009-04-16 21:58:30
|
Hi Dave, Thanks for the info. I haven't managed to run the tests yet, but I'll let you know what I find. I've got another couple of questions... Possible string length issue? ----------------------------- I know it seems unlikely, but there isn't a 100 character limit for paths is there? I'm seeing the listener fail to start up when the path to the .../tmp/sockets directory exceeds 99 chars (presumably there's a null terminator). Unfortunately the error has scrolled off my screen, but it was along the lines of: 'could not listen on unix socket : trscxpool-handoff' followed by a gripe about a double free operation from glibc. NB - 'trscxpool' is the ID from the configuration file. When I looked into the tmp/sockets directory I saw the file was present, but truncated so it looked like this: trscxpool-= instead of : trscxpool-handoff= I then moved to a shorter path and recompiled and things went fine. The file was created and the tool worked just fine. I've taken a look through the code and I can't see an obvious string length issue (it's great to see that you've put a lot of effort into avoiding the coredump magnet of string.h :-) ) but I might be missing something. Parameterizing the /tmp directory --------------------------------- I can see in the code that it appears to be possible to parameterize where the /tmp directory lives. How would I do that, assuming that I'm using the sqlr-start utility? Thanks again for your help, Nick > SQL Relay does have a list of error codes, where if a query returns that > error, then SQL Relay says "oh, the connection must be down". Then it > reconnects and runs the query again. > > Every now and then, when a new version of the database comes out, there > are new error conditions that SQL Relay isn't aware of. This could be > one of those. > > If you can figure out exactly how to reproduce it, I can fix it and get > you a patch. > > Dave > > On Thu, 2009-04-16 at 11:09 -0400, ni...@sk... wrote: >> Hi Folks, >> >> I have an idea what the problem may be... The database engine itself is >> disconnecting the connections to SqlRelay. >> >> MySQL has a connection life timeout (I think it's 4 hours, by default). >> Once that time expires the connections are killed from the server side. >> >> Do you know if SqlRelay knows about behaviour and tries to re-animate >> connections? >> >> I'll try to do a few tests and see if I can reproduce the issue by >> tweaking the timeout on the server side. >> >> Thanks, >> >> >> Nick >> >> >> >> > We had this problem (although different error message as we were >> talking >> > to DB2) and the Cisco FWSM timing out TCP sessions was the culprit. I >> > think we set something up on the OS to twiddle the TCP keepalives so >> that >> > the FWSM saw enough traffic to keep the things going. If this sounds >> like >> > what might be causing your problem I can get the firewall and OS folks >> to >> > give me the details of exactly what they did... >> > >> > -----Original Message----- >> > From: David Muse [mailto:dav...@fi...] >> > Sent: Tuesday, April 14, 2009 1:06 PM >> > To: Discussion of topics related to SQL Relay >> > Subject: Re: [Sqlrelay-discussion] Unknown prepared statement handler? >> > >> > 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... >> > >> > >> > ------------------------------------------------------------------------------ >> > 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 >> > >> >> >> >> ------------------------------------------------------------------------------ >> Stay on top of everything new and different, both inside and >> around Java (TM) technology - register by April 22, and save >> $200 on the JavaOne (SM) conference, June 2-5, 2009, San Francisco. >> 300 plus technical and hands-on sessions. Register today. >> Use priority code J9JMT32. http://p.sf.net/sfu/p >> _______________________________________________ >> 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 > > ------------------------------------------------------------------------------ > Stay on top of everything new and different, both inside and > around Java (TM) technology - register by April 22, and save > $200 on the JavaOne (SM) conference, June 2-5, 2009, San Francisco. > 300 plus technical and hands-on sessions. Register today. > Use priority code J9JMT32. http://p.sf.net/sfu/p > _______________________________________________ > Sqlrelay-discussion mailing list > Sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion > |
|
From: David M. <dav...@fi...> - 2009-04-16 15:26:31
|
SQL Relay does have a list of error codes, where if a query returns that error, then SQL Relay says "oh, the connection must be down". Then it reconnects and runs the query again. Every now and then, when a new version of the database comes out, there are new error conditions that SQL Relay isn't aware of. This could be one of those. If you can figure out exactly how to reproduce it, I can fix it and get you a patch. Dave On Thu, 2009-04-16 at 11:09 -0400, ni...@sk... wrote: > Hi Folks, > > I have an idea what the problem may be... The database engine itself is > disconnecting the connections to SqlRelay. > > MySQL has a connection life timeout (I think it's 4 hours, by default). > Once that time expires the connections are killed from the server side. > > Do you know if SqlRelay knows about behaviour and tries to re-animate > connections? > > I'll try to do a few tests and see if I can reproduce the issue by > tweaking the timeout on the server side. > > Thanks, > > > Nick > > > > > We had this problem (although different error message as we were talking > > to DB2) and the Cisco FWSM timing out TCP sessions was the culprit. I > > think we set something up on the OS to twiddle the TCP keepalives so that > > the FWSM saw enough traffic to keep the things going. If this sounds like > > what might be causing your problem I can get the firewall and OS folks to > > give me the details of exactly what they did... > > > > -----Original Message----- > > From: David Muse [mailto:dav...@fi...] > > Sent: Tuesday, April 14, 2009 1:06 PM > > To: Discussion of topics related to SQL Relay > > Subject: Re: [Sqlrelay-discussion] Unknown prepared statement handler? > > > > 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... > > > > > > ------------------------------------------------------------------------------ > > 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 > > > > > > ------------------------------------------------------------------------------ > Stay on top of everything new and different, both inside and > around Java (TM) technology - register by April 22, and save > $200 on the JavaOne (SM) conference, June 2-5, 2009, San Francisco. > 300 plus technical and hands-on sessions. Register today. > Use priority code J9JMT32. http://p.sf.net/sfu/p > _______________________________________________ > 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 |
|
From: <ni...@sk...> - 2009-04-16 15:09:44
|
Hi Folks, I have an idea what the problem may be... The database engine itself is disconnecting the connections to SqlRelay. MySQL has a connection life timeout (I think it's 4 hours, by default). Once that time expires the connections are killed from the server side. Do you know if SqlRelay knows about behaviour and tries to re-animate connections? I'll try to do a few tests and see if I can reproduce the issue by tweaking the timeout on the server side. Thanks, Nick > We had this problem (although different error message as we were talking > to DB2) and the Cisco FWSM timing out TCP sessions was the culprit. I > think we set something up on the OS to twiddle the TCP keepalives so that > the FWSM saw enough traffic to keep the things going. If this sounds like > what might be causing your problem I can get the firewall and OS folks to > give me the details of exactly what they did... > > -----Original Message----- > From: David Muse [mailto:dav...@fi...] > Sent: Tuesday, April 14, 2009 1:06 PM > To: Discussion of topics related to SQL Relay > Subject: Re: [Sqlrelay-discussion] Unknown prepared statement handler? > > 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... > > > ------------------------------------------------------------------------------ > 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 > |
|
From: Lawrence, G. <gla...@uc...> - 2009-04-14 20:27:49
|
We had this problem (although different error message as we were talking to DB2) and the Cisco FWSM timing out TCP sessions was the culprit. I think we set something up on the OS to twiddle the TCP keepalives so that the FWSM saw enough traffic to keep the things going. If this sounds like what might be causing your problem I can get the firewall and OS folks to give me the details of exactly what they did... -----Original Message----- From: David Muse [mailto:dav...@fi...] Sent: Tuesday, April 14, 2009 1:06 PM To: Discussion of topics related to SQL Relay Subject: Re: [Sqlrelay-discussion] Unknown prepared statement handler? 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... |
|
From: David M. <dav...@fi...> - 2009-04-14 20:04:33
|
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 |
|
From: <ni...@sk...> - 2009-04-14 16:17:42
|
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
>
|
|
From: <ni...@sk...> - 2009-04-14 14:41:51
|
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 ...>
|
|
From: David M. <dav...@fi...> - 2009-04-13 05:36:59
|
Hello all,
SQL Relay 0.41 is out. Short changelog for this one:
* added configure test for ruby.h
* added a couple of new functions to the mysql drop-in library
* updated mysql drop-in lib docs explaining how to use with PHP
* added documentation for timequeries params
* if SQLConnectW isn't supported, ODBC driver uses non-unicode calls
* added maxsessioncount parameter and connections use it to override
ttl and bleed off
* applied Renat Sabitov's output bind patch
* added mysql last_insert_id() note to FAQ
Some folks have reported an issue with running out of cursors. I have
been able to reproduce this, but have not yet fixed the problem. There
should be a follow-up soon with a fix for that.
Get this release at: http://sqlrelay.sourceforge.net/download.html
David Muse
dav...@fi...
__________________________________________________
D O T E A S Y - "Join the web hosting revolution!"
http://www.doteasy.com
|
|
From: Alexandre F. <lex...@gm...> - 2009-04-03 15:19:28
|
Hi, My problem is with the connection between sqlrelay and sybase, it is not automatically reconnected. To test this case I kill the connection in sybase with a command in sqlrsh: > select * from master..sysprocesses; > kill spid After this command I can no longer ping the database > ping; database is down This is the debug info I get from the sqlr-connection ===================== done getting command ping unlinking /var/cache/sqlrelay/tmp/ipc/example-sybase1 relogging in... closing cursors... 0 1 2 3 4 done closing cursors... trying... initializing cursors... 0 1 2 3 4 done initializing cursors done relogging in creating /var/cache/sqlrelay/tmp/ipc/example-sybase1 getting command... ======================= If I restart sqlrsh the database stay down if I am connected to the same sqlr-connection process. To me, it seem sqlrelay try to reconnect to the db but fail to get a working connexion If I restart sqlrelay width "sqlr-stop ;sqlr-start -id example" then all the connections are working. versions: sqlrelay: 0.39 freetds: 0.82 sybase: 11.0.3 thanks -- Alexandre Forget |
|
From: Lawrence, G. <gla...@uc...> - 2009-03-31 03:39:38
|
Bad form to reply to oneself... But, I've figured out some more information.
It seems the issue is that the perl driver is changing the SQL I pass in.
In SQLRelay.pm there is this line:
$statement =~ s/\?/":" . ++$count/eg;
which turns every ? in statement into a :#... this seems really undesirable because a '?' shouldn't be interpreted as a bind parameter... but this code does do that and changes things.
It seems there is this:
$sth->STORE('NUM_OF_PARAMS', $count);
and then later this:
$sth->STORE('NUM_OF_PARAMS',$cursor->countBindVariables());
So, i'm wondering if count is old buggy code that should be removed. I'm tempted to pull this stuff out and see if it makes things work. Any thoughts?
Gabe
________________________________________
From: Lawrence, Gabriel
Sent: Monday, March 30, 2009 1:58 PM
To: sql...@li...
Subject: SQLRelay, bind_params and DB2
Howdy,
I'm testing out sql relay with a db2 install. I'm doing about the lamest bit of code possible just to make sure it's all going to work.
Code here:
#!/usr/bin/env perl
use DBI;
my $dbh=DBI->connect("DBI:SQLRelay:host=localhost;port=9000;socket=;"," XXXXXXXXXXXX","XXXXXXXXXXXX");
my $sth=$dbh->prepare("select * from db2actq.zip_codes where state = ?");
$sth->bind_param(1,"CA");
$sth->execute();
while (@data=$sth->fetchrow_array()) {
foreach $col (@data) {
print "\"$col\",";
}
print "\n";
}
$dbh->disconnect;
And I get an error result:
DBD::SQLRelay::st execute failed: [IBM][CLI Driver][DB2/SUN] SQL0312N The host variable "1" is used in a dynamic SQL statement, a view definition, or a trigger definition. SQLSTATE=42618
Which seems obvious enough... but, and documentation for SQL0312N has a simple enough resolution:
Use parameter markers (?) instead of host variables for dynamic SQL statements. Do not use host variables and parameter markers in view or trigger definitions.
Except... that’s what I thought I was doing... Am I missing something?
Thanks,
Gabe
------------------------------------
Gabriel Lawrence
Director, IT Security
UC San Diego
|
|
From: Lawrence, G. <gla...@uc...> - 2009-03-30 21:12:17
|
Howdy,
I'm testing out sql relay with a db2 install. I'm doing about the lamest bit of code possible just to make sure it's all going to work.
Code here:
#!/usr/bin/env perl
use DBI;
my $dbh=DBI->connect("DBI:SQLRelay:host=localhost;port=9000;socket=;"," XXXXXXXXXXXX","XXXXXXXXXXXX");
my $sth=$dbh->prepare("select * from db2actq.zip_codes where state = ?");
$sth->bind_param(1,"CA");
$sth->execute();
while (@data=$sth->fetchrow_array()) {
foreach $col (@data) {
print "\"$col\",";
}
print "\n";
}
$dbh->disconnect;
And I get an error result:
DBD::SQLRelay::st execute failed: [IBM][CLI Driver][DB2/SUN] SQL0312N The host variable "1" is used in a dynamic SQL statement, a view definition, or a trigger definition. SQLSTATE=42618
Which seems obvious enough... but, and documentation for SQL0312N has a simple enough resolution:
Use parameter markers (?) instead of host variables for dynamic SQL statements. Do not use host variables and parameter markers in view or trigger definitions.
Except... that's what I thought I was doing... Am I missing something?
Thanks,
Gabe
------------------------------------
Gabriel Lawrence
Director, IT Security
UC San Diego
|
|
From: David M. <dav...@fi...> - 2009-03-27 14:05:29
|
Posting them to this mailing list is the best way. Dave dav...@fi... On Fri, 2009-03-27 at 09:22 +0000, Peter Clapham wrote: > Hello David, > > If the tracker is outdated what is the preferred method for reporting > bugs etc ? > > Many thanks > Pete > > Hello Renat, > > > > I'm pretty much the only SQL Relay developer. Others have come and gone > > over the years, and lots of people have submitted patches, but I'm the > > primary developer. Admittedly development has slowed in recent years. > > For a while I focused on commercial support, and recently on other > > pursuits, but development does continue. > > > > If you have a patch, please send it to me directly, I'll review it and > > if it looks good, I'll include it in the next release. > > > > The tracker is outdated. I need to just disable it for now. > > > > David Muse > > dav...@fi... > > > > On Thu, 2009-03-26 at 15:35 +0300, Renat Sabitov wrote: > > > >> Hello. > >> > >> I have some questions about sqlrelay development process. How it is > >> organized? How could developers interested in some features to be > >> included in sqlrelay offer patches? > >> I found tracker on sourceforge > >> (http://sourceforge.net/tracker/?group_id=10238), but is it used by > >> David Muse? There is only open issues and none of them is closed. > >> > >> If I found a bug and have a patch, what should I do to get it fixed in > >> next release? > >> > >> I'm really looking forward for answers. > >> > >> > > > > > > __________________________________________________ > > D O T E A S Y - "Join the web hosting revolution!" > > http://www.doteasy.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 |
|
From: Peter C. <pc...@sa...> - 2009-03-27 09:22:42
|
Hello David, If the tracker is outdated what is the preferred method for reporting bugs etc ? Many thanks Pete > Hello Renat, > > I'm pretty much the only SQL Relay developer. Others have come and gone > over the years, and lots of people have submitted patches, but I'm the > primary developer. Admittedly development has slowed in recent years. > For a while I focused on commercial support, and recently on other > pursuits, but development does continue. > > If you have a patch, please send it to me directly, I'll review it and > if it looks good, I'll include it in the next release. > > The tracker is outdated. I need to just disable it for now. > > David Muse > dav...@fi... > > On Thu, 2009-03-26 at 15:35 +0300, Renat Sabitov wrote: > >> Hello. >> >> I have some questions about sqlrelay development process. How it is >> organized? How could developers interested in some features to be >> included in sqlrelay offer patches? >> I found tracker on sourceforge >> (http://sourceforge.net/tracker/?group_id=10238), but is it used by >> David Muse? There is only open issues and none of them is closed. >> >> If I found a bug and have a patch, what should I do to get it fixed in >> next release? >> >> I'm really looking forward for answers. >> >> > > > __________________________________________________ > D O T E A S Y - "Join the web hosting revolution!" > http://www.doteasy.com > > ------------------------------------------------------------------------------ > _______________________________________________ > Sqlrelay-discussion mailing list > Sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion > -- The Wellcome Trust Sanger Institute is operated by Genome Research Limited, a charity registered in England with number 1021457 and a company registered in England with number 2742969, whose registered office is 215 Euston Road, London, NW1 2BE. |
|
From: David M. <dav...@fi...> - 2009-03-27 03:23:35
|
Hello Renat, I'm pretty much the only SQL Relay developer. Others have come and gone over the years, and lots of people have submitted patches, but I'm the primary developer. Admittedly development has slowed in recent years. For a while I focused on commercial support, and recently on other pursuits, but development does continue. If you have a patch, please send it to me directly, I'll review it and if it looks good, I'll include it in the next release. The tracker is outdated. I need to just disable it for now. David Muse dav...@fi... On Thu, 2009-03-26 at 15:35 +0300, Renat Sabitov wrote: > Hello. > > I have some questions about sqlrelay development process. How it is > organized? How could developers interested in some features to be > included in sqlrelay offer patches? > I found tracker on sourceforge > (http://sourceforge.net/tracker/?group_id=10238), but is it used by > David Muse? There is only open issues and none of them is closed. > > If I found a bug and have a patch, what should I do to get it fixed in > next release? > > I'm really looking forward for answers. > __________________________________________________ D O T E A S Y - "Join the web hosting revolution!" http://www.doteasy.com |
|
From: Renat S. <sr...@st...> - 2009-03-26 13:07:41
|
Hello. I have some questions about sqlrelay development process. How it is organized? How could developers interested in some features to be included in sqlrelay offer patches? I found tracker on sourceforge (http://sourceforge.net/tracker/?group_id=10238), but is it used by David Muse? There is only open issues and none of them is closed. If I found a bug and have a patch, what should I do to get it fixed in next release? I'm really looking forward for answers. -- Renat Sabitov e-mail: sr...@st... Stack Soft jid: sr...@ja... |
|
From: Adrian-Cristian G. <adr...@gm...> - 2009-03-23 19:52:20
|
I have the same problem. Try instal 0.39.4. For me it works. The 0.40 keeps give me the same problem over and over. 2009/3/23 Luis Mauricio Calderon Castro <lmc...@ho...> > *Hi!!* > > *I’m having problems with the slr-start comand. Because the sqlr-listener > command doesn’t create the “*sqlr-listener-example*” file needed by the > sqlr-scaler command.* > > > > [root@localhost bin]# ./sqlr-start -debug -id example -config > /usr/local/firstworks/etc/sqlrelay.conf -localstatedir > /usr/local/firstworks/var > > > > Starting listener: > > sqlr-listener -id example -config /usr/local/firstworks/etc/sqlrelay.conf > -localstatedir /usr/local/firstworks/var -debug > > Debugging to: /usr/local/firstworks/var/sqlrelay/debug/sqlr-listener.718 > > > > Starting 3 connections to db1 : > > sqlr-connection-mysql -id example -connectionid db1 -config > /usr/local/firstworks/etc/sqlrelay.conf -localstatedir > /usr/local/firstworks/var -debug > > Debugging to: /usr/local/firstworks/var/sqlrelay/debug/sqlr-connection.720 > > sqlr-connection-mysql -id example -connectionid db1 -config > /usr/local/firstworks/etc/sqlrelay.conf -localstatedir > /usr/local/firstworks/var -debug > > Debugging to: /usr/local/firstworks/var/sqlrelay/debug/sqlr-connection.722 > > sqlr-connection-mysql -id example -connectionid db1 -config > /usr/local/firstworks/etc/sqlrelay.conf -localstatedir > /usr/local/firstworks/var -debug > > Debugging to: /usr/local/firstworks/var/sqlrelay/debug/sqlr-connection.724 > > > > Starting scaler: > > sqlr-scaler -id example -debug -config > /usr/local/firstworks/etc/sqlrelay.conf -localstatedir > /usr/local/firstworks/var > > > > sqlr-scaler error: > > The file > /usr/local/firstworks/var/sqlrelay/tmp/pids/sqlr-listener-example was not > found. > > This usually means that the sqlr-listener is not running. > > The sqlr-listener must be running for the sqlr-scaler to start. > > > > > > sqlr-scaler failed to start. > > > > > > Thanks to MP3.com for sponsoring: > > Clustered/Replicated database support. > > Perl API. > > Thanks to FeedLounge for sponsoring: > > Query routing and filtering. > > > > > > *However, if I execute the command by itself, the “*sqlr-listener-example*” > is created successfully.* > > * * > > *But when trying to access the database with the command, the response is > a failure. > *[root@localhost bin]# sqlrsh 192.168.1.123 9000 "" user1 password1 > > SQLRShell - Version 0.22 > > Connected to: 192.168.1.123:9000 as user1 > > > > type help; for a help. > > > > 0> ping; > > The database is down. > > * * > > *Also, in the sqlrelay.conf file I got this configuration.* > > <instance id="example" port="9000" socket="/tmp/examplesocket" > dbase="mysql" > > * * > > *Does this means that a file called “/tmp/examplesocket” has to be > created? Because I don’t see it anywhere.* > > * * > > *Thanks.* > > * * > > *Atte* > > *Luis Mauricio Calderon Castro.* > > ------------------------------ > Windows Live Hotmail now works up to 70% faster. Sign up today.<http://windowslive.com/Explore/Hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_faster_112008> > > > ------------------------------------------------------------------------------ > Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are > powering Web 2.0 with engaging, cross-platform capabilities. Quickly and > easily build your RIAs with Flex Builder, the Eclipse(TM)based development > software that enables intelligent coding and step-through debugging. > Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com > _______________________________________________ > Sqlrelay-discussion mailing list > Sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion > > |
16 messages has been excluded from this view by a project administrator.
