Bug #67286 InnoDB Online DDL hangs

• Submitted: 18 Oct 2012 12:04
• Modified: 18 Oct 2012 17:17
• Reporter: Shlomi Noach (OCA)
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.6.7
• OS: Linux
• Assigned to: Jon Olav Hauglid
• CPU Architecture: Any
• Tags: innodb, lock, Online DDL

[18 Oct 2012 12:04] Shlomi Noach

Description:
I am able to reproduce a ALTER TABLE on an InnoDB table which hangs forever:

node1 (sakila) > show processlist;
+----+----------+-----------+--------+---------+------+---------------------------------+-----------------------------------------+
| Id | User     | Host      | db     | Command | Time | State                           | Info                                    |
+----+----------+-----------+--------+---------+------+---------------------------------+-----------------------------------------+
|  9 | msandbox | localhost | sakila | Query   |    0 | init                            | show processlist                        |
|  8 | msandbox | localhost | sakila | Query   |  145 | Waiting for table metadata lock | alter table sakila.rental engine=innodb |
+----+----------+-----------+--------+---------+------+---------------------------------+-----------------------------------------+

How to repeat:
-- Using sakila database

-- Open two terminals.
-- On terminal #1, import sakila database (both schema and data)

-- On terminal #2:
node1 (sakila) > alter table sakila.rental engine=innodb;
> Query OK, 16044 rows affected (7.86 sec)
> Records: 16044  Duplicates: 0  Warnings: 0

-- So far so good.

-- On terminal #1, import sakila *again*, (both schema and data)

-- On terminal #2, ALTER TABLE *again*:
node1 (sakila) > alter table sakila.rental engine=innodb;

-- on terminal #2, and *while the alter table still running*, and utilizing online DDL, execute:
select min(last_update), max(last_update) from sakila.rental;
> +---------------------+---------------------+
> | min(last_update)    | max(last_update)    |
> +---------------------+---------------------+
> | 2006-02-15 21:30:53 | 2006-02-23 04:12:08 |
> +---------------------+---------------------+
1 row in set (2.02 sec)

select min(rental_date), max(return_date) from sakila.rental;
> +---------------------+---------------------+
> | min(rental_date)    | max(return_date)    |
> +---------------------+---------------------+
> | 2005-05-24 22:53:30 | 2005-09-02 02:35:22 |
> +---------------------+---------------------+
1 row in set (0.87 sec)

-- That's it: ALTER TABLE never completes.

==========================
More information:
when repeatedly invoking SHOW PROCESSSLIST from terminal #2, one can see:

node1 (sakila) > show processlist;
+----+----------+-----------+--------+---------+------+-------------+-----------------------------------------+
| Id | User     | Host      | db     | Command | Time | State       | Info                                    |
+----+----------+-----------+--------+---------+------+-------------+-----------------------------------------+
|  9 | msandbox | localhost | sakila | Query   |    0 | init        | show processlist                        |
|  8 | msandbox | localhost | sakila | Query   |    3 | System lock | alter table sakila.rental engine=innodb |
+----+----------+-----------+--------+---------+------+-------------+-----------------------------------------+
2 rows in set (0.00 sec)

node1 (sakila) > show processlist;
+----+----------+-----------+--------+---------+------+-------------------+-----------------------------------------+
| Id | User     | Host      | db     | Command | Time | State             | Info                                    |
+----+----------+-----------+--------+---------+------+-------------------+-----------------------------------------+
|  9 | msandbox | localhost | sakila | Query   |    0 | init              | show processlist                        |
|  8 | msandbox | localhost | sakila | Query   |    5 | copy to tmp table | alter table sakila.rental engine=innodb |
+----+----------+-----------+--------+---------+------+-------------------+-----------------------------------------+
2 rows in set (0.00 sec)

node1 (sakila) > show processlist;
+----+----------+-----------+--------+---------+------+---------------------------------+-----------------------------------------+
| Id | User     | Host      | db     | Command | Time | State                           | Info                                    |
+----+----------+-----------+--------+---------+------+---------------------------------+-----------------------------------------+
|  9 | msandbox | localhost | sakila | Query   |    0 | init                            | show processlist                        |
|  8 | msandbox | localhost | sakila | Query   |    9 | Waiting for table metadata lock | alter table sakila.rental engine=innodb |
+----+----------+-----------+--------+---------+------+---------------------------------+-----------------------------------------+

The third state is also the last - it never ends.

[18 Oct 2012 12:19] Shlomi Noach

For completeness, same happens on:
alter table sakila.rental row_format=compact;

I'm noting this because it may be unclear whether "ENGINE=InnoDB" is considered an online DDL operation, whereas "ROW_FORMAT=COMPACT" is indeed documented as such.

[18 Oct 2012 14:27] Jon Olav Hauglid

Hello!

Are you executing the queries in a transaction/with auto-commit off?
If so, try a COMMIT;

[18 Oct 2012 16:45] Shlomi Noach

Bingo! The import file did indeed set autocommit=0. A COMMIT on my SELECTs solved the problem.
Thank you for pointing this out. Bug can be closed as far as I am concerned.

[18 Oct 2012 17:17] Jon Olav Hauglid

Great! Happy to help. Closing the bug.

The final (short) phase of ALTER where the internal data dictionary is updated
requires exclusive access. That's why the ALTER was blocked by the active
transaction having a shared lock on the table.

Note that when you got "copy to tmp table" in the SHOW PROCESSLIST output
earlier, it means that ALTER was executing by copying to a temporary table
(i.e. ALGORITHM=COPY). And not in-place modification of the table 
(i.e. ALGORITHM=INPLACE).

Generally COPY is the fallback option for operations which are not
(yet!) supported by INPLACE.