MySQL Bugs: #49881: alter table on partition table will lock the table, no queries can read

Bug #49881	alter table on partition table will lock the table, no queries can read
Submitted:	22 Dec 2009 16:20	Modified:	4 Jan 2011 23:50
Reporter:	Chris C	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.1.41, 5.1.42, 5.5	OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any
Tags:	regression

Description:
When doing certain alter table commands that would build a temporary table, allowing the original table to remain open for queries, partitioned tables remain locked. Documentation states they should be readable:

In most cases, ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, and then the original table is deleted and the new one is renamed. While ALTER TABLE is executing, the original table is readable by other sessions. 

However, if the table is partitioned, this doesn't appear to be the case. Two statements I know will reproduce:

alter table <table_name> engine = innodb;

alter table <table_name> rebuild partition <partition name>;

I've marked this serious. We have no workaround currently. There is no way to rebuild fragmented tables in production as they lock all queries. May be similar to bugs 43867 and 46654, but those are deadlocks, these seem to be locks where a change to the table structure that prevents reads.

How to repeat:
New install of 5.5. Only option in my.cnf is innodb_file_per_table. 

create database ptest;

use ptest;

create table ptest.partition_test (id int auto_increment, date_inserted datetime, variable_name varchar(64), variable_value varchar(1024), primary key (id, date_inserted)) engine = innodb partition by range (to_days(date_inserted)) (partition y2009 values less than (to_days('2010-01-01')), partition y2010 values less than (to_days('2011-01-01')));

use information_schema;

insert into ptest.partition_test (variable_name, variable_value) select g1.variable_name, g1.variable_value from global_variables g1, global_variables g2, global_variables g3;

(NOTE - this could take a while - necessary to create a big table in order to make sure the alter table rebuild takes a while. Once done, in another session:)

use ptest;

select * from partition_test limit 10;

(This is to ensure you can query the table quickly and get results. Then, back in original session:)

alter table ptest.partition_test engine = innodb;

(Then, go to the new session again:)

select * from partition_test limit 10;

(This will hang. In a third session you can do a processlist and see it is waiting on the table.)

mysql> show processlist;
+----+------+-----------+--------------------+---------+------+-------------------+--------------------------------------------------+
| Id | User | Host      | db                 | Command | Time | State             | Info                                             |
+----+------+-----------+--------------------+---------+------+-------------------+--------------------------------------------------+
|  1 | root | localhost | information_schema | Query   |  530 | copy to tmp table | alter table ptest.partition_test engine = innodb |
|  2 | root | localhost | ptest              | Query   |  529 | Waiting for table | select * from partition_test limit 10            |
|  3 | root | localhost | NULL               | Query   |    0 | NULL              | show processlist                                 |
+----+------+-----------+--------------------+---------+------+-------------------+--------------------------------------------------+
3 rows in set (0.04 sec)

Once the alter table is finished, the query will finally finish...

mysql> select * from partition_test limit 10;
+----+---------------------+--------------------------------+----------------+
| id | date_inserted       | variable_name                  | variable_value |
+----+---------------------+--------------------------------+----------------+
|  1 | 0000-00-00 00:00:00 | MAX_PREPARED_STMT_COUNT        | 16382          |
|  2 | 0000-00-00 00:00:00 | CHARACTER_SET_CONNECTION       | latin1         |
|  3 | 0000-00-00 00:00:00 | HAVE_CRYPT                     | YES            |
|  4 | 0000-00-00 00:00:00 | CONCURRENT_INSERT              | 1              |
|  5 | 0000-00-00 00:00:00 | INNODB_VERSION                 | 1.0.5          |
|  6 | 0000-00-00 00:00:00 | AUTOMATIC_SP_PRIVILEGES        | ON             |
|  7 | 0000-00-00 00:00:00 | CHARACTER_SET_DATABASE         | latin1         |
|  8 | 0000-00-00 00:00:00 | BACK_LOG                       | 50             |
|  9 | 0000-00-00 00:00:00 | INNODB_LOCKS_UNSAFE_FOR_BINLOG | OFF            |
| 10 | 0000-00-00 00:00:00 | LOG_QUERIES_NOT_USING_INDEXES  | OFF            |
+----+---------------------+--------------------------------+----------------+
10 rows in set (11 min 9.34 sec)

Suggested fix:
Allow table and partition rebuilds to behave like non-partitioned tables. Queries should be allowed on the original table while the rebuild table is created.

Thank you for the report.

Verified as described.

To repeat modify sources as follow:

$bzr diff
=== modified file 'sql/sql_table.cc'
--- sql/sql_table.cc    2009-12-16 22:48:55 +0000
+++ sql/sql_table.cc    2009-12-23 07:14:05 +0000
@@ -7184,6 +7184,7 @@
     new_table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
     new_table->next_number_field=new_table->found_next_number_field;
     thd_proc_info(thd, "copy to tmp table");
+sleep(60);
     error= copy_data_between_tables(table, new_table,
                                     alter_info->create_list, ignore,
                                     order_num, order, &copied, &deleted,

Then run test and compare time:

--source include/have_innodb.inc

create table partition_test(
id int auto_increment,
date_inserted datetime,
variable_name varchar(64),
variable_value varchar(1024),
primary key (id, date_inserted))
engine = innodb
partition by range (to_days(date_inserted)) (
partition y2009 values less than (to_days('2010-01-01')), 
partition y2010 values less than (to_days('2011-01-01'))
)
;

insert into partition_test (date_inserted, variable_name, variable_value) values(now(), 'foo', 'bar');
insert into partition_test (date_inserted, variable_name, variable_value) values(date_add(now(), interval 1 month), 'foo', 'bar');
insert into partition_test (date_inserted, variable_name, variable_value) values(date_add(now(), interval 2 month), 'foo', 'bar');
insert into partition_test (date_inserted, variable_name, variable_value) values(date_add(now(), interval 3 month), 'foo', 'bar');
insert into partition_test (date_inserted, variable_name, variable_value) values(date_add(now(), interval 4 month), 'foo', 'bar');
insert into partition_test (date_inserted, variable_name, variable_value) values(date_add(now(), interval 5 month), 'foo', 'bar');
insert into partition_test (date_inserted, variable_name, variable_value) values(date_add(now(), interval 6 month), 'foo', 'bar');
insert into partition_test (date_inserted, variable_name, variable_value) values(date_add(now(), interval 7 month), 'foo', 'bar');
insert into partition_test (date_inserted, variable_name, variable_value) values(date_add(now(), interval 8 month), 'foo', 'bar');
insert into partition_test (date_inserted, variable_name, variable_value) values(date_add(now(), interval 9 month), 'foo', 'bar');
insert into partition_test (date_inserted, variable_name, variable_value) values(date_add(now(), interval 10 month), 'foo', 'bar');
insert into partition_test (date_inserted, variable_name, variable_value) values(date_add(now(), interval 11 month), 'foo', 'bar');
insert into partition_test (date_inserted, variable_name, variable_value) values(date_add(now(), interval 12 month), 'foo', 'bar');

--send alter table partition_test engine = innodb

connect (addconroot, localhost, root,,);
connection addconroot;

select now();
select * from partition_test;
select now();

connection default;
--reap
select now();

Problem is not repeatable with version 5.1, option --innodb_file_per_table does not matter.

You don't reproduce with 5.1? What innodb version? I can reproduce with 5.1 and the innodb plugin version 1.0.4.

Plugin version 1.0.5 I think. I tried development sources with bundled plugin.

Not - I just downloaded the latest mysql version: 5.1.41. The issue reproduced there. That build doesn't have the innodb plugin. I can download the 1.0.6 and test, but I'm curious - can you verify that you did not reproduce this and on what version? If there's a version that works, we'd love to try to see if we can workaround this. It's killing us right now...

Tried on 5.1.42 and innodb plugin 1.0.6 and reproduced again. I believe there is no workaround and no version that currently does NOT reproduce the issue.

This is repeatable with InnoDB of course, but only applicable to Plugin.

And I had mistake: repeatable with 5.1 for me too.

This is fixed by the patch for bug#46654.

The problem was that the partitioning alter code removed all instances of the table from the table cache, when it was sufficient to remove itself. That lead to all tables needed reopen, which was then waiting on the alter to finish.

Closing as duplicate.

I'm not able to test that fix unfortunately, as I don't have 5.6 nor 6.0. There is a backport request to 5.1, but I do not see it yet. If we can get the backport of the patch to 5.1, we'll test the fix. 
Thanks

Set back to "Verified" as 5.1 bug

Issue fully fixed in 5.5 (see bug#56172). Will not be back ported to 5.1