ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
In this article I want to share my investigation, where I face this issue in GCP (why specially mention, you will know at the end), and hope that it might save someone's else time.
The MySQL GCP version is: 5.7.33-google-log.
mysql>
ALTER TABLE t_user CHANGE COLUMN UserPassword UserPassword varchar(255) NOT
null, ALGORITHM=INPLACE, LOCK=NONE;
ERROR
1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column
type INPLACE. Try ALGORITHM=COPY.
mysql>
ALTER TABLE t_user CHANGE COLUMN UserPassword UserPassword varchar(70) NOT
null, ALGORITHM=INPLACE, LOCK=NONE;
Query
OK, 0 rows affected (0.01 sec)
Records:
0 Duplicates: 0 Warnings: 0
In MySQL, utf8 corresponds to 3 byte length per character, hence according to MySQL utf8 will change byte length around 85 characters.
According to documentation:
ALTER TABLE tbl_name CHANGE COLUMN c1 c1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;
The number of length bytes required by a VARCHAR column must remain the same. For VARCHAR columns of 0 to 255 bytes in size, one length byte is required to encode the value. For VARCHAR columns of 256 bytes in size or more, two length bytes are required. As a result, in-place ALTER TABLE only supports increasing VARCHAR column size from 0 to 255 bytes, or from 256 bytes to a greater size. In-place ALTER TABLE does not support increasing the size of a VARCHAR column from less than 256 bytes to a size equal to or greater than 256 bytes. In this case, the number of required length bytes changes from 1 to 2, which is only supported by a table copy (ALGORITHM=COPY). For example, attempting to change VARCHAR column size for a single byte character set from VARCHAR(255) to VARCHAR(256) using in-place ALTER TABLE returns this error:
ALTER TABLE tbl_name ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256); ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
The byte length of a VARCHAR column is dependant on the byte length of the character set.
Decreasing VARCHAR size using in-place ALTER TABLE is not supported. Decreasing VARCHAR size requires a table copy (ALGORITHM=COPY).
mysql>
show variables like '%character%';
+--------------------------+----------------------------+
|
Variable_name
|
Value
|
+--------------------------+----------------------------+
|
character_set_client |
utf8
|
|
character_set_connection |
utf8
|
|
character_set_database |
utf8
|
|
character_set_filesystem |
binary
|
|
character_set_results |
utf8
|
|
character_set_server |
utf8
|
|
character_set_system |
utf8
|
|
character_sets_dir |
/usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in
set (0.01 sec)
mysql>
show create table t_user\G
***************************
1. row ***************************
Table: t_user
Create
Table: CREATE TABLE `t_user` (
`UserId` int(11) NOT NULL AUTO_INCREMENT,
`LoginId` varchar(100) NOT NULL,
`ExpiresOn` datetime DEFAULT NULL,
`IsEnabled` tinyint(1) NOT NULL,
`EmailId` varchar(100) NOT NULL,
`UserPassword` varchar(50) NOT NULL,
`RefId1` varchar(100) DEFAULT NULL,
`RefId2` varchar(100) DEFAULT NULL,
`LocaleId` int(11) DEFAULT NULL,
`PartnerId` int(11) NOT NULL DEFAULT '1',
`LogCount` int(11) NOT NULL DEFAULT '0',
`IsLocked`
int(11) NOT NULL DEFAULT '0',
`LastLogin` datetime DEFAULT NULL,
`IsTechnical` tinyint(3) unsigned NOT NULL DEFAULT '0',
`UserName` varchar(100) DEFAULT NULL,
`ExternalId` varchar(60) DEFAULT NULL,
`IsConfigurator` tinyint(3) unsigned NOT NULL DEFAULT '0',
`globalid` varchar(50) NOT NULL,
`createdby` varchar(50) NOT NULL DEFAULT 'admin',
`modifiedby` varchar(50) NOT NULL DEFAULT 'admin',
`modifieddate` datetime DEFAULT NULL,
`createdate`
datetime DEFAULT NULL,
`pver` varchar(8) NOT NULL DEFAULT '0',
PRIMARY KEY (`UserId`),
UNIQUE KEY `UK_T_User_LoginId_PartnerId` (`LoginId`,`PartnerId`),
UNIQUE KEY `UK_T_User_EmailId` (`EmailId`),
UNIQUE KEY `uk_t_user_globalid` (`globalid`),
KEY
`FK_T_USER_T_PARTNER` (`PartnerId`),
CONSTRAINT `FK_T_USER_T_PARTNER` FOREIGN KEY (`PartnerId`) REFERENCES
`t_partner` (`PartnerId`) ON DELETE NO ACTION ON UPDATE NO ACTION
)
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in
set (0.00 sec)
But a comment in the last (Why I mention GCP specifically earlier, it might be true on other cloud vendor like AWS as well).
I have my local system setup with version: 5.7.33-log and I face no issue even having a higher byte length in case of cp850 (if it matters somehow), and command proceed successfully:
mysql> ALTER TABLE t_user CHANGE COLUMN UserPassword UserPassword varchar(512) NOT null, ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (2.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select count(1) from t_user;
+----------+
| count(1) |
+----------+
| 3 |
+----------+
1 row in set (0.04 sec)
I do not know about this strange behavior.
One might can open a case with MySQL or GCP.
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.33-log |
+------------+
1 row in set (0.00 sec)
mysql> show variables like '%char%';
+--------------------------+---------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| character_set_client | cp850 |
| character_set_connection | cp850 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | cp850 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.01 sec)
Reference
Comments
Post a Comment