Bug #95929 Duplicate entry for key 'PRIMARY' when querying information_schema.TABLES
Submitted: 22 Jun 23:08 Modified: 24 Jun 11:52
Reporter: Manuel Rigger Email Updates:
Status: Verified 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 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 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