H2 Memory Database: Unselectable Data
<p>I've been using a persistent data store for nearly a year now, connecting with the string</p>

<p><code>jdbc:h2:gpfp;MV_STORE=FALSE;TRACE_LEVEL_FILE=0;FILE_LOCK=NO</code></p>

<p>and running unit tests; all has been good and well.  Recently, I decided to try the complete in-memory mode instead of just using memory tables.  I connected to my database using the H2 console and executed</p>

<p><code>SCRIPT TO 'c:\temp\mpfp.db'</code></p>

<p>to generate a backup that I would load to an in-memory database using.</p>

<p><code>RUNSCRIPT FROM 'c:\temp\mpfp.db'</code></p>

<p>This appears to work well in-that I generate an in-memory database with the tables and content I need; I tested this both in the console and through my application.  However, when I run my unit tests I noticed a single test now fails (the 15th out of 19 to be exact)...  I'm executing a simple select on a column and the db engine is unable to retrieve the results, but if I select the entire dataset and scroll through I can find the value.  I've confirm this to NOT be a case-sensitivity issue in my manual testing.</p>

<p>What's happening here?  I checked the data in the database and in the generated file from SCRIPT TO.  I haven't changed any of my unit tests just the database composition; memory vs persistent.</p>

<p>It may help to know that the DDL that generates the original tables uses CREATE MEMORY TABLE for each table and I'm using H2 version 1.4.178...  Could this be a problem with MV_STORE?  I explicitly set it to false in the persistent store because I was having a similar problem where I wasn't getting results, but appending MV_STORE=FALSE to the jdbc:h2:mem:... didn't seem to do anything.  I also bumped the JVM memory to 2g and 4g as another test and the outcome is still the same.</p>