Backing up and Restoring MySQL Tables with autoincrement columns
----------------------------------------------------------------
Key: SEQUOIA-891
URL: https://forge.continuent.org/jira/browse/SEQUOIA-891
Project: Sequoia
Type: Bug
Components: Backup System
Versions: Sequoia 2.10
Environment: MySQL 5.0.24
Reporter: Wolfgang Klenk
I am running a Database Cluster with 2 MySQL Nodes (Distributed raidb1
Scenario) with 2 controllers (horizontal scalability) on seperate workstations.
Let's assume that one of the 2 nodes crashed and needs to be recreated using
the data from the still working node.
This should be no problem following the instructions in the "Sequoia
Administrator's Guide", Section "7.1 Recover from a controller failure".
However, if your database tables have autoincrement columns there is an issue:
- Backing up node1 (backup node1 dumpXYZ MySQLBackuper /dumps) writes a
mysqldump file.
- Unfortunatelly, this mysqldump file does NOT contain information about the
current value of the autoincrement column counters.
- If you use this file to restore the database on the failed node2, then the
autoincrement column counters in node1 and node2 will differ (the autoincrement
column counters on node2 will be "1").
- After enabling node2, if you issue an "INSERT" statement on the sequoia
cluster on a table with an autoincrement column, the autoincrement column will
get different values on node1 and node2.
- This is fatal if the autoincrement column is used as Primary Key, as now on
node1 and node2 the primary key is different.
Conclusion: The Backup/Restore Mechanism provided for MySQL with Sequoia 2.10
is worthless, as it can't be used in case of a node failure.
Possible solution:
The MySQLBackuper should add some statements to the dump file, that updates the
autoincrement column counters to the current values. This statement could look
like as follows:
ALTER TABLE login_monitor AUTO_INCREMENT=69140;
ALTER TABLE abc AUTO_INCREMENT=335;
ALTER TABLE xyz AUTO_INCREMENT=5;
You can manually create this statements using mysql command:
show table status from <database>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://forge.continuent.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
_______________________________________________
Sequoia mailing list
[email protected]
https://forge.continuent.org/mailman/listinfo/sequoia
