Many people use mysqldump –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases it works, but did you know there are some cases when you can get table entirely missing from the backup if you use this technique ?
The problem comes from the fact how MySQL’s Transactions work with DDL, In particular ALTER TABLE. When ALTER TABLE is Performed in many cases it will Create temporary table with modified structure, copy data to that table and when drop original table and rename such temporary table to original name.
How does data visibility works in this case ? DDLs are not transactional and as such the running transaction will not see the contents of old table once it is dropped, transaction also will see the new table which was created after transaction was started, including table created by ALTER TABLE statement. Transactions however apply to DATA which is stored in this table and so data which was inserted after start of transaction (by ALTER TABLE statement) will not be visible. In the end we will get new structure in the dump but no data.
Here is example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 | SESSION1: mysql> show tables; +--------------------+ | Tables_in_dumptest | +--------------------+ | A | | B | | C | +--------------------+ 3 rows in set (0.00 sec) mysql> select count(*) from A; +----------+ | count(*) | +----------+ | 2359296 | +----------+ 1 row in set (1.73 sec) mysql> select * from C; +------+ | t | +------+ | test | +------+ 1 row in set (0.00 sec) SESSION2: root@ubuntu:~/dump# mysqldump --single-transaction dumptest > dump.sql SESSION1: (before dump has completed) mysql> alter table C add i int not null; Query OK, 1 row affected (0.65 sec) Records: 1 Duplicates: 0 Warnings: 0 SESSION2: root@ubuntu:~/dump# tail -29 dump.sql DROP TABLE IF EXISTS `C`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `C` ( `t` char(255) NOT NULL, `i` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `C` -- LOCK TABLES `C` WRITE; /*!40000 ALTER TABLE `C` DISABLE KEYS */; /*!40000 ALTER TABLE `C` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2012-03-23 7:27:18 SESSION1: mysql> select * from C; +------+---+ | t | i | +------+---+ | test | 0 | +------+---+ 1 row in set (0.00 sec) |
As you can see as we altered table C at the same time as mysqldump was running we got table empty table with new structure in mysqldump instead of table with valuable data.
This is a pretty edge case scenario neither the less it can be problem for some workloads which run ALTER TABLE regularly during normal operation. I also hope if you get some empty
tables in your mysqldump –single-transaction backups you will know the potential cause for it.
What are potential solutions for this problem ? you can use mysqldump –lock-all-tables instead which does not have this problem at the cost of having database read only for the
duration of operation. You can also use Percona Xtrabackup, LVM or other database backup approach which does not relay on transaction visibility.
Hi Peter!
It is not really a secret… Quoting MySQL manual from http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html :
“While a –single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.”
Dima,
OK. My bad. Great to hear it is documented. Still I wonder how many people know about it… beyond you can use
–single-transaction to get consistent backup
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.
This is so mind-bogglingly stupid behaviour. What drugs were people on when they decided this kind of dumb behaviour was a good idea? I seriously want to know so I can avoid ever taking them.
Stewart,
I do not think this is a “stupid decision” which was ever taken. This is just how things were designed with MySQL and how transactions were retrofitted to it. MySQL has funny history this way. It still has lots of things going to its non transactional past, yet non transactional table support are all but gone in recent versions. The question is whenever MySQL will ever be redesigned to focus on transactional storage engines or whenever this will remain Drizzle’s turf.
As we learnt when doing a lot of the initial refactoring for Drizzle, turning MySQL into something that truly focused on transactions leads to some rather interesting corner cases you discover/remove. If they do, good luck to those with existing applications 🙂
I did the same test on windows2003 mysql 5.5.15
while mysqldump is going , alter table was BLOCKED!
Eventually mysqldump got the old table definition and old data in it.
DROP TABLE IF EXISTS
z;/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE
z(nchar(1) DEFAULT ”) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
—
— Dumping data for table
z—
LOCK TABLES
zWRITE;/*!40000 ALTER TABLE
zDISABLE KEYS */;INSERT INTO
zVALUES (‘a’);/*!40000 ALTER TABLE
zENABLE KEYS */;UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
…
5.5 introduces metadata locking which fixes the 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.
Ah that makes sense. MDL won’t kick in until the table has been accessed.
Only one way out. Make a backup copy of a replicated server. Prior to this, run a stop slave
I’m finding that invoking mysqldbexport.exe or mysqldbcopy.exe directly (without first launching MySQLWorkbench) seems to accomplish about any task blazingly fast while avoiding the issues associated with mysqldump.exe.
Taking backups from a replicated slave with replication stopped is probably the best way to do it with mysqldump. You could also look at making a script to iterate through the tables in the database and mysqldump them one at a time, each with –single-transaction. Problem with that is your coordinates would differ for each table… using this to rebuild a slave wouldn’t be a problem, just logistically painful — but couldn’t be used for a new master or stand-alone db server, as relational integrity could be compromised.
Agree with Peter. Though it is not a secret, too few people know this (making this as a secret) 🙂
Hi Peter,
I would like to know if indices on any given columns are included in mysqldump created by the MySQL database server.
Or is it that I should log into my remote host server and create them.
Regards,
Jimmy
Why would anyone want to Alter Table in the middle of a mysqldump ?