MySQL Bugs: #72662: Extending VARCHAR can't be done online in InnoDB

Bug #72662	Extending VARCHAR can't be done online in InnoDB
Submitted:	16 May 2014 0:34	Modified:	16 May 2014 18:06
Reporter:	Yoshinori Matsunobu (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6.18	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Online manual says extending VARCHAR column online is possible as long as the number of length bytes is not changed -- http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html#innodb-online-ddl...

---------
Extending VARCHAR size using an in-place ALTER TABLE statement, as in this example:

ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(255);

The number of length bytes required by a VARCHAR column must remain the same.....
---------

But in InnoDB, inplace update was not possible.

How to repeat:
mysql> create table x2 (id int primary key, `command_type` varchar(32) not null) engine=innodb;
Query OK, 0 rows affected (0.22 sec)

mysql> alter table x2 algorithm=inplace, change column command_type command_type varchar(64);
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

Here are code paths:

./sql/sql_table.cc
      case IS_EQUAL_PACK_LENGTH:
        /*
          New column type differs from the old one, but has compatible packed
          data representation. Depending on storage engine, such a change can
          be carried out by simply updating data dictionary without changing
          actual data (for example, VARCHAR(300) is changed to VARCHAR(400)).
        */
        ha_alter_info->handler_flags|= Alter_inplace_info::
                                         ALTER_COLUMN_EQUAL_PACK_LENGTH;
        break;

./storage/innobase/handler/handler0alter.cc
        if (ha_alter_info->handler_flags
            & ~(INNOBASE_INPLACE_IGNORE
                | INNOBASE_ALTER_NOREBUILD
                | INNOBASE_ALTER_REBUILD)) {

                if (ha_alter_info->handler_flags
                        & (Alter_inplace_info::ALTER_COLUMN_EQUAL_PACK_LENGTH
                           | Alter_inplace_info::ALTER_COLUMN_TYPE))
                        ha_alter_info->unsupported_reason = innobase_get_err_msg(
                                ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_COLUMN_TYPE);
                DBUG_RETURN(HA_ALTER_INPLACE_NOT_SUPPORTED);

Alter_inplace_info::ALTER_COLUMN_EQUAL_PACK_LENGTH == 1024, and
ha_alter_info->handler_flags | 1024 was true, and in both if clauses it returned true, and raised an error HA_ALTER_INPLACE_NOT_SUPPORTED.

Suggested fix:
Either update the document (clearly describing online extending column length not supported in InnoDB) or making InnoDB support online extending column length.

Hello Yoshinori,

Thank you for the report.
Imho - you are referring 5.7 manual instead of 5.6 manual. This FR was implemented in 5.7 - http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-1.html

I could get this working with 5.7 without any issue:

mysql> create table x2 (id int primary key, `command_type` varchar(32) not null) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> alter table x2 algorithm=inplace, change column command_type command_type varchar(64);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table x2\G
*************************** 1. row ***************************
       Table: x2
Create Table: CREATE TABLE `x2` (
  `id` int(11) NOT NULL,
  `command_type` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select version();
+----------------------------------------------+
| version()                                    |
+----------------------------------------------+
| 5.7.5-m15-enterprise-commercial-advanced-log |
+----------------------------------------------+
1 row in set (0.00 sec)

Thanks,
Umesh

Oh I thought I was reading 5.6 manual but actually it was 5.7 manual. Sorry for submitting a wrong bug report. Please close this.