Bug #95929 Duplicate entry for key 'PRIMARY' when querying information_schema.TABLES
Submitted: 22 Jun 2019 23:08 Modified: 4 Mar 19:36
Reporter: Manuel Rigger Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:8.0.16 OS:Ubuntu
Assigned to: CPU Architecture:x86

[22 Jun 2019 23:08] Manuel Rigger
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();
		}
	}

}
[24 Jun 2019 11:52] Umesh Shastry
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] Umesh Shastry
- 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 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.