… row in set (0.00 sec) SESSION2: root@ubuntu:~/dump# mysqldump –single-transaction dumptest > dump.sql SESSION1: (before dump has completed… at the same time as mysqldump was running we got table empty table with new structure in mysqldump instead of table with…. What are potential solutions for this problem ? you can use mysqldump –lock-all-tables instead which does not have this problem…

Eric, I tested it with MySQL 5.5 I think what huarong had is just different timing. ALTER TABLE Will be blocked if that table is being dumped right now. However if mysqldump is processing table “A” and you’re altering table “Z” at this time Alter Table will not be blocked.

… on windows2003 mysql 5.5.15 while mysqldump is going , alter table was BLOCKED! Eventually mysqldump got the old table definition and…

I wrote about the alter table issue a few years ago and filed bug http://bugs.mysql.com/bug.php?id=28432 which was incorrectly closed as fixed. I’m surprised that more people weren’t upset about it. Maybe I should have added notes about mysqldump.

… from http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html : “While a –single-transaction dump is in process, to… be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or…