Re: [Sqlrelay-discussion] Multiple sqlr-listener processes and no connections

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

Hi Ville,

Sorry for not responding earlier.

SQL Relay should automatically re-log in if it loses connection to the 
database.  I recently added a parameter to disable that behavior, but it 
should do it by default.

It sounds like the sqlr-connection-mysql process is crashing for some 
reason but the semaphores are in such a state that the sqlr-scaler 
doesn't realize this and doesn't spawn any new processes.

I've actually tried to reproduce this and gone through the code, looking 
for some semi-obvious thing that could cause it, but I can't find 
anything.  My guess is that there is some particular query that crashes 
the process.

The last time I encountered a problem like this, it was with oracle and 
the problem was that I misinterpreted characters for bytes and if OCI 
said "allocate 100 characters", I'd allocate a buffer of 100 bytes, 
which was fine until somebody started using unicode and the query 
returned multi-byte characters.  Something like that.  I looked again 
for that kind of thing, but I didn't see anything.

Nevertheless, I suspect something like that is the problem.  It would be 
helpful if you could find the query or condition that's causing the 
connections to crash, though I imagine that would be like looking for a 
needle in a haystack.

I'll be out of town for the next few days, but I can look into it again 
when I get back.

Dave
dav...@fi...

On 09/25/2012 06:00 AM, Ville Silventoinen wrote:
> Hi,
>
> Just few more details on this:
>
> - I run SQL Relay 0.46 with two different instances configured in
> sqlrelay.conf; one for Oracle and one for MySQL.
>
> - When the problem happens, it's always with the MySQL connections. All
> the sqlr-connection-mysql processes have stopped/crashed and there are
> several sqlr-listener processes for the MySQL instance. They seem to be
> unable to start any sqlr-connection-mysql processes.
>
> - Just guesswork, but could the problem be caused if for whatever reason
> SQL Relay fails to login to MySQL? Would that stop the
> sqlr-connection-mysql process? I've asked the MySQL admin, but we don't
> log the failed login attempts. I could turn on the connection debug mode
> in SQL Relay, but last time we did that, it filled the /var filesystem
> very fast, so I'd need to find some disk space for that.
>
> - I'm waiting when the problem happens next time so I can run the
> "sqlr-status -id INSTANCE" command to see what the raw semaphore numbers
> are. I saw an old thread that seems quite similar to this, but I'm not
> sure if it's related.
>
> Cheers,
> Ville
>
> On 4 September 2012 12:15, Ville Silventoinen
> <vil...@gm... <mailto:vil...@gm...>> wrote:
>
>     Hi,
>
>     I'm using SQL Relay 0.45 on RedHat 6. Sometimes SQL Relay goes into
>     a state where it starts several sqlr-listener processes, but there
>     are no sqlr-connection processes. The application that uses SQL
>     Relay hangs. There may be a problem with RedHat and the network card
>     we are using, which can cause temporary loss of network
>     connectivity. The only cure that I've found is to restart SQL Relay.
>
>     Today I decided to try SQL Relay 0.46. As I was running tests, our
>     DBAs shutdown the test Oracle database (I wasn't aware of this).
>     Again, the same situation happened: Several sqlr-listener processes
>     and no connections.
>
>     I'm looking for some insight into how I could configure SQL Relay so
>     it would automatically recover from the temporary loss of network
>     connectivity or database availability. Is this possible? Would it
>     help if I set maxlisteners to 0, listenertimeout to 60, or tweak
>     some other configuration variable?
>
>     Thanks for any advice,
>     Ville
>
>
>
>
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>                http://www.doteasy.com
>
>
>
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>                http://www.doteasy.com
>