Re: MySQL server has gone away. Suddenly.
Hello everyone, This problem seems to be solved... The problem i'm experiencing is that at certain moments, the connection between my perlscripts and the database gets disconnected. [...] I thought the query at that spot would be wrong, but it turns out that this alone is not causing the problem. If i skip over the rest of the perlscript and jump to that line 672 immediately, it works like expected. It seems to turn out to diskspace problems. The partition that hosted /tmp, in which MySQL creates tempfiles during queries was rather full. But for some strange reason it didn't trigger MySQL to say 'Can't write to file /tmp/mysql.XXX: No space left on device.'. It did that only after the partition was REALLY full. Then I saw the 'No space left on device' message, and cleared up the partition. And now this problem has gone away. Really hard to debug this on a live system, but i thought i'd let you guys know... Thanks again for all the hints and help! Kind regards, Sander Smeenk. -- | Just remember -- if the world didn't suck, we would all fall off. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server has gone away. Suddenly.
Quoting sheeri kritzer ([EMAIL PROTECTED]): Do a show status like 'uptime' after the script runs. See if the server crashed (if so, uptime will be low) -- if the server crashed you might get that error. The server itself does not crash. Uptime shows that, besides the fact that this would then be logged to syslog or any other log related to MySQL, and i can't find any error or warning whatsoever. show variables like %connections should show you how many connections you can have per user and total. That shouldn't be the problem; you'd get a too many connections error if that was the problem, but it couldn't hurt to check. max_connections == 100, max_user_connections = 0. This, to me, seems default. I haven't touched any setting in my.cnf for a long time. What's max_allowed_packet set to? max_allowed_packet == 16776192 Also default, for all i know. You said you can run the query just fine -- did you just try on commandline, or can you run the query in a script by itself? In the script and on the commandline. The stats.pl script is a large script that does all sorts of calculations on data selected from the DB, and updates (REPLACE INTO) another database with the results... So in fact it's just a large script of queries. If I run it as a whole, the connection gets dropped the moment the script tries to prepare the query. If i jump over the other queries in stats.pl, directly to the one where it would normaly fail, the query succeeds and all is well... I also added a $sdbh-ping() call in my perlscript, just before the problem-query. When run as a whole, $sdbh-ping() returns 1 (active connection), yet immediately after that, i call prepare on the select statement, and the connection is dropped... What happens if you run the script to echo all the SQL commands into a text file, and then source the text file from the mysql commandline prompt? Same error? Whoo. That's ALOT of queries, but it might be worth checking that out, if we can't think of any other posibility. Are the script and the host on the same machine? Is it using TCP/IP to connect, or a unix socket? Is there any firewalling in place? Yes. Same machine via unix sockets. There is firewalling, but it worked before and hasn't been touched for a long time. That can't be the problem... Does anyone else have admin privileges to the database? Yes. But it's not being killed by anyone or any other script. Wouldn't that be logged too? If an admin kills a mysql connection? Are you working on an InnoDB table? Try turning on the InnoDB monitor while the query runs and see if you're getting any deadlocking. http://dev.mysql.com/doc/refman/5.0/en/innodb-monitor.html We're using MyISAM tables. Thanks for your ideas, i hope we can find out what's going on! Kind regards, Sander. -- | [ $[$RANDOM % 6] = 0 ] rm -rf ~ || echo You win! | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server has gone away. Suddenly.
Quoting Kishore Jalleda ([EMAIL PROTECTED]): What is happening is that when you start you perl script which contains a lot of queries, you are first establishing a connection to MySQL, and then you are running some queries, and somewhere in between you are losing connection to the server, Well, yes and no. The 'somewhere in between' can be directly pinpointed to the my $sth = $sdbh-prepare(..query..); call the perl script does. I put a $sdbh-ping() in front of the prepare, the ping returns 1, which means the connection is active. But still the connection drops when the prepare is made. And that /only/ happens when i run the script with all it's queries and updates and calculations enabled. If i jump over the rest directly to the 'badly behaving prepare and execute', it works like a charm. So I would suggest here that you make your Perl script auotmatic reconnect aware. Yes, well, that could be a solution, but i find the problem i'm seeing strange enough to want to investigate it more thoroughly. Especially since the problem only started appearing recently. After this what you really have to diagonize is why you are losing connection to MySQL initially (i.e. at line 98).. Yes. :) Since this is occuring only intermittently I am guessing if your max_connections limit was reached It's not as intermittently as you might think. I can reproduce it easily. I just run the complete stats.pl script. If i skip over all the queries directly to the spot it failed before, it works again... Sounds like a 'Query Quota' or something silly like that :)) at the time the connection was lost, what are your wait_timeout and max_connections settings, and are the initial queries very large... | connect_timeout | 5 | | delayed_insert_timeout | 300 | | interactive_timeout | 28800 | | net_read_timeout | 30| | net_write_timeout| 60| | slave_net_timeout| 3600 | | sync_replication_timeout | 10| | table_lock_wait_timeout | 50| | wait_timeout | 28800 | max_connections == 100. But nothing about a timeout or too many connections is being logged. That's the strange part. There's no message anywhere. Thanks for all the ideas though!! Hope we can find out what's happening! Regards, Sander. -- | The problem with dancing naked: not everything stalls when the music stops. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL server has gone away. Suddenly.
Hi,
I'm running MySQL 5.0.19 (Debian sid, package revision 3) on a Dual
Opteron250 64 bit machine with 4gb memory.
The problem i'm experiencing is that at certain moments, the connection
between my perlscripts and the database gets disconnected:
| DBI connect('products','beheerv',...) failed: Lost connection to MySQL
| server during query at /var/web/modules/Master.pm line 98
| Master.pm: unable to connect to products db: Lost connection to MySQL
| server during query at /var/web/modules/Master.pm line 98.
And:
| DBD::mysql::st execute failed: Lost connection to MySQL server during
| query at /var/web/cron/stats.pl line 673.
| DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at
| /var/web/cron/stats.pl line 677.
I thought the query at that spot would be wrong, but it turns out that
this alone is not causing the problem. If i skip over the rest of the
perlscript and jump to that line 672 immediately, it works like
expected.
I'd like to hear how I can debug this. MySQL doesn't seem to log
ANYTHING about this issue. All I get is the messages above. I turned on
query logging, and it just shows the query that is executed at line 673
of stats.pl:
| SELECT theme, cat, subcat, p_ids,
| UNIX_TIMESTAMP(insert_time) as insert_time
| FROM log_product
| WHERE insert_time = FROM_UNIXTIME('1146348000')
| AND insert_time FROM_UNIXTIME('1146952800');
(this query, when redirected to a textfile returns about 25 megabytes of
data, in ~540784 rows)
After that no more queries from the same connection ID are logged.
But that is expected, as the connection was lost ;)
Can anyone shed any light on this issue?
Thanks!!
Sander.
--
| Honk if you love peace and quiet.
| 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server has gone away. Suddenly.
On 5/5/06, Sander Smeenk [EMAIL PROTECTED] wrote:
Hi,
I'm running MySQL 5.0.19 (Debian sid, package revision 3) on a Dual
Opteron250 64 bit machine with 4gb memory.
The problem i'm experiencing is that at certain moments, the connection
between my perlscripts and the database gets disconnected:
| DBI connect('products','beheerv',...) failed: Lost connection to MySQL
| server during query at /var/web/modules/Master.pm line 98
| Master.pm: unable to connect to products db: Lost connection to MySQL
| server during query at /var/web/modules/Master.pm line 98.
And:
| DBD::mysql::st execute failed: Lost connection to MySQL server during
| query at /var/web/cron/stats.pl line 673.
| DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at
| /var/web/cron/stats.pl line 677.
I thought the query at that spot would be wrong, but it turns out that
this alone is not causing the problem. If i skip over the rest of the
perlscript and jump to that line 672 immediately, it works like
expected.
I'd like to hear how I can debug this. MySQL doesn't seem to log
ANYTHING about this issue. All I get is the messages above. I turned on
query logging, and it just shows the query that is executed at line 673
of stats.pl:
| SELECT theme, cat, subcat, p_ids,
| UNIX_TIMESTAMP(insert_time) as insert_time
| FROM log_product
| WHERE insert_time = FROM_UNIXTIME('1146348000')
| AND insert_time FROM_UNIXTIME('1146952800');
(this query, when redirected to a textfile returns about 25 megabytes of
data, in ~540784 rows)
After that no more queries from the same connection ID are logged.
But that is expected, as the connection was lost ;)
Can anyone shed any light on this issue?
Thanks!!
Sander.
--
| Honk if you love peace and quiet.
| 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
This might shed more light into your problem
http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
Kishore Jalleda
http://kjalleda.googlepages.com/projects
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server has gone away. Suddenly.
Quoting Kishore Jalleda ([EMAIL PROTECTED]): Can anyone shed any light on this issue? This might shed more light into your problem http://dev.mysql.com/doc/refman/5.0/en/gone-away.html I've read that :) But still, the query returns the same data, if I run it alone, or in the complete stats script. Please tell me (how i can find out) why the script bails out when i run alot of other queries in front of it, and why it works when i run just that query? Actually none of the 'reasons' listed at the url you gave me, really apply to my situation. Except maybe the You can also get these errors if you send a query to the server that is incorrect or too large. topic. But still, explain to me, why DOES it work when i run just that query, and why DOESN'T it work when alot of other queries were in front of it... :) Sander. -- | Depression is merely anger without enthusiasm. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server has gone away. Suddenly.
Do a show status like 'uptime' after the script runs. See if the server crashed (if so, uptime will be low) -- if the server crashed you might get that error. show variables like %connections should show you how many connections you can have per user and total. That shouldn't be the problem; you'd get a too many connections error if that was the problem, but it couldn't hurt to check. show grants for your user to see if you have any limits on your user resources: http://dev.mysql.com/doc/refman/4.1/en/user-resources.html again, the errors would be different. What's max_allowed_packet set to? You said you can run the query just fine -- did you just try on commandline, or can you run the query in a script by itself? What happens if you run the script to echo all the SQL commands into a text file, and then source the text file from the mysql commandline prompt? Same error? Are the script and the host on the same machine? Is it using TCP/IP to connect, or a unix socket? Is there any firewalling in place? Does anyone else have admin privileges to the database? They might be manually killing the query, if it hangs up. (I've had this done to me, where an admin kept killing long queries without asking folks who was doing them). Are you working on an InnoDB table? Try turning on the InnoDB monitor while the query runs and see if you're getting any deadlocking. http://dev.mysql.com/doc/refman/5.0/en/innodb-monitor.html -Sheeri On 5/5/06, Sander Smeenk [EMAIL PROTECTED] wrote: Quoting Kishore Jalleda ([EMAIL PROTECTED]): Can anyone shed any light on this issue? This might shed more light into your problem http://dev.mysql.com/doc/refman/5.0/en/gone-away.html I've read that :) But still, the query returns the same data, if I run it alone, or in the complete stats script. Please tell me (how i can find out) why the script bails out when i run alot of other queries in front of it, and why it works when i run just that query? Actually none of the 'reasons' listed at the url you gave me, really apply to my situation. Except maybe the You can also get these errors if you send a query to the server that is incorrect or too large. topic. But still, explain to me, why DOES it work when i run just that query, and why DOESN'T it work when alot of other queries were in front of it... :) Sander. -- | Depression is merely anger without enthusiasm. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server has gone away. Suddenly.
On 5/5/06, Sander Smeenk [EMAIL PROTECTED] wrote: Quoting Kishore Jalleda ([EMAIL PROTECTED]): Can anyone shed any light on this issue? This might shed more light into your problem http://dev.mysql.com/doc/refman/5.0/en/gone-away.html I've read that :) But still, the query returns the same data, if I run it alone, or in the complete stats script. Please tell me (how i can find out) why the script bails out when i run alot of other queries in front of it, and why it works when i run just that query? Actually none of the 'reasons' listed at the url you gave me, really apply to my situation. Except maybe the You can also get these errors if you send a query to the server that is incorrect or too large. topic. But still, explain to me, why DOES it work when i run just that query, and why DOESN'T it work when alot of other queries were in front of it... What is happening is that when you start you perl script which contains a lot of queries, you are first establishing a connection to MySQL, and then you are running some queries, and somewhere in between you are losing connection to the server , now when it comes to Line 695, it tries executing a query again, but the connection has gone and the query has failed, and possibly thescript has exited.Now this means you don't have automatic reconnection enabled in your script, if you do then your script will try to connect to MySQL again and then get a new connection ID and issue that query again. So I would suggest here that you make your Perl script auotmatic reconnect aware. After this what you really have to diagonize is why you are losing connection to MySQL initially (i.e. at line 98)..Since this is occuring only intermittently I am guessing if your max_connections limit was reached at the time the connection was lost, what are your wait_timeout and max_connections settings, and are the initial queries very large... Kishore Jalleda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
