MySQL Bugs: #40452: index added online block DML on the same mysqld

Bug #40452	index added online block DML on the same mysqld
Submitted:	31 Oct 2008 10:33	Modified:	14 Nov 2008 20:22
Reporter:	Cyril SCETBON	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.1+	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
When we add an index online on a table (Millions of lines) DML orders on the same mysqld are blocked. However DML on other mysqld are working.

How to repeat:
create table test(id1 bigint,id2 bigint) engine=ndb;
insert into test select round(rand()*1000),round(rand()*1000);
insert into test select round(rand()*1000),round(rand()*1000) from test limit 200000;
insert into test select round(rand()*1000),round(rand()*1000) from test limit 200000;
insert into test select round(rand()*1000),round(rand()*1000) from test limit 200000;
insert into test select round(rand()*1000),round(rand()*1000) from test limit 200000;
insert into test select round(rand()*1000),round(rand()*1000) from test limit 200000;

TEST 1
------

on mysqld1 :
mysql> alter online table test add index(id2);
Query OK, 0 rows affected (5.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

on mysqld1 (another connection) :
mysql> select SQL_NO_CACHE id1 from test where id2=765 limit 1;
+------+
| id1  |
+------+
|   91 | 
+------+
1 row in set (4.13 sec)

as you can see it takes 4 seconds cause it waits for the end alter.

TEST 2
------

on mysqld1 :
mysql> alter online table test drop index id2;
Query OK, 0 rows affected (1.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter online table test add index(id2);
Query OK, 0 rows affected (5.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

on mysqld2 :
mysql>  select SQL_NO_CACHE id1 from test where id2=765 limit 1;
+------+
| id1  |
+------+
|  561 | 
+------+
1 row in set (0,96 sec)

as you can see it lasts less than 1 second cause it DOES NOT wait for the end alter executed on the first mysqld

This is a known behavior and limitation. Indexes are added online on the data nodes but the  MySQL Server executing this DDL still holds a lock during the operation. Other MySQL Servers can safely access the table.

If it's a known behaviour where is it documented ?

Changed bug category to Docs.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.