// 5.5.45

scripts/mysql_install_db --basedir=/export/umesh/server/binaries/mysql-5.5.45 --datadir=/export/umesh/server/binaries/mysql-5.5.45/77318
bin/mysqld --basedir=/export/umesh/server/binaries/mysql-5.5.45 --datadir=/export/umesh/server/binaries/mysql-5.5.45/77318 --core-file --socket=/tmp/mysql_ushastry.sock  --port=15000 --log-error=/export/umesh/server/binaries/mysql-5.5.45/77318/log.err 2>&1 &

create database if not exists test;
use test;
drop table if exists sbtest1;
CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB
/*!50100 PARTITION BY HASH (id)
PARTITIONS 8 */;

set @id:=0;
insert into `sbtest1` values 
 (@id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000))
,(@id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000))
,(@id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000))
,(@id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000));

insert into `sbtest1`(`id`,`k`,`c`,`pad`) select  @id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000)  from `sbtest1` k1, `sbtest1` k2, `sbtest1` k3, `sbtest1` k4,`sbtest1` k5,`sbtest1` k6, `sbtest1` k7, `sbtest1` k8, `sbtest1` k9,`sbtest1` k0,`sbtest1` ka, `sbtest1` kb, `sbtest1` kc, `sbtest1` kd limit 5000000;  

## Session 1 ALTER..

mysql> alter table sbtest1 add key(c);
Query OK, 0 rows affected (28.50 sec)
Records: 0  Duplicates: 0  Warnings: 0


## Session 2 SELECT..


mysql>  select * from sbtest1 limit 5;
+----+----+----------------------------------+----------------------------------+
| id | k  | c                                | pad                              |
+----+----+----------------------------------+----------------------------------+
|  1 |  2 | b72d798359ffde2d05d39dbf74874eff | d6ca5ca679401da61db9fa1f77c3f4eb |
|  9 | 10 | 81726190d537a65e449c41145bc206d8 | 0fe5dad13dd881fea359b26233d33560 |
| 17 | 18 | a8a87bbf1fc794957793a15228e78f01 | facbfe5c05cf1c4638c1d4c00bac76ac |
| 25 | 26 | d0b6d848e8345e806a46e9c3388bbc24 | a2e0452e8989122589a8795272d5c21b |
| 33 | 34 | 4e171bdb0f2944ed7d2e2a8350dc5c84 | 91f6f2823254698c587f67fc3a349a2c |
+----+----+----------------------------------+----------------------------------+
5 rows in set (26.31 sec)

## Session 3 SHOW PROCESSLIST..

mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+--------------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                           |
+----+------+-----------+------+---------+------+---------------------------------+--------------------------------+
|  1 | root | localhost | test | Query   |   29 | manage keys                     | alter table sbtest1 add key(c) |
|  2 | root | localhost | test | Query   |   26 | Waiting for table metadata lock | select * from sbtest1 limit 5  |
|  3 | root | localhost | NULL | Query   |    0 | NULL                            | show processlist               |
+----+------+-----------+------+---------+------+---------------------------------+--------------------------------+
3 rows in set (0.00 sec)


############ non-partitioned

create database if not exists test;
use test;
drop table if exists sbtest1;
CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB;

set @id:=0;
insert into `sbtest1` values 
 (@id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000))
,(@id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000))
,(@id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000))
,(@id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000));

insert into `sbtest1`(`id`,`k`,`c`,`pad`) select  @id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000)  from `sbtest1` k1, `sbtest1` k2, `sbtest1` k3, `sbtest1` k4,`sbtest1` k5,`sbtest1` k6, `sbtest1` k7, `sbtest1` k8, `sbtest1` k9,`sbtest1` k0,`sbtest1` ka, `sbtest1` kb, `sbtest1` kc, `sbtest1` kd limit 5000000;  


## Session 1 ALTER..

mysql> alter table sbtest1 add key(c);
Query OK, 0 rows affected (29.60 sec)
Records: 0  Duplicates: 0  Warnings: 0


## Session 2 SELECT..


mysql>  select * from sbtest1 limit 5;
+----+----+----------------------------------+----------------------------------+
| id | k  | c                                | pad                              |
+----+----+----------------------------------+----------------------------------+
|  1 |  2 | 9d0cf260b5c977213e7b7b7b63a17712 | 4426b5c9075a232690ea318d4e118082 |
|  3 |  4 | 2a7ce594746907f7a6f66e01aaefc478 | 99c76690342beb6b229dd62432c470d1 |
|  5 |  6 | 83fa8e22dc6a83391381e8f2785b42df | 24701088808ab228269cd1b766c38ef3 |
|  7 |  8 | b9fcf0753ffb2612d11af75acce6a532 | 169f502fb6e26c1e3cc6de6675448a32 |
|  9 | 10 | 5d4e869e5676bb1f249f0f55c3978cc5 | 283929560f7c4c6552105543dbbfeae7 |
+----+----+----------------------------------+----------------------------------+
5 rows in set (0.00 sec)


## Session 3 SHOW PROCESSLIST..

mysql> show processlist;
+----+------+-----------+------+---------+------+-------------+--------------------------------+
| Id | User | Host      | db   | Command | Time | State       | Info                           |
+----+------+-----------+------+---------+------+-------------+--------------------------------+
|  1 | root | localhost | test | Query   |    5 | manage keys | alter table sbtest1 add key(c) |
|  2 | root | localhost | test | Sleep   |    2 |             | NULL                           |
|  3 | root | localhost | NULL | Query   |    0 | NULL        | show processlist               |
+----+------+-----------+------+---------+------+-------------+--------------------------------+
3 rows in set (0.00 sec)