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

Hello Marko, Ramil,

On Mon, Jun 18, 2007 at 03:38:05PM +0300, Marko M=E4kel=E4 wrote:

> Sorry, I was looking at an older MySQL tree that created the primary ke=
y in
> the non-smart way.  Also the newer tree reaches the lines you are askin=
g for,
> but ha_commit_trans() does not invoke innobase_commit, probably because
> thd->transaction is empty:
>=20
> $5 =3D {savepoints =3D 0x0, all =3D {nht =3D 0, no_2pc =3D false, ht =3D=
 {
>       0x0 <repeats 15 times>}}, stmt =3D {nht =3D 0, no_2pc =3D false, =
ht =3D {
>       0x0 <repeats 15 times>}}, on =3D true, xid =3D {formatID =3D 0,=20
>     gtrid_length =3D 0, bqual_length =3D 0, data =3D '\0' <repeats 127 =
times>},=20
>   xa_state =3D XA_NOTR, xid_state =3D {xid =3D {formatID =3D -1, gtrid_=
length =3D 20,=20
>       bqual_length =3D 0,=20
>       data =3D "MySQLXid\001\000\000\000\003", '\0' <repeats 114 times>=
},=20
>     xa_state =3D XA_NOTR, in_thd =3D true}, m_pending_rows_event =3D 0x=
0,=20
>   changed_tables =3D 0x0, mem_root =3D {free =3D 0x88b74a8, used =3D 0x=
0,=20
>     pre_alloc =3D 0x88b74a8, min_malloc =3D 32, block_size =3D 8136, bl=
ock_num =3D 4,=20
>     first_block_usage =3D 0,=20
>     error_handler =3D 0x81b4034 <sql_alloc_error_handler>}}
>=20
> Note that thd->transaction.all.nht =3D=3D 0 and thd->transaction.stmt.n=
ht =3D=3D 0.
>=20
> I suppose that thd->transaction is cleared earlier in
> mysql_alter_table(), when the transaction is committed before invoking
> handler::add_index():
>=20
>   else
>   {
>     VOID(pthread_mutex_lock(&LOCK_open));
>     wait_while_table_is_used(thd, table, HA_EXTRA_FORCE_REOPEN);
>     table->file->ha_external_lock(thd, F_WRLCK);
>     alter_table_manage_keys(table, table->file->indexes_are_disabled(),
>                             alter_info->keys_onoff);
>     table->file->ha_external_lock(thd, F_UNLCK);
>     VOID(pthread_mutex_unlock(&LOCK_open));
>     error=3D ha_commit_stmt(thd);
>     if (ha_commit(thd))
>       error=3D 1;
>   }
>=20
> Before the call to ha_commit_stmt(), we have thd->transaction.stmt like
> this:
>=20
> {nht =3D 1, no_2pc =3D false, ht =3D { 0x87953a8, 0x0 <repeats 14 times=
>}}
>=20
> That is the InnoDB handlerton.  After the call, the handlerton array is
> empty.
>=20
> This can be fixed by re-registering the handler at
> ha_innobase::add_index(), but I would prefer it if mysql_alter_table()
> called ha_innobase::start_stmt() or delayed the commit until the very
> end.

Thanks for the debugging, now it's clear.
Indeed, something was changed recently (June 1st), the ha_commit was
moved up to earlier than before (it was to fix BUG#28652). This was a
bugfix in 5.0 and when merged into 5.1 it probably made a new bug as
the commit now happens before add_index(), and thus InnoDB does not
receive any commit order for its open table on which it has just added
an index. Can be just the usual automerge problem.

Ramil, please consider my suggestion: we could move the commit a
little down, to after add_index() and drop_index(), in other words, to
just before we unlock tables:

- move ha_commit stuff here -
  if (table->s->tmp_table !=3D NO_TMP_TABLE)
  {
    /* We changed a temporary table */
    if (error)
      goto err1;
etc

I would even do the commit independently of if "new_table" is NULL or
not: if new_table!=3DNULL, it still makes sense to commit the changes
made to this temporary table. I think the pre-bug-fix code committed
in all cases. What do you think?

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