| Bug #115644 | UNIQUE constraint violations are possible in 8.0.36 (and upstream 8.0.37) | ||
|---|---|---|---|
| Submitted: | 19 Jul 1:35 | Modified: | 24 Aug 5:41 |
| Reporter: | Marcos Albe (OCA) | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
| Version: | 8.0.37 | OS: | Any |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[19 Jul 1:35]
Marcos Albe
[19 Jul 14:29]
Marcos Albe
My apologies; The sed line I provided is incorrect (it was the one I used to verify the regex was correct); So where it reads:
> ~$ head -n40 /tmp/dupes_artificial.sql |sed -E 's/VALUES \(([0-9]{1,3}),/VALUES \(\12,/g'
It should read:
> ~$ sed -i -E 's/VALUES \(([0-9]{1,3}),/VALUES \(\12,/g' /tmp/dupes_artificial.sql
[23 Jul 10:07]
MySQL Verification Team
Hello Marcos,
Thank you for the report and test case.
I tried to reproduce the issue on 8.0.36 and even on 8.0.38(GA) with provided steps but not seeing the reported issue. Is there anything I'm missing here? Please let me know.
--
-- 8.0.36/8.0.38
BugNumber=115644
rm -rf $BugNumber/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/$BugNumber --log-error-verbosity=3
bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/$BugNumber --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/$BugNumber/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 --early-plugin-load=keyring_file.so --loose-keyring_file_data=keyring-file 2>&1 &
bin/mysql -uroot -S/tmp/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.36-commercial MySQL Enterprise Server - Commercial
Copyright (c) 2000, 2024, 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> CREATE DATABASE IF NOT EXISTS cdrn_olap;
Query OK, 1 row affected (0.01 sec)
mysql> USE cdrn_olap;
Database changed
mysql> DROP TABLE IF EXISTS email_warning_notification;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE email_warning_notification ( email_warning_notification_key bigint NOT NULL AUTO_INCREMENT, case_id bigint NOT NULL, email varchar(255) NOT NULL, updated timestamp NULL DEFAULT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (email_warning_notification_key), UNIQUE KEY case_id_UNIQUE (case_id,email), KEY email_idx (email) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ENCRYPTION='y';
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> show create table email_warning_notification\G
*************************** 1. row ***************************
Table: email_warning_notification
Create Table: CREATE TABLE `email_warning_notification` (
`email_warning_notification_key` bigint NOT NULL AUTO_INCREMENT,
`case_id` bigint NOT NULL,
`email` varchar(255) NOT NULL,
`updated` timestamp NULL DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`email_warning_notification_key`),
UNIQUE KEY `case_id_UNIQUE` (`case_id`,`email`),
KEY `email_idx` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ENCRYPTION='y'
1 row in set (0.00 sec)
mysql>
-- mysql random data load
./mysql_random_data_load --user=root --bulk-size=1000 cdrn_olap email_warning_notification 200000; ./mysql_random_data_load --user=root --bulk-size=1000 cdrn_olap email_warning_notification 200000; ./mysql_random_data_load --user=root --bulk-size=1000 cdrn_olap email_warning_notification 200000; ./mysql_random_data_load --user=root --bulk-size=1000 cdrn_olap email_warning_notification 200000
INFO[2024-07-23T11:41:19+02:00] Starting
12s [====================================================================] 100%
INFO[2024-07-23T11:41:33+02:00] 200000 rows inserted
INFO[2024-07-23T11:41:33+02:00] Starting
12s [====================================================================] 100%
INFO[2024-07-23T11:41:46+02:00] 200000 rows inserted
INFO[2024-07-23T11:41:46+02:00] Starting
13s [====================================================================] 100%
INFO[2024-07-23T11:42:00+02:00] 200000 rows inserted
INFO[2024-07-23T11:42:00+02:00] Starting
13s [====================================================================] 100%
INFO[2024-07-23T11:42:14+02:00] 200000 rows inserted
-- follow steps from the report
-- The flush table for export so we can copy the ibd and companion files:
mysql> FLUSH TABLE cdrn_olap.email_warning_notification FOR EXPORT;
Query OK, 0 rows affected (0.00 sec)
mysql>
-- Then copy the files to some other folder:
cp -v /export/home/tmp/ushastry/mysql-8.0.36/115644/cdrn_olap/email_warning_notification.* /tmp/;
‘/export/home/tmp/ushastry/mysql-8.0.36/115644/cdrn_olap/email_warning_notification.cfg’ -> ‘/tmp/email_warning_notification.cfg’
‘/export/home/tmp/ushastry/mysql-8.0.36/115644/cdrn_olap/email_warning_notification.cfp’ -> ‘/tmp/email_warning_notification.cfp’
‘/export/home/tmp/ushastry/mysql-8.0.36/115644/cdrn_olap/email_warning_notification.ibd’ -> ‘/tmp/email_warning_notification.ibd’
-- Then we unlock:
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql>
-- Then we emulate a situation where table needs to be restored:
mysql> DROP TABLE IF EXISTS email_warning_notification;
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE email_warning_notification ( email_warning_notification_key bigint NOT NULL AUTO_INCREMENT, case_id bigint NOT NULL, email varchar(255) NOT NULL, updated timestamp NULL DEFAULT NULL, created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (email_warning_notification_key), UNIQUE KEY case_id_UNIQUE (case_id,email), KEY email_idx (email) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> -- And we discard the tablespace to import the backup ibd we exported before:
mysql>
mysql> ALTER TABLE cdrn_olap.email_warning_notification DISCARD TABLESPACE;
Query OK, 0 rows affected (0.01 sec)
mysql> -- Then we copy the ibd and companion files back into datadir
mysql>
mysql>
cp -v /tmp/email_warning_notification.* /export/home/tmp/ushastry/mysql-8.0.36/115644/cdrn_olap/;
‘/tmp/email_warning_notification.cfg’ -> ‘/export/home/tmp/ushastry/mysql-8.0.36/115644/cdrn_olap/email_warning_notification.cfg’
‘/tmp/email_warning_notification.cfp’ -> ‘/export/home/tmp/ushastry/mysql-8.0.36/115644/cdrn_olap/email_warning_notification.cfp’
‘/tmp/email_warning_notification.ibd’ -> ‘/export/home/tmp/ushastry/mysql-8.0.36/115644/cdrn_olap/email_warning_notification.ibd’
ls -lh 115644/cdrn_olap/
total 237M
-rw-r----- 1 umshastr common 1.3K Jul 23 11:55 email_warning_notification.cfg
-rw-r----- 1 umshastr common 100 Jul 23 11:55 email_warning_notification.cfp
-rw-r----- 1 umshastr common 236M Jul 23 11:57 email_warning_notification.ibd
mysql> -- And finally re-import the tablspace we had exported before
mysql>
mysql> ALTER TABLE cdrn_olap.email_warning_notification IMPORT TABLESPACE;
Query OK, 0 rows affected (0.96 sec)
mysql> -- Finally, to have some duplicate values for the UNIQUE key, I dumped rows with mysqldump:
mysql>
mysql>
bin/mysqldump -uroot --set-gtid-purged=OFF --no-create-db --no-create-info --single-transaction --skip-add-drop-table --insert-ignore --extended-insert=FALSE cdrn_olap email_warning_notification | head -n100 > /tmp/dupes_artificial.sql
mysqldump: Got errno 32 on write
ls -l /tmp/dupes_artificial.sql
-rw-r--r-- 1 umshastr common 13897 Jul 23 11:56 /tmp/dupes_artificial.sql
wc -l /tmp/dupes_artificial.sql
100 /tmp/dupes_artificial.sql
-- And with a bit of sed we added "2" at the end of every PK so that would not conflict and leave only the UNIQUE values to generate conflicts
sed -i -E 's/VALUES \(([0-9]{1,3}),/VALUES \(\12,/g' /tmp/dupes_artificial.sql
ls -l /tmp/dupes_artificial.sql
-rw-r--r-- 1 umshastr common 13974 Jul 23 11:57 /tmp/dupes_artificial.sql
-- When we imported the dupes_artificial.sql some duplicates were correctly rejected…
bin/mysql -uroot -S/tmp/mysql.sock -f cdrn_olap < /tmp/dupes_artificial.sql. <-- no dups reported here
mysql> SELECT GROUP_CONCAT(email_warning_notification_key), COUNT(*) AS cnt FROM email_warning_notification GROUP BY case_id,email HAVING cnt > 1;
Empty set (0.40 sec)
mysql> SELECT GROUP_CONCAT(email_warning_notification_key), COUNT(*) AS cnt FROM email_warning_notification GROUP BY case_id,email HAVING cnt > 1;
Empty set (0.36 sec)
Sincerely,
Umesh
[23 Jul 22:59]
Marcos Albe
Hello Umesh, Thanks for looking into this; I'm genuinely perplexed you can't reproduce. I had a colleague independently verify it was reproducible after your update, as I wanted to have someone run the steps withouth having more insight, and he indeed reproduced... but he was also using dbdeployer to create the sandbox. I will try again, but using your mysqld/mysqld_safe commands, to see if that makes a difference. Will get back to you soon. Thanks! Marcos
[24 Jul 5:41]
MySQL Verification Team
Hello Marcos, Thank you for the update, will wait for your inputs. In the meantime will check internally as well as I recall something related was reported by our Shane. regards, Umesh
[25 Aug 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
