MySQL: inconsistent result sets from information_schema

Question

This is using MySQL 5.7.28 as a Docker image, in case that's relevant. The client is JDBC 5.1.48, running on GraalVM 11.20 on Mac.

When I run the following query in the information_schema schema, I get 3,105 rows:

PreparedStatement ps = conn.prepareStatement("select * from COLUMNS");
ResultSet rs = ps.executeQuery();
int numRows = 0;
while (rs.next()) {
    numRows++;
}

But if I do this repeatedly in 100 parallel threads, each thread using its own connection, I occasionally get a slightly different result set. About 0.5% of the time, the number of rows is slightly lower, like 3,098. It's inconsistent, but easily reproducible.

Notice that this is not select count(*) -- this is select * and then iterate over all the rows.

This only happens against tables in the information_schema catalog -- for instance the TABLES table.

When doing the same thing against tables in the mysql catalog, or any other catalog actually, this does not happen, so my best guess is that this is due to the fact that the information_schema schema is special, like maybe it's cached in the JDBC driver, or the server, or something like that.

Can anyone shed any light on this?

Here is the complete code, which should run against any MySQL once you change the URL, user name and password:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.ArrayList;
import java.util.concurrent.atomic.AtomicInteger;

public class ManyQueryThreads {

    private static final List<Thread> threads = new ArrayList<>();

    private static final List<QueryThread> queryThreads = new ArrayList<>();

    private static final int NUM_THREADS = 100;

    private static final int NUM_ITERS = 20;

    /**
     * This gets set by the first thread to complete the query. All subsequent queries are expected
     * to return the same number of rows.
     */
    private static final AtomicInteger expectedNumRows = new AtomicInteger(-1);

    private static class QueryThread implements Runnable {

        public boolean done = false;

        @Override
        public void run() {
            Connection conn;
            try {
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/information_schema?useSSL=false",
                        "root", "Password1");
            }
            catch(Exception ex) {
                throw new RuntimeException(ex);
            }

            for (int i = 0; i < NUM_ITERS; i++) {
                try {
                    PreparedStatement ps = conn.prepareStatement("select * from COLUMNS");
                    ResultSet rs = ps.executeQuery();
                    int numRows = 0;
                    while (rs.next()) {
                        numRows++;
                    }
                    expectedNumRows.compareAndExchange(-1, numRows);
                    if (numRows != expectedNumRows.get()) {
                        System.out.println("Incorrect number of rows, expected " +
                                expectedNumRows.get() + ", but got " + numRows + ", thread " + Thread.currentThread().getId() +
                                ", iteration " + i);
                    }

                    rs.close();
                    ps.close();
                }
                catch (Exception ex) {
                    throw new RuntimeException(ex);
                }
            }

            try {
                conn.close();
            }
            catch(Exception ex) {
                throw new RuntimeException(ex);
            }

            done = true;
        }
    }

    public static void main(String[] args) throws Exception {

        System.out.println("Creating " + NUM_THREADS + " threads...");
        long startTime = System.currentTimeMillis();
        for (int i = 0; i < NUM_THREADS; i++) {
            QueryThread qt = new QueryThread();
            queryThreads.add(qt);
            Thread t = new Thread(qt);
            threads.add(t);
        }

        System.out.println("Threads created, starting them for " + NUM_ITERS + " iterations...");
        for (int i = 0; i < NUM_THREADS; i++) {
            threads.get(i).start();
        }

        // Now wait for everyone to be done
        System.out.println("Threads are running, waiting for them to end...");
        deathWatch:
        while (true) {
            for (int i = 0; i < NUM_THREADS; i++) {
                QueryThread qt = queryThreads.get(i);
                if ( ! qt.done) {
                    Thread.sleep(100);
                    continue deathWatch;
                }
            }
            break deathWatch;
        }

        System.out.println("All done, time elapsed: " + (System.currentTimeMillis() - startTime) + "ms.");
    }
}

Typical output:

Creating 100 threads...
Threads created, starting them for 20 iterations...
Threads are running, waiting for them to end...
Incorrect number of rows, expected 3105, but got 3099, thread 49, iteration 4
Incorrect number of rows, expected 3105, but got 3086, thread 17, iteration 6
Incorrect number of rows, expected 3105, but got 3101, thread 64, iteration 8
Incorrect number of rows, expected 3105, but got 2853, thread 57, iteration 12
etc...