Softpedia >Linux >Database >Database Engines >H2 Database Engine >  Changelog

H2 Database Engine Changelog

What's new in H2 Database Engine 1.3.175

Jan 18, 2014
  • EXPLAIN was incorrect for queries with "ANY" or "SOME" conditions.
  • CallableStatement with "out" parameters: running the same statement twice could result in an exception ("parameter not set").
  • 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.
  • Issue 73: MySQL compatibility: support REPLACE, patch by Cemo Koc.
  • The spatial index now works in MVCC mode when using the MVStore storage.
  • MVStore: concurrency problems have been fixed. The API has been simplified.
  • Improve error message when dropping an index that belongs to a constraint, specify constraint in error message.
  • Issue 518: java.sql.Connection.commit() freezes after LOB modification with EXCLUSIVE connection
  • Issue 517: Create or replace view statement has no effect on the others already existing JDBC connection
  • Support 123L syntax as in Java; example: SELECT (2000000000L*2).
  • Issue 520: Add support for sequence max value, min value and cycle, patch by Daniel Gredler.
  • Issue 521: ScriptReader should implement Closeable
  • Issue 524: RunScript.execute does not close its Statement, patch from Gaul.
  • Add support for DB2 "WITH UR" clause, patch from litailang
  • Added support for ON DUPLICATE KEY UPDATE like MySQL with the values() function to update with the value that was to be inserted. Patch from Jean-Francois Noel.
  • Issue 522: Treat empty strings like NULL in Oracle compatibility mode, patch by Daniel Gredler.
  • Issue 527: Oracle compatibility mode: incorrect scale behavior, patch by Daniel Gredler.
  • Slightly reduce the memory cost of View metadata.
  • Extend support of "GRANT ALTER ANY SCHEMA TO " to allow grantee ability to manipulate tables
  • Issue 532: Javadoc for ErrorCode.ROLES_AND_RIGHT_CANNOT_BE_MIXED looks wrong
  • Issue 528: Add Oracle-compatible TO_CHAR function, patch by Daniel Gredler.
  • Issue 534: CREATE ALIAS NOCACHE, Patch by Nicolas Fortin
  • Fix an issue with storing Unicode surrogate pairs in CLOB columns.
  • H2 console: add SHIFT+ENTER "run selected" functionality
  • Fix bug in DB2 syntax "select * from test with ur", patch from litailang
  • Fix bug in DROP ALL OBJECTS when dealing with inter-schema dependencies.

New in H2 Database Engine 1.3.174 (Oct 21, 2013)

  • LIRS cache: bugfix for caches that only contain non-resident entries.
  • For in-memory databases, queries with a "group by" column that is also a hash index threw a RuntimeException.
  • Improved error message for some syntax errors.
  • File system abstraction: if used directly, some file systems did not work correctly with spliced byte buffers (the database engine doesn't use those).
  • To use the MVStore storage engine (which is still work in progress), append ";mv_store=true" to the database URL. Using the MVTableEngine when creating the table is no longer recommended.
  • To compile user defined functions, the javax.tools.JavaCompiler is now used if available, and no temporary files are created. This should solve problems when multiple H2 database concurrently compile the same user defined functions. To disable, system the system property "h2.javaSystemCompiler" to false.
  • Concurrently creating function aliases in different databases could result in the exception "javac: file not found".
  • The function "regexp_replace" threw the wrong kind of exception if the replacement string was invalid.
  • A checkpoint is now done every MAX_LOG_SIZE / 2 instead of every MAX_LOG_SIZE, so that the transaction log doesn't grow as large.
  • MVStore table engine: new setting "retention_time" to configure the time to retain old data. The default is 45 seconds.
  • The method TableEngine.createTable() now returns a Table object.
  • For read-only databases, for the trace level "debug", the trace info is written to the temp directory.
  • Closing the file lock will now wait until the background thread is stopped.
  • In version 1.3.172, a performance regression was introduced when fixing the issue 389 (when there is a multi-column primary key, H2 does not seem to always pick the right index). This was related to boosting an index that matches the "order by" column list (the wrong index was used in some cases).
  • 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.
  • Fix bug with ALLOW_LITERALS=NONE, where the periodic analyze table on insert would throw an exception. A similar problem was fixed in the Console tool.
  • Issue 510: Make org.h2.bnf public for consumption by external projects, patch by Nicolas Fortin
  • Issue 509: Important fix on ValueGeometry, patch by Nicolas Fortin (with some tweaking) Make ValueGeometry#getDimensionCount more reliable. Add unit test to check for illegal ValueGeometry comparison Add unit test for conversion of Geometry object into Object Add optional export to MANIFEST.MF for JTS Geometry classes Validate that geometry values can be represented in WKB.
  • Issue 506: RFE: Include Thread.getName() in case of a deadlock
  • Adding support for "GRANT ALTER ANY SCHEMA TO ", patch by John Yates

New in H2 Database Engine 1.3.173 (Jul 29, 2013)

  • This version improves MySQL and PostgreSQL compatibility, improves the MVStore storage engine, and adds the new data type "geometry" for spatial data.

New in H2 Database Engine 1.3.171 (Mar 18, 2013)

  • Security: the TCP server did not correctly restrict access rights of clients in some cases. This was specially a problem when using the flag "tcpAllowOthers".
  • H2 Console: the session timeout can now be configured using the system property "h2.consoleTimeout".
  • Issue 431: Improved compatibility with MySQL: support for "ENGINE=InnoDB charset=UTF8" when creating a table.
  • Issue 249: Improved compatibility with MySQL in the MySQL mode: now the methods DatabaseMetaData methods stores*Case*Identifiers return the same as MySQL when using the MySQL mode.
  • Issue 434: H2 Console didn't work in the Chrome browser due to a wrong viewport argument.
  • There was a possibility that the .lock.db file was not deleted when the database was closed, which could slow down opening the database.
  • The SQL script generated by the "script" command contained inconsistent newlines on Windows.
  • When using trace level 4 (SLF4J) in the server mode, a directory "trace.db" and an empty file was created on the client side. This is no longer made.
  • Optimize IN(...) queries: there was a bug in version 1.3.170 if the type of the left hand side didn't match the type of the right hand side. Fixed.
  • Optimize IN(...) queries: there was a bug in version 1.3.170 for comparison of the type "X IN(NULL, NULL)". Fixed.
  • Timestamps with timezone that were passed as a string were not always converted correctly. For example "2012-11-06T23:00:00.000Z" was converted to "2012-11-06" instead of to "2012-11-07" in the timezone CET. Thanks a lot to Steve Hruda for reporting the problem!
  • New table engine "org.h2.mvstore.db.MVTableEngine" that internally uses the MVStore to persist data. To try it out, append ";DEFAULT_TABLE_ENGINE=org.h2.mvstore.db.MVTableEngine" to the database URL. This is still very experimental, and many features are not supported yet. The data is stored in a file with the suffix ".mv.db".
  • New connection setting "DEFAULT_TABLE_ENGINE" to use a specific table engine if none is set explicitly. This is to simplify testing the MVStore table engine.
  • MVStore: encrypted stores are now supported. Only standardized algorithms are used: PBKDF2, SHA-256, XTS-AES, AES-128.
  • MVStore: improved API thanks to Simo Tripodi.
  • MVStore: maps can now be renamed.
  • MVStore: store the file header also at the end of each chunk, which results in a further reduced number of write operations.
  • MVStore: a map implementation that supports concurrent operations.
  • MVStore: unified exception handling; the version is included in the messages.
  • MVStore: old data is now retained for 45 seconds by default.
  • MVStore: compress is now disabled by default, and can be enabled on request.
  • Support ALTER TABLE ADD ... AFTER. Patch from Andrew Gaul (argaul at gmail.com). Fixes issue 401.
  • Improved OSGi support. H2 now registers itself as a DataSourceFactory service. Fixes issue 365.
  • Add a DISK_SPACE_USED system function. Fixes issue 270.
  • Fix a compile-time ambiguity when compiling with JDK7, thanks to a patch from Lukas Eder.
  • Supporting dropping an index for Lucene full-text indexes.
  • Optimized performance for SELECT ... ORDER BY X LIMIT Y OFFSET Z queries for in-memory databases using partial sort (by Sergi Vladykin).
  • Experimental off-heap memory storage engine "nioMemFS:" and "nioMemLZF:", suggestion from Mark Addleman.
  • Issue 438: JdbcDatabaseMetaData.getSchemas() is no longer supported as of 1.3.169.
  • MySQL compatibility: support for ALTER TABLE tableName MODIFY [COLUMN] columnName columnDef. Patch from Ville Koskela.
  • Issue 404: SHOW COLUMNS FROM tableName does not work with ALLOW_LITERALS=NUMBERS.
  • Throw an explicit error to make it clear we don't support the TRIGGER combination of SELECT and FOR EACH ROW.
  • Issue 439: Utils.sortTopN does not handle single-element arrays.

New in H2 Database Engine 1.3.170 (Dec 3, 2012)

  • Issue 407: The TriggerAdapter didn't work with CLOB and BLOB columns.
  • PostgreSQL compatibility: support for data types BIGSERIAL and SERIAL as an alias for AUTO_INCREMENT.
  • Issue 417: H2 Console: the web session timeout didn't work, resulting in a memory leak. This was only a problem if the H2 Console was run for a long time and many sessions were opened.
  • Issue 412: Running the Server tool with just the option "-browser" will now log a warning.
  • Issue 411: CloseWatcher registration was not concurrency-safe.
  • MySQL compatibility: support for CONCAT_WS. Thanks a lot to litailang for the patch!
  • PostgreSQL compatibility: support for EXTRACT(WEEK FROM dateColumn). Thanks to Prashant Bhat for the patch!
  • Fix for a bug where we would sometimes use the wrong unique constraint to validate foreign key constraints.
  • Support BOM at the beginning of files for the RUNSCRIPT command
  • Fix in calling SET @X = IDENTITY() where it would return NULL incorrectly
  • Fix ABBA deadlock between adding a constraint and the H2-Log-Writer thread.
  • Optimize IN(...) queries where the values are constant and of the same type.
  • Restore tool: the parameter "quiet" was not used and is now removed.
  • Fix ConcurrentModificationException when creating tables and executing SHOW TABLES in parallel. Reported by Viktor Voytovych.
  • Serialization is now pluggable using the system property "h2.javaObjectSerializer". Thanks to Sergi Vladykin for the patch!

New in H2 Database Engine 1.3.169 (Sep 21, 2012)

  • The default jar file is now compiled for Java 6.
  • The new jar file will probably not end up in the central Maven repository in the next few weeks because Sonatype has disabled automatic synchronization from SourceForge (which they call 'legacy sync' now). It will probably take some time until this is sorted out. The H2 jar files are deployed to http://h2database.com/m2-repo/com/h2database/h2/maven-metadata.xml and http://hsql.sourceforge.net/m2-repo/com/h2database/h2/maven-metadata.xml as usual.
  • A part of the documentation and the H2 Console has been changed to support the Apple retina display.
  • The CreateCluster tool could not be used if the source database contained a CLOB or BLOB. The root cause was that the TCP server did not synchronize on the session, which caused a problem when using the exclusive mode.
  • Statement.getQueryTimeout(): only the first call to this method will query the database. If the query timeout was changed in another way than calling setQueryTimeout, this method will always return the last value. This was changed because Hibernate calls getQueryTimeout() a lot.
  • Issue 416: PreparedStatement.setNString throws AbstractMethodError. All implemented JDBC 4 methods that don't break compatibility with Java 5 are now included in the default jar file.
  • Issue 414: for some functions, the parameters were evaluated twice (for example "char(nextval(..))" ran "nextval(..)" twice).
  • The ResultSetMetaData methods getSchemaName and getTableName could return null instead of "" (an empty string) as specified in the JDBC API.
  • Added compatibility for "SET NAMES" query in MySQL compatibility mode.

New in H2 Database Engine 1.3.168 (Jul 14, 2012)

  • The message "Transaction log could not be truncated" was sometimes written to the .trace.db file even if there was no problem truncating the transaction log.
  • New system property "h2.serializeJavaObject" (default: true) that allows to disable serializing Java objects, so that the objects compareTo and toString methods can be used.
  • Dylan has translated the H2 Console tool to Korean. Thanks a lot!
  • Executing the statement CREATE INDEX IF ALREADY EXISTS if the index already exists no longer fails for a read only database.
  • MVCC: concurrently updating a row could result in the row to appear deleted in the second connection, if there are multiple unique indexes (or a primary key and at least one unique index). Thanks a lot to Teruo for the patch!
  • Fulltext search: in-memory Lucene indexes are now supported.
  • Fulltext search: UUID primary keys are now supported.
  • Apache Tomcat 7.x will now longer log a warning when unloading the web application, if using a connection pool.
  • H2 Console: support the Midori browser (for Debian / Raspberry Pi)
  • When opening a remote session, don't open a temporary file if the trace level is set to zero
  • Use HMAC for authenticating remote LOB id's, removing the need for maintaining a cache, and removing the limit on the number of LOBs per result set.
  • H2 Console: HTML and XML documents can now be edited in an updatable result set. There is (limited) support for editing multi-line documents.

New in H2 Database Engine 1.3.164 (Feb 4, 2012)

  • Updating a result set in a trigger is now supported.

New in H2 Database Engine 11.3.162 (Nov 28, 2011)

  • The following system properties are no longer supported: h2.allowBigDecimalExtensions, h2.emptyPassword, h2.minColumnNameMap, h2.returnLobObjects, h2.webMaxValueLength.
  • When using a VPN, starting a H2 server did not work (for some VPN software).
  • Oracle compatibility: support for DECODE(...).
  • Lucene fulltext search: creating an index is now faster if the table already contains data. Thanks a lot to Angel Leon from the FrostWire Team for the patch!
  • Update statements with a column list in brackets did not work if the list only contains one column. Example: update test set (id)=(id).
  • Read-only databases in a zip file did not work when using the -baseDir option.
  • Issue 334: SimpleResultSet.getString now also works for Clob columns.
  • Subqueries with an aggregate did not always work. Example: select (select count(*) from test where a = t.a and b = 0) from test t group by a
  • Server: in some (theoretical) cases, exceptions while closing the connection were ignored.
  • Server.createTcpServer, createPgServer, createWebServer: invalid arguments are now detected.
  • The selectivity of LOB columns is no longer calculated because indexes on LOB columns are not supported (however this should have little effect on performance, as the selectivity is calculated from the hash code and not the data).
  • New experimental system property "h2.modifyOnWrite": when enabled, the database file is only modified when writing to the database. When enabled, the serialized file lock is much faster for read-only operations.
  • A NullPointerException could occur in TableView.isDeterministic for invalid views.
  • Issue 180: when deserializing objects, the context class loader is used instead of the default class loader if the system property "h2.useThreadContextClassLoader" is set. Thanks a lot to Noah Fontes for the patch!
  • When using the exclusive mode, LOB operations could cause the thread to block. This also affected the CreateCluster tool (when using BLOB or CLOB data).
  • The optimization for "group by" was not working correctly if the group by column was aliased in the select list.
  • Issue 326: improved support for case sensitive (mixed case) identifiers without quotes when using DATABASE_TO_UPPER=FALSE.

New in H2 Database Engine 1.3.160 (Sep 13, 2011)

  • Computed columns could not refer to itself.
  • Issue 340: Comparison with "x = all(select ...)" or similar in a view or subquery that was used as a table returned the wrong result.
  • Comparison with "x = all(select ...)" or similar returned the wrong result for some cases (for example if the subquery returned no rows, or multiple rows with the same value, or null, or if x was null).
  • Issue 335: Could not run DROP ALL OBJECTS DELETE FILES on older databases with CLOB or BLOB data. The problem was that the metadata table was not locked in some cases, so that a rollback could result in a corrupt database in a database if the lob storage was upgraded.
  • Source code switching using //## has been simplified. The Java 1.5 tag has been removed because is no longer needed.
  • The JDBC methods PreparedStatement.setTimestamp, setTime, and setDate with a calendar, and the methods ResultSet.getTimestamp, getTime, and getDate with a calendar converted the value in the wrong way, so that for some timestamps the converted value was wrong (where summertime starts, one hour per year).
  • Invalid tables names in 'order by' columns were not detected in some cases. Example: select x from dual order by y.x
  • Issue 329: CASE expression: data type problem is not detected.
  • Issue 311: File lock mode serialized: selecting the next value from a sequence didn't work after a pause, because the database thought this is a read-only operation.

New in H2 Database Engine 1.3.159 (Aug 14, 2011)

  • Creating a temporary table with the option 'transactional' will now also create the indexes in transactional mode, if the indexes are included in the 'create table' statement as follows: "create local temporary table temp(id int primary key, name varchar, constraint x index(name)) transactional".
  • The database file size grows now 35%, but at most 256 MB at a time.
  • Improved error message on network configuration problems.
  • The build now support an offline build using ./build.sh offline. This will list the required dependencies if jar files are missing.
  • The BLOB / CLOB data was dropped a little bit before the table was dropped. This could cause "lob not found" errors when the process was killed while a table was dropped.
  • "group_concat(distinct ...)" did not work correctly in a view or subquery (the 'distinct' was lost). Example: select * from (select group_concat(distinct 1) from system_range(1, 3));
  • Database URLs can now be re-mapped to another URL using the system property "h2.urlMap", which points to a properties file with database URL mappings.
  • When using InputStream.skip, trying to read past the end of a BLOB failed with the exception "IO Exception: Missing lob entry: ..." [90028-...].
  • The in-memory file system "memFS:" now has limited support for directories.
  • To test recovery, append ;RECOVER_TEST=64 to the database URL. This will simulate an application crash after each 64 writes to the database file. A log file named databaseName.h2.db.log is created that lists the operations. The recovery is tested using an in-memory file system, that means it may require a larger heap setting.
  • Converting a hex string to a byte array is now faster.
  • The SQL statement "shutdown defrag" could corrupt the database if the process was killed while the shutdown was in progress. The same problem could occur when the database setting "defrag_always" was used.

New in H2 Database Engine 1.3.157 (Jun 27, 2011)

  • The syntax for CREATE TABLE ... AS SELECT ... has been changed: options such as NOT PERSISTENT are now before the query (AS SELECT), because otherwise they might be interpreted as a part of the query.
  • With version 1.3.156, the DB2 mode could not be used with CLOB and BLOB data.
  • When upgrading from an older 1.3.x version to version 1.3.156, CLOB and BLOB data was lost in many cases.
  • The optimization for COUNT(..) on columns that are not nullable was also used for COUNT(DISTINCT ..), which is incorrect.
  • PgServer: non-admin users could not open a database.
  • Non-admin users could not open a database using a mode (MODE=xxx) if the database was already open, even if the mode matched.
  • The SQL state of the following exceptions has been changed: From 90009, 90010, 90011 to 22007: "Cannot parse DATE/TIME/TIMESTAMP constant ...".
  • There is a problem when opening a database file in a timezone that has different daylight saving rules: the time part of dates where the daylight saving doesn't match will differ. This is not a problem within regions that use the same rules (such as, within USA, or within Europe), even if the timezone itself is different. As a workaround, export the database to a SQL script using the old timezone, and create a new database in the new timezone. The same problem occurs when accessing a database over TCP/IP if the client and server timezones use different rules, if an old server or client is used.

New in H2 Database Engine 1.3.156 (Jun 19, 2011)

  • Issue 323: NullPointerException when using IN(...) with a function table.
  • Clob.toString() and Blob.toString() now include the trace identifier.
  • The SQL parser silently ignored characters such as '^' or '\'. Now a syntax error is thrown.
  • ROUND(..) now also works with just one parameter.
  • Access to system tables is now more restrictive for non-admin users (the tables can be still listed, but some of the data is not included).
  • BLOB: InputStream.skip is now more efficient in embedded mode.
  • The default expression of a column may no longer reference the table, because a database with such a table couldn't be opened normally.
  • Referenced objects in check constraints could be dropped, which resulted in a database that can't be opened normally.
  • Improved Oracle compatibility for NVL2. Thanks again to litailang for the patch.
  • CSVREAD now supports the option 'preserveWhitespace'.
  • Recursive queries with many rows could throw an IndexOutOfBoundsException.
  • The auto-server mode can't be combined with an in-memory database. This invalid combination wasn't detected so far. Now trying to open a database in this way fails.
  • Improved performance for reading and writing date and time values.
  • Java functions: array component types are now preserved, so that a ResultSet.getObject() will return Integer[] if the Java functions returns Integer[]. Thanks to Noel Grandin for the patch. Component types are transferred over the network, but not persisted in the database file as of now.
  • Recover tool: the script generated by the recover tool didn't work if fulltext search was used, because the triggers were created before the primary keys.
  • Issue 321: the database does not expect XA rollback without XA prepare (an exception was unnecessarily written into the .trace.db file).
  • Read-only databases: some queries (such as distinct queries) tried to create temporary tables, which failed for read only databases.
  • This database no longer uses finalize() except for temporary files. The system property "h2.runFinalize" is no longer supported. Unclosed connections are still detected, but the opening stack trace is now collected only if connections are not closed repeatedly (starting with the second unclosed connection). To detect unclosed connections, a PhantomReference is used.
  • Issue 322: Left joins didn't work when used as subquery in the form clause.
  • A prepared statement if type CALL that returned a result set could throw a NullPointerException if executed multiple times.
  • The following built-in functions were marked as deterministic and therefore not re-executed when using in a prepared statement: LOCK_TIMEOUT, LINK_SCHEMA, CANCEL_SESSION, FILE_READ.
  • Count on a column that can not be null is now optimized to COUNT(*). Thanks a lot to Ross Judson for providing a patch and test case!
  • The following prepared statements are now cached as well: insert, update, delete, call, merge, and transactional commands.
  • The built-in connection pool is now a bit faster.
  • The scale was not set correctly in some cases when using CREATE TABLE AS SELECT if there was no explicit column definition.
  • When trying to connect to a server using TCP/IP failed, it will retry at most until the timeout. Before, it was the timeout multiplied with the retry count.

New in H2 Database Engine 1.3.155 (May 30, 2011)

  • Compatibility with other databases has been improved.
  • The faster ICU4J collator is now supported.
  • Support for Java 1.3 and Java 1.4 has been removed.

New in H2 Database Engine 1.3.151 Beta (Feb 14, 2011)

  • Server: it was possible to open a database outside of the base URL (if one was set). The test case was broken.
  • The H2 Console tool now supports a database URL as a command line option.
  • Clob.getSubString and Blob.getBytes are now up to 3 times faster.
  • New database setting DEFAULT_CONNECTION (disabled by default) to support DriverManager.getConnection("jdbc:default:connection"). Please note the Oracle JDBC driver will try to resolve this database URL if it is loaded before the H2 driver.
  • JaQu: the static map Db.TOKENS was not synchronized.
  • DatabaseMetaData.getProcedureColumns returned the wrong data (it also returned the Connection parameter if there was any).
  • Issue 284: If the query cache was used (enabled by default in version 1.3.x), and multiple threads used the same connection, and the same query but different prepared statements, parameters of one prepared statement could be overwritten by another.
  • If the query cache was used (enabled by default in version 1.3.x), parameters of re-used queries were not reset in prepared statements.
  • CallableStatement: the first row of the result set was skipped when using CallableStatement.execute().
  • Batch update exceptions now include the root cause and all chained exceptions (getNextException).
  • Reading a large result set with a BLOB or CLOB column could throw a NullPointerException.
  • Support for the MS SQL Server syntax VARCHAR(MAX).
  • The Recover tool could print "/ by zero" on an empty database.
  • For tables without a single column primary key of type INT or LONG, the unique row id had gaps, which didn't look nice.
  • Each table now has a pseudo-column "_ROWID_" to get the unique row id (only enabled for version 1.3.x).
  • User defined functions can now have parameters of any class. Values of type OTHER (or OBJECT or JAVA_OBJECT) are automatically de-serialized in that case.
  • Linked tables: for NUMERIC column, Oracle reports precision 0 and scale -127. A workaround has been implemented (the same as for DECIMAL columns in version 1.3.150).
  • Issue 279: Auto-Server mode: unclear error message when trying to connect using AUTO_SERVER if the database is already open without the flag (Connection is broken: "null").
  • Issue 238: Can drop a column that has a single-column constraint.

New in H2 Database Engine 1.3.150 Beta (Jan 28, 2011)

  • CSVREAD / CSVWRITE: instead of setting the options one by one, all options can be combined into a space separated key-value pairs.
  • CSVREAD / CSV tool: there is a new option "lineCommentCharacter" to set or disable line comments. For H2 version 1.2, the default is '#' (as before). For H2 version 1.3, line comments are disabled by default.
  • Issue 277: JaQu didn't correctly convert a CLOB column to a String.
  • ./build.sh testNetwork could block if there was a network configuration problem.
  • Reading input streams from the classpath is now supported. Example: RUNSCRIPT FROM 'classpath:org/h2/samples/newsfeed.sql'.
  • PreparedStatement.toString() now includes the parameter values in a human readable format.
  • New database setting OPTIMIZE_IN_SELECT (enabled by default for version 1.3.x). If enabled, IN(SELECT...) conditions are faster if there are many rows in the table or subquery.
  • Linked tables: for DECIMAL column, Oracle reports precision 0 and scale -127. A workaround has been implemented.
  • TCP Server: a base directory with a prefix with a database name with prefix didn't work as expected (this only applies to non-disk and wrapped file systems).
  • Version 1.3: when re-running the same query so that the previous result was re-used, and if the result set was large so that it was stored externally (in a temp file or in a temp table), then reading rows from both result sets resulted in an exception or wrong behavior.
  • A new sample application that shows how to create a read-only database in a zip file where the database file is split into multiple smaller parts.
  • The Backup tool now supports compressing all files in a given directory.
  • H2 Console: the special syntax @list and @meta can not be combined.
  • The wrong error message was thrown when trying to open a database where the database file could not be read for some reason (for example because the split file system was used, and the file was split at the wrong position).
  • There was a memory leak in the trace system. Opening and closing many connections could run out of memory.
  • The scan-resistant cache type "TQ" (two queue) is again available. To use it, append ;CACHE_TYPE=TQ to the database URL.

New in H2 Database Engine 1.3.148 Beta (Dec 13, 2010)

  • The built-in connection pool was simplified a bit. The dispose() method no longer throws an exception (it only logs it).
  • The database upgrade (to upgrade from H2 version 1.1.x) has been simplified.
  • The feature to log all errors (system properties h2.logAllErrors and h2.logAllErrorsFile) has been removed.
  • When starting the H2 Console, the properties file can now be completely disabled.
  • Server.openBrowser no longer writes to System.out directly if opening the URL failed.
  • The INSERT optimizations (INSERT ... DIRECT SORTED SELECT) were not parsed correctly when using a column list.
  • H2 Console: the tables and columns are now listed for SQLite as well.
  • H2 Console: improved autocomplete feature (also simplified the source code for this feature).
  • The DbStarter servlet context listener now starts the server before opening a connection, so that connecting using the server mode works.
  • The Shell tool can now enable, disable, and display the current autocommit mode.
  • There is a limitation in the cluster mode: The SQL statement SET AUTOCOMMIT FALSE doesn't work. To disable autocommit, the method Connection.setAutoCommit(false) needs to be called. This is now documented. See also issue 259.
  • Connection pool / DataSource: a NullPointerException was thrown when using a database URL that doesn't start with "jdbc:h2:".
  • Using the java.sql.Blob or java.sql.Clob interfaces could throw the wrong exception after the object was closed.
  • LOB: in server mode, if the feature "LOBs in database" is disabled, the temporary files are now created in the temp directory (the same as when "LOBs in database" is enabled).

New in H2 Database Engine 1.2.147 (Nov 22, 2010)

  • H2 Console: the server properties directory can now be set on the command line using java ... -properties . Issue 159.
  • New feature INSERT INTO ... DIRECT SELECT, mainly to speed up loading tables from a CSV file. When using DIRECT, then the results from the query are directly applied in the target table without any intermediate step (without temporary file).
  • Converting binary data to UUID was incorrect when the converting more than 31 bytes. Now always the first 16 bytes are used.
  • Improved date / time arithmetics. Now supported are multiplying and dividing a time with / by a number, adding and subtracting a time to / from a time or date, subtracting a time or timestamp from a timestamp.
  • The Ant build script has been removed because it is no longer up to date.
  • Nested outer joins without brackets were not working as expected. Example: select * from a inner join b inner join c on c.id = b.id on b.id = a.id;
  • JMX (the Java management extension) is now supported. Issue 253.
  • Creating a comment on a column didn't work if the schema name was equal the database name.
  • When using multi-version concurrency, re-running a prepared statement with the same parameters would sometimes give the same result even if another connection committed a change (the previous result was sometimes re-used incorrectly).
  • When using SELECT_FOR_UPDATE_MVCC (lock only the selected rows when using MVCC), the selected rows were sometimes not locked correctly.
  • When using Lucene 3, the index files were not always closed when the database was closed.
  • Concurrently preparing a statement and altering a table could throw a table not found exception.
  • When concurrently preparing many statements with a subquery, in some cases the query didn't run (Column "..." must be in the GROUP BY list).
  • CallableStatement: now the syntax "{? = CALL...}" is supported as well.
  • Lob in databases: two small BLOB or CLOB values were always considered equal. This also affected unique indexes.
  • The shutdown hook is (re-) enabled by default even for H2 version 1.3.x.
  • When creating a database from a recovery script, the same user can now be used (CREATE USER now always includes IF EXISTS).
  • The H2 Console now prints a status message if started with parameters.

New in H2 Database Engine 1.3.146 Beta (Nov 9, 2010)

  • This is a beta version. The next version will be 1.2.147. The differences between 1.2.x and 1.3.x are:
  • In version 1.3.x, h2.lobInDatabase (store CLOB and BLOB in the database file) is enabled by default.
  • In version 1.3.x, ANALYZE_AUTO is 2000 (automatic ANALYZE).
  • In version 1.3.x, DB_CLOSE_ON_EXIT (the shutdown hook) is disabled by default.
  • In version 1.3.x, DROP_RESTRICT (default action for DROP is RESTRICT) is enabled.
  • In version 1.3.x, FUNCTIONS_IN_SCHEMA (allow to store functions in a schema) is enabled.
  • In version 1.3.x, LARGE_TRANSACTIONS (support for very large transactions) is enabled. Changes to tables without a primary key can be buffered to disk.
  • In version 1.3.x, MAX_MEMORY_ROWS_DISTINCT is set to a 10000.
  • In version 1.3.x, NESTED_JOINS (nested joins and right outer joins) is enabled.
  • In version 1.3.x, OPTIMIZE_INSERT_FROM_SELECT (speed up CREATE TABLE ... AS SELECT) is enabled.
  • In version 1.3.x, OPTIMIZE_OR (convert OR conditions to IN(..) if possible) is enabled.
  • In version 1.3.x, QUERY_CACHE_SIZE is enabled.
  • In version 1.3.x, SELECT_FOR_UPDATE_MVCC (MVCC and SELECT FOR UPDATE) is enabled.
  • In version 1.3.x, RUNSCRIPT and SCRIPT (commands and tools), use UTF-8 by default (ScriptCommand.charset).
  • In version 1.3.x, Lucene 3 is used by default. Issue 147.
  • When the system property h2.lobInDatabase is set, the lob tables were always created when closing the database, even if the tables were not needed.
  • In the MySQL mode, SHOW TABLES didn't work, and meta data tables were not case insensitive. Updatable result sets didn't work as expected. Issue 249.
  • Connection-created Clob and Blob objects can now be filled using Clob.setCharacterStream(1), Clob.setString(1, s), Blob.setBytes(1, x), Blob.setBinaryStream(1). Issue 235.
  • Trying to convert a very small CLOB to a BLOB or a very small CLOB to a BLOB threw a NullPointerException.
  • INFORMATION_SCHEMA.SETTINGS: now the current page count can be queried using select * from information_schema.settings where name = 'info.PAGE_COUNT'.
  • For an invalid value exception, the parameter name and value was switched in some cases.
  • When using trace level 3, opening a database no longer prints there is a EOFException (because that is the expected behavior).
  • CALL calling a Java function with return type ResultSet and return value null threw a ClassCastException.
  • SELECT ... FROM CSVREAD no longer creates a temporary file. Some large imports are now twice as fast.

New in H2 Database Engine 1.2.145 (Nov 2, 2010)

  • A new String cache is now created at most once every 5 second, so that out of memory exceptions are not artificially delayed.
  • After closing a database, the writer thread will stop after 100 ms at the latest (instead of 1 second).
  • Opening a large database could fail in theory if the process was killed and then not fully compacted (because the unique transaction log key was re-used).
  • Views now store the schema for Java functions if "functions in schemas" is enabled (system property h2.functionsInSchema).
  • Improved error message if the LOB files are already deleted on the client side.
  • Closing a Statement or PreparedStatement at the same time as executing a statement could throw a strange exception. Issue 241.
  • The Recover tool did not work with compressed lob files (set compress_lob lzf). Also, sometimes the Recover tool was very slow, specially using a 64-bit JVM, because it tried to de-compress lob files even if they were not compressed (which could allocate a lot of memory).
  • H2 Console: webAllowOthers and webSSL were swapped if they didn't exist in the properties file. Issue 244.
  • When the system property h2.lobInDatabase is set, CREATE TABLE ... AS SELECT with a LOB column did not always work.
  • Some system properties are not supported any longer, because they can already be set in the database URL. The constants are: CACHE_SIZE_DEFAULT, CACHE_TYPE_DEFAULT, DEFAULT_LOCK_MODE, LOCK_MODE_READ_COMMITTED, DEFAULT_MAX_LENGTH_INPLACE_LOB, DEFAULT_MAX_LENGTH_INPLACE_LOB2, DEFAULT_MAX_MEMORY_UNDO, DEFAULT_MAX_OPERATION_MEMORY, DEFAULT_PAGE_SIZE, DEFAULT_RESULT_SET_CONCURRENCY, MIN_WRITE_DELAY.
  • Various system properties have been replaced with database level settings: ALIAS_COLUMN_NAME, ANALYZE_AUTO, ANALYZE_SAMPLE, DATABASE_TO_UPPER, DEFAULT_ESCAPE, DEFRAG_ALWAYS, DROP_RESTRICT, ESTIMATED_FUNCTION_TABLE_ROWS, FUNCTIONS_IN_SCHEMA, LARGE_RESULT_BUFFER_SIZE, LARGE_TRANSACTIONS, MAX_COMPACT_COUNT, MAX_COMPACT_TIME, MAX_MEMORY_ROWS_DISTINCT, MAX_QUERY_TIMEOUT, NESTED_JOINS, OPTIMIZE_DISTINCT, OPTIMIZE_EVALUATABLE_SUBQUERIES, OPTIMIZE_INSERT_FROM_SELECT, OPTIMIZE_IN_LIST, OPTIMIZE_IS_NULL, OPTIMIZE_OR, OPTIMIZE_SUBQUERY_CACHE, OPTIMIZE_TWO_EQUALS, OPTIMIZE_UPDATE, PAGE_STORE_INTERNAL_COUNT, PAGE_STORE_TRIM, QUERY_CACHE_SIZE, RECOMPILE_ALWAYS, RECONNECT_CHECK_DELAY, SELECT_FOR_UPDATE_MVCC, SHARE_LINKED_CONNECTIONS. See the Javadoc documentation of DbSettings for details. The system properties are still supported for backward compatibility.
  • When the system property h2.nestedJoins was enabled, some outer joins returned the wrong result.
  • Opening a database could throw a NullPointerException.
  • After a crash, the database file did not always shrink because old transaction log pages were not removed from the file.
  • Various CallableStatement methods could throw a NullPointerException.
  • LOB files were not deleted when running DROP ALL OBJECTS.
  • MS SQL Server compatibility: support "N" in front of string literals ("National Language" strings). Issue 240.
  • CAST: when converting a string to binary, it is hex encoded (every byte two characters); a hex string can be converted to a number by first converting it to binary. Examples: CAST(CAST('FFFF' AS BINARY) AS INT) = 65535, CAST(65535 AS BINARY) = '0000FFFF'.
  • When a domain (user defined data type) contained a user defined function, the database could not be opened.
  • CAST('011' AS INT) will no longer use decode the value as an octal number (using Integer.decode) but now use Integer.parseInt. The same logic applied to byte, short, and long. This also means that trying to convert hex numbers (0x...) with CAST now fails.
  • A table in the Recover tool script could not be imported if the script contained duplicate keys.

New in H2 Database Engine 1.2.144 (Oct 16, 2010)

  • The Windows .bat files did not contain CR+LF.
  • The LOB storage (when the system property h2.lobInDatabase is set) is now only initialized when needed.
  • The index condition columnName IS NULL was not efficient when using a clustered index.
  • The Recover tool did not work for databases larger than 2 GB.
  • In memory database: outer joins with a condition "column is null" could return the wrong result.
  • Temporary files were not created in the temporary directory of the system, but in the current working directory. This affected temporary undo log files for read-only databases, temporary files for large result sets when using read-only databases, and temporary LOB files when storing LOBs in the database (system property h2.lobInDatabase). When the setting h2.lobInDatabase is not set, temporary LOB files are still stored in a sub-directory (.lobs.db/) of the current working directory.
  • New experimental feature to speed up CREATE TABLE ... AS SELECT. To enable the feature, set the system property h2.optimizeInsertFromSelect to true. For large queries, performance can be doubled.
  • When LOB objects are stored in the database (using the experimental setting h2.lobInDatabase), and using the MVCC mode, opening a database with uncommitted transactions could throw a NullPointerException.
  • Support of stored procedures with OUT parameters in CallableStatement is implemented.
  • PreparedStatement.getMetaData() was not always consistent with resulting ResultSet.getMetaData().
  • The build tool now uses JAVA_HOME for javac as well. Issue 233.
  • Opening and closing encrypted databases is now much faster.
  • H2 Console: new experimental feature to support file download and upload, but only if there is a directory called "transfer" in the current working directory. For security reasons, only very simple file names are supported. Sub-directories are not supported. To upload, use transfer.jsp; to download, use transfer/fileName.
  • A new sample application that shows how to manually create an encrypted and compressed script file.
  • Performance has been improved a bit.
  • DatabaseMetaData.getJDBCMajorVersion now returns 4 (instead of 3) when the driver is compiled with Java 6 (the default).
  • Re-added the internal utility class BitField which improves performance of opening and closing a database (because it supports setByte / getByte, unlike java.util.BitSet).
  • INFORMATION_SCHEMA.SESSIONS: the start time of a SQL statement is no longer set in each case. It is only set for long running statements. This change should improve performance a bit on some devices, specially when using Android.
  • A old databases without user name and password (created with H2 version 1.2.142 or older) couldn't be opened with version 1.2.143. Fixed.
  • The database upgrade classes have been renamed.
  • The database did not automatically upgrade when using a connection pool or data source.
  • JaQu: a database object can now also be created using a DataSource. Issue 227.
  • The built-in profiler now better supports the Dalvik VM.
  • The jarSmall build target no longer includes assertions (SysProperties.CHECK, CHECK2, system properties h2.check and h2.check2).
  • The jarSmall build target excluded the DbUpgrade classes by mistake.

New in H2 Database Engine 1.2.143 (Sep 21, 2010)

  • If the user name and password are not set or empty, then the password is not hashed. To disable this behavior, set the system property h2.emptyPassword to false. TCP server: the default user name for the management database is now an empty string.
  • Cluster: auto-commit was disabled after opening a connection.
  • Connection.getAutoCommit() is now much faster, specially when using the server mode.
  • Statement.cancel() had no effect when using the server mode.
  • SCRIPT: the SQL script no longer contains settings that match the default value.
  • Statement.cancel() did not work when running RUNSCRIPT and SCRIPT.
  • Statement.cancel() could cause a NullPointerException.
  • In some cases (specially when using a small cache size or a large database over 1 GB) for some operations like ALTER TABLE the data was written before the transaction log entries, which could cause a corrupt database if the process was killed during the ALTER TABLE operation. Thanks a lot to Victor Pyankov for helping solve this problem!
  • The Recover tool could throw a ArrayIndexOutOfBoundsException when trying to recover a corrupt database file.
  • The SCRIPT command and tool now uses the default file encoding for the system now instead of UTF-8 (as before). In most cases, this will not affect existing applications, as the encoding only affects how SQL identifiers (table names and so on) that contain special characters are stored. Table data is escaped, and therefore is no problem.
  • The SCRIPT command and tool now also support the CHARSET option (like RUNSCRIPT).
  • When closing a database, sometimes empty space in the database was not freed up.
  • Trying to create a view with parameters in the query will now throw an exception. So far, creating the view was allowed, but the parameter value was not used (null was used instead).
  • New experimental feature SHUTDOWN DEFRAG. This option re-orders the pages while closing the database so that table scans are faster.
  • When using the MULTI_THREADED option, concurrently reading from a database (specially from a larger database, or when using a small cache size) could throw an exception.
  • When adding a comment to a column that has a check constraint, the database couldn't be re-opened.
  • New system property h2.lobClientMaxSizeMemory to set the maximum size of a LOB object to keep in memory on the client side when using the server mode.

New in H2 Database Engine 1.2.142 (Sep 1, 2010)

  • Documentation for using H2 on Android devices has been added under Tutorial - Android.
  • An ArrayIndexOutOfBoundsException was thrown when querying the table information_schema.function_aliases while there are any user defined aggregate functions.
  • The jar files are now about 50 KB smaller. In the last few versions they contained superfluous classes.
  • The default time to compact a database when closing (system property h2.maxCompactTime) is now 0.2 seconds instead of 1 second.
  • Opening an closing a database is now faster.
  • In theory, when using indexes with large index rows (or when using a very small page size), removing rows could throw an internal exception in some cases.
  • Inserting rows in reverse order could throw an ArrayIndexOutOfBoundsException in some cases (specially rows larger than the page size, which is 2 KB by default). Issue 226.
  • H2 Console: with Chrome and Safari, resizing the table/query frame doesn't work. Issue 225.
  • New experimental database file locking mechanism "FS" to use native file locking (no *.lock.db file is created in this case, and no background thread is started). This mechanism may not work on all systems (on some systems it's possible to lock the same file multiple times within the same virtual machine, and on some system native file locking is not working or unlocking is not working).

New in H2 Database Engine 1.2.141 (Aug 22, 2010)

  • New experimental pseudo-encryption algorithm "FOG". It makes the data appear to be encrypted. This algorithm is cryptographically extremely weak, and should only be used to hide data from reading the plain text using a text editor. Please let us know if you think this is useful or not.
  • Documentation: the grammar and function documentation can now be easier translated.
  • Password hash: in addition to connecting with the plain text password, H2 now supports connecting with the password hash. Like this you don't have to store plain text passwords in config files. For details, see the documentation at Advanced / Password Hash.
  • Lucene 3.x support was added in the source code, however it is not yet enabled by default and is not yet supported when using the default h2 jar file. To enable Lucene 3.x support, the source code of H2 needs to be switched using ./build.sh -Dlucene=3 switchSource, and then re-compile. To switch the source code back use ./build.sh -Dlucene=2 switchSource (replace ./build.sh with build.bat on Windows). The plan is to use Lucene 3 by default in H2 version 1.3.x. Issue 147.
  • The native fulltext search could cause a Java level deadlock if searching from multiple connections concurrently.
  • CREATE FORCE VIEW didn't work in most cases if a referenced table didn't exist.
  • MVCC: when trying to insert two rows with the same key from two connections, the second connection immediately threw the exception "Unique index or primary key violation". Instead, the second connection now waits throwing the exception until the first connection committed the change (same as when trying to concurrently update the same row).
  • Server mode: if the client ran with a different timezone setting than the server, date values got shifted by the difference between the timezones. This problem affected the data types DATE, TIME, and TIMESTAMP, when using PreparedStatement.setDate / setTime / setTimestamp and ResultSet.getDate / getTime / getTimestamp. To solve the problem, both the client and the server need to be updated (otherwise the old transfer protocol is used).
  • The built-in connection pool (JdbcConnectionPool) did not always honor the login timeout (the timeout could occur much too early). Thanks a lot to Dario Fassi for the patch!
  • Translation: the new messages have been translated to Spanish. Thanks a lot to Dario Fassi!
  • The table INFORMATION_SCHEMA.SETTINGS now contains all H2-specific system properties (the ones that start with "h2.") and that are explicitly set. Previously, some H2-specific settings (for example h2.analyzeAuto) were missing in this list.
  • EXPLAIN ANALYZE with an in-memory database threw an exception. Issue 216.
  • Data modifications (inserts, updates, and deletes) are now up to 5 times faster because converting objects to byte arrays is avoided if possible.
  • LOG=0 is now a bit faster (previously undo log entries were still written).
  • The command line tools now say so if the source directory of an option doesn't exist.
  • It is now allowed to truncate a table if referential integrity has been disabled for this table or database.
  • For unencrypted databases, the automatic upgrade temporary script file is now unencrypted again.

New in H2 Database Engine 1.2.139 (Jul 11, 2010)

  • There was a bug in the internal IntIntHashMap. There were now known side effects however.
  • ANALYZE now uses less memory.
  • Memory mapped file system: improved error messages if there is a problem.
  • The Lucene fulltext search did not work well when using special file systems such as split or nioMapped.
  • The memory mapped file system (nioMapped: file prefix) couldn't seek past the file length.
  • Some file system operations did not work with stacked file systems (for example split and nioMapped). This also affected the DeleteDbFiles tool.
  • New experimental query cache. The cache is only used if the SQL statement and all parameters match. Each session has it's own cache with the given size. Only the last returned result per query is cached. Only SELECT statements are cached (excluding UNION and FOR UPDATE statements). This works for both statements and prepared statement. To enable, set the system property h2.commandCacheSize to a value larger than 0. There is currently no plan to enable this option by default in future versions.
  • Conditions with many OR operations could throw an UnsupportedOperationException if h2.optimizeOr was enabled.
  • XA connection: after transaction commit or rollback, the physical connection is set into autocommit mode.
  • MULTI_THREADED did not work correctly and could throw the exception "try to add a record twice" for larger databases because cache access was not correctly synchronized.
  • Automatic database upgrade from non page store is now possible. If the classes from the h2mig_pagestore_addon.jar file are in the classpath, the database will be converted to the current page store format automatically. The file can be found here: http://h2database.com/h2mig_pagestore_addon.jar
  • The MultiDimension tools was extended with a few helper methods. The API was unified a bit.
  • ODBC: MS Access could not link to a table with unique index or primary key. To solve this problem, index meta data is currently disabled for ODBC. When using the new H2 version to access a database over ODBC, the PostgreSQL catalog is automatically upgraded. Using a database with an upgraded PostgreSQL catalog with an older version of H2 is still possible, but not over ODBC.
  • ODBC: MS Access could not link to a table with a name containing '_'.
  • ODBC: additional connection settings can now be added to the database name. Example: ~/test;cipher=xtea. Therefore, encrypted databases are supported.
  • Local temporary tables can now be created without having to commit a transaction using CREATE LOCAL TEMPORARY TABLE TEMP(ID INT PRIMARY KEY) TRANSACTIONAL.
  • New system property h2.dropRestrict (default false) to change the default action for DROP TABLE and DROP VIEW (false for CASCADE, true for RESTRICT). The plan is to enable this property by default in version 1.3.x.
  • DROP TABLE now also supports CASCADE (still the default) and RESTRICT.
  • The wrong exception was thrown when trying to connect to a server if the server was not running.
  • UNION queries where the first query contains a nested query were parsed incorrectly. Example: "select 1 from (select 2 from dual) union select 3 from dual" was parsed as "select 1 from ((select 2 from dual) union select 3 from dual)". Fixed.
  • Support ALTER SCHEMA name RENAME TO newName (rename schema). (patch from Kerry Sainsbury)

New in H2 Database Engine 1.2.138 (Jun 28, 2010)

  • New experimental features have been added to support nested joins and a case-sensitive catalog name.
  • The benchmarks have been run using recent database versions.

New in H2 Database Engine 1.2.133 (Apr 10, 2010)

  • A new experimental LOB storage mechanism is available. When enabled, CLOB and BLOB data is stored in hidden tables in the database. To use it, set the system property "h2.lobInDatabase" to "true". Compression is not yet implemented. Duplicate objects are only stored once. Usually writing is slower, but reading is faster (mainly because of caching).
  • The Shell tool threw a NumberFormatException on a empty statement.
  • The h2small-*.jar did not support "drop all objects delete files". Now it is supported (the DeleteDbFiles tool is now included).
  • Operations that don't fit in memory are now faster. Temporary file handling was changed.
  • The default maximum log stream size is now 2 MB. This is a good value according to some performance tests.
  • New system property "h2.syncMethod" to configure what method to call when closing the database, on checkpoint, and on CHECKPOINT SYNC. The default is "sync" which calls RandomAccessFile.getFD().sync().
  • ROWNUM could not be used for pagination. The follow query returned no rows: select x from (select *, rownum as r from system_range(1, 3)) where r=2;
  • Workaround for a java.sql.Date issue: years above 9999 are not formatted correctly. When calling ResultSet.getString() on a date value, an alternative formatting algorithm is used.
  • Prepared statements with nested subqueries did not always return the correct result. Example: select ?, ?, (select count(*) from test t, (select id from test where 0=?) t2 where t2.id=t.id) from test
  • When using MULTI_THREADED=TRUE, the exception following exception could be thrown: "object already exists: TABLES"
  • Comparison of integer columns against float numbers was not always correct. Issue 182.
  • H2 Console: Editing the result of a multi-line queries failed. Issue 183.
  • H2 Console: if the settings file (.h2.properties) can't be accessed, the exception is now ignored.

New in H2 Database Engine 1.2.131 (Mar 5, 2010)

  • Older versions of H2 (version 1.2.127 and older) could not connect to a new version (version 1.2.129 and newer). The connection blocked when trying to connect. Now the connection no longer blocks, instead a SQL exception is thrown.
  • In versions 1.2.129 and 1.2.130, a database could not be opened sometimes after dropping tables or indexes, and then creating new indexes for existing tables. The exception contained the text "parent not found" (version 1.2.129) or "Table not found" (version 1.2.130).
  • After the database was closed, a null pointer exception could occur in Database.flush.
  • An IndexOutOfBoundsException could occur in the page store (eg. on "delete from table"). Fixed.

New in H2 Database Engine 1.2.122 (Oct 29, 2009)

  • The new page store file format has been improved.
  • The native fulltext search now supports streaming.
  • The sources and Javadoc files are now in the Maven repository.
  • The SQL syntax is now documented using HTML railroad diagrams.

New in H2 Database Engine 1.2.120 (Oct 5, 2009)

  • New databases are now stored in a new file format.
  • The compatibility with PostgreSQL has been improved.
  • Linked tables now support default values when inserting, updating, or merging.
  • Writing to linked tables in readonly databases is now allowed.

New in H2 Database Engine 1.1.118 (Sep 4, 2009)

  • Better optimizations for OR, IN(..), and IN(SELECT..) are available, but not enabled by default yet.
  • GaeVFS (Google App Engine Virtual File System) is better supported.
  • Various tools now use Java 5 var-args.
  • The ORM tool JaQu now includes a decompiler to support pure Java / Scala conditions.
  • The Italian translation of H2 Console has been completed.

New in H2 Database Engine 1.1.114 (Jun 2, 2009)

  • Changes and new functionality:
  • Java 1.5 is now required to run H2.
  • Fulltext search: Data is no longer deleted and re-inserted if there was no change.
  • Bugfixes:
  • ResultSetMetaData.getColumnClassName returned the wrong class for CLOB and BLOB columns.
  • In some situations, an ArrayIndexOutOfBoundsException was thrown when adding rows.
  • The Recover tool did not always work.

New in H2 Database Engine 1.1.113 (May 22, 2009)

  • Shell tool: the built-in commands EXIT, HELP, ?, LIST, and so on didn't work with a semicolon at the end.
  • JDK 1.5 is now required to build the jar file. However it is still possible to create a jar file for Java 1.4. For details, see buildRelease.sh and buildRelease.bat. As an alternative, compile using JDK 1.5 or 1.6 and use Retrotranslator to create a Java 1.4 version (http://retrotranslator.sourceforge.net/).
  • When deleting or updating many rows in a table, the space in the index file was not re-used in the default mode (persistent database, b-tree index, LOG=1). This caused the index file to grow over time. Workarounds were to delete and re-created the index file, alter the table (add a remove a column), or append ;LOG=2 to the database URL. To disable the change, set the system property h2.reuseSpaceBtreeIndex to false.
  • Identifiers with a digit and then a dollar sign didn't work. Example: A1$B.
  • MS SQL Server compatibility: support for linked tables with NVARCHAR, NCHAR, NCLOB, and LONGNVARCHAR.
  • Android: Workaround for a problem when using read-only databases in zip files (skip seems to be implemented incorrectly on the Android system).
  • Calling execute() or prepareStatement() with null as the SQL statement now throws an exception.
  • Benchmark: the number of executed statements was incorrect. The H2 database was loaded at the beginning of the test to collect results, now it is loaded at the very end. Thanks to Fred Toussi from HSQLDB for reporting those problems. However the changed do not affect the relative performance.
  • H2 Console: command line settings are no longer stored in the properties file. They are now only used for the current process, except if they are explicitly saved.
  • Cache: support for a second level soft-references cache. To enable it, append ;CACHE_TYPE=SOFT_LRU (or SOFT_TQ) to the database URL, or set the system property h2.cacheTypeDefault to "SOFT_LRU" / "SOFT_TQ". Enabling the second level cache reduces performance for small databases, but speeds up large databases. It makes sense to use it if the available memory size is unknown. Thanks a lot to Jan Kotek!

New in H2 Database Engine 1.1.112 (May 4, 2009)

  • JdbcPreparedStatement.toString() could throw a NullPointerException.
  • EclipseLink: Added H2Platform.supportsIdentity().
  • Connection pool: the default login timeout is now 5 minutes.
  • After truncating tables, opening large databases could become slow because indexes were always re-built unnecessarily when opening.
  • More bugs in the server-less multi-connection mode have been fixed: Sometimes parameters of prepared statements were lost when a reconnecting. Concurrent read operations were slow. To improve performance, executeQuery(..) must be used for queries (execute(..) switches to the write mode, which is slow).
  • GROUP BY queries with a self-join (join to the same table) that were grouped by columns with indexes returned the wrong result in some cases.
  • Improved error message when the .lock.db file modification time is in the future.
  • The MERGE statement now returns 0 as the generated key if the row was updated.
  • Running code coverage is now automated.
  • A file system implementation can now be registered using FileSystem.register.
  • The database file system is no longer included in the jar file, it moved to the test section.

New in H2 Database Engine 1.1.108 (Mar 1, 2009)

  • Multiple processes can read and write to a database without having to start a server.
  • The compatibility with MySQL has been improved.
  • Exception messages now always contain the SQL statement and the row data.
  • The autocomplete feature has been improved a bit.
  • When the shutdown hook closed the database, the last log file was deleted too early.

New in H2 Database Engine 1.1.107 (Jan 26, 2009)

  • Enabling the trace mechanism by creating a file is no longer supported. The MySQL compatibility extension fromUnixTime now uses the English locale.
  • Some database metadata operations did not work for non-admin users.
  • When using LOG=2, the index file grew quickly in some situations.
  • In versions 1.1.105-106, old encrypted script files could not be processed.

New in H2 Database Engine 1.1.106 (Jan 4, 2009)

  • Built-in data types can now only be changed if no tables exist. DatabaseMetaData.getPrimaryKeys: The column PK_NAME now contains the constraint name instead of the index name.
  • The license was changed a bit: previously, the license was modified to say "Swiss law".
  • This is changed back to the original "US law". Statement.setQueryTimeout did not work correctly for some statements.
  • Nested IN(..) constructs did not always work.

New in H2 Database Engine 1.1.105 (Dec 19, 2008)

  • The tool DeleteDbFiles sometimes deleted files of other databases.
  • LIKE and IN(..) did not always work inside a sub-query.
  • User defined aggregate functions did not always work.
  • MySQL compatibility has been improved.
  • Foreign key constraints with mixed case column names did not work.
  • In JaQu, the maximum length of a column can now be set.

New in H2 Database Engine 1.1.104 (Nov 29, 2008)

  • The emergency reserve file has been removed.
  • The DATEDIFF function is now compatible with MS SQL Server.
  • Improved plugins for ActiveObjects and Oracle Toplink Essential are included.
  • The cache memory usage calculation is more conservative.
  • Large databases on FAT file systems are now supported.
  • The database now tries to detect if the Web application is stopped.
  • A memory leak in the fulltext search is fixed.
  • The JaQu object-relation mapping tool has been improved.

New in H2 Database Engine 1.1.103 (Nov 7, 2008)

  • Improved PostgreSQL compatibility for the functions NEXTVAL and CURRVAL.
  • Aliases for built-in data types can now be re-mapped.
  • Less heap memory is needed when multiple databases are open.
  • Date values that match the daylight saving time end were not allowed in time zones where the daylight saving time ends at midnight for years larger than 2037.
  • This is a bug in java.util.Date, but a workaround is implemented in H2 that solves most problems.
  • Referential integrity for in-memory databases didn't work in some cases.

New in H2 Database Engine 1.1.102 (Oct 24, 2008)

  • ResultSetMetaData.getColumnName now returns the alias name, except for table columns.
  • Temporary files are now deleted at the latest when the database is closed.
  • This release fixes a memory leak which occurred when creating and dropping tables in a loop.
  • In-memory databases no longer write files when using BLOB or CLOB.

New in H2 Database Engine 1.1.101 (Oct 17, 2008)

  • OSGi is now supported.
  • Improved Glassfish/Toplink support has been added.
  • Compatibility with MS SQL Server has been improved for linked tables.
  • Queries with more than ten tables are now faster.
  • Opening large databases is now faster.
  • New ISO date functions have been implemented.
  • Local temporary tables now support indexes.
  • The automatic server and re-connect features have been improved.

New in H2 Database Engine 1.1.100 (Oct 5, 2008)

  • Some features are now enabled by default that were previously optional.
  • The database driver can now automatically reconnect if the connection is lost.
  • The H2 Console tool now works with the JDBC-ODBC bridge and supports command line options.
  • Database names can now be one character long.
  • Large objects now work for in-memory databases in server mode in Linux.

New in H2 Database Engine 1.0.79 (2008-09-26) (Sep 27, 2008)

  • Row level locking is now enabled when using MVCC (multi version concurrency).
  • Multiple processes can now access the same database.
  • The SHOW SQL statement is now supported.
  • The linked table can now share a connection, and can be read-only, temporary, and point to different schemas.
  • The storage re-use algorithm and the hash calculation for byte arrays are faster.

New in H2 Database Engine 1.0.78 (2008-08-28) (Aug 29, 2008)

  • Column aliases can now be used in GROUP BY and HAVING.
  • Java methods with variable number of parameters can now be used.
  • MySQL date functions were added.
  • The Japanese translation has been improved.