| Bug #118693 | Increasing length of varchar field should be inplace. | ||
|---|---|---|---|
| Submitted: | 22 Jul 5:52 | Modified: | 22 Jul 6:32 |
| Reporter: | Li Yirong (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 8.0.42 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution, DDL, innodb, Inplace upgrade | ||
[22 Jul 5:59]
Li Yirong
I have written a patch for supporting this functionality. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: 0001-Bug-118693-Increasing-length-of-varchar-field-should.patch (application/octet-stream, text), 7.96 KiB.
[22 Jul 6:09]
Li Yirong
add some tags.
[22 Jul 6:16]
Li Yirong
I have made mistakes in above **HOW TO REPEAT**, repeat like below:
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');
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN pk pk VARCHAR(300);
ALTER TABLE t1 ADD INDEX t1_c (c);
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c c VARCHAR(300);
SELECT * FROM t1;
SELECT * FROM t1 FORCE INDEX (t1_c);
[22 Jul 6:32]
MySQL Verification Team
Hello Li Yirong! Thank you for the report and contribution. regards, Umesh
Description: Innodb has supported inplace increasing length of varchar in limited intervals [1, 255] and [256, ...]. But if we change length across these intervals, MySQL will report error of unsupported. In fact, during inplace alter, length and content of varchar will be saved in `dfield_t` type, then convert to rec and save to b-tree. Regardless of the underlying varchar storage format, its content can always be stored correctly after this conversion process, so it is reasonable to allow inplace operations to increase the length of varchar fields across intervals. How to repeat: 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'); --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN pk pk VARCHAR(300); ALTER TABLE t1 ADD INDEX t1_c (c); --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c c VARCHAR(200); SELECT * FROM t1; SELECT * FROM t1 FORCE INDEX (t1_c); Suggested fix: 1. For alter of varchar, we can define a new `IS_EQUAL_INPLACE` for it. All operation of increasing its length will return it in `is_equal` method. 2. For `IS_EQUAL_INPLACE`, we need recreate table inplace. Fill `Alter_inplace_info::RECREATE_TABLE` in handler_flags.