H2 Database Engine

The download package comes with advanced documentation and the database's source code.

What is new in this release:

• MVStore: in some cases, CLOB/BLOB data blocks were removed incorrectly when opening a database.
• MVStore: updates that affected many rows were were slow in some cases if there was a secondary index.
• Using "runscript" with autocommit disabled could result in a lock timeout on the internal table "SYS".
• Issue 603: there was a memory leak when using H2 in a web application. Apache Tomcat logged an error message: "The web application ... created a ThreadLocal with key of type [org.h2.util.DateTimeUtils$1]".
• When using the MVStore, running a SQL script generate by the Recover tool from a PageStore file failed with a strange error message (NullPointerException), now a clear error message is shown.
• Issue 605: with version 1.4.186, opening a database could result in an endless loop in LobStorageMap.init.
• Queries that use the same table alias multiple times now work. Before, the select expression list was expanded incorrectly. Example: "select * from a as x, b as x".
• The MySQL compatibility feature "insert ... on duplicate key update" did not work with a non-default schema.
• Issue 599: the condition "in(x, y)" could not be used in the select list when using "group by".
• The LIRS cache could grow larger than the allocated memory.
• A new file system implementation that re-opens the file if it was closed due to the application calling Thread.interrupt(). File name prefix "retry:". Please note it is strongly recommended to avoid calling Thread.interrupt; this is a problem for various libraries, including Apache Lucene.
• MVStore: use RandomAccessFile file system if the file name starts with "file:".
• Allow DATEADD to take a long value for count when manipulating milliseconds.
• When using MV_STORE=TRUE and the SET CACHE_SIZE setting, the cache size was incorrectly set, so that it was effectively 1024 times smaller than it should be.
• Concurrent CREATE TABLE... IF NOT EXISTS in the presence of MULTI_THREAD=TRUE could throw an exception.
• Fix bug in MVStore when creating lots of temporary tables, where we could run out of transaction IDs.
• Add support for PostgreSQL STRING_AGG function. Patch by Fred Aquiles.
• Fix bug in "jdbc:h2:nioMemFS" isRoot() function. Also, the page size was increased to 64 KB.

What is new in version 1.3.187:

• MVStore: in some cases, CLOB/BLOB data blocks were removed incorrectly when opening a database.
• MVStore: updates that affected many rows were were slow in some cases if there was a secondary index.
• Using "runscript" with autocommit disabled could result in a lock timeout on the internal table "SYS".
• Issue 603: there was a memory leak when using H2 in a web application. Apache Tomcat logged an error message: "The web application ... created a ThreadLocal with key of type [org.h2.util.DateTimeUtils$1]".
• When using the MVStore, running a SQL script generate by the Recover tool from a PageStore file failed with a strange error message (NullPointerException), now a clear error message is shown.
• Issue 605: with version 1.4.186, opening a database could result in an endless loop in LobStorageMap.init.
• Queries that use the same table alias multiple times now work. Before, the select expression list was expanded incorrectly. Example: "select * from a as x, b as x".
• The MySQL compatibility feature "insert ... on duplicate key update" did not work with a non-default schema.
• Issue 599: the condition "in(x, y)" could not be used in the select list when using "group by".
• The LIRS cache could grow larger than the allocated memory.
• A new file system implementation that re-opens the file if it was closed due to the application calling Thread.interrupt(). File name prefix "retry:". Please note it is strongly recommended to avoid calling Thread.interrupt; this is a problem for various libraries, including Apache Lucene.
• MVStore: use RandomAccessFile file system if the file name starts with "file:".
• Allow DATEADD to take a long value for count when manipulating milliseconds.
• When using MV_STORE=TRUE and the SET CACHE_SIZE setting, the cache size was incorrectly set, so that it was effectively 1024 times smaller than it should be.
• Concurrent CREATE TABLE... IF NOT EXISTS in the presence of MULTI_THREAD=TRUE could throw an exception.
• Fix bug in MVStore when creating lots of temporary tables, where we could run out of transaction IDs.
• Add support for PostgreSQL STRING_AGG function. Patch by Fred Aquiles.
• Fix bug in "jdbc:h2:nioMemFS" isRoot() function. Also, the page size was increased to 64 KB.

What is new in version 1.3.186:

• MVStore: imported BLOB and CLOB data sometimes disappeared. This was caused by a bug in the ObjectDataType comparison.
• Reading from a StreamStore now throws an IOException if the underlying data doesn't exist.
• MVStore: if there is an exception while saving, the store is now in all cases immediately closed.
• MVStore: the dump tool could go into an endless loop for some files.
• MVStore: recovery for a database with many CLOB or BLOB entries is now much faster.
• Group by with a quoted select column name alias didn't work. Example: select 1 "a" from dual group by "a"
• Auto-server mode: the host name is now stored in the .lock.db file.

What is new in version 1.3.185:

• MVStore: imported BLOB and CLOB data sometimes disappeared. This was caused by a bug in the ObjectDataType comparison.
• Reading from a StreamStore now throws an IOException if the underlying data doesn't exist.
• MVStore: if there is an exception while saving, the store is now in all cases immediately closed.
• MVStore: the dump tool could go into an endless loop for some files.
• MVStore: recovery for a database with many CLOB or BLOB entries is now much faster.
• Group by with a quoted select column name alias didn't work. Example: select 1 "a" from dual group by "a"
• Auto-server mode: the host name is now stored in the .lock.db file.

What is new in version 1.3.182:

• MVStore: improved error messages and logging; improved behavior if there is an error when serializing objects.
• OSGi: the MVStore packages are now exported.
• With the MVStore option, when using multiple threads that concurrently create indexes or tables, it was relatively easy to get a lock timeout on the "SYS" table.
• When using the multi-threaded option, the exception "Unexpected code path" could be thrown, specially if the option "analyze_auto" was set to a low value.
• In the server mode, when reading from a CLOB or BLOB, if the connection was closed, a NullPointerException could be thrown instead of an exception saying the connection is closed.
• DatabaseMetaData.getProcedures and getProcedureColumns could throw an exception if a user defined class is not available.
• Issue 584: the error message for a wrong sequence definition was wrong.
• CSV tool: the rowSeparator option is no longer supported, as the same can be achieved with the lineSeparator.
• Descending indexes on MVStore tables did not work properly.
• Issue 579: Conditions on the "_rowid_" pseudo-column didn't use an index when using the MVStore.
• Fixed documentation that "offset" and "fetch" are also keywords since version 1.4.x.
• The Long.MIN_VALUE could not be parsed for auto-increment (identity) columns.

What is new in version 1.3.181:

• Improved MySQL compatibility by supporting "use schema". Thanks a lot to Karl Pietrzak for the patch!
• Writing to the trace file is now faster, specially with the debug level.
• The database option "defrag_always=true" did not work with the MVStore.
• The JDBC escape syntax {ts 'value'} did not interpret the value as a timestamp. The same for {d 'value'} (for date) and {t 'value'} (for time). Thanks to Lukas Eder for reporting the issue.
• File system abstraction: support replacing existing files using move (currently not for Windows).
• The statement "shutdown defrag" now compresses the database (with the MVStore). This command can greatly reduce the file size, and is relatively fast, but is not incremental.
• The MVStore now automatically compacts the store in the background if there is no read or write activity, which should (after some time; sometimes about one minute) reduce the file size. This is still work in progress, feedback is welcome!
• Change default value of PAGE_SIZE from 2048 to 4096 to more closely match most file systems block size (PageStore only; the MVStore already used 4096).

What is new in version 1.3.180:

• MVStore: the store is now auto-compacted automatically up to some point, to avoid very large file sizes. This area is still work in progress.
• Sequences of temporary tables (auto-increment or identity columns) were persisted unnecessarily in the database file, and were not removed when re-opening the database.
• MVStore: an IndexOutOfBoundsException could sometimes occur MVMap.openVersion when concurrently accessing the store.
• The LIRS cache now re-sizes the internal hash map if needed.
• Optionally persist session history in the H2 console.
• Add client-info property to get the number of servers currently in the cluster and which servers that are available.
• Fix bug in changing encrypted DB password that kept the file handle open when the wrong password was supplied.

What is new in version 1.3.179:

• Recursive queries with many result rows (more than the setting "max_memory_rows") did not work correctly.
• The license has changed to MPL 2.0 + EPL 1.0.
• MVStore: temporary tables from result sets could survive re-opening a database, which could result in a ClassCastException.
• Issue 566: MVStore: unique indexes that were created later on did not work correctly if there were over 5000 rows in the table.
• MVStore: creating secondary indexes on large tables results in missing rows in the index.
• Metadata: the password of linked tables is now only visible for admin users.
• For Windows, database URLs of the form "jdbc:h2:/test" where considered relative and did not work unless the system property "h2.implicitRelativePath" was used.
• Windows: using a base directory of "C:/" and similar did not work as expected.
• Follow JDBC specification on Procedures MetaData, use P0 as return type of procedure.
• Issue 531: IDENTITY ignored for added column.
• FileSystem: improve exception throwing compatibility with JDK
• Spatial Index: adjust costs so we do not use the spatial index if the query does not contain an intersects operator.
• Fix multi-threaded deadlock when using a View that includes a TableFunction.
• Fix bug in dividing very-small BigDecimal numbers.

What is new in version 1.3.176:

• The file locking method 'serialized' is no longer documented, as it will not be available in version 1.4.
• The static method Csv.getInstance() was removed. Use the public constructor instead.
• The default user name for the Script, RunScript, Shell, and CreateCluster tools are no longer "sa" but an empty string.
• The stack trace of the exception "The object is already closed" is no longer logged by default.
• If a value of a result set was itself a result set, the result could only be read once.
• Column constraints are also visible in views.
• Granting a additional right to a role that already had a right for that table was not working.
• Spatial index: a few bugs have been fixed (using spatial constraints in views, transferring geometry objects over TCP/IP, the returned geometry object is copied when needed).
• Issue 551: the datatype documentation was incorrect.
• Issue 368: ON DUPLICATE KEY UPDATE did not work for multi-row inserts. Test case from Angus Macdonald.
• OSGi: the package javax.tools is now imported (as an optional).
• H2 Console: auto-complete is now disabled by default, but there is a hot-key (Ctrl+Space).
• H2 Console: auto-complete did not work with multi-line statements.
• CLOB and BLOB data was not immediately removed after a rollback.
• There is a new Aggregate API that supports the internal H2 data types (GEOMETRY for example). Thanks a lot to Nicolas Fortin for the patch!
• Referential integrity constraints sometimes used the wrong index, such that updating a row in the referenced table incorrectly failed with a constraint violation.
• The Polish translation was completed and corrected.
• Issue 545: Unnecessary duplicate code was removed.
• The profiler tool can now process files with full thread dumps.

What is new in version 1.3.175:

• Union queries: duplicate rows could be returned if the sub-queries contained "order by".
• The GEOMETRY data type now works for user defined functions that return a result set.
• PostgreSQL compatibility: the PgServer was not working properly when the setting database_to_upper was set to false.
• JdbcDataSource: the methods setUrl and getUrl where added as aliases for setURL and getURL. This should solve problems with the HikariCP (Hikari connection pool).
• Issue 537: H2 Console: when loading the schema, incorrect JDBC calls where issued, which caused the exception "Column PRECISION not found".
• Improved Geometry processing (issue 535).
• The collation can now be set in the database URL, even if there are data tables, if the collection is the same as the current collation.
• Improved Oracle compatibility for CASE WHEN and DECODE.
• The statement "drop all objects" did not work if a table depends on a view via a constraint.
• Subqueries or views with "order by" an alias expression could not be executed due to a regression introduced in version 1.3.174.

What is new in version 1.3.174:

• Improved spatial index and data type.
• Issue 467: OSGi Class Loader (ability to create reference to class in other ClassLoader, for example in another OSGi bundle).
• Queries "group by" that contain a subquery with an aggregate function returned the wrong result in some cases.
• Fix bug in unique and non-unique hash indexes which manifested as incorrect results when the search key was a different cardinal type from the table index key. e.g. where the one was INT and the other was LONG
• Bug: Changes to the database structure did not result in the Session query cache being invalidated.
• New feature from Davide Cavestro - allow using custom Java object serialization engines on a per-DB basis.
• When running the Recover tool on very large (>6G) databases, some statistics were reported with negative numbers.
• Add a CONTAINS_UNCOMMITTED column to the SESSIONS metadata table, to allow detecting when rogue sessions are creating large transactions.
• Some small fixes to the GEOMETRY support, patches by Nicolas Fortin.
• The BNF tool and the autocomplete feature are exported in OSGi, which makes it possible for smart editors to do autocomplete. Patch from Nicolas Fortin.
• Fix DROP ALL OBJECTS and DROP SCHEMA in the presence of tables with computed column dependencies.
• Session-temporary LOB's could sometimes accumulate, increasing the size of the DB file until shutdown. Now they are cleared out at every commit.
• There was a bug where a hash index with more than one column would be silently converted to a regular index. It will now throw an exception.
• Query Statistics: new feature which stores the newest 100 SQL queries executed and their performance data. Useful for tracking down badly performing queries.
• Fix an LOB deadlock between reading and updating LOB columns.
• Support the JDBC DatabaseMetaData#getClientInfoProperties() method, in the sense of returning an empty result, in order to make WebSphere happy.
• Make Server#openBrowser respect the $BROWSER environment variable. Add "chromium" to the list of browsers tried. Patch from Felix Kaiser.
• Fix to org.h2.util.ScriptReader when handling unclosed block comments.
• Make org.h2.util.ScriptReader throw a better exception when handling broken scripts which generate extremely large statements.

What is new in version 1.3.173:

• Support empty statements that just contains a comment.
• Server mode: if there was an error while reading from a LOB, the session was closed in some cases.
• Issue 463: Driver name and version are now the same in OsgiDataSourceFactory and JdbcDatabaseMetaData.
• JaQu: The data type VARCHAR is now (again) used for Strings (no longer TEXT, except when explicitly set).
• For in-memory databases, creating an index on a CLOB or BLOB column is no longer supported. This is to simplify the MVTableEngine.
• New column "information_schema.tables.row_count_estimate".
• Issue 468: trunc(timestamp) could return the wrong value (+12 hours), and trunc(number) throw a NullPointerException.
• The expression trunc(number) threw a NullPointerException.
• Fixed a deadlock when updating LOB's concurrently. See TestLob.testDeadlock2().
• Fixed a deadlock related to very large temporary result sets.
• Add "-list" command line option to Shell tool so that result-list-mode can be triggered when reading from a file.