From: Ingo Strüwing
Date: August 31 2009 6:28pm
Subject: Meeting preparation [Re: Restore locking: TRUNCATE resets
 AUTO_INCREMENT on InnoDB]
List-Archive: http://lists.mysql.com/backup/20
Message-Id: <4A9C165C.4030008@sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8BIT

Hi Lars,

Konstantin suggested to change restore locking to this pattern:

1) Take BML (backup meta data lock)
2) Restore DDL (DROP/CREATE)
3) LOCK TABLES <> WRITE
4) TRUNCATE TABLES <>
5) Restore data
6) FLUSH TABLES
7) UNLOCK TABLES

I implemented this in WL#4844/Bug#42895.

It solved our locking problems, especially Bug#40944 and Bug#41716.

However, a new problem arose: TRUNCATE TABLES resets the AUTO_INCREMENT
value to zero. MyISAM and MEMORY restore the value from the maximum
index value, which is good in most cases, but can be wrong if an
explicit value has been set right before BACKUP. InnoDB leaves the value
at zero, which is pretty wrong.

I see 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.


Jørgen votes for A.2. For a later version, we should store the original
value in the backup image explicitly. (Not try to parse it from the meta
data statements).

Konstantin votes for storing the original value in the backup image
explicitly.

I, myself agree with Jørgen (and thus with Konstantin, but only in a
later version). This approach would fix WL#4844/Bug#42895 and thus
Bug#40944 and Bug#41716 with minimal effort.

 
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