title: MySQL: inconsistent result sets from information_schema
body: <p>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.</p>
  
  <p>When I run the following query in the <code>information_schema</code> schema, I get 3,105 rows:</p>
  
  <pre><code>PreparedStatement ps = conn.prepareStatement("select * from COLUMNS");
  ResultSet rs = ps.executeQuery();
  int numRows = 0;
  while (rs.next()) {
      numRows++;
  }
  </code></pre>
  
  <p>But if I do this repeatedly in 100 parallel threads, each thread using its own connection, I <em>occasionally</em> 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.</p>
  
  <p>Notice that this is not <code>select count(*)</code> -- this is <code>select *</code> and then iterate over all the rows.</p>
  
  <p>This only happens against tables in the <code>information_schema</code> catalog -- for instance the <code>TABLES</code> table.</p>
  
  <p>When doing the same thing against tables in the <code>mysql</code> catalog, or any other catalog actually, this does not happen, so my best guess is that this is due to the fact that the <code>information_schema</code> schema is special, like maybe it's cached in the JDBC driver, or the server, or something like that.</p>
  
  <p>Can anyone shed any light on this?</p>
  
  <p>Here is the complete code, which should run against any MySQL once you change the URL, user name and password:</p>
  
  <pre><code>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&lt;Thread&gt; threads = new ArrayList&lt;&gt;();
  
      private static final List&lt;QueryThread&gt; queryThreads = new ArrayList&lt;&gt;();
  
      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 &lt; 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 &lt; 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 &lt; 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 &lt; 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.");
      }
  }
  
  </code></pre>
  
  <p>Typical output:</p>
  
  <pre><code>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...
  </code></pre>