MySQL Bugs: #92194: Tables upgraded from 8.0.11 ALGORITHM=INSTANT doesn't work unless you rebuild it

Bug #92194	Tables upgraded from 8.0.11 ALGORITHM=INSTANT doesn't work unless you rebuild it
Submitted:	26 Aug 2018 8:16	Modified:	10 Oct 2018 22:12
Reporter:	Gabriela Ferrara	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	8.0.12	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ALTER TABLE, INSTANT add column

Description:
On a server upgraded directly from 8.0.11 (as first install) to 8.0.12, when trying to use ALGORITHM=INSTANT to add an instant column to a table, it causes error:

ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE

Mind you not, the table satisfies all the requisites listed on https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/. It has Dynamic ROW_FORMAT, it doesn't have a FULLTEXT index and it's being appended.

How to repeat:
On a 8.0.11

* Create a table:

    CREATE TABLE t1 (
      a INT,
      b INT
    );

* Upgrade the server to 8.0.12
* try to perform the ALTER TABLE with ADD COLUMN:

ALTER TABLE t1
  ADD COLUMN c INT,
  ADD COLUMN d INT DEFAULT 1000,
  ALGORITHM =INSTANT;

Error: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE

* Rebuild the table:

ALTER TABLE t1
engine=InnoDB;

* Execute the ALTER TABLE again.
* It works

-------------------------

This problem does not occur if a similar table is created already on the 8.0.12 version:

    CREATE TABLE t2 (
      a INT,
      b INT
    );

Then Running the following will work as it shows on the blog:

ALTER TABLE t2
  ADD COLUMN c INT,
  ADD COLUMN d INT DEFAULT 1000,
  ALGORITHM =INSTANT;

Here's a way of reproducing the bug using dbdeployer 1.10.0 (https://github.com/datacharmer/dbdeployer):

$ dbdeployer --version
dbdeployer version 1.10.0

$ dbdeployer deploy single 8.0.11
Database installed in $HOME/sandboxes/msb_8_0_11
run 'dbdeployer usage single' for basic instructions'
. sandbox server started

$ dbdeployer deploy single 8.0.12
Database installed in $HOME/sandboxes/msb_8_0_12
run 'dbdeployer usage single' for basic instructions'
.. sandbox server started

$ ~/sandboxes/msb_8_0_11/use test 

# In MySQL 8.0.11:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_str` varchar(255) DEFAULT NULL,
  `screen_name` varchar(255) DEFAULT NULL,
  `response` json DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_str_UNIQUE` (`id_str`),
  KEY `id_str` (`id_str`),
  KEY `ix_screen_name` (`screen_name`)
) ENGINE=InnoDB AUTO_INCREMENT=31800 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

$ dbdeployer admin upgrade msb_8_0_11 msb_8_0_12

[...]

$ ~/sandboxes/msb_8_0_12/use test

# In MySQL 8.0.12

ALTER TABLE users
  ADD COLUMN active integer DEFAULT 1 NOT NULL,
  ALGORITHM =INSTANT;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.

Hello Gabriela,

Thank you for the report!

regards,
Umesh

Posted by developer:
 
Fixed in 8.0.14.

For ALTER TABLE, ALGORITHM=INSTANT was incorrectly rejected on tables
created in a MySQL version prior to 8.0.12.