Bug #42470 MySQLDump hanging - stuck at a particular table
Submitted: 30 Jan 2009 9:25 Modified: 17 Sep 2010 11:27
Reporter: Karthik Swaminathan Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:MySQL 5.0.18 nt OS:Windows (XP 2002 sp 2)
Assigned to: CPU Architecture:Any

[30 Jan 2009 9:25] Karthik Swaminathan
Description:
I am using this DOS command to backup all databases on my server. 

mysqldump -v -q -hlocalhost --net_buffer_length=4 -rdbbackup.sql -uroot  -ppwd --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs

It seems to be going normally. But some times ( approx. 50% of the times ), it gets stuck at a particular table. Every time, it is a different table. Sometimes it completes without getting stuck at all. 

I am taking backups using the same command in other servers, of similar databases.. I fail to understand why it should fail on that specific machine alone? 

Please help. I am ready to provide any inputs that you might require to research this issue.

How to repeat:
by running mysqldump command mentioned above repeatedly.
[30 Jan 2009 9:51] Valeriy Kravchuk
Thank you for a problem report. Please, next time when mysqldump will get stuck, connect to the same server with mysql command line client and get the results of

show processlist;

Send/upload them to the bug report.

I think mysqldump is just waiting for a table lock on some MyISAM table.

Side note: please, upgrade to a newer version, 5.0.67 at least. 5.0.18 is 3+ years old already...
[30 Jan 2009 10:46] Karthik Swaminathan
show processlist

Attachment: attempt1_stuck.xls (application/vnd.ms-excel, text), 2.42 KiB.

[30 Jan 2009 10:50] Karthik Swaminathan
Hi

Thanks for that quick response. 

I have checked all the tables in the schemas that I have created. Everyone of the tables is InnoDB.
I have attached results of show processlist as you requested. 

There can't be any table locks, because when i was checking this sometime ago, there was no other connection to the server... 

This time when I was trying to recreate the problem, mysqldump got stuck the first time. But subsequently, I tried 4-5 times, mysqldump never got stuck..

Really wierd... 
Thanks for your help.

I recently tried to upgrade to 5.1. But was totally stumped!!! Anyway am planning to try later.
[30 Jan 2009 10:55] Valeriy Kravchuk
Please, send the results as plain text, just copy and paste them to the text file. I do not have proper version of Excel here to read your information.

Also, please, send the results of:

SHOW INNODB STATUS\G

when mysqldump hangs.

In any case we do not fix bugs in older versions. So, you have to reproduce this problem on current version, 5.0.67+ and/or 5.1.30 first.
[2 Feb 2009 11:39] Karthik Swaminathan
show processlist - 2nd Feb 2009

Attachment: processlist.txt (text/plain), 540 bytes.

[2 Feb 2009 11:39] Karthik Swaminathan
innodb status - 2nd Feb 2009

Attachment: innodb_status.txt (text/plain), 2.91 KiB.

[2 Feb 2009 11:44] Karthik Swaminathan
This is how I managed to recreate the bug today.

Initially I was running my batch script from a certain directory.

All that the backup script contained was only this - 

mysqldump -v -q -hlocalhost --net_buffer_length=4 -rdbbackup.sql -uroot  -ppwd
--single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs

I ran the batch script 3-4 times and it was working perfectly.

Then I just copied the batch script to another location in the hard disk and ran it from there. Immediately it started hanging... This is not just an accident. I have observed this sort of behaviour earlier as well..

I ran "show innodb status\G" and "show process list" as you requested when mysqldump was hanging... I have uploaded text files...

Please look into this.

Thanks

Karthik
[22 Feb 2009 14:38] Valeriy Kravchuk
As you cab see from these:

+----+------+----------------+-------+---------+------+-------+------------------+
| Id | User | Host           | db    | Command | Time | State | Info |
+----+------+----------------+-------+---------+------+-------+------------------+
| 65 | root | localhost:1079 | books | Sleep   |  121 |       | NULL |
| 71 | root | localhost:1086 | NULL  | Query   |    0 | NULL  | show processlist |
+----+------+----------------+-------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

nothing is running at the moment. So, please, get these results when mysqldump is running but got stuck on some table. I'd expect to see some query at this moment. 

If there are no queries running, please, check if you have enough disk space and proper permissions at OS level.
[11 Mar 2009 11:02] Karthik Swaminathan
As per what I saw in the command line mysqldump didnt finish. I ran mysqldump in verbose mode and according to what was displayed, it was trying to dump one of the tables of the database. At that time, when it was "hanging" like that at that table, I ran the 2 commands that you asked me to run. 

I have lots and lots of disk space. That should nt be a problem. I have installed and am running the same database version/application in the same OS in other computers without any problem. Also, it is not that this mysqldump fails always. It fails at times and works at times for the same database and even if i am dumping to the same folder in this computer.
[11 Mar 2009 11:07] Karthik Swaminathan
Btw, surprisingly I was not notified by email when this bug was updated by you (developer) on the 22nd of February !?!?!?
[4 Jun 2009 13:09] Valeriy Kravchuk
So, had you been able to reproduce the problem with some recent (5.0.82 and 5.1.34 at the moment) MySQL server version(s)?
[5 Jun 2009 3:06] Karthik Swaminathan
The same version I have installed in other places and I dont face this problem. The place where I have installed this is for production. Wouldn't want to disturb it because it is running smoothly. 

Will re-installing the database solve my problem?
[11 Jun 2009 21:03] Sveta Smirnova
Thank you for the feedback.

>  [2 Feb 12:44] Karthik Swaminathan
> This is how I managed to recreate the bug today.
...
> Then I just copied the batch script to another location in the hard disk and ran it from
> there. Immediately it started hanging... 

This can show you have problems with your environment. Please check operating system error logs.

> The same version I have installed in other places and I dont face this problem. The place
> where I have installed this is for production. Wouldn't want to disturb it because it is
> running smoothly. 
>
> Will re-installing the database solve my problem?

If you experience the problem on single machine and if you have enough free space on it you can try following:

1. Copy datadir and configuration file to second location, say datadir2
2. Start same 5.0.18 mysqld with datadir2 and different port
3. Try to repeat error with second mysqld process.
4. If error occurs with second mysqld install current version 5.0.82 on same machine, but use separate port and datadir2
5. Try if problem repeatable for new version.

As alternative to point 1 you can try to load dump to second instance.
[12 Jun 2009 3:21] Karthik Swaminathan
Why has the status changed to 'Need feedback'?
[12 Jun 2009 3:33] Karthik Swaminathan
Sorry didnt go through your message fully
[15 Jun 2009 9:25] Sveta Smirnova
Because we need you to test with current version and answer questions from my last comment.
[15 Jul 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[15 Sep 2010 20:00] Max K
Had the same problem.  Turns out adding the --skip-lock-tables option when using msyqldump fixed the issue.
[16 Sep 2010 18:31] Sveta Smirnova
Max,

thank you for the feedback. But we need test case repeatable with latest version to be able to say if this is MySQL bug or not.
[17 Sep 2010 5:07] Karthik Swaminathan
Hi

I fixed this by installing MYSQL 5.0.88. Afterwards I haven't had this problem
[17 Sep 2010 11:27] Sveta Smirnova
Thank you for the feedback.

Closing bug as "Can't repeat" because last comment.