From: Date: June 18 2007 2:59pm
Subject: Re: CREATE INDEX is not committed?
List-Archive: http://lists.mysql.com/internals/34742
Message-Id: <20070618125920.GA5135@mysql.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hello Marko,

On Mon, Jun 18, 2007 at 12:06:33PM +0300, Marko M=E4kel=E4 wrote:
> On Wed, Jun 13, 2007 at 04:35:35PM +0200, Guilhem Bichot wrote:
> > On Wed, Jun 13, 2007 at 03:56:34PM +0300, Marko M=E4kel=E4 wrote:
> > > I wonder what the correct behavior is.  I would expect MySQL to com=
mit
> > > before and after handler::add_index().
> >=20
> > After: I agree. Before: why?
>=20
> If mysqld is killed during data dictionary operations, I suspect that
> the rollback after InnoDB crash recovery will not delete or un-rename
> the .frm file.  In this case (fast ALTER TABLE), MySQL could believe
> that the table was altered.  Based on the output of SHOW CREATE TABLE,
> the user would assume that all preceding transactions were committed.

Anyway, a commit before the add_index() wouldn't help: the new frm
would be committed and not be consistent with InnoDB's dictionary
which hasn't added the index. Or I don't understand.
If we view the frm and innodb's dictionary as two resources, to have
them always in sync after a crash we would need two-phase commit
between them. In sql_table.cc in 5.1, we have the "ddl_log" (used only
when altering partitions if I remember correctly), which can be used
to finish re-partitioning if a crash happened in the middle (it
handles delete/replace/create operations on tables, which happen
during re-partitioning). It could be extended to handle any crash
during ALTER TABLE (out of partitions), but that would require help
from the engine. For example, at restart we find out in this ddl_log
that we wanted to create a new index in InnoDB, we would ask InnoDB to
create the new index, and InnoDB would create if if not already (but
give no error if already created, for the ddl_log to be idempotent).

Something for the future I guess ;)

--=20
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Mr. Guilhem Bichot <guilhem@stripped>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Lead Software Engineer
/_/  /_/\_, /___/\___\_\___/   Bordeaux, France
       <___/   www.mysql.com  =20