Mac vs PC database-related anomaly

[bwd]

We have developers running Activiti 5.4 on a mixture of Windows 7 and Mac boxes using MySQL 5.5 as the data store (each developer has a local database).

Things seem to work fine on Windows, but the Mac developers are reporting the following error when starting a workflow:

Apr 19, 2011 1:51:42 PM org.activiti.engine.impl.interceptor.CommandContext close
SEVERE: Error while closing command context
org.apache.ibatis.exceptions.PersistenceException:
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`foo`.`act_ru_identitylink`, CONSTRAINT `ACT_FK_TSKASS_TASK` FOREIGN KEY (`TASK_ID_`) REFERENCES `ACT_RU_TASK` (`ID_`))
### The error may involve org.activiti.persistence.insertIdentityLink-Inline
### The error occurred while setting parameters
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`instance_2`.`act_ru_identitylink`, CONSTRAINT `ACT_FK_TSKASS_TASK` FOREIGN KEY (`TASK_ID_`) REFERENCES `ACT_RU_TASK` (`ID_`))
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:100)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:87)
        at org.activiti.engine.impl.db.DbSqlSession.flushInserts(DbSqlSession.java:392)
        at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:319)
        at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:134)
        at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:90)
        at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:49)
        at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
        at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
        at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
        at org.activiti.engine.impl.RuntimeServiceImpl.startProcessInstanceByKey(RuntimeServiceImpl.java:49)

When we look at the query logs, it appears to be associated with the following SQL:


118 Query     insert into ACT_RU_TASK (ID_, REV_, NAME_, DESCRIPTION_, PRIORITY_, CREATE_TIME_, OWNER_,
                      ASSIGNEE_, DELEGATION_, EXECUTION_ID_, PROC_INST_ID_, PROC_DEF_ID_, TASK_DEF_KEY_, DUE_DATE_)
    values ('157',
            1,
            'foo',
            'bar',
            50,
            '2011-04-20 13:15:52',
            null,
            null,
            null,
            '146',
            '146',
            'sid-1e597cf9-9344-4dd7-922b-9b63534e2660:3:131',
            'sid-30C80E2F-EBA5-43E5-86ED-164CF88DC1E5',
            null
           )

and then a few queries later
                 
118 Query     insert into ACT_RU_IDENTITYLINK (ID_, REV_, TYPE_, USER_ID_, GROUP_ID_, TASK_ID_)
    values ('158',
            1,
            'candidate',
            '2:2',
            null,
            '157')
117 Query     rollback
117 Query     SET autocommit=1

One of the developers was able to get past this by dropping the foreign key constraint, but this later resulted in a similar error associated with another query.

FYI - We're running multiple Process Engines in a single servlet container, but each is given a distinct DataSource pointing to a different database schema.

One additional question came up during a review of the query logs:  Is Activiti modifying the connections it obtains from the DataSource connection pool?  (Someone noticed the 'SET autocommit' statements.)

Thanks,

BD

[jbarrez]

That sure seems strange, because all our developers work on macs and we rrun often on MySQL.

The autocommit is only for DDL statements, so no worries there.

The only thing I can think of is that the mac Mysql installations have a differen default. Could you check on differences in storage engine, transaction isolation, etc .. ?

[bwd]

Yes - Strange.  The flows work consistently under Windows and Linux, but fail with some consistency under MacOS.

[mcphilip]

Did bwd's issue ever get resolved?  I'm seeing the exact same behavior as originally reported.  I've never seen the issue doing process development on a Windows 7 environment but a coworker who is just getting started with Activiti on a MacOS environment is seeing the issue fairly consistently.

Any help would be much appreciated.

[jbarrez]

Afaik no.

All Activiti devs use a mac, so we should have seen this error already.

Which version of MySQL are u using? How did you install it?

[crkhd]

'mcphilip' posted his query on my behalf. I am seeing this issue with: 
MySQL Version: Ver 14.14 Distrib 5.5.9, for osx10.6 (i386) using readline 5.1. I
MySQL Installer :  Mac OS X 10.6 (installer format).

Thanks!

[ronald_van_kuij]

Joram asked if someone with this problem

"Could you check on differences in storage engine, transaction isolation, etc .. ?"

Is that possible?

[crkhd]

Following are the Storage Engine and Transaction Isolation settings from my MySQL instance. Please let me know if i need to provide any other details. Thanks!

mysql> show global variables;

innodb_adaptive_flushing                          | ON                                                                                                                     |
| innodb_adaptive_hash_index                        | ON                                                                                                                     |
| innodb_additional_mem_pool_size                   | 8388608                                                                                                                |
| innodb_autoextend_increment                       | 8                                                                                                                      |
| innodb_autoinc_lock_mode                          | 1                                                                                                                      |
| innodb_buffer_pool_instances                      | 1                                                                                                                      |
| innodb_buffer_pool_size                           | 134217728                                                                                                              |
| innodb_change_buffering                           | all                                                                                                                    |
| innodb_checksums                                  | ON                                                                                                                     |
| innodb_commit_concurrency                         | 0                                                                                                                      |
| innodb_concurrency_tickets                        | 500                                                                                                                    |
| innodb_data_file_path                             | ibdata1:10M:autoextend                                                                                                 |
| innodb_data_home_dir                              |                                                                                                                        |
| innodb_doublewrite                                | ON                                                                                                                     |
| innodb_fast_shutdown                              | 1                                                                                                                      |
| innodb_file_format                                | Antelope                                                                                                               |
| innodb_file_format_check                          | ON                                                                                                                     |
| innodb_file_format_max                            | Antelope                                                                                                               |
| innodb_file_per_table                             | OFF                                                                                                                    |
| innodb_flush_log_at_trx_commit                    | 1                                                                                                                      |
| innodb_flush_method                               |                                                                                                                        |
| innodb_force_recovery                             | 0                                                                                                                      |
| innodb_io_capacity                                | 200                                                                                                                    |
| innodb_lock_wait_timeout                          | 50                                                                                                                     |
| innodb_locks_unsafe_for_binlog                    | OFF                                                                                                                    |
| innodb_log_buffer_size                            | 8388608                                                                                                                |
| innodb_log_file_size                              | 5242880                                                                                                                |
| innodb_log_files_in_group                         | 2                                                                                                                      |
| innodb_log_group_home_dir                         | ./                                                                                                                     |
| innodb_max_dirty_pages_pct                        | 75                                                                                                                     |
| innodb_max_purge_lag                              | 0                                                                                                                      |
| innodb_mirrored_log_groups                        | 1                                                                                                                      |
| innodb_old_blocks_pct                             | 37                                                                                                                     |
| innodb_old_blocks_time                            | 0                                                                                                                      |
| innodb_open_files                                 | 300                                                                                                                    |
| innodb_purge_batch_size                           | 20                                                                                                                     |
| innodb_purge_threads                              | 0                                                                                                                      |
| innodb_read_ahead_threshold                       | 56                                                                                                                     |
| innodb_read_io_threads                            | 4                                                                                                                      |
| innodb_replication_delay                          | 0                                                                                                                      |
| innodb_rollback_on_timeout                        | OFF                                                                                                                    |
| innodb_spin_wait_delay                            | 6                                                                                                                      |
| innodb_stats_on_metadata                          | ON                                                                                                                     |
| innodb_stats_sample_pages                         | 8                                                                                                                      |
| innodb_strict_mode                                | OFF                                                                                                                    |
| innodb_support_xa                                 | ON                                                                                                                     |
| innodb_sync_spin_loops                            | 30                                                                                                                     |
| innodb_table_locks                                | ON                                                                                                                     |
| innodb_thread_concurrency                         | 0                                                                                                                      |
| innodb_thread_sleep_delay                         | 10000                                                                                                                  |
| innodb_use_native_aio                             | OFF                                                                                                                    |
| innodb_use_sys_malloc                             | ON                                                                                                                     |
| innodb_version                                    | 1.1.5                                                                                                                  |
| innodb_write_io_threads                           | 4

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+———————–+—————–+
| @@GLOBAL.tx_isolation | @@tx_isolation  |
+———————–+—————–+
| REPEATABLE-READ       | REPEATABLE-READ |
+———————–+—————–+

[crkhd]

Another update on this is that i tried the same code pointing to a MySQL instance running on a WindowsXP machine and it works just fine.

Thanks!

[ronald_van_kuij]

I there a difference in the MySQL config of the windows machine? (sorry, have no windows, nor mac so cannot check myself)