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
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
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
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
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
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
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.
Sun Microsystems GmbH
Dipl.-Inf. Susanne Ebrecht, Support Engineer
33615 Bielefeld, Germany
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