AnsweredAssumed Answered

Error migrating DB to v5.11 schema

Question asked by brianshowers on Dec 5, 2012
Latest reply on Dec 14, 2012 by trademak
I just tried to upgrade to the new v5.11 version, but unfortunately, the schema upgrade SQL is not valid.  If you attempt to run it on MySQL instances that don't allow invalid Dates to be created, you'll get the following error:


2012-12-05 16:43:23 ERROR org.activiti.engine.impl.db.DbSqlSession problem during schema upgrade, statement 'create table ACT_RE_MODEL (
ID_ varchar(64) not null,
REV_ integer,
NAME_ varchar(255),
KEY_ varchar(255),
CATEGORY_ varchar(255),
CREATE_TIME_ timestamp,
LAST_UPDATE_TIME_ timestamp,
VERSION_ integer,
META_INFO_ varchar(4000),
DEPLOYMENT_ID_ varchar(64),
EDITOR_SOURCE_VALUE_ID_ varchar(64),
EDITOR_SOURCE_EXTRA_VALUE_ID_ varchar(64),
primary key (ID_)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Invalid default value for 'LAST_UPDATE_TIME_'
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.7.0_04]
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) ~[na:1.7.0_04]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.7.0_04]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:525) ~[na:1.7.0_04]
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) ~[mysql-connector-java-5.1.21.jar:na]
        at com.mysql.jdbc.Util.getInstance(Util.java:386) ~[mysql-connector-java-5.1.21.jar:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053) ~[mysql-connector-java-5.1.21.jar:na]
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074) ~[mysql-connector-java-5.1.21.jar:na]
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006) ~[mysql-connector-java-5.1.21.jar:na]
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468) ~[mysql-connector-java-5.1.21.jar:na]
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629) ~[mysql-connector-java-5.1.21.jar:na]
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2713) ~[mysql-connector-java-5.1.21.jar:na]
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2663) ~[mysql-connector-java-5.1.21.jar:na]
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:888) ~[mysql-connector-java-5.1.21.jar:na]
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:730) ~[mysql-connector-java-5.1.21.jar:na]
        at org.activiti.engine.impl.db.DbSqlSession.executeSchemaResource(DbSqlSession.java:1015) [activiti-engine-5.11.jar:5.11]
        at org.activiti.engine.impl.db.DbSqlSession.executeSchemaResource(DbSqlSession.java:966) [activiti-engine-5.11.jar:5.11]
        at org.activiti.engine.impl.db.DbSqlSession.dbSchemaUpgrade(DbSqlSession.java:945) [activiti-engine-5.11.jar:5.11]
        at org.activiti.engine.impl.db.DbSqlSession.dbSchemaUpdate(DbSqlSession.java:826) [activiti-engine-5.11.jar:5.11]
        at org.activiti.engine.impl.db.DbSqlSession.performSchemaOperationsProcessEngineBuild(DbSqlSession.java:1100) [activiti-engine-5.11.jar:5.11]
        at org.activiti.engine.impl.SchemaOperationsProcessEngineBuild.execute(SchemaOperationsProcessEngineBuild.java:25) [activiti-engine-5.11.jar:5.11]
        at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24) [activiti-engine-5.11.jar:5.11]
        at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:60) [activiti-engine-5.11.jar:5.11]
        at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42) [activiti-spring-5.11.jar:na]
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130) [spring-tx-3.1.2.RELEASE.jar:3.1.2.RELEASE]
        at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40) [activiti-spring-5.11.jar:na]
        at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:32) [activiti-engine-5.11.jar:5.11]
        at org.activiti.engine.impl.ProcessEngineImpl.<init>(ProcessEngineImpl.java:75) [activiti-engine-5.11.jar:5.11]
        at org.activiti.engine.impl.cfg.ProcessEngineConfigurationImpl.buildProcessEngine(ProcessEngineConfigurationImpl.java:311) [activiti-engine-5.11.jar:5.11]
        at org.activiti.spring.SpringProcessEngineConfiguration.buildProcessEngine(SpringProcessEngineConfiguration.java:60) [activiti-spring-5.11.jar:na]

The problem is that the upgrade attempts to add TIMESTAMP columns without specifying a valid default value.  When this happens, MySQL will implicitly set the default to the string "0000-00-00 00:00:00".  See the following description of the schema after a migration to v5.11:


mysql> describe ACT_RE_MODEL;
+——————————-+—————+——+—–+———————+—————————–+
| Field                         | Type          | Null | Key | Default             | Extra                       |
+——————————-+—————+——+—–+———————+—————————–+
| ID_                           | varchar(64)   | NO   | PRI | NULL                |                             |
| REV_                          | int(11)       | YES  |     | NULL                |                             |
| NAME_                         | varchar(255)  | YES  |     | NULL                |                             |
| KEY_                          | varchar(255)  | YES  |     | NULL                |                             |
| CATEGORY_                     | varchar(255)  | YES  |     | NULL                |                             |
| CREATE_TIME_                  | timestamp     | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| LAST_UPDATE_TIME_             | timestamp     | NO   |     | 0000-00-00 00:00:00 |                             |
| VERSION_                      | int(11)       | YES  |     | NULL                |                             |
| META_INFO_                    | varchar(4000) | YES  |     | NULL                |                             |
| DEPLOYMENT_ID_                | varchar(64)   | YES  | MUL | NULL                |                             |
| EDITOR_SOURCE_VALUE_ID_       | varchar(64)   | YES  | MUL | NULL                |                             |
| EDITOR_SOURCE_EXTRA_VALUE_ID_ | varchar(64)   | YES  | MUL | NULL                |                             |
+——————————-+—————+——+—–+———————+—————————–+
12 rows in set (0.02 sec)

However, "0000-00-00 00:00:00" is not a value that can be marshaled into either a java.sql.Date or a java.util.Date.  If you ever attempt to do so, Java will throw a parse exception.  As a result, MySQL has the ability to configure it's sql-mode to disallow these invalid date strings.  This can be done by settings one/all of the following sql-mode values: TRADITIONAL, NO_ZERO_IN_DATE, NO_ZERO_DATE.  These settings make MySQL behave more like a traditional database by disallowing many of the non-standard MySQL-isms.  I've verified that if you relax the sql-mode, the Activiti upgrade can proceed.  However, that's not a viable solution since relaxing the sql-mode is a global change that applies to all databases on that server (not just the Activiti DB).

As a result, the only viable fix is to change the upgrade SQL to provide a valid default value (NULL or some valid date).

Outcomes