| Bug #96295 | DISABLE KEYS, DELAY_KEY_WRITE results in "Data truncated for functional index" | ||
|---|---|---|---|
| Submitted: | 23 Jul 2019 13:12 | Modified: | 29 Jul 2019 8:44 |
| Reporter: | Manuel Rigger | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 8.0.17 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[24 Jul 2019 6:10]
MySQL Verification Team
Hello Manuel Rigger, Thank you for the report. regards, Umesh
[29 Jul 2019 8:40]
Dmitry Lenev
Posted by developer: Hello! This bug describes the same issue as bug #29962076 "NON-UNIQUE FUNCTIONAL INDEX PREVENTS PRIMARY KEY FROM BEING DROPPED". The issue affects any ALTER TABLE which is executed using COPY algorithm (e.g. both "ALTER TABLE t0 DISABLE KEYS, DELAY_KEY_WRITE 0" and "DROP INDEX `PRIMARY` ON t0"). And the bug report bug#29962076 in its turn is marked as duplicate of bug#29959656 "INDEX MAKES DELETE FAIL WITH "TRUNCATED INCORRECT DOUBLE VALUE"". Therefore I am marking this bug as a duplicate of bug#95997 / #29959656 "INDEX MAKES DELETE FAIL WITH "TRUNCATED INCORRECT DOUBLE VALUE".
Description: ALTER TABLE DISABLE KEYS, DELAY_KEY_WRITE can result in an error "Data truncated for functional index", as demonstrated on the example below, which is unexpected. How to repeat: CREATE TABLE t0(c0 INT); CREATE INDEX i0 ON t0(('a' = t0.c0)); INSERT IGNORE INTO t0(c0) VALUES(1); ALTER TABLE t0 DISABLE KEYS, DELAY_KEY_WRITE 0; -- ERROR 3751 (01000): Data truncated for functional index 'i0' The error occurs irrespective of whether DELAY_KEY_WRITE is set to 0 or 1. Splitting the ALTER TABLE actions up into two separate ALTER TABLEs avoids the error: CREATE TABLE t0(c0 INT); CREATE INDEX i0 ON t0(('a' = t0.c0)); INSERT IGNORE INTO t0(c0) VALUES(1); ALTER TABLE t0 DISABLE KEYS; ALTER TABLE t0 DELAY_KEY_WRITE 0; -- no error Interestingly, the error does not occur if INSERT is used without the IGNORE. Also note that the ALTER TABLE results into the following warning, since DISABLE KEYS is only supported for MyISAM tables: "Table storage engine for '...' doesn't have this option".