Mysql - Loosing connections

[transparent]

I am working on a site based on a apache server running mysql and chillisoft.

About 50% of the time I get the error message:

ADODB.Recordset.1, 80004005, Lost connection to MySQL server during query

If I refresh the page - this often resolves the problem. Any Ideas whats going on?

Many thanks for any help.

Transparent

 

[cluelessNewbie]

I've been getting the the same problem on a batch job that adds about 1.5M records using a perl script.

AFAIK the mysql docs say that this can be caused by 3 things:
1) The connection timed out for real due to inactivity: not relevant in my case
2) A bad query: Well given, that you're just resending the same data not likely.
3) The packet max is too low: I'm currently trying this option.
If you
mysql> show variables;
I'll bet that you see that the
max_allowed_packet is set to 1048576 (1MB)
A recomended fix is to up this ... I'm trying it by increasing this to 33554432 (something over 30MB) a, hopefully, ridiculously large number. I'm about two hours into a ~8Hr build so I'll let you know how it turns out but so far I've noticed a speed improvement (placebo?).

You increase the max_allowed_packet size when you start the daemon. So for me I manually started the daemon with...
# safe_mysqld --user=mysql -O max_allowed_packet=33554432 &

If this works I'll put the option into the linux boot scripts properly.

I hope this is relevant to your problem.

 

[cluelessNewbie]

fyi: in this scenario when you refresh you actually just reestablishing the connection... provided I got all the info straight (look at the handle! ;-))

 

[rycamor]

A lost connection during a query can be caused by other things also. If there is a hardware problem on the machine itself, such as bad RAM chip, etc... then communication can be interrupted. Also, if the kernel itself or the software connecting to MySQL has a problem, you can be hit by this.

In other words, it might not be a MySQL problem at all, but something in I/O or related software. Have you looked at '/var/log/messages'? Also, what happens in the Apache error log, whenever you have this problem. (usually in /var/log/httpd/error_log). Then finally, check your MySQL error log ("/path/to/mysql/data/hostname.err&quot.

The server itself might just be getting bogged down. Chillisoft is not the fastest piece of software on the block. I personally loath the idea of trying to implement ASP on a non-Windows system. I assume you are running a Linux server. Sometimes Linux requires a lot more tuning to get it to handle the load you experience. I have had this sort of problem several times on Linux systems.

If this is an older Linux kernel, such as the 2.2 series, then you also might be running into the "max file descriptors" limit. Many of those systems defaulted to allowing a maximum of 1024 file descriptors in use at any time. This may sound like a lot, but if 150 people hit your site at one time, and the page they request has other includes, and also you are saving session data in tempfiles, you can run into that limit really fast. -------------------------------------------

"Calculus is just the meaningless manipulation of higher symbols"
                          -unknown F student

 

[pascalxricher]

Does the max_allowed_packet solution will help for this problem ?

'Can't connect to MySQL Server on 192.168.1.134 (10055)' in my vb program... it just append sometimes, just like if I do too much query in a Do While. and If I enter in the debug mode, I do a Step Next, the query open well... Weird !

 

[cluelessNewbie]

rycamor, is correct I haven't done a thorough analysis of the problem & given my level of experience I probobly can't. As he said, many other thing may be causing the problem, so definitely(!!) have a look-see at those. The list he provided were things I didn't know to look at... now I do, so thank you rycamor.

I can only speak to my limited experience & what I've read (Sect A.2.2). Really, I choose my handle as a "Buyer beware." kinda thing so...

Now speaking to my limited experience. Increasing the max_allowed_packet seemed to help in my case. Was this a one off coincidience? Well, it failed in annoyingly random places & times before this (about 5 times) & worked this time.

Also I've had the same problem w/ both a localhost & net server. It took several times for the net version to work but eventuually it did without the new startup option. So maybe I was due.

Totally different machines, linux setups, & DB setups. Of course none of this rules out anything rycamor posted above.

PS: As a workaround (gag & retch) maybe we could trap for the error & reestablish the connection when needed... if none of these shenanigans really work.

 

[transparent]

Heres my solution:

I just looked at our configuration for chilisoft asp. There is an option
in the server which makes it use cached database connections - ie - even if
your script always tried to open the connection, asp would attempt to use
the connection already established. I disabled this option it did the trick.

Cheers fro the help lads

Transparent

 

[geolaw]

Transparent:
Can you post the location of the chilisoft file and
the option you changed?

I think this is probably in

/path/to/casp/apache-server-3000/casp.cfng:
connectionpooling=yes

but would like to confirm this before making a change.