List:Backup« Previous MessageNext Message »
From:Susanne Ebrecht Date:July 28 2009 8:24am
Subject:Re: Restore locking: TRUNCATE resets AUTO_INCREMENT on InnoDB
View as plain text  
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

Thread
Restore locking: TRUNCATE resets AUTO_INCREMENT on InnoDBIngo Strüwing24 Jul
  • Please advise [Re: Restore locking: TRUNCATE resets AUTO_INCREMENT onInnoDB]Ingo Strüwing24 Jul
    • Re: Please advise [Re: Restore locking: TRUNCATE resets AUTO_INCREMENTon InnoDB]Ingo Strüwing27 Jul
      • Re: Please advise [Re: Restore locking: TRUNCATE resets AUTO_INCREMENTon InnoDB]Konstantin Osipov27 Jul
      • Re: Please advise [Re: Restore locking: TRUNCATE resets AUTO_INCREMENTon InnoDB]Rafal Somla28 Jul
        • Re: Please advise [Re: Restore locking: TRUNCATE resets AUTO_INCREMENTon InnoDB]Ingo Strüwing28 Jul
          • Re: Please advise [Re: Restore locking: TRUNCATE resets AUTO_INCREMENTon InnoDB]Charles Bell28 Jul
            • Re: Please advise [Re: Restore locking: TRUNCATE resets AUTO_INCREMENTon InnoDB]Ingo Strüwing28 Jul
      • Re: Please advise [Re: Restore locking: TRUNCATE resets AUTO_INCREMENTon InnoDB]Jørgen Løland13 Aug
      • Meeting preparation [Re: Restore locking: TRUNCATE resetsAUTO_INCREMENT on InnoDB]Ingo Strüwing31 Aug
  • Re: Restore locking: TRUNCATE resets AUTO_INCREMENT on InnoDBSusanne Ebrecht28 Jul
    • Re: Restore locking: TRUNCATE resets AUTO_INCREMENT on InnoDBIngo Strüwing28 Jul
      • Re: Restore locking: TRUNCATE resets AUTO_INCREMENT on InnoDBSusanne Ebrecht28 Jul
      • Re: Restore locking: TRUNCATE resets AUTO_INCREMENT on InnoDBAnn W. Harrison28 Jul
Re: Meeting preparation [Re: Restore locking: TRUNCATE resetsAUTO_INCREMENT on InnoDB]Ingo Strüwing1 Sep