Bug #103632 Can't write; duplicate key in table
Submitted: 8 May 2021 12:23 Modified: 13 Aug 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

[8 May 2021 12:23] Luuk V
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.
[8 May 2021 12:50] Luuk V
I did a test on Ubuntu with version:
8.0.23-0ubuntu0.20.04.1

And the problem is also there.
[8 May 2021 13:12] MySQL Verification Team
Hello Luuk V,

Thank you for the report and test case.

regards,
Umesh
[24 Aug 2021 23:21] Nino Skopac
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.
[25 Aug 2021 1:58] Nino Skopac
An addendum to my previous comment: 

Removing the columns' DEFAULT and making them all NOT NULL doesn't have an effect on the issue.
[19 Jun 9:58] Luuk V
Updated the OS, this problem is seen on Windows and on Ubuntu (as far as I have tested)
[13 Aug 16:09] Luuk V
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
[13 Aug 16:23] Nino Skopac
Thanks for the repro, Luuk.