[ https://forge.continuent.org/jira/browse/SEQUOIA-891?page=all ] Mathieu Peltier closed SEQUOIA-891: -----------------------------------
Tested > 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 > Assignee: Mathieu Peltier > Fix For: Sequoia 3.0, Sequoia 2.10.5 > > > 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
