mysql service and dbd::mysql

rhoadesjn has asked for the wisdom of the Perl Monks concerning the following question:

Has anyone had any issues with the mysql service dropping unexpectantly when adding info into a database using dbd::mysql? This seems to happen about once a week for one particual perl program. However, I have this perl program working on 20 other different servers with no issues. Mysql does not generate any useful error logs and perl does not tell me anything except the service dropped. Any insight into the potential issue or ways to debug the issue would be helpful! Thanks.

Comment on mysql service and dbd::mysql

Replies are listed 'Best First'.
Re: mysql service and dbd::mysql by dug (Chaplain) on Nov 12, 2003 at 22:10 UTC
Does it happen after a lack of activity? It's possible that your wait_timeout setting for mysql is low enough the connection your program thinks is open isn't actually open. DBD::mysql has a "reconnect" feature to work around this "morning bug". Its behaviour has changed a bit in recent releases, though, so we'll need to know more about your environment to help diagnose this. What does your connect string look like? Are you running this program stand-alone or under mod_perl (or some other environment)? Thanks, -- dug	[reply]
Re: mysql service and dbd::mysql by tachyon (Chancellor) on Nov 13, 2003 at 00:46 UTC
If this is a long running perl program then you are probably getting a "MySQL server has gone away" error issue. The DBH will time out after 8 hours (by default). You either do a transparent reconnection or potentially up the timout in /etc/my.cnf. I have seen the PHP guys advocate upping the time out as a fix (bad solution generally) but be aware you will run out of connections unless you close them on exit... `[root@devel3 james]# cat /etc/my.cnf [client] socket=/var/lib/mysql/mysql.sock [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock #set-variable=wait_timeout=3600 <---------- set-variable=max_connections=40 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [root@devel3 james]#` [download] cheers tachyon	[reply] [d/l]
Re: mysql service and dbd::mysql by hmerrill (Friar) on Nov 13, 2003 at 14:24 UTC
I don't know what platform you are running MySQL on, but I know on Red Hat Linux that MySQL logging was NOT turned on by default. In order to turn on logging I had to modify the /etc/rc.d/init.d/mysql start/stop/status script - where it invokes safe_mysqld, I add '--log=/var/log/mysqld.log' and so changed the line `from this: /usr/bin/safe_mysqld --defaults-file=/etc/my.cnf >/dev/null 2> +&1 & to this: /usr/bin/safe_mysqld --defaults-file=/etc/my.cnf --log=/var/lo +g/mysqld.log >/dev/null 2>&1 &` [download] and remember to restart the mysqld server('service mysqld restart' on Red Hat Linux) to make the changes take effect. If you can get MySQL to log messages it might help you figure out what the problem is. HTH.	[reply] [d/l]

Back to Seekers of Perl Wisdom