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-m...
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:
Of course, the same change will also have to be made in activiti.mysql.create.engine.sql
. 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-m...
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
