MySQL Bugs: #88072: some forms of VARCHAR size extension are not metadata only when they should be

Bug #88072	some forms of VARCHAR size extension are not metadata only when they should be
Submitted:	12 Oct 2017 8:40	Modified:	17 Jan 2018 10:27
Reporter:	Mikhail Izioumtchenko (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.6, 5.7, 8.0, 5.7.19	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
when a VARCHAR column in an InnoDB table is not indexed statements like

[CREATE TABLE t (v VARCHAR(2) character set latin1) engine=InnoDB]
ALTER TABLE t MODIFY v VARCHAR(3) character set latin1;

are metadata only and so almost instantaneous.
However if the column is indexed the index is rebuilt even when it is not necessary. It is not necessary if the only thing that changes is the VARCHAR max length 
and the number of bytes to represent the length does not change.

While the problem may be specific to InnoDB tables, the bug is in the server layer.
The minimal testcase is below. I'll provide a more extensive testcase and a fix on github.

How to repeat:
=== test
--source include/have_innodb.inc
--source include/have_debug.inc

--echo #
--echo # Bug N
--echo #

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

--echo # without the patch index is rebuilt whereas it should not
CREATE TABLE t1 (fld1 VARCHAR(3), KEY idx1(fld1))
ENGINE=InnoDB;
SET debug="+d,innodb_index_drop_count_zero";

--echo # without the patch, an error is reported.
ALTER TABLE t1 MODIFY fld1 VARCHAR(4);

--echo #cleanup
DROP TABLE t1;
SET debug="-d,innodb_index_drop_count_zero";
=== end test

=== result
#
# Bug 42
#
DROP TABLE IF EXISTS t1;
# without the patch index is rebuilt whereas it should not be
CREATE TABLE t1 (fld1 VARCHAR(3), KEY idx1(fld1))
ENGINE=InnoDB;
SET debug="+d,innodb_index_drop_count_zero";
# without the patch, an error is reported.
ALTER TABLE t1 MODIFY fld1 VARCHAR(4);
#cleanup
DROP TABLE t1;
SET debug="+d,innodb_index_drop_count_zero";
=== end result

Suggested fix:
I will provide it a bit later

Does this demonstrate the issue accurately?  Notice time taken for second ALTER.

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

mysql> create table t(a varchar(2))engine=innodb default character set=latin1;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t values('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a');
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> insert into t(a) select a.a from t a,t b,t c,t d,t e,t f,t g;
Query OK, 4782969 rows affected (17.63 sec)
Records: 4782969  Duplicates: 0  Warnings: 0

mysql> alter table t modify a varchar(3),algorithm=inplace,lock=none;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> create table s(a varchar(2),key(a))engine=innodb default character set=latin1;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into s values('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a'),('a');
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> insert into s(a) select a.a from s a,s b,s c,s d,s e,s f,s g;
Query OK, 4782969 rows affected (26.33 sec)
Records: 4782969  Duplicates: 0  Warnings: 0

mysql> alter table s modify a varchar(3),algorithm=inplace,lock=none;
Query OK, 0 rows affected (19.96 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.19    |
+-----------+
1 row in set (0.00 sec)

Hello Mikhail,

Thank you for the report and Shane for the test case to confirm reported issue.

regards,
Umesh

Shane's (thank you) test case is a common sense thing. My test case submission
is technical it actually shows the code path how on top of MODIFY column, an index DROP/ADD
is insisted on.
My original patch has run into some other bug when I was extending the test case.
Try v1 VARCHAR(4), i1 integere, KEY (v1,i1). Now ALTER ... MODIFY v1 VARCHAR(8).
I have a patch that cover this as well, testing now.

patch based on 5.7.20

Attachment: bug88072.diff (application/octet-stream, text), 2.19 KiB.

extended test case

Attachment: bug88072.test (application/octet-stream, text), 3.41 KiB.

test result file

Attachment: bug88072.result (application/octet-stream, text), 3.14 KiB.

will try to put the patch to github later

testing (all successful in that results are identical with baseline,
some 1-4% tests were failing on both on my small Centos7 machine)
--do-test=alter --big-test
suite=innodb --big-test
entire MySQL test suite but without --big-test

Hello Mikhail,

Thank you for the contribution.
We wanted to use your patch but observed that you haven't signed OCA. Please note that in order to submit contributions you must first sign the Oracle Contribution Agreement (OCA). More details are described in "Contributions" tab, please ensure to re-send the patch via that tab. Otherwise we would not be able to accept it.

Thanks,
Umesh

patch based on 5.7.20 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug88072.diff (application/octet-stream, text), 2.19 KiB.

testcase (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug88072.test (application/octet-stream, text), 3.41 KiB.

testcase result file (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug88072.result (application/octet-stream, text), 3.14 KiB.

submitted the patch through Contributions