From: Ingo Strüwing
Date: July 28 2009 6:38am
Subject: Re: Restore locking: TRUNCATE resets AUTO_INCREMENT on InnoDB
List-Archive: http://lists.mysql.com/backup/11
Message-Id: <4A6E9CFA.5000800@sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8BIT

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.

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.

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