From: Susanne Ebrecht Date: July 28 2009 8:24am Subject: Re: Restore locking: TRUNCATE resets AUTO_INCREMENT on InnoDB List-Archive: http://lists.mysql.com/backup/13 Message-Id: <4A6EB5A4.9000401@sun.com> MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset=UTF-8 Content-Transfer-Encoding: 8BIT Ingo Strüwing schrieb: > Hi Susanne, > > Susanne Ebrecht, 28.07.2009 06:34: > > ... > >> According to my researches here I would recommend that we avoid to reset >> AUTO_INC during TRUNCATE. Users won't expect such a behaviour. >> Especially not, when they used SERIAL. The only way to change/reset >> AUTO_INC should be via ALTER. >> > > Thank you very much for your analyze. I suggest you to file a bug report > against TRUNCATE. > Ok. I will look deeper into this and look what exactly is the bug here. We should not change already documented expected MySQL own behaviours ... Means I have to read our docs here first. > In the backup team we still have the problem to restore the > auto_increment value to what it was at the time of BACKUP. Even if > TRUNCATE would not change the value, with the current locking scheme, > concurrent DML on freshly created tables could modify the value. This > could also happen during data load. So we still need a solution to > restore the value after data load. Unless we decide that a reproduction > of the auto_increment value is not a requirement for BACKUP/RESTORE. > Here we have a split between theory and praxis. Theory and SQL Standard Committee says that SERIAL is a not safe data type. Because when you backup your table and then restore it, it could happen that you have different values in your restored database as in your original database. It is not recommended at all to use SERIAL as primary key when you have other tables in your database using this as foreign key reference. The opinion about what happens with SEQUENCES in general already differ in SQL Standard Committee. In praxis users expect same values after restoring for SEQUENCE columns besides for data type SERIAL. But there are tons of users outside who would also expect same values for data type SERIAL. Also tons of users are using SERIAL or MySQL Auto_INC as PK and other tables with FK referencing to the auto_inc PK. My opinion is that it just would be more user friendly when we try to restore same values as in original table. So do PostgreSQL and the IBMs. I have no clue how it is in Oracle or MSSQL. Anyway to say it with a mix from MySQL and PostgreSQL slang here is how PostgreSQL and IBM solved: Consider, SERIAL just is an aliases here for INTEGER NOT NULL UNIQUE DEFAULT calling_sequence() Backup (Informix and older versions of Postgres): 1) they backup sequence 2) they backup table (without data) and use INTEGER DEFAULT calling_sequence() here instead of SERIAL 3) lock table for writing 4) look for last_insert_id and store somewhere (i.e. in RAM) 5) backup data including the data for SERIAL columns 6) unlock table 7) change sequence to new start value = last_insert_id Or more complex (DB2 and Postgres today): 1) they backup table (without data) and use INTEGER without constraints 2) lock table for writing 3) look for last_insert_id and store somewhere (i.e. in RAM) 4) backup data including the data for SERIAL columns 5) unlock table 6) create sequence with new start value = last_insert_id 7) alter table ... alter column ... add constraint not null unique default calling_sequence() Restore (Informix and older versions of Postgres): 1) create sequence (this is necessary to be done before create table) 2) create table 3) insert "all" data (including the old values of the serial columns) 4) alter sequence to new start value Or (DB2 and Postgres today): 1) create table without constraints and using INTEGER instead of SERIAL 2) insert "all" data (including the old values of the serial columns) 3) create sequence with start value given in backup 4) alter table ... alter column ... add constraint not null unique default calling_sequence() Honestly, I am not able to see a problem here to do it similar way in MySQL. Just remember highest value and after inserting all data just change AUTO_INC value to the highest value. Or just restore table without auto_inc first and add auto_inc at the end (after inserting data) to the column. Because especially MySQL has so much users who are ignoring the Standard committee warning that it is dangerous to use SERIAL/AUTO_INC columns as references for FK columns I really would recommend to always restore exact same values as there were before. Also we already have had tons of complains in statement based replication that auto_inc column value could not be same on master and slave. I really only can recommend to take care here that values are the same before and after restore. Just for your information: Ten years ago PostgreSQL was as ugly as MySQL on the topic backup/restore/dump. With the PostgreSQL release from 2005 (afair it was PG 8.0) they totally changed backup/dump and since then it is really excellent. Of course it is more complex and of course I am able to provide every single step here. But to say it short they changed backup/dump to the following principal: 1) backup/restore all tables without indexes and constraints 2) backup/restore data 3) add indexes and constraints to the tables Somewhere between or after this they create triggers, fuctions, sequences, views and so on ... I would have to look for the exact sequence of creations here. In any case all indexes and constraints just will be added to the tables almost at the end of the whole procedure. Before we reinvent the wheel it just would make sense to look here how others solved it. Susanne -- Sun Microsystems GmbH Dipl.-Inf. Susanne Ebrecht, Support Engineer 33615 Bielefeld, Germany http://www.sun.com Registered Office: Sonnenallee 1, D-85551 Kirchheim-Heimstetten Commercial register of the Local Court of Munich: HRB 161028 Managing Directors: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Chairman of the Supervisory Board: Martin Haering