MySQL Bugs: #112587: Change in MySQL error message format for 1062 Duplicate Entry from v5.7 to v8.0

Bug #112587	Change in MySQL error message format for 1062 Duplicate Entry from v5.7 to v8.0
Submitted:	2 Oct 2023 13:29	Modified:	16 Oct 2023 17:38
Reporter:	Christopher Jacoby	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Linux
Assigned to:	Jon Stephens	CPU Architecture:	x86

Description:
We recently upgraded the major engine version of our AWS Aurora RDS MySQL databases from 5.7.12 to 8.0.28.

After the upgrade, we began to see errors emerge from a stored procedure. After debugging, we found the root cause to be a difference in the MySQL error message format between versions 5.7.12 to 8.0.28. We experienced this specifically with respect to ERROR 1062: Duplicate Entry.

On 5.7.12, the error message's key value is only the name of the unique key constraint, whereas on 8.0.28 the error message's key value is the unique key constraint prefixed with the table name.

Below are examples of the differing error message formats we see on each version.

## 5.7.12:
```
ERROR 1062 (23000): Duplicate entry '1234' for key 'unique_col'
```

## 8.0.28:
```
ERROR 1062 (23000): Duplicate entry '1234' for key 'employee_table.unique_col'
```

Despite the MySQL error number being consistent (1062), the error message format changed. This threw off our stored procedure, which checks the key value and takes different error handling paths depending on which specific unique key constraint was failed.

How to repeat:
1. Spin up two MySQL databases on versions 5.7.12 and 8.0.28.
2. On each database, define a table with a `UNIQUE KEY` constraint, example below.
3. On each database, attempt to insert a record which fails the unique key constraint.
4. Note the difference in the MySQL error messages which come back on the respective database versions.

## Example CREATE TABLE
CREATE TABLE `test_table(
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
`col` VARCHAR(48) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_col` (`col`)
)

Suggested fix:
Ideally the error message formatting would be consistent between MySQL major engine version changes. Alternatively, it would be helpful if this change information was captured in detailed release notes, so we could adjust for these changes preemptively, rather than encountering database stored procedure errors having already applied the engine upgrade.

Hi Mr. Jacoby,

Thank you very much.

We agree with you that this should be documented in some Release Note.

Verified as reported.

As part of addressing the bug -

1) Is the behavior going to be rolled back to the previous error message format as to match in v5.7 and all previous versions of MySQL 

OR 

2)The release notes would be updated with expected error message format (tablename.key) for v8.0

Ideally, it should be 1) to be a backward compatible update and to avoid any impact to existing applications. 

Please let us know.

Hi,

We can not give you the immediate answer, but most likely it will be the second option.

Because, that error message is changed, not only in 8.0, but in 8.1 and 8.2.

Fixed in mysqldoc rev 76967. 

See https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-server...

Thanks.

Thank you, Jon.