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:
None 
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
Description:
Client program is both command line and mysqlworkbench.

I am attempting to update a single column in a table (calls) with some values supplied in another table (myupdates).  The column to be updated is indexed, but not PRIMARY.

When there are a sufficient number of rows in the myupdates table I receive the error message:
ERROR 1032 (HY000): Can't find record in 'calls'

How to repeat:
script attached: bugreport_update_error.sql

I have tried several things:

* remove all indexes from calls -> no error
* reduce number of records in myupdates (in my case <12K) -> no error
* remove PRIMARY KEY constraint, but create similar composite INDEX -> no error
* create a new field of CONCAT(callName, callTS), set this as PRIMARY KEY -> no error
* remove PRIMARY KEY constraint, but create similar composite UNIQUE -> error

So I am led to conclude it is caused by the presence of a unique composite key.

Suggested fix:
My current workaround is to:
1 - remove PRIMARY KEY
2 - apply updates (non PRIMARY KEY columns)
3 - add PRIMARY KEY
in a single transaction

But this is very inefficient.
[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.