topic Re: Error migrating DB to v5.11 schema in Alfresco Archive

Error migrating DB to v5.11 schema

brianshowers — Wed, 05 Dec 2012 17:14:45 GMT

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 probl

Re: Error migrating DB to v5.11 schema

trademak — Thu, 06 Dec 2012 09:16:45 GMT

Hi,

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

Best regards,

Re: Error migrating DB to v5.11 schema

fwachs — Thu, 06 Dec 2012 23:17:19 GMT

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)

Re: Error migrating DB to v5.11 schema

brianshowers — Thu, 06 Dec 2012 23:49:49 GMT

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.

Re: Error migrating DB to v5.11 schema

fwachs — Thu, 06 Dec 2012 23:58:11 GMT

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

Re: Error migrating DB to v5.11 schema

fwachs — Fri, 07 Dec 2012 00:41:40 GMT

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

Re: Error migrating DB to v5.11 schema

jbarrez — Fri, 07 Dec 2012 10:42:27 GMT

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

Re: Error migrating DB to v5.11 schema

fwachs — Fri, 07 Dec 2012 11:36:12 GMT

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)

Re: Error migrating DB to v5.11 schema

brianshowers — Fri, 07 Dec 2012 21:15:41 GMT

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

Re: Error migrating DB to v5.11 schema

fwachs — Fri, 07 Dec 2012 21:17:32 GMT

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

Re: Error migrating DB to v5.11 schema

brianshowers — Fri, 07 Dec 2012 21:27:57 GMT

@jbarrez and @trademak

Out of the box, MySQL uses a very lenient set of sql-mode settings that allow you to do all sorts of non-compliant SQL. In fact, the out of the box settings are pretty poor for running a high availability system since they attempt to "fix" bad SQL for you with what the server "thinks" you meant. This is pretty awful of MySQL because it can hide bugs in code and corrupt data in ways that you don't expect.

If you change your QA environment to set sql-mode=TRADITIONAL or even just sql-mode=NO_ZERO_IN_DATE, NO_ZERO_DATE, you should be able to reproduce the problem pretty easily. That sql-mode setting is much more indicative of what a production system would use since it enforces much more error checking by MySQL without the DB trying to guess at what the invalid SQL was trying to do.

Re: Error migrating DB to v5.11 schema

jbarrez — Mon, 10 Dec 2012 09:41:03 GMT

I definitely see your point of why it is useful in production.
But I'm not a DBA, and so far this is the first time I hear about it (having done 5+ years of jbpm and Activiti on MySQL).

Ie. I'm having a hard time finding how widespread this setting is in the wild. What is the impact on the SQL, on performance, etc?
What would we need to do to make the current scripts compatible?

Re: Error migrating DB to v5.11 schema

brianshowers — Mon, 10 Dec 2012 16:02:13 GMT

I'm certain that the reason you've never run into it is that you almost certainly always write ANSI compatible SQL without thinking about it . It's just this one time that you've run into the case where MySQL's default settings will create non ANSI compatible SQL.

As far as the impact goes, there will be none. There is no performance impact since putting a server into TRADITIONAL mode simply causes the query parser to reject non-standard SQL syntax. In the default setting, the parser will "correct" invalid syntax for you. There will also be no impact on backwards compatibility since any SQL query that is valid in TRADITIONAL mode is also valid in the default MySQL mode. If you want to learn more about MySQL's TRADITIONAL mode, this is a pretty good (and quick) read: http://answers.oreilly.com/topic/171-how-to-use-the-sql-mode-to-control-bad-input-data-handling-in-mysql/

As far as the fix goes, I think it's extremely simple. In the activiti.mysql.upgradestep.510.to.511.engine.sql, when creating the ACT_RE_MODEL table, you simply need to provide a default value for the CREATE_TIME_ and LAST_UPDATE_TIME_ columns. Presumably, 'null' would be fine. When you don't specify a default for a TIMESTAMP type, MySQL will use the value "0000-00-00 00:00:00" which is not a valid ANSI SQL timestamp. So the fix should simply be:


create table ACT_RE_MODEL (
    ID_ varchar(64) not null,
    REV_ integer,
    NAME_ varchar(255),
    KEY_ varchar(255),
    CATEGORY_ varchar(255),
    CREATE_TIME_ timestamp null,
    LAST_UPDATE_TIME_ timestamp null,
    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;

Of course, the same change will also have to be made in activiti.mysql.create.engine.sql

Re: Error migrating DB to v5.11 schema

trademak — Wed, 12 Dec 2012 10:33:10 GMT

Hi,

We've changed the MySQL scripts according to your suggestion. Could you check if it's ok now?
So the changes are made on the current master on Github.

Best regards,

Re: Error migrating DB to v5.11 schema

brianshowers — Wed, 12 Dec 2012 16:57:38 GMT

That did it! All of the activiti-engine unit tests continue to pass for me, and I am now able to both upgrade an existing 5.10 schema and create a new schema from scratch.

Is there a plan to cut a 5.11.x release that will contain this fix, or will we be forced to wait for the 5.12 release? I could fork the code and release my own 5.11.x release, but I would be nervous that the eventual upgrade path from our custom 5.11.x to the official 5.12 would not handle the schema migration correctly.

Also, for the final fix, you may also want a 5.12 (or 5.11.1) patch that will add the default values for anyone who upgraded without the fix. Otherwise, there would be two different schema's in the wild that both say they are v5.11 but have different table definitions.

Re: Error migrating DB to v5.11 schema

trademak — Thu, 13 Dec 2012 20:44:38 GMT

Hi,

Thanks for the feedback. We'll publish 5.12-SNAPSHOTS that contain the fix.
If there are more people that run into this issue we might consider updating the 5.11 release.
For now people can use the updated script in the repository.

Best regards,

Re: Error migrating DB to v5.11 schema

brianshowers — Thu, 13 Dec 2012 20:55:36 GMT

How would that work using a 5.12-SNAPSHOT version? Based on what I've seen, when I run the upgrade using a v5.12-SNAPSHOT copy of the code, the DB schema will then be marked as v5.12-SNAPSHOT. Once that happens, it doesn't seem like any other 5.12-SNAPSHOT versions could ever be applied since the schema upgrade will have been partially applied. Furthermore, would migration from 5.12-SNAPSHOT to the eventual 5.12 work?

It seems to me like there would need to be a new official 5.11 release since that's which version the fix is labeled as. If a 5.12-SNAPSHOT version was ever applied to a DB, that DB will be left in a state that can't be auto-migrated from.

Re: Error migrating DB to v5.11 schema

trademak — Fri, 14 Dec 2012 08:22:27 GMT

I would like to know how many people are using this MySQL strict option, because right now I've only heard this issue from you.
If there are more people with this issue then doing a fix 5.11 release again would make sense.
Also the automatic schema update option for Activiti is something I myself would not use on a non-development machine db.
I would always use the database scripts directly. And then the current fix already helps out.

Best regards,