InnoDB ERROR 1846 (0A000) when creating an index in MySQL
I recently had an issue where I was trying to hot-create an index on an InnoDB table running on MySQL 5.6.47, and received the following error:
The MySQL documentation says:
Tables created before MySQL 5.6 that include temporal columns
(DATE, DATETIME or TIMESTAMP) and have not been rebuilt using
ALGORITHM=COPY do not support ALGORITHM=INPLACE However that didn't seem to be the case as the .frm file looked to have been created using 5.6.47 (and this issue was reproduced on an RDS instance):
# hexdump -s 0x33 -n 2 -v -d tblExample.frm
0000033 50647
0000035In this case, it seems the table schema was using the old format for DATETIME (this can happen under current versions if you use CREATE TABLE LIKE referencing an older table) and the solution was to force them to be recreated. Note that creating a single index with ALGORITHM=COPY would have had the same effect.
mysql> ALTER TABLE tblExample ADD INDEX test(testCol), ALGORITHM=INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
mysql> ALTER TABLE tblExample FORCE;
Query OK, 10 rows affected, 1 warning (0.15 sec)
Records: 10 Duplicates: 0 Warnings: 1
mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------+
| Note | 1880 | TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format. |
+-------+------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE tblExample ADD INDEX test(testCol), ALGORITHM=INPLACE;
Query OK, 0 rows affected, 0 warning (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0Recreating the table with ALTER TABLE tblExample ENGINE=InnoDB or even altering the file format with ALTER TABLE tblExample ROW_FORMAT=compressed , even if specifying ALGORITHM=COPY does not resolve the problem. You need to either modify the table or specify FORCE.
Also, dropping an index with ALGORITHM=INPLACE should work even if creating an index does not, so it may be a good idea to remove unnecessary indexes first to reduce the time it takes to COPY.
I tested this on an 80GB table with 40+ indexes totalling around 40GB extra and the time saving was substantial:
mysql with indexes intact> ALTER TABLE tblExample FORCE;
Query OK, 30709475 rows affected, 1 warning (9 hours 6 min 32.04 sec)
Records: 30709475 Duplicates: 0 Warnings: 1mysql with indexes removed> ALTER TABLE tblExample FORCE;
Query OK, 30709475 rows affected, 1 warning (3 hours 51 min 34.74 sec)
Records: 30709475 Duplicates: 0 Warnings: 1