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.