Hyland Connect

brianshowers · ‎12-05-2012

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).

trademak · ‎12-06-2012

Hi,

That's really strange, because everything runs okay in our QA environment for MySQL.
Which version of MySQL are you using?

Best regards,

fwachs · ‎12-06-2012

On my end it throws:

Dec 6, 2012 11:11:45 PM org.activiti.engine.impl.db.DbSqlSession executeSchemaResource
INFO: performing upgrade on engine with resource org/activiti/db/upgrade/activiti.mysql.upgradestep.510.to.511.engine.sql
Dec 6, 2012 11:11:45 PM org.activiti.engine.impl.db.DbSqlSession executeSchemaResource
SEVERE: problem during schema upgrade, statement 'alter table ACT_RE_DEPLOYMENT
add CATEGORY_ varchar(255)
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate column name 'CATEGORY_'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:532)

And it goes on and on and on with the same kind of problem.

My mysql version is :
5.5.28 MySQL Community Server (GPL)

brianshowers · ‎12-06-2012

We are using MySQL 5.5.25, but this should be reproduceable in just about any version of MySQL. The key is that we configure MySQL to run with sql-mode=TRADITIONAL. This settings is just an alias for the following sql-mode values:


mysql> show variables;
+—————————————————+——————————————————————————————————————————————————+
| Variable_name                                     | Value                                                                                                                                                |
+—————————————————+——————————————————————————————————————————————————+
…
| sql_mode                                          | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
…
+—————————————————+——————————————————————————————————————————————————+

I'm not positive about the cause of fwachs's error, but it could be related. Since DDL statements are not transactional in MySQL, the upgrade get's partially applied before the error occurs. Because of the error, the upgrade stops without setting the schema.version value in act_ge_property. This means that when the service is re-started, it will again try to do the upgrade. But this time, it will fail with "table already exists" errors because part of it was already applied.

fwachs · ‎12-06-2012

What you say makes sense, but i'm not sure if you are pointing to a solution somehow

fwachs · ‎12-06-2012

Update, I have ran activiti.mysql.upgradestep.510.to.511.engine.sql on mysql and the webapp starts correctly, but whenever I restart it, the db gets rollbacked to 5.10 and I have to run activiti.mysql.upgradestep.510.to.511.engine.sql again.

I am completely lost now

jbarrez · ‎12-07-2012

I've done the following:

- Manually created the schema using the 5.10 schema DDL
- Executed the upgrade scripts manually.

But I did not get any of the erros from above. I did check the sql_mode, and I see nothing is set for me.

But I did get another error: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
which I could execute when I set SET SQL_SAFE_UPDATES=0; temporarily.

So there might be something wrong there, but I'm not yet sure what exactly… Like Tijs said, out QA uses a default Mysql for linux, nothing tweaked. And there, there are no issues whatsoever….

fwachs · ‎12-07-2012

I have ran the scripts manually too, but for some reason they get rolled back, any idea?

Check this out

mysql> select * from ACT_GE_PROPERTY;
+—————-+————————————————————————————————————————————–+——+
| NAME_          | VALUE_                                                                                                                               | REV_ |
+—————-+————————————————————————————————————————————–+——+
| next.dbid      | 2801                                                                                                                                 |   29 |
| schema.history | create(5.10) upgrade(5.10->5.11) upgrade(5.11->5.10) upgrade(5.11->5.10) upgrade(5.11->5.10) upgrade(5.11->5.10) upgrade(5.11->5.10) |    7 |
| schema.version | 5.10                                                                                                                                 |    7 |
+—————-+————————————————————————————————————————————–+——+
3 rows in set (0.00 sec)

brianshowers · ‎12-07-2012

@fwachs, I'm afraid that I'm not suggesting any sort of work around. From what I can tell, the only two options are to globally change your sql-mode setting to something more lenient (not viable) or to fix the upgrade patch to explicitly specify a valid default value for all TIMESTAMP fields. Personally, I think that's the only real way out of this dilemma, but it requires a code patch.

fwachs · ‎12-07-2012

Since I am not on prod right now, I took the easy way…Dropped activiti db and started from scratch importing my models again.
Works like a charm

Hyland Connect

Error migrating DB to v5.11 schema