MySQL error 1846: ALGORITHM=INPLACE is not supported
Problem
When you use the INPLACE algorithm to extend the length of a VARCHAR field in an RDS for MySQL instance, you may receive the following error:
ERROR 1846 (0A000):ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.Possible causes
You cannot use the INPLACE algorithm to extend a VARCHAR field from a size smaller than 256 bytes to a size of 256 bytes or larger.
The supported ranges for field length extension are:
Before extension | After extension |
Less than 256 bytes | Less than 256 bytes |
Equal to or greater than 256 bytes | Greater than 256 bytes |
For more information about extending VARCHAR fields using the INPLACE algorithm, see the official MySQL documentation.
In VARCHAR(50), 50 specifies the character length. The number of bytes that the field occupies depends on the character set:
For the ASCII character set, one character occupies one byte. When you extend a field from
VARCHAR(50)toVARCHAR(100), the space occupied changes from 50 bytes to 100 bytes. In this case, the INPLACE algorithm is supported.For the utf8 character set, one character occupies a maximum of three bytes. When you extend a field from
VARCHAR(50)toVARCHAR(100), the maximum space occupied changes from 150 bytes to 300 bytes. In this case, the INPLACE algorithm is not supported.
Solutions
If you are not extending a VARCHAR field from a size smaller than 256 bytes to a size of 256 bytes or larger, you can use the INPLACE algorithm. To do this, set the ALGORITHM parameter to INPLACE.
NoteWhen you use the INPLACE algorithm to extend a VARCHAR field, make sure that the extension is within the supported length ranges. For more information, see Possible causes.
To extend a VARCHAR field from a size smaller than 256 bytes to a size of 256 bytes or larger, you must use the COPY algorithm. To do this, set the ALGORITHM parameter to COPY.
Sample command:
ALTER TABLE `table1` CHANGE COLUMN `col1` VARCHAR(256) DEFAULT NULL, ALGORITHM=COPY;