MySQL Bugs: #77097: InnoDB Online DDL should support change data type

Bug #77097	InnoDB Online DDL should support change data type
Submitted:	19 May 2015 14:20	Modified:	4 Jan 2018 10:32
Reporter:	Morgan Tocker	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Currently InnoDB does not support online ddl for changing data types.  This restriction makes use of this feature very difficult, because this remains quite a commonly required operation.

For an example of how common this is a restriction:

The sakila example schema uses a TINYINT UNSIGNED for staff_id, which means a maximum of 255 employees during the lifetime of business operation (unreasonably low restriction).

To be increased to an INTEGER (more reasonable), it will require the payment, rental, staff, store tables to be locked in shared mode while the tables are rebuilt.  On the payment and rental tables this could take a considerable amount of time, during which time no new movies will be able to be rented out.  The result could be catastrophic to the business, leading to more customers choosing online alternatives.

How to repeat:
mysql> CREATE TABLE my_innodb (id tinyint NOT NULL PRIMARY KEY auto_increment, b varchar(20));
Query OK, 0 rows affected (0.05 sec)

mysql> ALTER TABLE my_innodb CHANGE id id INT NOT NULL, LOCK=NONE;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Cannot change column type INPLACE. Try LOCK=SHARED.

Some more column changes which are not online in 5.6.24
- ascii -> latin1 
- char(10) -> char(11)
- NULL -> NOT NULL

ascii data is valid latin1 (the reverse is only true if all chars<127)
ascii data is valid utf8 (the reverse is only true if only single byte chars are used)
ascii is 7-bit per character, but often stored as 1-byte (8-bit) per character. latin1 is always 1-byte per character.

mysql-5.6.24> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `foo` char(10) CHARACTER SET ascii DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql-5.6.24> ALTER TABLE t1 MODIFY COLUMN `foo` char(10) CHARACTER SET latin1 DEFAULT NULL, LOCK=NONE, ALGORITHM=INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
mysql-5.6.24> ALTER TABLE t1 MODIFY COLUMN `foo` char(10) CHARACTER SET latin1 DEFAULT NULL, LOCK=NONE, ALGORITHM=COPY;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.
mysql-5.6.24> ALTER TABLE t1 MODIFY COLUMN `foo` char(11) CHARACTER SET ascii DEFAULT NULL, LOCK=NONE, ALGORITHM=INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
mysql-5.6.24> ALTER TABLE t1 MODIFY COLUMN `foo` char(11) CHARACTER SET ascii DEFAULT NULL, LOCK=NONE, ALGORITHM=COPY;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.
mysql-5.6.24> ALTER TABLE t1 MODIFY COLUMN `foo` char(10) CHARACTER SET ascii NOT NULL, LOCK=NONE, ALGORITHM=INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: cannot silently convert NULL values, as required in this SQL_MODE. Try ALGORITHM=COPY.
mysql-5.6.24> ALTER TABLE t1 MODIFY COLUMN `foo` char(10) CHARACTER SET ascii NOT NULL, LOCK=NONE, ALGORITHM=COPY;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.

5.7.7 is similar but allows NULL to NOT NULL change.

mysql-5.7.7-rc-debug> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `foo` char(10) CHARACTER SET ascii DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql-5.7.7-rc-debug> ALTER TABLE t1 MODIFY COLUMN `foo` char(10) CHARACTER SET latin1 DEFAULT NULL, LOCK=NONE, ALGORITHM=INPLACE; 
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
mysql-5.7.7-rc-debug> ALTER TABLE t1 MODIFY COLUMN `foo` char(10) CHARACTER SET latin1 DEFAULT NULL, LOCK=NONE, ALGORITHM=COPY;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.
mysql-5.7.7-rc-debug> ALTER TABLE t1 MODIFY COLUMN `foo` char(11) CHARACTER SET ascii DEFAULT NULL, LOCK=NONE, ALGORITHM=INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
mysql-5.7.7-rc-debug> ALTER TABLE t1 MODIFY COLUMN `foo` char(11) CHARACTER SET ascii DEFAULT NULL, LOCK=NONE, ALGORITHM=COPY;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.
mysql-5.7.7-rc-debug> ALTER TABLE t1 MODIFY COLUMN `foo` char(10) CHARACTER SET ascii NOT NULL, LOCK=NONE, ALGORITHM=INPLACE;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0

Verified:

--
drop table if exists t;
create table t(a mediumint(8) not null auto_increment,b blob,primary key(a))engine=innodb;
insert into t(b) values(uuid()),(uuid()),(uuid()),(uuid()),(uuid()),(uuid()),(uuid()),(uuid()),(uuid()),(uuid());
insert into t(b) select uuid() from t a,t b,t c,t d,t e,t f;
alter table t change a a int(8) not null auto_increment, algorithm=inplace,lock=none;
alter table t change a a int(8) not null auto_increment, algorithm=copy,lock=shared;
--

Above testcase outputs:

mysql> create table t(a mediumint(8) not null auto_increment,b blob,primary key(a))engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t(b) values(uuid()),(uuid()),(uuid()),(uuid()),(uuid()),(uuid()),(uuid()),(uuid()),(uuid()),(uuid());
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> insert into t(b) select uuid() from t a,t b,t c,t d,t e,t f;
Query OK, 1000000 rows affected (50.66 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> alter table t change a a int(8) not null auto_increment, algorithm=inplace,lock=none;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
mysql> alter table t change a a int(8) not null auto_increment, algorithm=copy,lock=shared;
Query OK, 1000010 rows affected (46.66 sec)
Records: 1000010  Duplicates: 0  Warnings: 0

https://bugs.mysql.com/bug.php?id=90865 marked as duplicate of this one.