Limitations of H2 for unit tests

classic Classic list List threaded Threaded
3 messages Options
Darius Jazayeri-2 Darius Jazayeri-2
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Limitations of H2 for unit tests

Hi All,

I just discovered a particular limitation to H2 for our junit tests, and a workaround, that I thought I'd share.

I was trying to write a complex integration test for the moduledistro module as a BaseModuleContextSensitiveTest. In the test I write a GP telling it to use a temporary folder to store modules (instead of clobbering my ~/.OpenMRS/modules), and then the code I call also ends up reading the GP table several times.

I get error output like this in the console:
ERROR - JDBCExceptionReporter.logExceptions(234) |2012-05-15 16:12:20,755| Timeout trying to lock table "GLOBAL_PROPERTY"; SQL statement:
select this_.property as property33_0_, this_.property_value as property2_33_0_, this_.description as descript3_33_0_, this_.datatype as datatype33_0_, this_.datatype_config as datatype5_33_0_, this_.preferred_handler as preferred6_33_0_, this_.handler_config as handler7_33_0_, this_.uuid as uuid33_0_ from global_property this_ where lower(this_.property)=? [50200-135]
ERROR - JDBCExceptionReporter.logExceptions(234) |2012-05-15 16:12:22,773| Timeout trying to lock table "GLOBAL_PROPERTY"; SQL statement:
select this_.property as property33_0_, this_.property_value as property2_33_0_, this_.description as descript3_33_0_, this_.datatype as datatype33_0_, this_.datatype_config as datatype5_33_0_, this_.preferred_handler as preferred6_33_0_, this_.handler_config as handler7_33_0_, this_.uuid as uuid33_0_ from global_property this_ where lower(this_.property)=? [50200-135]
ERROR - ModuleFactory.notifySuperUsersAboutModuleFailure(305) |2012-05-15 16:12:22,862| Unable to send an alert to the super users
org.springframework.transaction.UnexpectedRollbackException: Transaction rolled back because it has been marked as rollback-only
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:717)
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:393)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:120)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at $Proxy57.notifySuperUsers(Unknown Source)
at org.openmrs.module.ModuleFactory.notifySuperUsersAboutModuleFailure(ModuleFactory.java:302)
at org.openmrs.module.ModuleFactory.startModuleInternal(ModuleFactory.java:605)
at org.openmrs.api.context.Daemon$1.run(Daemon.java:57)
ERROR - JDBCExceptionReporter.logExceptions(234) |2012-05-15 16:12:24,871| Timeout trying to lock table "GLOBAL_PROPERTY"; SQL statement:
select this_.property as property33_0_, this_.property_value as property2_33_0_, this_.description as descript3_33_0_, this_.datatype as datatype33_0_, this_.datatype_config as datatype5_33_0_, this_.preferred_handler as preferred6_33_0_, this_.handler_config as handler7_33_0_, this_.uuid as uuid33_0_ from global_property this_ where lower(this_.property)=? [50200-135]

Some of the stack trace is:
java.lang.RuntimeException: Failed to start module metadatasharing because of: Error while trying to start module
could not execute query
org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
org.hibernate.loader.Loader.doList(Loader.java:2536)
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
org.hibernate.loader.Loader.list(Loader.java:2271)
org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119)
org.hibernate.impl.SessionImpl.list(SessionImpl.java:1716)
org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
org.hibernate.impl.CriteriaImpl.uniqueResult(CriteriaImpl.java:369)
 ** org.openmrs.api.db.hibernate.HibernateAdministrationDAO.getGlobalPropertyObject(HibernateAdministrationDAO.java:264)
 ** org.openmrs.api.impl.AdministrationServiceImpl.getGlobalPropertyObject(AdministrationServiceImpl.java:662)
(snip)
$Proxy44.getGlobalPropertyObject(Unknown Source)
 ** org.openmrs.module.ModuleFactory.runDiff(ModuleFactory.java:669)
 ** org.openmrs.module.ModuleFactory.startModuleInternal(ModuleFactory.java:538)
 ** org.openmrs.api.context.Daemon$1.run(Daemon.java:57)


Quick summary: you can tell H2 via the database URL that you want to enable Multi-Version Concurrency Control (MySQL uses this in INNODB tables, but H2 sadly does not enable it by default).

The workaround: add this snippet to your unit test (that extends BaseModuleContextSensitiveTest:
@Override
public Properties getRuntimeProperties() {
    Properties props = super.getRuntimeProperties();
    String url = props.getProperty(Environment.URL);
    if (url.contains("jdbc:h2:") && !url.contains(";MVCC=TRUE")) {
        props.setProperty(Environment.URL, url + ";MVCC=true");
    }
    return props;
}

After adding this snippet I no longer get that error. (Though sadly I get a different one, due to not being able to execute the sqldiff for a module I'm loading.) Hopefully it helps someone someday.

Cheers,
Darius

[hidden email] from OpenMRS Developers' mailing list
Daniel Kayiwa Daniel Kayiwa
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Limitations of H2 for unit tests


Hi Darius,

Did you intentionally make one lower and the other upper case? Or it does not matter?
{MVCC=TRUE vs MVCC=true}


On Wed, May 16, 2012 at 2:25 AM, Darius Jazayeri <[hidden email]> wrote:
Hi All,

I just discovered a particular limitation to H2 for our junit tests, and a workaround, that I thought I'd share.

I was trying to write a complex integration test for the moduledistro module as a BaseModuleContextSensitiveTest. In the test I write a GP telling it to use a temporary folder to store modules (instead of clobbering my ~/.OpenMRS/modules), and then the code I call also ends up reading the GP table several times.

I get error output like this in the console:
ERROR - JDBCExceptionReporter.logExceptions(234) |2012-05-15 16:12:20,755| Timeout trying to lock table "GLOBAL_PROPERTY"; SQL statement:
select this_.property as property33_0_, this_.property_value as property2_33_0_, this_.description as descript3_33_0_, this_.datatype as datatype33_0_, this_.datatype_config as datatype5_33_0_, this_.preferred_handler as preferred6_33_0_, this_.handler_config as handler7_33_0_, this_.uuid as uuid33_0_ from global_property this_ where lower(this_.property)=? [50200-135]
ERROR - JDBCExceptionReporter.logExceptions(234) |2012-05-15 16:12:22,773| Timeout trying to lock table "GLOBAL_PROPERTY"; SQL statement:
select this_.property as property33_0_, this_.property_value as property2_33_0_, this_.description as descript3_33_0_, this_.datatype as datatype33_0_, this_.datatype_config as datatype5_33_0_, this_.preferred_handler as preferred6_33_0_, this_.handler_config as handler7_33_0_, this_.uuid as uuid33_0_ from global_property this_ where lower(this_.property)=? [50200-135]
ERROR - ModuleFactory.notifySuperUsersAboutModuleFailure(305) |2012-05-15 16:12:22,862| Unable to send an alert to the super users
org.springframework.transaction.UnexpectedRollbackException: Transaction rolled back because it has been marked as rollback-only
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:717)
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:393)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:120)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at $Proxy57.notifySuperUsers(Unknown Source)
at org.openmrs.module.ModuleFactory.notifySuperUsersAboutModuleFailure(ModuleFactory.java:302)
at org.openmrs.module.ModuleFactory.startModuleInternal(ModuleFactory.java:605)
at org.openmrs.api.context.Daemon$1.run(Daemon.java:57)
ERROR - JDBCExceptionReporter.logExceptions(234) |2012-05-15 16:12:24,871| Timeout trying to lock table "GLOBAL_PROPERTY"; SQL statement:
select this_.property as property33_0_, this_.property_value as property2_33_0_, this_.description as descript3_33_0_, this_.datatype as datatype33_0_, this_.datatype_config as datatype5_33_0_, this_.preferred_handler as preferred6_33_0_, this_.handler_config as handler7_33_0_, this_.uuid as uuid33_0_ from global_property this_ where lower(this_.property)=? [50200-135]

Some of the stack trace is:
java.lang.RuntimeException: Failed to start module metadatasharing because of: Error while trying to start module
could not execute query
org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
org.hibernate.loader.Loader.doList(Loader.java:2536)
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
org.hibernate.loader.Loader.list(Loader.java:2271)
org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119)
org.hibernate.impl.SessionImpl.list(SessionImpl.java:1716)
org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
org.hibernate.impl.CriteriaImpl.uniqueResult(CriteriaImpl.java:369)
 ** org.openmrs.api.db.hibernate.HibernateAdministrationDAO.getGlobalPropertyObject(HibernateAdministrationDAO.java:264)
 ** org.openmrs.api.impl.AdministrationServiceImpl.getGlobalPropertyObject(AdministrationServiceImpl.java:662)
(snip)
$Proxy44.getGlobalPropertyObject(Unknown Source)
 ** org.openmrs.module.ModuleFactory.runDiff(ModuleFactory.java:669)
 ** org.openmrs.module.ModuleFactory.startModuleInternal(ModuleFactory.java:538)
 ** org.openmrs.api.context.Daemon$1.run(Daemon.java:57)


Quick summary: you can tell H2 via the database URL that you want to enable Multi-Version Concurrency Control (MySQL uses this in INNODB tables, but H2 sadly does not enable it by default).

The workaround: add this snippet to your unit test (that extends BaseModuleContextSensitiveTest:
@Override
public Properties getRuntimeProperties() {
    Properties props = super.getRuntimeProperties();
    String url = props.getProperty(Environment.URL);
    if (url.contains("jdbc:h2:") && !url.contains(";MVCC=TRUE")) {
        props.setProperty(Environment.URL, url + ";MVCC=true");
    }
    return props;
}

After adding this snippet I no longer get that error. (Though sadly I get a different one, due to not being able to execute the sqldiff for a module I'm loading.) Hopefully it helps someone someday.

Cheers,
Darius

[hidden email] from OpenMRS Developers' mailing list



--
If we keep uppermost in our minds the unkind and unjust acts of others, we shall find it impossible to love them as Christ has loved us; but if our thoughts dwell upon the wondrous love and pity of Christ for us, the same spirit will flow out to others.

[hidden email] from OpenMRS Developers' mailing list
Darius Jazayeri-3 Darius Jazayeri-3
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Limitations of H2 for unit tests

Hmm, that was unintentional.

I believe that either lowercase or uppercase works, but it should be consistent. :-)

-Darius

On Wed, May 16, 2012 at 12:32 AM, Daniel Kayiwa <[hidden email]> wrote:

Hi Darius,

Did you intentionally make one lower and the other upper case? Or it does not matter?
{MVCC=TRUE vs MVCC=true}


On Wed, May 16, 2012 at 2:25 AM, Darius Jazayeri <[hidden email]> wrote:
Hi All,

I just discovered a particular limitation to H2 for our junit tests, and a workaround, that I thought I'd share.

I was trying to write a complex integration test for the moduledistro module as a BaseModuleContextSensitiveTest. In the test I write a GP telling it to use a temporary folder to store modules (instead of clobbering my ~/.OpenMRS/modules), and then the code I call also ends up reading the GP table several times.

I get error output like this in the console:
ERROR - JDBCExceptionReporter.logExceptions(234) |2012-05-15 16:12:20,755| Timeout trying to lock table "GLOBAL_PROPERTY"; SQL statement:
select this_.property as property33_0_, this_.property_value as property2_33_0_, this_.description as descript3_33_0_, this_.datatype as datatype33_0_, this_.datatype_config as datatype5_33_0_, this_.preferred_handler as preferred6_33_0_, this_.handler_config as handler7_33_0_, this_.uuid as uuid33_0_ from global_property this_ where lower(this_.property)=? [50200-135]
ERROR - JDBCExceptionReporter.logExceptions(234) |2012-05-15 16:12:22,773| Timeout trying to lock table "GLOBAL_PROPERTY"; SQL statement:
select this_.property as property33_0_, this_.property_value as property2_33_0_, this_.description as descript3_33_0_, this_.datatype as datatype33_0_, this_.datatype_config as datatype5_33_0_, this_.preferred_handler as preferred6_33_0_, this_.handler_config as handler7_33_0_, this_.uuid as uuid33_0_ from global_property this_ where lower(this_.property)=? [50200-135]
ERROR - ModuleFactory.notifySuperUsersAboutModuleFailure(305) |2012-05-15 16:12:22,862| Unable to send an alert to the super users
org.springframework.transaction.UnexpectedRollbackException: Transaction rolled back because it has been marked as rollback-only
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:717)
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:393)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:120)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at $Proxy57.notifySuperUsers(Unknown Source)
at org.openmrs.module.ModuleFactory.notifySuperUsersAboutModuleFailure(ModuleFactory.java:302)
at org.openmrs.module.ModuleFactory.startModuleInternal(ModuleFactory.java:605)
at org.openmrs.api.context.Daemon$1.run(Daemon.java:57)
ERROR - JDBCExceptionReporter.logExceptions(234) |2012-05-15 16:12:24,871| Timeout trying to lock table "GLOBAL_PROPERTY"; SQL statement:
select this_.property as property33_0_, this_.property_value as property2_33_0_, this_.description as descript3_33_0_, this_.datatype as datatype33_0_, this_.datatype_config as datatype5_33_0_, this_.preferred_handler as preferred6_33_0_, this_.handler_config as handler7_33_0_, this_.uuid as uuid33_0_ from global_property this_ where lower(this_.property)=? [50200-135]

Some of the stack trace is:
java.lang.RuntimeException: Failed to start module metadatasharing because of: Error while trying to start module
could not execute query
org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
org.hibernate.loader.Loader.doList(Loader.java:2536)
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
org.hibernate.loader.Loader.list(Loader.java:2271)
org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119)
org.hibernate.impl.SessionImpl.list(SessionImpl.java:1716)
org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
org.hibernate.impl.CriteriaImpl.uniqueResult(CriteriaImpl.java:369)
 ** org.openmrs.api.db.hibernate.HibernateAdministrationDAO.getGlobalPropertyObject(HibernateAdministrationDAO.java:264)
 ** org.openmrs.api.impl.AdministrationServiceImpl.getGlobalPropertyObject(AdministrationServiceImpl.java:662)
(snip)
$Proxy44.getGlobalPropertyObject(Unknown Source)
 ** org.openmrs.module.ModuleFactory.runDiff(ModuleFactory.java:669)
 ** org.openmrs.module.ModuleFactory.startModuleInternal(ModuleFactory.java:538)
 ** org.openmrs.api.context.Daemon$1.run(Daemon.java:57)


Quick summary: you can tell H2 via the database URL that you want to enable Multi-Version Concurrency Control (MySQL uses this in INNODB tables, but H2 sadly does not enable it by default).

The workaround: add this snippet to your unit test (that extends BaseModuleContextSensitiveTest:
@Override
public Properties getRuntimeProperties() {
    Properties props = super.getRuntimeProperties();
    String url = props.getProperty(Environment.URL);
    if (url.contains("jdbc:h2:") && !url.contains(";MVCC=TRUE")) {
        props.setProperty(Environment.URL, url + ";MVCC=true");
    }
    return props;
}

After adding this snippet I no longer get that error. (Though sadly I get a different one, due to not being able to execute the sqldiff for a module I'm loading.) Hopefully it helps someone someday.

Cheers,
Darius

[hidden email] from OpenMRS Developers' mailing list



--
If we keep uppermost in our minds the unkind and unjust acts of others, we shall find it impossible to love them as Christ has loved us; but if our thoughts dwell upon the wondrous love and pity of Christ for us, the same spirit will flow out to others.

[hidden email] from OpenMRS Developers' mailing list


[hidden email] from OpenMRS Developers' mailing list
Loading...