MySQL Bugs: #83082: Update failing with composite primary key

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

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.

script to replicate error

Attachment: bugreport_update_error.sql (text/plain), 1.43 MiB.

I realised my suggested workaround is invalid because ALTER TABLE causes an implicit commit.

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.

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.