From 74fbcd2d0b3b3953af8f8eef1f0d262f4281c517 Mon Sep 17 00:00:00 2001 From: lqs <1396119095@qq.com> Date: Tue, 22 Jul 2025 13:56:57 +0800 Subject: [PATCH] Bug#118693 Increasing length of varchar field should be inplace --- .../innodb/r/innodb-alter-varchar.result | 34 +++++++++++++++---- .../suite/innodb/t/innodb-alter-varchar.test | 34 +++++++++++++++---- sql/field.cc | 8 ++++- sql/field.h | 1 + sql/sql_table.cc | 6 ++++ 5 files changed, 68 insertions(+), 15 deletions(-) diff --git a/mysql-test/suite/innodb/r/innodb-alter-varchar.result b/mysql-test/suite/innodb/r/innodb-alter-varchar.result index cdd0180f0cf..d39525e0cc7 100644 --- a/mysql-test/suite/innodb/r/innodb-alter-varchar.result +++ b/mysql-test/suite/innodb/r/innodb-alter-varchar.result @@ -32,17 +32,16 @@ INSERT INTO t1 VALUES ('aa'), ('bb'); INSERT INTO t2 VALUES ('aa'), ('bb'); ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(6); ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(255); -# We cannot shrink the size or cross the 256-byte boundary. +# We cannot shrink the size. ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(6); ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. -ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(256); -ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. -# ALGORITHM=COPY has more limitations with FOREIGN KEY. -ALTER TABLE t2 CHANGE COLUMN fk fk VARCHAR(256); -ERROR HY000: Cannot change column 'fk': used in a foreign key constraint 'fk' ALTER TABLE t2 ALGORITHM=INPLACE, DROP FOREIGN KEY fk; -ALTER TABLE t2 CHANGE COLUMN fk fk VARCHAR(256); +ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(256); ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(300); +SELECT * FROM t2; +fk +aa +bb SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(99999); ERROR 42000: BLOB/TEXT column 'fk' used in key specification without a key length @@ -52,3 +51,24 @@ ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(99999); ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. DROP TABLE t2, t1; SET sql_mode = default; +# +# Bug#118693 SUPPORT IN-PLACE EXTENSION OF INCREASING LENGTTH OF VARCHAR COLUMN +# +CREATE TABLE t1(pk VARCHAR(200), c VARCHAR(100)) DEFAULT CHARSET ASCII ENGINE INNODB; +INSERT INTO t1 VALUES ('a', 'b'); +INSERT INTO t1 VALUES (REPEAT('a', 127), 'c'); +INSERT INTO t1 VALUES (REPEAT('a', 200), 'd'); +ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN pk pk VARCHAR(300); +ALTER TABLE t1 ADD INDEX t1_c (c); +ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c c VARCHAR(200); +SELECT * FROM t1; +pk c +a b +aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa c +aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa d +SELECT * FROM t1 FORCE INDEX (t1_c); +pk c +a b +aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa c +aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa d +drop table t1; diff --git a/mysql-test/suite/innodb/t/innodb-alter-varchar.test b/mysql-test/suite/innodb/t/innodb-alter-varchar.test index 9d71181fbdf..729b4b3dfb1 100644 --- a/mysql-test/suite/innodb/t/innodb-alter-varchar.test +++ b/mysql-test/suite/innodb/t/innodb-alter-varchar.test @@ -34,17 +34,17 @@ INSERT INTO t1 VALUES ('aa'), ('bb'); INSERT INTO t2 VALUES ('aa'), ('bb'); ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(6); ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(255); ---echo # We cannot shrink the size or cross the 256-byte boundary. +--echo # We cannot shrink the size. --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(6); ---error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(256); ---echo # ALGORITHM=COPY has more limitations with FOREIGN KEY. ---error ER_FK_COLUMN_CANNOT_CHANGE -ALTER TABLE t2 CHANGE COLUMN fk fk VARCHAR(256); +# Incrasing field's length will not affect old data, so this limitations can be avoided. +# --echo # ALGORITHM=COPY has more limitations with FOREIGN KEY. +# --error ER_FK_COLUMN_CANNOT_CHANGE +# ALTER TABLE t2 CHANGE COLUMN fk fk VARCHAR(256); ALTER TABLE t2 ALGORITHM=INPLACE, DROP FOREIGN KEY fk; -ALTER TABLE t2 CHANGE COLUMN fk fk VARCHAR(256); +ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(256); ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(300); +SELECT * FROM t2; SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; --error ER_BLOB_KEY_WITHOUT_LENGTH ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(99999); @@ -54,3 +54,23 @@ ALTER TABLE t2 ALGORITHM=INPLACE, DROP INDEX fk; ALTER TABLE t2 ALGORITHM=INPLACE, CHANGE COLUMN fk fk VARCHAR(99999); DROP TABLE t2, t1; SET sql_mode = default; + +--echo # +--echo # Bug#118693 SUPPORT IN-PLACE EXTENSION OF INCREASING LENGTTH OF VARCHAR COLUMN +--echo # +CREATE TABLE t1(pk VARCHAR(200), c VARCHAR(100)) DEFAULT CHARSET ASCII ENGINE INNODB; + +INSERT INTO t1 VALUES ('a', 'b'); +INSERT INTO t1 VALUES (REPEAT('a', 127), 'c'); +INSERT INTO t1 VALUES (REPEAT('a', 200), 'd'); + +ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN pk pk VARCHAR(300); + +ALTER TABLE t1 ADD INDEX t1_c (c); + +ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c c VARCHAR(200); + +SELECT * FROM t1; +SELECT * FROM t1 FORCE INDEX (t1_c); + +drop table t1; \ No newline at end of file diff --git a/sql/field.cc b/sql/field.cc index a493b5ed6e0..aca88ca942f 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -6890,10 +6890,16 @@ Field *Field_varstring::new_key_field(MEM_ROOT *root, TABLE *new_table, uint Field_varstring::is_equal(const Create_field *new_field) const { DBUG_TRACE; - if (change_prevents_inplace(*this, *new_field)) { + if (sql_type_prevents_inplace(*this, *new_field) || + new_field->pack_length() < pack_length() || + charset_prevents_inplace(*this, *new_field)) { return IS_EQUAL_NO; } + if (new_field->max_display_width_in_bytes() >= 256 && row_pack_length() < 256) { + return IS_EQUAL_INPLACE; + } + if (new_field->charset == field_charset && new_field->pack_length() == pack_length()) { return IS_EQUAL_YES; diff --git a/sql/field.h b/sql/field.h index 59e80940d07..58368a56a8b 100644 --- a/sql/field.h +++ b/sql/field.h @@ -253,6 +253,7 @@ enum type_conversion_status { #define IS_EQUAL_NO 0 #define IS_EQUAL_YES 1 #define IS_EQUAL_PACK_LENGTH 2 +#define IS_EQUAL_INPLACE 3 #define my_charset_numeric my_charset_latin1 #define MY_REPERTOIRE_NUMERIC MY_REPERTOIRE_ASCII diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 700ce54cf95..ee3a1c73dea 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -12049,6 +12049,12 @@ static bool fill_alter_inplace_info(THD *thd, TABLE *table, ha_alter_info->handler_flags |= Alter_inplace_info::ALTER_STORED_COLUMN_TYPE; break; + case IS_EQUAL_INPLACE: + DBUG_PRINT("inplace", ("Field %s: IS_EQUAL_INPLACE for '%s'", + field->field_name, thd->query().str)); + /* New column type is incompatible with old one. But can be update + inplace. */ + ha_alter_info->handler_flags |= Alter_inplace_info::RECREATE_TABLE; case IS_EQUAL_YES: /* New column is the same as the old one or the fully compatible with -- 2.43.5