Bug #88764 "ALTER TABLE MODIFY..." takes time even if leving table as is
Submitted: 5 Dec 2017 17:14 Modified: 18 Feb 2018 18:05
Reporter: teo teo Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.6.20 OS:Any
Assigned to: CPU Architecture:Any

[5 Dec 2017 17:14] teo teo 
Description:
If I do a query like:
"ALTER TABLE MODIFY <column> <column_definition>,
             MODIFY <column> <column_definition>,
..."

and the specified column definitions are exactly as the current columns are, then this should take a fraction of a second because there's nothing to be done, no matter how many millions of rows the table has.

Instead, on a huge table it takes a while, comparable to the time it took when the columns actually need to be modified.

That's ridiculous.

How to repeat:
see description
[17 Dec 2017 12:56] MySQL Verification Team 
Hi!

Thank you for your report.

However, we do need some other info, before we can proceed. First of all, what version is this, next which storage engine are you using and last, but not least, a test case. A test case should include a definition of the table and the exact DDL command that took so much time to finish.
[18 Jan 2018 1:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[18 Jan 2018 15:50] teo teo 
Version is 5.6.20, storage engine is innodb.

Regarding a test case, there is enough info in the report to create one from scratch.
[18 Jan 2018 17:29] MySQL Verification Team 
Maybe you have tables that required upgrading of physical format from 5.5 ?
Seemed to work on a int and varchar column, see a simple test:

-- ----------
drop table if exists t;
set sql_mode='';
create table t(a int,b varchar(255) charset utf8,key(a),key(b))engine=innodb;
insert into t values(),(),(),(),(),(),(),(),(),();
insert into t select floor(rand()*10000),uuid() from t a,t b,t c,t d,t e,t f;
show create table t\G
alter table t modify a int, modify b varchar(255) charset utf8;
show create table t\G
select version();
-- ----------

mysql> drop table if exists t;
Query OK, 0 rows affected (0.21 sec)

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t(a int,b varchar(255) charset utf8,key(a),key(b))engine=innodb;
Query OK, 0 rows affected (0.02 sec)

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

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

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL,
  `b` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table t modify a int, modify b varchar(255) charset utf8;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL,
  `b` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.38    |
+-----------+
1 row in set (0.00 sec)
[19 Feb 2018 1:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".