| Bug #90073 | Unexpected ALGORITHM=INPLACE behavior in 8.0 | ||
|---|---|---|---|
| Submitted: | 14 Mar 2018 14:18 | Modified: | 16 Mar 2018 9:03 |
| Reporter: | Kumar Verma (Platinum Quality Contributor) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 8.0.5, 8.0.4, 8.0.3,8.0.2,8.0.1 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[16 Mar 2018 9:03]
Nisha Padmini Gopalakrishnan
Posted by developer: In 5.7, the default collation used is utf8mb4_general_ci. Hence when you try to alter the column `text_col` using INPLACE algorithm without specifying any collation i.e alter table mytable1 ALGORITHM=INPLACE, modify `text_col` text CHARACTER SET utf8mb4 NOT NULL; The operation succeeds as there is no change in collation. However on 8.0, the default collation has changed to 'utf8mb4_0900_ai_ci'. This means when you try to ALTER the column without specifying the collation explicitly as mentioned above, the default collation(utf8mb4_0900_ai_ci) is used for the column which is different from the collation used by the column during CREATE TABLE i.e 'utf8mb4_general_ci' Hence changing the collation cannot be done using INPLACE algorithm and an error is reported in 8.0. If needed, the collation for the column can be explicitly specified as in CREATE TABLE and the INPLACE operation succeeds. mysql> alter table mytable1 algorithm=inplace, modify `text_col` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0 Thus marking this report as not a bug.
[16 Mar 2018 13:55]
Simon Mudd
Thank you for the explanation. What seems confusing therefore is the error message. It does not give enough context to make the problem understandable: "Reason: Cannot change column type INPLACE." Can something be done to improve this such as: "Reason: Cannot change column type from utf8mb4/utf8mb4_general_ci to utf8mb4/utf8mb4_0900_ai_ci INPLACE." (or similar) This would make the cause of the problem more visible to users. This type of error is likely to bite anyone who "upgrades via replication" unless they are careful so the clarification would at least point to collation issue which is often overlooked. It may also help others where a collation difference pops up. So extending the error message to be more concise seems useful it that were possible.
Description: Modifying a text column with INPLACE shows the below error in 8.0.1+ (but 8.0.0 works the same as 5.7.20) mysql> alter table mytable1 ALGORITHM=INPLACE, modify `text_col` text CHARACTER SET utf8mb4 NOT NULL; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. Modifying it in 5.7.20 works as expected: mysql> alter table mytable1 algorithm=inplace, modify `text_col` text CHARACTER SET utf8mb4 NOT NULL; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 Spot checked some other data types and the only other difference I could find was when switching from NOT NULL to NULL - 8.0.0 allows silently converting NULL values as opposed to 5.7 which throws an error. This may be a new feature and not a bug. On 8.0.0: mysql> alter table mytable1 algorithm=inplace, modify timestamp1 timestamp NOT NULL DEFAULT '1970-01-01'; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 On 5.7.20: mysql> alter table mytable1 algorithm=inplace, modify timestamp1 timestamp NOT NULL DEFAULT '1970-01-01'; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: cannot silently convert NULL values, as required in this SQL_MODE. Try ALGORITHM=COPY. How to repeat: CREATE TABLE `mytable1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id2` int(10) unsigned NOT NULL DEFAULT '0', `id3` int(10) unsigned NOT NULL DEFAULT '0', `status` enum('status1','status2','status3','status4','status5') NOT NULL DEFAULT 'status5', `id4` int(10) unsigned NOT NULL DEFAULT '0', `timestamp1` timestamp NULL DEFAULT NULL, `timestamp2` timestamp NULL DEFAULT NULL, `text_col` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, PRIMARY KEY (`id`), KEY `id3` (`id3`), KEY `status` (`status`), KEY `id2` (`id2`), KEY `timestamp2` (`timestamp2`) ) ENGINE=InnoDB AUTO_INCREMENT=76 DEFAULT CHARSET=latin1; mysql> alter table mytable1 ALGORITHM=INPLACE, modify `text_col` text CHARACTER SET utf8mb4 NOT NULL; mysql> alter table mytable1 algorithm=inplace, modify timestamp1 timestamp NOT NULL DEFAULT '1970-01-01';