Migrating from a v1 database to v2 failed

[KewlCat]

Hi

I'm trying to install the recently released v1.3. When I first launch the reader, this is what the log says :

20 Dec 2013 16:43:03,150 INFO com.sismics.util.jpa.DbOpenHelper.open(DbOpenHelper.java:67) Opening database and executing incremental updates 20 Dec 2013 16:43:03,153 INFO com.sismics.util.jpa.DbOpenHelper.open(DbOpenHelper.java:117) Found database version 1, new version is 2, executing database incremental upd ate scripts 20 Dec 2013 16:43:03,162 INFO com.sismics.util.jpa.DbOpenHelper.executeAllScript(DbOpenHelper.java:163) Executing script: dbupdate-002-0.sql 20 Dec 2013 16:43:03,299 INFO com.sismics.util.jpa.DbOpenHelper.open(DbOpenHelper.java:119) Database upgrade complete 20 Dec 2013 16:43:03,300 INFO com.sismics.util.jpa.EMF.getEntityManagerProperties(EMF.java:81) Configuring EntityManager from environment parameters

So far, so good

But then...

20 Dec 2013 16:43:06,206 INFO com.sismics.reader.core.listener.async.ArticleCreatedAsyncListener.onArticleCreated(ArticleCreatedAsyncListener.java:46) Index updated in 37 3ms 20 Dec 2013 16:43:07,375 WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(SqlExceptionHelper.java:143) SQL Error: -5501, SQLState: 42501 20 Dec 2013 16:43:07,375 ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(SqlExceptionHelper.java:144) user lacks privilege or object not found: T_JOB 20 Dec 2013 16:43:07,380 ERROR com.sismics.util.filter.RequestContextFilter.doFilter(RequestContextFilter.java:100) An exception occured, rolling back current transaction javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: user lacks privilege or object not found: T_JOB at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1360) at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1288) at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:261) at com.sismics.reader.core.dao.jpa.JobDao.findByCriteria(JobDao.java:108) at com.sismics.reader.rest.resource.UserResource.info(UserResource.java:541) (...) Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: T_JOB (...) Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: T_JOB

What did I do wrong ?

[jendib]

The T_JOB table was added in database v1, so you should already have it.

[KewlCat]

Maybe I did too big a jump in versions ? Should I install v1.2 first, then v1.3 ?

[jendib]

Database v1 was introduced in 1.2: https://github.com/sismics/reader/tree/1.2/reader-core/src/main/resources/db/update

So maybe you've installed a snapshot version compiled yourself?

[KewlCat]

% cat WEB-INF/classes/config.properties api.current_version=1.2-SNAPSHOT api.min_version=1.0

Yes I did. How do I know exactly which version ?

[jendib]

If you don't have those table, but you are in db1, then it was before this commit: https://github.com/sismics/reader/commit/083b4a341124921c4e8110def87341119493f1e2

There is no easy way to fix this, it is not recommended to compile and install snapshot version on "production".

You should stop your server, open the database (in READER_HOME/db/) with a HSQLDB client, and execute the diff between: https://github.com/sismics/reader/blob/96391104d64d6174fc5462036627affe841eaf76/reader-core/src/main/resources/db/update/dbupdate-001-0.sql and https://github.com/sismics/reader/blob/1.2/reader-core/src/main/resources/db/update/dbupdate-001-0.sql

which is:

create cached table T_JOB (JOB_ID_C varchar(36) not null, JOB_IDUSER_C varchar(36), JOB_NAME_C varchar(50) not null, JOB_CREATEDATE_D datetime not null, JOB_STARTDATE_D datetime, JOB_ENDDATE_D datetime, JOB_DELETEDATE_D datetime, primary key (JOB_ID_C));
create cached table T_JOB_EVENT ( JOE_ID_C varchar(36) not null, JOE_IDJOB_C varchar(36), JOE_NAME_C varchar(50) not null, JOE_VALUE_C varchar(250), JOE_CREATEDATE_D datetime not null, JOE_DELETEDATE_D datetime, primary key (JOE_ID_C));
alter table T_JOB add constraint FK_JOB_IDUSER_C foreign key (JOB_IDUSER_C) references T_USER (USE_ID_C) on delete restrict on update restrict;
alter table T_JOB_EVENT add constraint FK_JOE_IDJOB_C foreign key (JOE_IDJOB_C) references T_JOB (JOB_ID_C) on delete restrict on update restrict;
create index IDX_ART_PUBLICATIONDATE_D on T_ARTICLE (ART_PUBLICATIONDATE_D, ART_ID_C);
create index IDX_USA_STARREDDATE_D on T_USER_ARTICLE (USA_STARREDDATE_D, USA_ID_C);

[KewlCat]

I'll try to find a command-line hsqldb client and "patch" the DB.

It's a version from around July 19th, apprently... % cat META-INF/maven/com.sismics.reader/reader-web/pom.properties

Generated by Maven

Fri Jul 19 12:26:34 CEST 2013

version=1.2-SNAPSHOT ...

It's been in "testing" because I wanted to try and test new features, but I had no time to continue testing and was "good enough" for daily use... so it stayed in "production" that way...

[jendib]

It's ok to use snapshot version as long as you keep up with database upgrades manually.

Good luck.

[KewlCat]

Thanks, I'm going to need it !

That'll teach me :-D