| Bug #95929 | Duplicate entry for key 'PRIMARY' when querying information_schema.TABLES | ||
|---|---|---|---|
| Submitted: | 22 Jun 2019 23:08 | Modified: | 4 Mar 2020 19:36 |
| Reporter: | Manuel Rigger | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 8.0.16 | OS: | Ubuntu |
| Assigned to: | CPU Architecture: | x86 | |
[24 Jun 2019 11:52]
MySQL Verification Team
Hello Manuel Rigger, Thank you for the report and test case. Verified as described with 8.0.16 build. regards, Umesh
[24 Jun 2019 11:52]
MySQL Verification Team
- MySQL server 8.0.16 rm -rf 95929/ bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/95929 --log-error-verbosity=3 bin/mysqld --basedir=$PWD --datadir=$PWD/95929 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/95929/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv=/tmp/ 2>&1 & - run provided java test case after adjusting environment details ant -f C:\\Work\\MySQLJava\\Bug95929 -Dnb.internal.action.name=run run init: deps-jar: Created dir: C:\Work\MySQLJava\Bug95929\build Updating property file: C:\Work\MySQLJava\Bug95929\build\built-jar.properties Created dir: C:\Work\MySQLJava\Bug95929\build\classes Created dir: C:\Work\MySQLJava\Bug95929\build\empty Created dir: C:\Work\MySQLJava\Bug95929\build\generated-sources\ap-source-output Compiling 1 source file to C:\Work\MySQLJava\Bug95929\build\classes compile: run: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'triggerbug30-t0' for key 'PRIMARY' at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:782) at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:666) at Bug95929$1.run(Bug95929.java:37) at java.base/java.lang.Thread.run(Thread.java:834) java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'triggerbug9-t0' for key 'PRIMARY' at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:782) at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:666) at Bug95929$1.run(Bug95929.java:37) at java.base/java.lang.Thread.run(Thread.java:834) java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'triggerbug20-t0' for key 'PRIMARY' at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:782) at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:666) at Bug95929$1.run(Bug95929.java:37) at java.base/java.lang.Thread.run(Thread.java:834) java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'triggerbug27-t0' for key 'PRIMARY' at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:782) at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:666) at Bug95929$1.run(Bug95929.java:37) at java.base/java.lang.Thread.run(Thread.java:834) java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'triggerbug26-t0' for key 'PRIMARY' at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:782) at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:666) at Bug95929$1.run(Bug95929.java:37) at java.base/java.lang.Thread.run(Thread.java:834) java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'triggerbug23-t0' for key 'PRIMARY' at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:782) at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:666) at Bug95929$1.run(Bug95929.java:37) at java.base/java.lang.Thread.run(Thread.java:834)
[4 Mar 2020 19:36]
Paul DuBois
Posted by developer: Fixed in 8.0.20. For INFORMATION_SCHEMA queries, a race condition could result in multiple attempts to insert a key when updating the dynamic statistics tables, producing a duplicate-key error.
Description: When using the HEAP/MEMORY or MyISAM engines, querying the information_schema.TABLES sometimes fails with an error like "Duplicate entry 'triggerbug28-t0' for key 'PRIMARY'". I could only reproduce this bug when using multiple threads that operate on the same or distinct databases. Initially, I could reproduce the bug only with "SET optimizer_switch = 'derived_merge=off';", but the test case attached to the bug report does not require it. How to repeat: The bug can be reproduced by starting multiple threads that operate on distinct databases that are created every run, and which execute the following two statements: CREATE TABLE t0(c0 INT) ENGINE = MyISAM; select * from information_schema.TABLES; This should result in errors that look like this: "Duplicate entry 'triggerbug21-t0' for key 'PRIMARY'". The following Java program can be used to conveniently reproduce the error: import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class TriggerBug { private static final String USER_NAME = "TODO"; private static final String CONNECTION_STRING = "TODO"; private static final String PASSWORD = "TODO"; public static void main(String[] args) { String query = "DROP DATABASE IF EXISTS triggerbug%d;\n" + "CREATE DATABASE triggerbug%d;\n" + "USE triggerbug%d;\n" + "CREATE TABLE t0(c0 INT) ENGINE = MyISAM;\n" + "select * from information_schema.TABLES;\n"; for (int i = 0; i < 32; i++) { final int index = i; Runnable r = new Runnable() { @Override public void run() { try (Connection con = DriverManager.getConnection(CONNECTION_STRING, USER_NAME, PASSWORD)) { while (true) { for (String s : String.format(query, index, index, index).split("\n")) { try (Statement st = con.createStatement()) { st.execute(s); } } } } catch (SQLException e1) { e1.printStackTrace(); } } }; new Thread(r).start(); } } }