title: h2 index corruption? embedded database loaded with runscript has "invisible" rows
body: <p>Using h2 in embedded mode, I am restoring an in memory database from a script backup that was previously generated by h2 using the SCRIPT command.  </p>
  
  <p>I use this URL:</p>
  
  <pre><code>jdbc:h2:mem:main
  </code></pre>
  
  <p>I am doing it like this:</p>
  
  <pre><code>FileReader script = new FileReader("db.sql");
  RunScript.execute(conn,script);
  </code></pre>
  
  <p>which, according to the doc, should be similar to this SQL:</p>
  
  <pre><code>RUNSCRIPT FROM 'db.sql'
  </code></pre>
  
  <p>And, inside my app they do perform the same.  But if I run the load using the web console using h2.bat, I get a different result.</p>
  
  <p>Following the load of this data in my app, there are rows that I know are loaded but are not accessible to me via a query.  And these queries demonstrate it:</p>
  
  <pre><code>select count(*) from MY_TABLE yields 96576
  select count(*) from MY_TABLE where ID &lt;&gt; 3238396 yields 96575
  select count(*) from MY_TABLE where ID = 3238396 yields 0
  </code></pre>
  
  <p>Loading the web console and using the same RUNSCRIPT command and file to load yields a database where I <b>can</b> find the row with that ID.</p>
  
  <p>My first inclination was that I was dealing with some sort of locking issue.  I have tried the following (with no change in results):</p>
  
  <ol>
  <li>manually issuing a conn.commit() after the RunScript.execute()</li>
  <li>appending ;LOCK_MODE=3 and the ;LOCK_MODE=0 to my URL</li>
  </ol>
  
  <p>Any pointers in the right direction on how I can identify what is going on?  I ended up inserting :</p>
  
  <pre><code>Server.createWebServer("-trace","-webPort","9083").start()
  </code></pre>
  
  <p>So that I could run these queries through the web console to sanity check what was coming back through JDBC.  The problem happens consistently in my app and consistently doesn't happen via the web console.  So there must be something at work.</p>
  
  <p>The table schema is not exotic.  This is the schema column from </p>
  
  <pre><code>select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME='MY_TABLE'
  
  CREATE MEMORY TABLE PUBLIC.MY_TABLE(
     ID INTEGER SELECTIVITY 100,
     P_ID INTEGER SELECTIVITY 4,
     TYPE VARCHAR(10) SELECTIVITY 1,
     P_ORDER DECIMAL(8, 0) SELECTIVITY 11,
     E_GROUP INTEGER SELECTIVITY 1,
     P_USAGE VARCHAR(16) SELECTIVITY 1
  )
  </code></pre>
  
  <p>Any push in the right direction would be really appreciated.</p>
  
  <p><B>EDIT</B></p>
  
  <p>So it seems that the database is corrupted in some way just after running the RunScript command to load it.  As I was trying to debug to find out what is going on, I tried executing the following:</p>
  
  <pre><code>delete from MY_TABLE where ID &lt;&gt; 3238396
  </code></pre>
  
  <p>And I ended up with:</p>
  
  <pre><code>Row not found when trying to delete from index "PUBLIC.MY_TABLE_IX1: 95326", SQL statement:
  delete from MY_TABLE where ID &lt;&gt; 3238396 [90112-178] 90112/90112 (Help)
  </code></pre>
  
  <p>I then tried dropping and recreating all my indexes from within the context, but it had no effect on the overall problem.</p>
  
  <p>Help!</p>
  
  <p><b>EDIT 2</b></p>
  
  <p>More information:  The problem occurs due to the creation of an index.  (I believe I have found a bug in h2 and I have working on creating a minimal case that reproduces it).  The simple code below will reproduce the problem, if you have the right set of data.</p>
  
  <pre><code>public static void main(String[] args)
  {
        try
        {
               final String DB_H2URL = "jdbc:h2:mem:main;LOCK_MODE=3";
               Class.forName("org.h2.Driver");
               Connection c = DriverManager.getConnection(DB_H2URL, "sa", "");
               FileReader script = new FileReader("db.sql");
               RunScript.execute(c,script);
               script.close();
               Statement st = c.createStatement();
               ResultSet rs = st.executeQuery("select count(*) from MY_TABLE where P_ID = 3238396");
               rs.next();
               if(rs.getLong(1) == 0)
                     System.err.println("It happened");
               else
                     System.err.println("It didn't happen");
        } catch (Throwable e) {
               // TODO Auto-generated catch block
               e.printStackTrace();
        }
  }
  </code></pre>
  
  <p>I have reduced the db.sql script to about 5000 rows and it still happens.  When I attempted to go to 2500 rows, it stopped happening.  If I remove the last line of the db.sql (which is the index creation), the problem will also stop happening.  The last line is this:</p>
  
  <pre><code>CREATE INDEX PUBLIC.MY_TABLE_IX1 ON PUBLIC.MY_TABLE(P_ID);
  </code></pre>
  
  <p>But the data is an important player in this.  It still appears to only ever be the one row and the index somehow makes it inaccessible.</p>
  
  <p><b>EDIT 3</b></p>
  
  <p>I have identified the minimal data example to reproduce.  I stripped the table schema down  to a single column, and I found that the values in that column don't seem to matter -- just the number of rows.  Here is the contents of (snipped with obvious stuff) of my db.sql generated via the SCRIPT command:</p>
  
  <pre><code>;            
  CREATE USER IF NOT EXISTS SA SALT '8eed806dbbd1ea59' HASH '6d55cf715c56f4ca392aca7389da216a97ae8c9785de5d071b49de5436b0c003' ADMIN;          
  CREATE MEMORY TABLE PUBLIC.MY_TABLE(
      P_ID INTEGER SELECTIVITY 100
  );   
  -- 5132 +/- SELECT COUNT(*) FROM PUBLIC.MY_TABLE;            
  INSERT INTO PUBLIC.MY_TABLE(P_ID) VALUES
  (1),
  (2),
  (3),
  ...  snipped you obviously have breaks in the bulk insert here ...
  (5143),
  (3238396);    
  CREATE INDEX PUBLIC.MY_TABLE_IX1 ON PUBLIC.MY_TABLE(P_ID);
  </code></pre>
  
  <p>But that will recreate the problem.  [Note that my numbering skips a number every time there was a bulk insert line.  So there really is 5132 rows, though you see 5143  select count(*) from MY_TABLE yields 5132].  Also, I seem to be able to recreate the problem in the WebConsole directly now by doing:</p>
  
  <pre><code>drop table MY_TABLE
  runscript from 'db.sql'
  select count(*) from MY_TABLE where P_ID = 3238396
  </code></pre>
  
  <p>You have recreated the problem if you get 0 back from the select when you know you have a row in there.</p>
  
  <p>Oddly enough, I seem to be able to do</p>
  
  <pre><code>select * from MY_TABLE order by P_ID desc
  </code></pre>
  
  <p>and I can see the row at this point.  But going directly for the row:</p>
  
  <pre><code>select * from MY_TABLE where P_ID = 3238396
  </code></pre>
  
  <p>Yields nothing.</p>
  
  <p>I just realized that I should note that I am using <b>h2-1.4.178.jar</b></p>