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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.056 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.