Hi,
I need help with some unexpected behavior in Hibernate. For some reason, the underlying RDBMS thinks the database is readonly when I believe it should be open for read/write (but only in certain situations).
I am developing a desktop application with an embedded RDBMS in it, managed by Hibernate. This is what I am attempting to do in my application:
(1) Open the database read-only.
(2) Read version data from the database to determine if the database was created by a newer or older version of my software, and handle it appropriately.
(3) Close the database.
(4) If the software version recorded in the database is okay, open the database for read and write.
(5) Proceed with my application logic.
(6) Write changes to the database. This is where I run into problems in my application.
In my application, I can reproduce the unexpected behavior consistently when I use HSQLDB and when I use H2. Unfortunately, when I boil the problem down to a simple stand-alone program, I can reproduce it only with HSQLDB.
My simple stand-alone program is below, and it will use H2 or HSQLDB, depending on the command-line arguments.
To reproduce the behavior by using HSQLDB:
(1) Delete the database files if they exist.
(2) > java HibernateExample HSQLDB. It runs just fine the first time through, when no database exists.
(3) > java HibernateExample HSQLDB. This time (when re-opening an existing database) HSQL thinks it is read-only when it should not be, and Hibernate errors out with this message:
Code:
Jul 11, 2014 11:37:55 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: invalid transaction state: read-only SQL-transaction
Exception in thread "main" org.hibernate.exception.GenericJDBCException: could not prepare statement
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:196)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareStatement(StatementPreparerImpl.java:96)
at org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl.buildBatchStatement(AbstractBatchImpl.java:152)
at org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl.getBatchStatement(AbstractBatchImpl.java:141)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3102)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3587)
at org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:103)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:453)
at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:345)
at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350)
at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56)
at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1218)
at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:421)
at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101)
at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:177)
at HibernateExample.manipulatePersistentObjects(HibernateExample.java:293)
at HibernateExample.main(HibernateExample.java:39)
Caused by: java.sql.SQLException: invalid transaction state: read-only SQL-transaction
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$1.doPrepare(StatementPreparerImpl.java:103)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186)
... 16 more
Caused by: org.hsqldb.HsqlException: invalid transaction state: read-only SQL-transaction
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.Session.checkReadWrite(Unknown Source)
at org.hsqldb.StatementDMQL.checkAccessRights(Unknown Source)
at org.hsqldb.StatementInsert.<init>(Unknown Source)
at org.hsqldb.ParserDML.compileInsertStatement(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
at org.hsqldb.Session.compileStatement(Unknown Source)
at org.hsqldb.StatementManager.compile(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 20 more
To show how it runs with H2:
(1) Delete the database files if they exist.
(2) > java HibernateExample H2. It runs just fine the first time through, when no database exists.
(3) > java HibernateExample H2. It runs just fine when re-opening an existing database. This can be re-run all day without any problems.
This is confusing behavior:
(1) Delete the databases files if they exist.
(2) > java UberMain HSQLDB. It runs just fine even though it is calling "HibernateExample HSQLDB" twice.
And there seems to be another issue (it might be related):
(1) Comment out Line 33 of HibernateExample.java.
(2) Uncomment Line 34 of HibernateExample.java.
(3) Delete the database files if they exist.
(4) > java HibernateExample HSQLDB. It runs just fine the first time through, when no database exists. Notice that when readAndDisplayPersistentObjects(...) re-opens the database, the program can read the software version data from the database just fine.
(5) > java HibernateExample HSQLDB. This time, it acts as if the software version data was never written to the database.
I am NOT seeing this behavior in my larger application.
Now run it with H2:
(6) Delete the database files if they exist.
(7) > Java HibernateExample H2. It runs okay.
(8) > Java HibernateExample H2. It still runs okay.
Finally:
If both Line 33 and Line 34 are of HibernateExample.java both commented out, then everything runs as expected, for both HSQLDB and for H2. However, this means I lose my ability to manage the evolution of my schema.
I'm using H2 1024-06-23 (1.4.179). I'm using Hypersonic 2.3.2. I'm using Hibernate 4.3.1 (and I have tested it on Hibernate 4.3.5).
I've been chasing this issue for a week, so any help would be most appreciated.
Thanks,
Chris
Here's my code:
Code:
public class HibernateExample
{
final public static String DATABASE_NAME = "C:\\SFT\\testDatabase";
final public static int SOFTWARE_VERSION = 1;
final static public void main( String arguments[] )
{
boolean useH2;
org.hibernate.SessionFactory sessionFactory;
HibernateExample example;
if ( arguments.length == 0 )
{
System.err.println( "Must supply an argument, either \"H2\" or \"HSQLDB\"" );
return;
}
switch ( arguments[0] )
{
case "H2":
useH2 = true;
break;
case "HSQLDB":
useH2 = false;
break;
default:
System.err.println( "Unrecognized argument: " + arguments[0] );
return;
}
example = new HibernateExample();
example.checkDatabaseForSoftwareVersion_A( useH2 );
//example.checkDatabaseForSoftwareVersion_B( useH2 );
sessionFactory = example.getHibernateSessionFactory( useH2 );
try
{
example.manipulatePersistentObjects( sessionFactory );
example.readAndDisplayPersistentObjects( sessionFactory );
}
finally
{
sessionFactory.close();
}
if ( arguments.length == 1 )
{
System.exit( 0 );
}
}
private void checkDatabaseForSoftwareVersion_A( boolean useH2 )
{
java.io.File databaseFile;
org.hibernate.cfg.Configuration configuration;
java.util.Properties properties;
org.hibernate.boot.registry.StandardServiceRegistryBuilder standardServiceRegistryBuilder;
org.hibernate.service.ServiceRegistry serviceRegistry;
org.hibernate.SessionFactory sessionFactory;
org.hibernate.Session session;
java.util.List<SoftwareVersionData> versionDataList;
if ( useH2 )
{
databaseFile = new java.io.File( DATABASE_NAME + ".h2.db" );
}
else
{
databaseFile = new java.io.File( DATABASE_NAME + ".properties" );
}
if ( !(databaseFile.exists()) )
{
System.out.println( "Database file does not exist." );
return;
}
java.util.logging.Logger.getLogger( "org.hibernate" ).setLevel( java.util.logging.Level.SEVERE );
configuration = new org.hibernate.cfg.Configuration();
if ( useH2 )
{
configuration.setProperty( "hibernate.dialect", "org.hibernate.dialect.H2Dialect" );
configuration.setProperty( "hibernate.connection.driver_class", org.h2.Driver.class.getName() );
configuration.setProperty( "hibernate.connection.url", "jdbc:h2:file:" + DATABASE_NAME + ";ACCESS_MODE_DATA=r" );
configuration.setProperty( "hibernate.connection.username", "sa" );
configuration.setProperty( "hibernate.connection.password", "" );
}
else
{
configuration.setProperty( "hibernate.dialect", "org.hibernate.dialect.HSQLDialect" );
configuration.setProperty( "hibernate.connection.driver_class", org.hsqldb.jdbcDriver.class.getName() );
configuration.setProperty( "hibernate.connection.url", "jdbc:hsqldb:file:" + DATABASE_NAME + ";readonly=true" );
configuration.setProperty( "hibernate.connection.username", "SA" );
configuration.setProperty( "hibernate.connection.password", "" );
}
configuration.setProperty( "hibernate.connection.pool_size", "1" );
configuration.setProperty( "hibernate.cache.provider_class", "org.hibernate.cache.internal.NoCacheProvider" );
configuration.setProperty( "hibernate.show_sql", "false" );
configuration.addAnnotatedClass( SoftwareVersionData.class );
properties = configuration.getProperties();
standardServiceRegistryBuilder = new org.hibernate.boot.registry.StandardServiceRegistryBuilder();
standardServiceRegistryBuilder.applySettings( properties );
serviceRegistry = standardServiceRegistryBuilder.build();
sessionFactory = configuration.buildSessionFactory( serviceRegistry );
try
{
session = sessionFactory.openSession();
session.setDefaultReadOnly( true );
try
{
versionDataList = (java.util.List<SoftwareVersionData>)( session.createQuery("from "+SoftwareVersionData.class.getName()).list() );
// Do something with software version data here
for ( SoftwareVersionData versionData : versionDataList )
{
System.out.println( "VersionData = " + versionData.getVersion() );
}
session.flush();
}
finally
{
session.close();
}
}
finally
{
sessionFactory.close();
}
}
private void checkDatabaseForSoftwareVersion_B( boolean useH2 )
{
java.io.File databaseFile;
org.hibernate.cfg.Configuration configuration;
java.util.Properties properties;
org.hibernate.boot.registry.StandardServiceRegistryBuilder standardServiceRegistryBuilder;
org.hibernate.service.ServiceRegistry serviceRegistry;
org.hibernate.SessionFactory sessionFactory;
org.hibernate.Session session;
SoftwareVersionData versionData;
if ( useH2 )
{
databaseFile = new java.io.File( DATABASE_NAME + ".h2.db" );
}
else
{
databaseFile = new java.io.File( DATABASE_NAME + ".properties" );
}
if ( !(databaseFile.exists()) )
{
return;
}
java.util.logging.Logger.getLogger( "org.hibernate" ).setLevel( java.util.logging.Level.SEVERE );
configuration = new org.hibernate.cfg.Configuration();
if ( useH2 )
{
configuration.setProperty( "hibernate.dialect", "org.hibernate.dialect.H2Dialect" );
configuration.setProperty( "hibernate.connection.driver_class", org.h2.Driver.class.getName() );
configuration.setProperty( "hibernate.connection.url", "jdbc:h2:file:" + DATABASE_NAME + ";ACCESS_MODE_DATA=r" );
configuration.setProperty( "hibernate.connection.username", "sa" );
configuration.setProperty( "hibernate.connection.password", "" );
}
else
{
configuration.setProperty( "hibernate.dialect", "org.hibernate.dialect.HSQLDialect" );
configuration.setProperty( "hibernate.connection.driver_class", org.hsqldb.jdbcDriver.class.getName() );
configuration.setProperty( "hibernate.connection.url", "jdbc:hsqldb:file:" + DATABASE_NAME + ";readonly=true" );
configuration.setProperty( "hibernate.connection.username", "SA" );
configuration.setProperty( "hibernate.connection.password", "" );
}
configuration.setProperty( "hibernate.connection.pool_size", "1" );
configuration.setProperty( "hibernate.cache.provider_class", "org.hibernate.cache.internal.NoCacheProvider" );
configuration.setProperty( "hibernate.show_sql", "false" );
configuration.addAnnotatedClass( SoftwareVersionData.class );
properties = configuration.getProperties();
standardServiceRegistryBuilder = new org.hibernate.boot.registry.StandardServiceRegistryBuilder();
standardServiceRegistryBuilder.applySettings( properties );
serviceRegistry = standardServiceRegistryBuilder.build();
sessionFactory = configuration.buildSessionFactory( serviceRegistry );
try
{
session = sessionFactory.openSession();
session.setDefaultReadOnly( true );
try
{
versionData = (SoftwareVersionData)( session.createQuery("from "+SoftwareVersionData.class.getName()).uniqueResult() );
// Do something with software version data here
System.out.println( "VersionData = " + versionData.getVersion() );
}
finally
{
session.close();
}
}
finally
{
sessionFactory.close();
}
}
private org.hibernate.SessionFactory getHibernateSessionFactory( boolean useH2 )
{
org.hibernate.cfg.Configuration configuration;
java.util.Properties properties;
org.hibernate.boot.registry.StandardServiceRegistryBuilder standardServiceRegistryBuilder;
org.hibernate.service.ServiceRegistry serviceRegistry;
java.util.logging.Logger.getLogger( "org.hibernate" ).setLevel( java.util.logging.Level.SEVERE );
configuration = new org.hibernate.cfg.Configuration();
if ( useH2 )
{
configuration.setProperty( "hibernate.dialect", "org.hibernate.dialect.H2Dialect" );
configuration.setProperty( "hibernate.connection.driver_class", org.h2.Driver.class.getName() );
configuration.setProperty( "hibernate.connection.url", "jdbc:h2:file:" + DATABASE_NAME );
configuration.setProperty( "hibernate.connection.username", "sa" );
configuration.setProperty( "hibernate.connection.password", "" );
}
else
{
configuration.setProperty( "hibernate.dialect", "org.hibernate.dialect.HSQLDialect" );
configuration.setProperty( "hibernate.connection.driver_class", org.hsqldb.jdbcDriver.class.getName() );
configuration.setProperty( "hibernate.connection.url", "jdbc:hsqldb:file:" + DATABASE_NAME );
configuration.setProperty( "hibernate.connection.username", "SA" );
configuration.setProperty( "hibernate.connection.password", "" );
}
configuration.setProperty( "hibernate.connection.pool_size", "1" );
configuration.setProperty( "hibernate.cache.provider_class", "org.hibernate.cache.internal.NoCacheProvider" );
configuration.setProperty( "hibernate.show_sql", "false" );
configuration.setProperty( "hibernate.hbm2ddl.auto", "update" );
configuration.addAnnotatedClass( MyPersistentObject.class );
configuration.addAnnotatedClass( SoftwareVersionData.class );
properties = configuration.getProperties();
standardServiceRegistryBuilder = new org.hibernate.boot.registry.StandardServiceRegistryBuilder();
standardServiceRegistryBuilder.applySettings( properties );
serviceRegistry = standardServiceRegistryBuilder.build();
return configuration.buildSessionFactory( serviceRegistry );
}
final private void manipulatePersistentObjects( org.hibernate.SessionFactory sessionFactory )
{
org.hibernate.Session session;
boolean committed;
org.hibernate.Transaction transaction;
java.sql.Connection connection;
SoftwareVersionData versionData;
MyPersistentObject myPersistentObject;
session = sessionFactory.openSession();
try
{
committed = false;
transaction = session.beginTransaction();
try
{
versionData = (SoftwareVersionData)( session.createQuery("from "+SoftwareVersionData.class.getName()).uniqueResult() );
if ( versionData != null )
{
versionData.update();
System.out.println( "Updating version data" );
session.update( versionData );
}
else
{
versionData = new SoftwareVersionData( SOFTWARE_VERSION );
System.out.println( "Saving version data" );
session.save( versionData );
}
transaction.commit();
committed = true;
}
finally
{
if ( !committed )
{
transaction.rollback();
}
}
}
finally
{
session.close();
}
session = sessionFactory.openSession();
try
{
committed = false;
transaction = session.beginTransaction();
try
{
myPersistentObject = new MyPersistentObject( "First persistent object" );
session.save( myPersistentObject );
transaction.commit();
committed = true;
}
finally
{
if ( !committed )
{
transaction.rollback();
}
}
}
finally
{
session.close();
}
}
final private void readAndDisplayPersistentObjects( org.hibernate.SessionFactory sessionFactory )
{
org.hibernate.Session session;
java.util.List<MyPersistentObject> persistentObjects;
SoftwareVersionData versionData;
session = sessionFactory.openSession();
try
{
session.beginTransaction();
persistentObjects = (java.util.List<MyPersistentObject>)( session.createQuery("from "+MyPersistentObject.class.getName()).list() );
System.out.println( " List:" );
for ( MyPersistentObject persistentObject : persistentObjects )
{
persistentObject.display();
}
versionData = (SoftwareVersionData)( session.createQuery("from "+SoftwareVersionData.class.getName()).uniqueResult() );
System.out.println( " Software version = " + versionData.getVersion() );
session.getTransaction().commit();
}
finally
{
session.close();
}
}
}
Code:
@javax.persistence.Entity
public class MyPersistentObject
{
@javax.persistence.Id
@javax.persistence.GeneratedValue( generator = "increment" )
@org.hibernate.annotations.GenericGenerator( name = "increment", strategy = "increment" )
private Long m_persistenceID;
private String m_title;
// Hibernate needs a no-argument constructor
private MyPersistentObject()
{
}
// for application use, to create new persistent objects
public MyPersistentObject( String title )
{
m_title = title;
}
public void display()
{
System.out.println( " Title = " + m_title );
}
}
Code:
@javax.persistence.Entity
public class SoftwareVersionData
{
@javax.persistence.Id
private Long m_persistenceID = 1L;
private int m_version;
private SoftwareVersionData()
{
}
SoftwareVersionData( int version )
{
m_version = version;
}
int getVersion()
{
return m_version;
}
void update()
{
m_version = HibernateExample.SOFTWARE_VERSION;
}
}
Code:
public class UberMain
{
final static public void main( String args[] )
{
String arguments[];
int index;
arguments = new String[ args.length + 1 ];
for ( index=0; index<args.length; index++ )
{
arguments[ index ] = args[ index ];
}
arguments[ args.length ] = "";
HibernateExample.main( arguments );
HibernateExample.main( arguments );
System.exit( 0 );
}
}
Login
FAQ
Search