Hibernate query using hsqldb fails on Hudson / Jenkins, work on local

I have a quite big project in java using Spring 3 + Hibernate 3.3.2.GA. I'm using Maven 2 and JUnit 4, and Jenkins has been our CI server for a couple days now (though it was Hudson before and we had the same issue).

Since a week or so, I have a weird issue where a Maven build works and all tests pass in local builds, but the CI server reports it's unstable. The project is configured as a Maven 2 project on the CI server, so the command being executed is the exact same (clean install site). The issue arose when a new field was added to an existing entity. All tests requiring to persist an instance of such entity fail on Jenkins with the following message:

     could not insert:> [com.monits.fcoach.model.Challenge];
     SQL [insert into challenge (id, name, sortingStrategy, year) values (null,
     ?, ?, ?)];

    nested exception is  org.hibernate.exception.DataException:could not insert:[com.monits.fcoach.model.Challenge]

The stacktrace however, is what's really interesting:

org.springframework.dao.DataIntegrityViolationException: could not insert: [com.monits.fcoach.model.Challenge]; SQL [insert into challenge (id, name, sortingStrategy, year) values (null, ?, ?, ?)]; nested exception is org.hibernate.exception.DataException: could not insert: [com.monits.fcoach.model.Challenge]
    at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:642)
    at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411)
    at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
    at org.springframework.orm.hibernate3.HibernateTemplate.save(HibernateTemplate.java:683)
    at com.monits.fcoach.dao.GenericHibernateDao.create(GenericHibernateDao.java:101)
    at com.monits.fcoach.dao.GenericDAOHibernateTest.setUp(GenericDAOHibernateTest.java:113)
    at com.monits.fcoach.dao.ChallengeDaoHibernateTest.setUp(ChallengeDaoHi开发者_Go百科bernateTest.java:75)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:27)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:31)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:82)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:240)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:180)
    at org.apache.maven.surefire.junit4.JUnit4TestSet.execute(JUnit4TestSet.java:62)
    at org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.executeTestSet(AbstractDirectoryTestSuite.java:140)
    at org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.execute(AbstractDirectoryTestSuite.java:127)
    at org.apache.maven.surefire.Surefire.run(Surefire.java:177)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.maven.surefire.booter.SurefireBooter.runSuitesInProcess(SurefireBooter.java:345)
    at org.apache.maven.surefire.booter.SurefireBooter.main(SurefireBooter.java:1009)
Caused by: org.hibernate.exception.DataException: could not insert: [com.monits.fcoach.model.Challenge]
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:100)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:64)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2176)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2656)
    at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:71)
    at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
    at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:321)
    at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:204)
    at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:130)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:210)
    at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:56)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:195)
    at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:50)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:93)
    at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:563)
    at org.hibernate.impl.SessionImpl.save(SessionImpl.java:551)
    at org.hibernate.impl.SessionImpl.save(SessionImpl.java:547)
    at org.springframework.orm.hibernate3.HibernateTemplate$12.doInHibernate(HibernateTemplate.java:686)
    at org.springframework.orm.hibernate3.HibernateTemplate$12.doInHibernate(HibernateTemplate.java:1)
    at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
    ... 38 more
Caused by: java.sql.SQLException: Column count does not match in statement [insert into challenge (id, name, sortingStrategy, year) values (null, ?, ?, ?)]
    at org.hsqldb.jdbc.Util.throwError(Unknown Source)
    at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
    at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
    at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
    at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:116)
    at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:54)
    ... 56 more

As stated above, the root cause is:

java.sql.SQLException: Column count does not match in statement [insert into challenge (id, name, sortingStrategy, year) values (null, ?, ?, ?)]

4 columns names, 4 values, yet it doesn't match....

That sortingStrategy is the field we added when the build started failing.

The project runs smoothly on the application container, everything works fine from the IDE and using Maven in all dev machines, but it fails constantly on Jenkins / Hudson with this message. All tests performing an insert for this entity fail in the exact same way.

The class is this:

package com.monits.fcoach.model;

import java.util.Set;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToMany;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.UniqueConstraint;

import com.monits.fcoach.sorting.StandardStandingsSorting;

@Entity
@Table(name = "challenge", uniqueConstraints={
        @UniqueConstraint(columnNames={"name", "year"})
    })
public class Challenge {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Column(nullable = false)
    private String name;

    @Column(nullable = false)
    private int year;

    // This is the field we added when the build started failing
    @Column(nullable = true)
    private String sortingStrategy;

    @ManyToMany
    private Set<League> leagues;

    @OneToMany
    @JoinColumn(name="challenge_id", insertable=false, updatable=false)
    private Set<Match> matches;

    ...
}

Any ideas are welcome, I'm pretty much lost...

I would try using it with a free style project that call Maven on the command line. Sometimes the embedded Maven version in the M2 plugin has weird issues. That is generally the preferred way to build a Maven project anyway.

As pointed out by TheStijn in the comments (hw wouldn't reply so I can properly mark his answer as correct), the solution was to delete the hsql database file and let jenkins create it from scratch. Apparently the update sometimes fails on HSQL, this has happened to me twice already since this original post, but switching it to create on my test script or using in-memory HSQL databases works properly.

We had a similar jenkins' tests fail while our local dev tests were successful. We discovered that the jenkins box was updated and the account/ name was changed. Our app was using the system user.name in our persistence (insert and update) which worked locally but jenkins' username was longer and our table had a shorter length, hence the maven tests we had in jenkins were failing. Got that sorted...