Hello,

TokuDB supports inplace alter table operations including inplace
column addition. While implementing this feature on MySQL 5.6 we
encountered a schema inconsistency problem that occurs if mysqld
crashes in the right place during an inplace alter table operation
(described below). We uses the FRM discovery mechanism to resync
MySQL's view of the schema with the TokuDB view of the schema. We
also implemented FRM discovery in the partition storage engine. If
MySQL made the FRM rename operation transactional, then there would be
no need to synchronize the schema with the discovery mechanism.

Here are some details. Suppose that one has a simple table:

create table t (a int primary key);

and one adds a new column to the table:

alter table t add column b int;

If mysqld crashes after the inplace alter operation is committed and
the table is closed but before the new FRM file is renamed, then when
mysqld comes up after the crash, the table schema described by the FRM
(the old FRM) is inconsistent with the engine's schema since the new
schema was committed.

TokuDB uses FRM discovery to resync the FRM schema with the engine's
view of the schema.

Now, suppose that one has a partitioned table:

create table t (a int primary key) partition by...

and one adds a new column to the table:

alter table t add column b int;

If mysqld crashes during the alter table operation, the table schema
described by the old FRM is inconsistent with the engines' schema. If
the partition storage engine supported FRM discovery, then the
inconsistency could be resolved. Unfortunately, it does not.

Just for fun, we implemented FRM discovery in the partition storage
engine. It retrieves the FRM from the engine (if possible) and
returns it.

The problem with inplace alter table operations isthat the rename of
the FRM schema is not transactional. Are there any plans to address
this?

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals

Hello Rich!

* Rich Prohaska <prohaska (AT) tokutek (DOT) com> [12/09/09 00:48]:
This not only problem with in-place operations in 5.6, it is
also present in 5.1/5.5 and also exists in some form for ALTER TABLE
executed through creation and copying data to temporary table.

So the problem is not really something new and was there almost
forever.

We have already bugs reported about this and similar issues
(for example, see http://bugs.mysql.com/bug.php?id=25922).
But since this is pre-existing problem, which doesn't happen often,
and fixing it requires fairly big changes to ALTER TABLE/other DDL
it is unlikely that this will happen in 5.6 timeframe.

Best regards,
Dmitry

--
Dmitry Lenev, Software Developer
Oracle Development SPB/MySQL, www.mysql.com

Are you MySQL certified? http://www.mysql.com/certification

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals

Hello

How does one recover from crashes that occur when altering a partitioned table, especially if the operation is committed on the storage engine but the frm is old?

Rich Prohaska

On Sep 12, 2012, at 3:41 AM, Dmitry Lenev <Dmitry.Lenev (AT) oracle (DOT) com> wrote:

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals

Hello Rich!

* Rich Prohaska <prohaska7 (AT) gmail (DOT) com> [12/09/12 18:55]:
The only way that comes to my mind is to finish .frm renaming manually
(i.e. by manually renaming .frm files on disk).

But, indeed, this is not a satisfactory solution for normal users and
a better solution is necessary (e.g. automatic recovery/redo in some
form).

--
Dmitry Lenev, Software Developer
Oracle Development SPB/MySQL, www.mysql.com

Are you MySQL certified? http://www.mysql.com/certification

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals