List:Backup« Previous MessageNext Message »
From:Ingo Strüwing Date:July 24 2009 9:29am
Subject:Please advise [Re: Restore locking: TRUNCATE resets AUTO_INCREMENT on
InnoDB]
View as plain text  
Hi backup team,

Ingo Strüwing, 24.07.2009 08:56:

> Hi Konstantin,
> 
> Restore locking looks pretty good now.
> http://lists.mysql.com/commits/79209
> 
> However, TRUNCATE didn't fix the AUTO_INCREMENT problem.
> 
> The AUTO_INCREMENT value is now retained for some engines, tested are
> MyISAM and MEMORY, but still cleared by others, tested is InnoDB.

The problem can be partially repeated outside of RESTORE.
For MyISAM do:
  CREATE TABLE t1 (c1 INT AUTO_INCREMENT PRIMARY KEY) ENGINE=MyISAM;
  INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8);
  SHOW CREATE TABLE t1;
This shows in the table options: AUTO_INCREMENT=9
Now save the files t1.MYI and t1.MYD.
Now do, what RESTORE does for MyISAM tables:
  DROP TABLE t1;
  CREATE TABLE `t1` (`c1` int(10) NOT NULL AUTO_INCREMENT, PRIMARY KEY
(`c1`)) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
  TRUNCATE TABLE t1; -- Here AUTO_INCREMENT=9 vanishes.
  FLUSH TABLES;
  -- Copy back the saved files t1.MYI and t1.MYD.
Now the table option reappears.
This may mean that the auto_increment value is retrieved from the engine.

MEMORY tables are restored differently. After TRUNCATE, the rows are
inserted one by one. So the engine can keep track of the auto_increment
value. After restore, the old value is restored.

InnoDB behaves differently. Though it is restored like MEMORY, row by
row, it still does not keep track of the auto_increment value during
restore. But it does so, if the values are inserted by SQL INSERT
statements. So what are we doing wrong?

Anyway, even if a certain auto_increment value is set explicitly with
ALTER TABLE, TRUNCATE will clear it. After data load it can get the
maximum value at best. The configured one is lost anyway.

We considered to save and restore the auto_increment value explicitly.
This would solve the above problem too. Can we live with the problem
until we implement restore of the auto_increment value?

Regards
Ingo
-- 
Ingo Strüwing, Database Group
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Geschäftsführer: Thomas Schröder,   Wolfgang Engels,   Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Häring   HRB München 161028
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