Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
5.5.35
-
None
-
None
-
None
-
CentOS (64-bit)
Description
While checking progress of ALTER TABLE, I found that "progress" value in SHOW PROCESSLIST and information_schema.processlist table are different. In fact, information_schema.processlist table is gives accurate progress for ALTER TABLE.
[root@centos nil]# mysql -uroot -proot -e "select ID, USER, HOST, DB, TIME, STATE, INFO, STAGE, MAX_STAGE, PROGRESS from information_schema.processlist where ID = 2; show processlist"
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
| ID | USER | HOST | DB | TIME | STATE | INFO | STAGE | MAX_STAGE | PROGRESS |
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
| 2 | root | localhost | nil | 3 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 1 | 2 | 4.279 |
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
| 2 | root | localhost | nil | Query | 3 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 2.140 |
|
| 29 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
[root@centos nil]#
|
[root@centos nil]# mysql -uroot -proot -e "select ID, USER, HOST, DB, TIME, STATE, INFO, STAGE, MAX_STAGE, PROGRESS from information_schema.processlist where ID = 2; show processlist"
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
| ID | USER | HOST | DB | TIME | STATE | INFO | STAGE | MAX_STAGE | PROGRESS |
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
| 2 | root | localhost | nil | 8 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 1 | 2 | 14.304 |
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
| 2 | root | localhost | nil | Query | 9 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 7.152 |
|
| 31 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
[root@centos nil]#
|
[root@centos nil]#
|
[root@centos nil]# mysql -uroot -proot -e "select ID, USER, HOST, DB, TIME, STATE, INFO, STAGE, MAX_STAGE, PROGRESS from information_schema.processlist where ID = 2; show processlist"
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
| ID | USER | HOST | DB | TIME | STATE | INFO | STAGE | MAX_STAGE | PROGRESS |
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
| 2 | root | localhost | nil | 14 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 1 | 2 | 25.986 |
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
| 2 | root | localhost | nil | Query | 15 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 12.993 |
|
| 32 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
[root@centos nil]# mysql -uroot -proot -e "select ID, USER, HOST, DB, TIME, STATE, INFO, STAGE, MAX_STAGE, PROGRESS from information_schema.processlist where ID = 2; show processlist"
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
| ID | USER | HOST | DB | TIME | STATE | INFO | STAGE | MAX_STAGE | PROGRESS |
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
| 2 | root | localhost | nil | 20 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 1 | 2 | 37.117 |
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
| 2 | root | localhost | nil | Query | 21 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 18.558 |
|
| 33 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
[root@centos nil]#
|
[root@centos nil]#
|
[root@centos nil]# mysql -uroot -proot -e "select ID, USER, HOST, DB, TIME, STATE, INFO, STAGE, MAX_STAGE, PROGRESS from information_schema.processlist where ID = 2; show processlist"
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
| ID | USER | HOST | DB | TIME | STATE | INFO | STAGE | MAX_STAGE | PROGRESS |
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
| 2 | root | localhost | nil | 25 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 1 | 2 | 45.613 |
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
| 2 | root | localhost | nil | Query | 25 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 22.807 |
|
| 34 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
[root@centos nil]#
|
[root@centos nil]#
|
[root@centos nil]# mysql -uroot -proot -e "select ID, USER, HOST, DB, TIME, STATE, INFO, STAGE, MAX_STAGE, PROGRESS from information_schema.processlist where ID = 2; show processlist"
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
| ID | USER | HOST | DB | TIME | STATE | INFO | STAGE | MAX_STAGE | PROGRESS |
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
| 2 | root | localhost | nil | 30 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 1 | 2 | 54.117 |
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
| 2 | root | localhost | nil | Query | 30 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 27.059 |
|
| 35 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
[root@centos nil]#
|
[root@centos nil]#
|
[root@centos nil]# mysql -uroot -proot -e "select ID, USER, HOST, DB, TIME, STATE, INFO, STAGE, MAX_STAGE, PROGRESS from information_schema.processlist where ID = 2; show processlist"
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
| ID | USER | HOST | DB | TIME | STATE | INFO | STAGE | MAX_STAGE | PROGRESS |
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
| 2 | root | localhost | nil | 35 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 1 | 2 | 64.565 |
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
| 2 | root | localhost | nil | Query | 36 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 32.284 |
|
| 36 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
[root@centos nil]#
|
[root@centos nil]# mysql -uroot -proot -e "select ID, USER, HOST, DB, TIME, STATE, INFO, STAGE, MAX_STAGE, PROGRESS from information_schema.processlist where ID = 2; show processlist"
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
| ID | USER | HOST | DB | TIME | STATE | INFO | STAGE | MAX_STAGE | PROGRESS |
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
| 2 | root | localhost | nil | 41 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 1 | 2 | 74.495 |
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
| 2 | root | localhost | nil | Query | 41 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 37.248 |
|
| 37 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
[root@centos nil]#
|
[root@centos nil]# mysql -uroot -proot -e "select ID, USER, HOST, DB, TIME, STATE, INFO, STAGE, MAX_STAGE, PROGRESS from information_schema.processlist where ID = 2; show processlist"
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
| ID | USER | HOST | DB | TIME | STATE | INFO | STAGE | MAX_STAGE | PROGRESS |
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
| 2 | root | localhost | nil | 51 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 1 | 2 | 93.185 |
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
| 2 | root | localhost | nil | Query | 51 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 46.593 |
|
| 38 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
[root@centos nil]#
|
[root@centos nil]# mysql -uroot -proot -e "select ID, USER, HOST, DB, TIME, STATE, INFO, STAGE, MAX_STAGE, PROGRESS from information_schema.processlist where ID = 2; show processlist"
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
| ID | USER | HOST | DB | TIME | STATE | INFO | STAGE | MAX_STAGE | PROGRESS |
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
| 2 | root | localhost | nil | 54 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 1 | 2 | 98.300 |
|
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
| 2 | root | localhost | nil | Query | 54 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 49.157 |
|
| 39 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
|
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
|
[root@centos nil]#
|
[root@centos nil]# mysql -uroot -proot -e "select ID, USER, HOST, DB, TIME, STATE, INFO, STAGE, MAX_STAGE, PROGRESS from information_schema.processlist where ID = 2; show processlist"
|
+----+------+-----------+------+------+-------+------+-------+-----------+----------+
|
| ID | USER | HOST | DB | TIME | STATE | INFO | STAGE | MAX_STAGE | PROGRESS |
|
+----+------+-----------+------+------+-------+------+-------+-----------+----------+
|
| 2 | root | localhost | nil | 0 | | NULL | 0 | 0 | 0.000 |
|
+----+------+-----------+------+------+-------+------+-------+-----------+----------+
|
+----+------+-----------+------+---------+------+-------+------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+------+-----------+------+---------+------+-------+------------------+----------+
|
| 2 | root | localhost | nil | Sleep | 1 | | NULL | 0.000 |
|
| 40 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
|
+----+------+-----------+------+---------+------+-------+------------------+----------+
|
[root@centos nil]#
|
[root@centos nil]#
|