Bug #83082 | Update failing with composite primary key | ||
---|---|---|---|
Submitted: | 21 Sep 2016 14:49 | Modified: | 22 Sep 2016 7:15 |
Reporter: | Martin Humphries | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: DDL | Severity: | S2 (Serious) |
Version: | 5.7.13 | OS: | Windows (7 Ultimate SP1) |
Assigned to: | CPU Architecture: | Any | |
Tags: | 1032 |
[21 Sep 2016 14:49]
Martin Humphries
[21 Sep 2016 14:54]
Martin Humphries
script to replicate error
Attachment: bugreport_update_error.sql (text/plain), 1.43 MiB.
[21 Sep 2016 15:02]
Martin Humphries
I realised my suggested workaround is invalid because ALTER TABLE causes an implicit commit.
[21 Sep 2016 17:28]
MySQL Verification Team
Thank you for the bug report. I got the below behavior: In early version like 5.6 the composed primary key is too long: c:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > " Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.6.34 Source distribution PULL: 2016-SEP-07 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.6 > USE bug Database changed mysql 5.6 > DROP TABLE IF EXISTS `calls`; Query OK, 0 rows affected (0.14 sec) mysql 5.6 > /*!40101 SET @saved_cs_client = @@character_set_client */; Query OK, 0 rows affected (0.00 sec) mysql 5.6 > /*!40101 SET character_set_client = utf8 */; Query OK, 0 rows affected (0.00 sec) mysql 5.6 > CREATE TABLE `calls` ( -> `responseContentID` int(11) NOT NULL AUTO_INCREMENT, -> `dbLoadDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> `callName` varchar(500) NOT NULL, -> `callTS` datetime NOT NULL, -> PRIMARY KEY (`callName`,`callTS`), -> KEY `responseContentID` (`responseContentID`) -> ) ENGINE=InnoDB AUTO_INCREMENT=28397 DEFAULT CHARSET=utf8; ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes On current 5.7 the table is created and fails with update query: c:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > " Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.7.16 Source distribution PULL: 2016-SEP-07 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.7 > USE bug Database changed mysql 5.7 > SOURCE c:/tmp/bug.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql 5.7 > UPDATE calls c -> INNER JOIN -> myupdates u -> ON c.responseContentID=u.originalResponseContentID -> SET c.responseContentID=u.replacementResponseContentID; ERROR 1032 (HY000): Can't find record in 'calls' mysql 5.7 > SHOW CREATE TABLE calls\G *************************** 1. row *************************** Table: calls Create Table: CREATE TABLE `calls` ( `responseContentID` int(11) NOT NULL AUTO_INCREMENT, `dbLoadDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `callName` varchar(500) NOT NULL, `callTS` datetime NOT NULL, PRIMARY KEY (`callName`,`callTS`), KEY `responseContentID` (`responseContentID`) ) ENGINE=InnoDB AUTO_INCREMENT=28397 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql 5.7 > ALTER TABLE calls ENGINE MyISAM; ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes mysql 5.7 > You notice MyISAM Engine doesn't support that primary or unique key. If I change the size of the column then the query works on 5.7: mysql 5.7 > UPDATE calls c -> INNER JOIN -> myupdates u -> ON c.responseContentID=u.originalResponseContentID -> SET c.responseContentID=u.replacementResponseContentID; Query OK, 16061 rows affected (2 min 26.70 sec) Rows matched: 16061 Changed: 16061 Warnings: 0 mysql 5.7 > SHOW CREATE TABLE calls\G *************************** 1. row *************************** Table: calls Create Table: CREATE TABLE `calls` ( `responseContentID` int(11) NOT NULL AUTO_INCREMENT, `dbLoadDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `callName` varchar(200) NOT NULL, `callTS` datetime NOT NULL, PRIMARY KEY (`callName`,`callTS`), KEY `responseContentID` (`responseContentID`) ) ENGINE=InnoDB AUTO_INCREMENT=28397 DEFAULT CHARSET=utf8 1 row in set (0.03 sec) mysql 5.7 > SHOW VARIABLES LIKE "%version%"; +-------------------------+---------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------+ | innodb_version | 5.7.16 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1,TLSv1.1 | | version | 5.7.16 | | version_comment | Source distribution PULL: 2016-SEP-07 | | version_compile_machine | x86_64 | | version_compile_os | Win64 | +-------------------------+---------------------------------------+ 8 rows in set (0.00 sec) Anyway a bug can't allow to create the table calls with original columns reported or if allowed the update should works.
[22 Sep 2016 7:15]
Martin Humphries
Ah, thats excellent to know - thank you for responding. I can change my db to accomodate this easily now, but did spend a lot of time on it yesterday, but hopefully this can be rectified in future versions.