Re: Please advise [Re: Restore locking: TRUNCATE resets AUTO_INCREMENT on InnoDB]

[Jørgen Løland]

Ingo Strüwing wrote:
> Hi team,
> 
> Ingo Strüwing, 24.07.2009 11:29:
> 
> ...
>> 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?
> 
> 
> An idea to solve this was to retrieve the auto_increment value from the
> tables before they are truncated. And then set these values after data
> restore.
> 
> This would improve the situation a lot, but still leave stray INSERTs a
> chance to increment the values between CREATE TABLE and LOCK TABLES.
> 
> Not that a big RESTORE creates a lot of tables and other database
> objects, which can take a remarkable time. Only then all tables are
> locked in one go.
> 
> Still we would have two options to make this safe:
> 
> A) Explicitly transport the auto_increment value.
>    A.1) Since the value is part of the meta data of a table in the
>         backup image, we could modify si_objects to save the value
>         in a context object so that the value used to create the last
>         table can be queried from si_objects. However, this means that
> 	si_objects will have to parse the serialization string.
>    A.2) Accept the problem until we change the backup image format.
>    A.3) Do A.1 now and change it after A.2 is implemented.
>         But the A.1 code cannot go away for backward compatibility.

With all the problems we had when parsing SQL to check that grants were 
not tampered with, I recommend that we do not make any attempts at 
parsing the SQL. Just think of the problems we'll get in if somebody 
decides to name a column 'auto_increment'. I agree with Susanne that the 
users will expect that the AUTO_INCREMENT value has not changed after 
RESTORE, and I agree with Kostja that the best way of solving this is 
most likely to store the value in the image.

However, I think it is acceptable to postpone this fix to Betony since 
backup is an experimental feature in Azalea. As such, A.2 looks most 
promising to me.

-- 
Jørgen Løland