List:Backup« Previous MessageNext Message »
From:Ingo Strüwing Date:July 27 2009 4:26pm
Subject:Re: Please advise [Re: Restore locking: TRUNCATE resets AUTO_INCREMENT
on InnoDB]
View as plain text  
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.

B) Ask for a meta data locking scheme, which takes meta data locks
before table creation, which stay until after the data load and the
final FLUSH TABLES. Then we could extract the auto_increment values from
the tables after their creation and set these back after data load.

There has not been a decision so far.

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