Bug #60388 while creating a secondary index with plugin, queries are blocked until it ends
Submitted: 8 Mar 2011 11:56 Modified: 10 Mar 2011 20:32
Reporter: luo longjiu Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.5.9,5.1.48 OS:Linux
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D3 (Medium)

[8 Mar 2011 11:56] luo longjiu
Description:
Test1:
InnoDB plugin 1.0.9/MySQL 5.1.48
root@test 10:09:51>desc test_plg;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | YES  | MUL | NULL    |                |
| name2 | varchar(30) | YES  |     | NULL    |                |
| dd    | datetime    | YES  |     | NULL    |                |
| dd2   | datetime    | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

$mysql -uroot 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 420689
Server version: 5.1.48-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@(none) 09:49:50>select @@innodb_version;
+------------------+
| @@innodb_version |
+------------------+
| 1.0.9            |
+------------------+
Sesion1:
 root@test 09:56:11>select * from test_plg where id =1;
+----+---------+-------+---------------------+---------------------+
| id | name    | name2 | dd                  | dd2                 |
+----+---------+-------+---------------------+---------------------+
|  1 | ssdsdsd | sdxss | 2011-03-07 22:03:26 | 2011-03-07 22:03:26 |
+----+---------+-------+---------------------+---------------------+
1 row in set (0.00 sec)

Session2:create a secondary index:
root@test 10:03:47>alter table test_plg add  index ind_name(name);
Query OK, 0 rows affected (37.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

Session1:
root@test 10:03:50>select * from test_plg where id =1;
+----+---------+-------+---------------------+---------------------+
| id | name    | name2 | dd                  | dd2                 |
+----+---------+-------+---------------------+---------------------+
|  1 | ssdsdsd | sdxss | 2011-03-07 22:03:26 | 2011-03-07 22:03:26 |
+----+---------+-------+---------------------+---------------------+
1 row in set (30.59 sec)

root@(none) 11:15:42>show full processlist;
  Id: 420689
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 19
  State: manage keys
   Info: alter table test_plg add  index ind_name(name)
*************************** 3. row ***************************
     Id: 425388
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 16
  State: Waiting for table
   Info: select * from test_plg where id =1

Test2:
InnoDB plugin 1.1.5/MySQL 5.5.9:
$mysql -uroot --socket /home/dongkai.zmj/mysql-5.5.9-linux2.6-x86_64/run/mysql.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17626
Server version: 5.5.9-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@(none) 09:50:21>select @@innodb_version;
+------------------+
| @@innodb_version |
+------------------+
| 1.1.5            |
+------------------+
1 row in set (0.00 sec)

Sesion1:
root@test 10:07:51>select * from test_plg where id=1;
+----+---------+-------+---------------------+---------------------+
| id | name    | name2 | dd                  | dd2                 |
+----+---------+-------+---------------------+---------------------+
|  1 | ssdsdsd | sdxss | 2011-03-07 21:51:44 | 2011-03-07 21:51:44 |
+----+---------+-------+---------------------+---------------------+
1 row in set (0.00 sec)

Session2:create a secondary index:
root@test 10:07:56>alter table test_plg add index ind_name(name);
Query OK, 0 rows affected (1 min 47.59 sec)
Records: 0  Duplicates: 0  Warnings: 0
Session1:
root@test 10:08:05>select * from test_plg where id=1;
+----+---------+-------+---------------------+---------------------+
| id | name    | name2 | dd                  | dd2                 |
+----+---------+-------+---------------------+---------------------+
|  1 | ssdsdsd | sdxss | 2011-03-07 21:51:44 | 2011-03-07 21:51:44 |
+----+---------+-------+---------------------+---------------------+
1 row in set (1 min 45.17 sec)

root@(none) 11:15:42>show full processlist;
+-------+------+-----------+------+---------+------+---------------------------------+-----------------------------------------------+
| Id    | User | Host      | db   | Command | Time | State                           | Info                                          |
+-------+------+-----------+------+---------+------+---------------------------------+-----------------------------------------------+
| 17626 | root | localhost | test | Query   |    6 | manage keys                     | alter table test_plg add index ind_name(name) |

How to repeat:
CREATE TABLE `test_plg` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `name2` varchar(30) DEFAULT NULL,
  `dd` datetime DEFAULT NULL,
  `dd2` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ind_name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=gbk;

Insert 200 million rows ;

session1:
alter table test_plg add  index ind_name(name);

session2:
select * from test_plg where id=1;

mysql -uroot -e "show full processlist";
[8 Mar 2011 12:05] luo longjiu
detail

Attachment: while creating a secondary index with plugin, queries are blocked until it ends.docx (application/vnd.openxmlformats-officedocument.wordprocessingml.document, text), 19.00 KiB.

[8 Mar 2011 12:38] Valeriy Kravchuk
Please, send the output of

select @@autocommit;

from your environment.
[8 Mar 2011 15:51] luo longjiu
root@(none) 11:49:14>select @@autocommit
    -> ;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)
[8 Mar 2011 15:59] luo longjiu
CREATE TABLE `test_plg` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `name2` varchar(30) DEFAULT NULL,
  `dd` datetime DEFAULT NULL,
  `dd2` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=gbk;

there is no secondary indexes in the table  before inserting rows;
[10 Mar 2011 20:27] Sveta Smirnova
Thank you for the report.

Verified as described in version 5.1. Since version 5.5.10 problem is not repeatable.
[10 Mar 2011 20:32] Sveta Smirnova
This is duplicate of bug #42230 really which is fixed in 5.5.10