Bug #40452 index added online block DML on the same mysqld

• Submitted: 31 Oct 2008 11:33
• Modified: 14 Nov 2008 21:22
• Reporter: Cyril SCETBON
• Status: Closed
• Category: Server: Docs
• Severity: S2 (Serious)
• Version: 5.1+
• OS: Any
• Assigned to: Jon Stephens

[31 Oct 2008 11:33] Cyril SCETBON

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

[12 Nov 2008 15:01] Bernhard Ocklin

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.

[13 Nov 2008 10:19] Cyril SCETBON

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

[14 Nov 2008 8:45] Jon Stephens

Changed bug category to Docs.

[14 Nov 2008 21:22] Jon Stephens

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.