MySQL Bugs: #91862: Wrong instant_cols in information_schema.innodb

Bug #91862	Wrong instant_cols in information_schema.innodb_tables
Submitted:	2 Aug 2018 3:23	Modified:	25 Oct 2018 7:08
Reporter:	feng guo	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	8.0.12	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When adding columns multitimes,information_schema.innodb_tables's instant_cols field shows wrong value

How to repeat:
root@test11:13:29>CREATE TABLE t1 (a INT, b INT);
Query OK, 0 rows affected (0.01 sec)

root@test11:19:55>SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name    | instant_cols |
+----------+---------+--------------+
|     1113 | test/t1 |            0 |
+----------+---------+--------------+
1 row in set (0.00 sec)

root@test11:20:00>ALTER TABLE t1 ADD COLUMN c INT, ADD COLUMN d INT DEFAULT 1000, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@test11:20:05>SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name    | instant_cols |
+----------+---------+--------------+
|     1113 | test/t1 |            2 |
+----------+---------+--------------+

1 row in set (0.00 sec)

above result is right

root@test11:20:11>ALTER TABLE t1 ADD COLUMN e INT, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@test11:20:46>SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name    | instant_cols |
+----------+---------+--------------+
|     1113 | test/t1 |            2 |
+----------+---------+--------------+
1 row in set (0.00 sec)

wrong value,instant_cols should be 2+1 =3

root@test11:20:51>optimize table t1;
+---------+----------+----------+-------------------------------------------------------------------+
| Table   | Op       | Msg_type | Msg_text                                                          |
+---------+----------+----------+-------------------------------------------------------------------+
| test.t1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.t1 | optimize | status   | OK                                                                |
+---------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.01 sec)

root@test11:20:57>SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name    | instant_cols |
+----------+---------+--------------+
|     1114 | test/t1 |            0 |
+----------+---------+--------------+
1 row in set (0.00 sec)

0 is right value, since optimize will rebuild table

root@test11:21:02>ALTER TABLE t1 ADD COLUMN f INT, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@test11:21:06>SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name    | instant_cols |
+----------+---------+--------------+
|     1114 | test/t1 |            5 |
+----------+---------+--------------+
1 row in set (0.00 sec)

5 is wrong value, since I just add one column

Hello feng guo,

Thank you for the report!

Thanks,
Umesh

I think this is either a doc problem or genuine bug.

What this "instant_cols" shows is the "number of user columns when the first instant ADD COLUMN happens", that is, before we do any instant add column, how many existing non-instant user columns there are. So in your case, it is always 2 for the first set of add columns, as there are column "a" and "b". Once optimizer table is done, all 5 columns are no longer instant, so it will show always 5 columns.

However, the doc does mistakenly show this is num of added columns:
INSTANT_COLS shows the number of columns added instantly using ALTER TABLE ... ADD COLUMN with ALGORITHM=INSTANT. 

We will check if this will be fixed as doc bug or code bug.

The correct description for the column is:

"INSTANT_COLS shows number of columns in the table prior to adding the first instant column"

The following information was updated:
https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-system-tables.html

Changes should appear online within 24 hours.