| Bug #96010 | Non-unique functional index prevents PRIMARY KEY from being dropped | ||
|---|---|---|---|
| Submitted: | 26 Jun 2019 17:29 | Modified: | 1 Jul 2019 12:59 |
| Reporter: | Manuel Rigger | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 8.0.16 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[26 Jun 2019 20:13]
MySQL Verification Team
Thank you for the bug report.
[1 Jul 2019 12:58]
Dmitry Lenev
Posted by developer: Hello! The underlying issue described in this bug report is the same as one that is described in bug #95997/#29959656 "INDEX MAKES DELETE FAIL WITH "TRUNCATED INCORRECT DOUBLE VALUE"". In strict SQL mode, which is default nowadays (see section 5.1.11 Server SQL Modes/ Strict SQL Mode in MySQL 8.0 manual -- https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict) warning about invalid value for the row being inserted or processed by ALTER TABLE is promoted to error and causes statement failure. However, usage of IGNORE clause in INSERT (or turning strict mode off temporarily) allows to avoid this promotion and thus the row with invalid value gets inserted. This is documented effect of IGNORE clause (see 13.2.6 INSERT Syntax in 8.0 manual https://dev.mysql.com/doc/refman/8.0/en/insert.html): "... Data conversions that would trigger errors abort the statement if IGNORE is not specified. With IGNORE, invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort. ..." So we end up with table containing row for which index expression produces invalid value and for which other statements that require evaluation of this value (ALTER TABLE in case of this bug report and DELETE in case of bug #95997/#29959656) will produce error in strict mode. The workaround is to either use IGNORE clause in these statements (where supported) or to temporarily turn off strict mode temporarily for them. Taking into account the above I am closing this report as duplicate of #95997/#29959656 "INDEX MAKES DELETE FAIL WITH "TRUNCATED INCORRECT DOUBLE VALUE"". I will provide more elaborate explanation/comments for the former bug report.
Description: When creating indexes that combine strings and numbers, an error "Truncated incorrect INTEGER value: 'a'" can occur when trying to drop a table's PRIMARY KEY. How to repeat: CREATE TABLE t0(c3 INT PRIMARY KEY); CREATE INDEX i0 ON t0(("a" | 0)); INSERT IGNORE INTO t0(c3) VALUES(1); DROP INDEX `PRIMARY` ON t0; -- unexpected: ERROR 1292 (22007): Truncated incorrect INTEGER value: 'a' The error can also be reproduced with an ALTER TABLE: ALTER TABLE t0 DROP PRIMARY KEY; -- ERROR 1292 (22007): Truncated incorrect INTEGER value: 'a' This bug report is similar to one that I created for a DELETE statement that fails to delete a record due to a similar index (see https://bugs.mysql.com/bug.php?id=95997). As stated in this bug report, I believe that the INSERT IGNORE should not allow a value that is considered to be invalid to be inserted into the table.