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: | |
| 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
[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 2022 9:58]
Luuk V
Updated the OS, this problem is seen on Windows and on Ubuntu (as far as I have tested)
[13 Aug 2022 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 2022 16:23]
Nino Skopac
Thanks for the repro, Luuk.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.059 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.