From: Ingo Strüwing
Date: July 24 2009 9:29am
Subject: Please advise [Re: Restore locking: TRUNCATE resets AUTO_INCREMENT on
 InnoDB]
List-Archive: http://lists.mysql.com/backup/3
Message-Id: <4A697F01.70909@sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8BIT

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