MySQL Bugs: #103632: Can't write; duplicate key in table

Bug #103632	Can't write; duplicate key in table
Submitted:	8 May 2021 12:23	Modified:	13 Aug 2022 16:09
Reporter:	Luuk V	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.23, 8.0.24, 5.7.34	OS:	Any (Windows, Ubuntu)
Assigned to:		CPU Architecture:	Any

Description:
When running next query, I am receiving a duplicate key in table.

ERROR 1022 (23000): Can't write; duplicate key in table 'C:\WINDOWS\SERVIC~1\NETWOR~1\AppData\Local\Temp\#sql65c0_d_10f'

How to repeat:
DROP TABLE IF EXISTS ints;
CREATE TABLE ints (i INT primary key);
INSERT INTO ints VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

SELECT r, count(*) 
FROM (
  SELECT i, floor(rand()*4)+1 r 
  FROM ints 
  WHERE i<=10) x 
GROUP BY x.r ;

Suggested fix:
Providing the correct output, which is a counted result of the random numbers `r` begin generated.

I did a test on Ubuntu with version:
8.0.23-0ubuntu0.20.04.1

And the problem is also there.

Hello Luuk V,

Thank you for the report and test case.

regards,
Umesh

Not a full test case, but an idea:

select date(created_at), type, status, count(*)
from api_logs
where date(created_at) = curdate() 
group by date(created_at), type, status with rollup 

Causes "Can't write; duplicate key in table '/rdsdbdata/tmp/#sql2a82_773_8'"

Removing with rollup and then running the query works.
Removing date(created_at) from both the select and the group by and then running the query also works, i.e.:

select type, status, count(*)
from api_logs
where date(created_at) = curdate() 
group by type, status with rollup

CREATE TABLE `api_logs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `type` enum('subtype1','subtype2') DEFAULT NULL,
  `request_uri` varchar(255) NOT NULL DEFAULT '',
  `status` enum('successful','failed') DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `composite` (`type`,`status`,`request_uri`,`created_at`) USING BTREE,
  KEY `type_on_date_and_time_with_status` (`type`,(cast(`created_at` as date)),(cast(`created_at` as time)),`status`)
) ENGINE=InnoDB AUTO_INCREMENT=17056969 DEFAULT CHARSET=utf8mb3;

Running MySQL 8.0.25.

An addendum to my previous comment: 

Removing the columns' DEFAULT and making them all NOT NULL doesn't have an effect on the issue.

Updated the OS, this problem is seen on Windows and on Ubuntu (as far as I have tested)

Testing this on 8.0.29.

- I can no longer reproduce a problem using my own test-case (with the `ints` table)
- With the table `api_logs`, MySQL will CRASH

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.29 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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 [test]> select * from api_logs;
+----------+----------+-------------+------------+---------------------+
| id       | type     | request_uri | status     | created_at          |
+----------+----------+-------------+------------+---------------------+
| 17056969 | subtype1 |             | successful | 2022-08-13 17:00:00 |
| 17056970 | subtype1 |             | successful | 2022-08-13 17:00:00 |
| 17056971 | subtype1 |             | successful | 2022-08-13 17:00:00 |
| 17056972 | subtype1 |             | successful | 2022-08-13 17:00:00 |
| 17056973 | subtype1 |             | successful | 2022-08-13 17:00:00 |
| 17056974 | subtype1 |             | successful | 2022-08-13 17:10:00 |
| 17056975 | subtype1 |             | successful | 2022-08-13 17:10:00 |
+----------+----------+-------------+------------+---------------------+
7 rows in set (0.01 sec)

MySQL [test]> select date(created_at), type, status, count(*)
    -> from api_logs
    -> where date(created_at) = curdate()
    -> group by date(created_at), type, status with rollup;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost:3306' (10061)
ERROR:
Can't connect to the server

MySQL [test]>

**error_log**
2022-08-13T16:04:37.651774Z 0 [System] [MY-010931] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: ready for connections. Version: '8.0.29'  socket: ''  port: 3306  MySQL Community Server - GPL.
16:05:04 UTC - mysqld got exception 0xc0000005 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x20cac9a7140
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
7ff72a8b0f8d    mysqld.exe!?find_type2@@YAIPEBUTYPELIB@@PEBD_KPEBUCHARSET_INFO@@@Z()
7ff72a82ee07    mysqld.exe!?store@Field_enum@@UEAA?AW4type_conversion_status@@PEBD_KPEBUCHARSET_INFO@@@Z()
7ff72a670b3b    mysqld.exe!?save_in_field_inner@Item@@MEAA?AW4type_conversion_status@@PEAVField@@_N@Z()
7ff72a670736    mysqld.exe!?save_in_field@Item@@QEAA?AW4type_conversion_status@@PEAVField@@_N@Z()
7ff72aab94d4    mysqld.exe!?Init@TemptableAggregateIterator@@UEAA_NXZ()
7ff72a930a3e    mysqld.exe!?ExecuteIteratorQuery@Query_expression@@QEAA_NPEAVTHD@@@Z()
7ff72a932226    mysqld.exe!?execute@Query_expression@@QEAA_NPEAVTHD@@@Z()
7ff72a83bd5e    mysqld.exe!?execute_inner@Sql_cmd_dml@@MEAA_NPEAVTHD@@@Z()
7ff72a83bbdc    mysqld.exe!?execute@Sql_cmd_dml@@UEAA_NPEAVTHD@@@Z()
7ff72a794013    mysqld.exe!?mysql_execute_command@@YAHPEAVTHD@@_N@Z()
7ff72a78f1b1    mysqld.exe!?dispatch_sql_command@@YAXPEAVTHD@@PEAVParser_state@@@Z()
7ff72a78ded2    mysqld.exe!?dispatch_command@@YA_NPEAVTHD@@PEBTCOM_DATA@@W4enum_server_command@@@Z()
7ff72a78f4f0    mysqld.exe!?do_command@@YA_NPEAVTHD@@@Z()
7ff72a5a1728    mysqld.exe!?modify_thread_cache_size@Per_thread_connection_handler@@SAXK@Z()
7ff72ba3b519    mysqld.exe!??$endl@DU?$char_traits@D@std@@@std@@YAAEAV?$basic_ostream@DU?$char_traits@D@std@@@0@AEAV10@@Z()
7ff72b617fdc    mysqld.exe!?my_thread_self_setname@@YAXPEBD@Z()
7ff955cc6c0c    ucrtbase.dll!_recalloc()
7ff9566c54e0    KERNEL32.DLL!BaseThreadInitThunk()
7ff95838485b    ntdll.dll!RtlUserThreadStart()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (20cb2302830): select date(created_at), type, status, count(*)
from api_logs
where date(created_at) = curdate() 
group by date(created_at), type, status with rollup
Connection ID (thread ID): 8
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

**query_log**
C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe, Version: 8.0.29 (MySQL Community Server - GPL). started with:
TCP Port: 3306, Named Pipe: D:/TEMP/MYSQL.sock
Time                 Id Command    Argument
2022-08-13T16:04:49.172327Z	    8 Connect	luuk@localhost on test using SSL/TLS
2022-08-13T16:04:49.173112Z	    8 Query	select @@version_comment limit 1
2022-08-13T16:04:57.215091Z	    8 Query	select * from api_logs
2022-08-13T16:05:04.407147Z	    8 Query	select date(created_at), type, status, count(*)
from api_logs
where date(created_at) = curdate() 
group by date(created_at), type, status with rollup

Thanks for the repro, Luuk.