MySQL Bugs: #24989: 'Explicit or implicit commit' error/server crash with concurrent transactions

Bug #24989	'Explicit or implicit commit' error/server crash with concurrent transactions
Submitted:	12 Dec 2006 1:17	Modified:	3 Aug 2007 16:54
Reporter:	Rob Siklos	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.36-BK, 5.0.32, 5.0.27-community-max-nt	OS:	Linux (Linux, WinXP)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	bfsm_2007_02_15, bfsm_2007_05_31, bfsm_2007_06_21, bfsm_2007_06_28

Description:
I have a trigger on a table called "data_staging".  If I have two concurrent transactions which update this table, the second transaction consistently gives the following error message:

[MySQL][ODBC 3.51 Driver][mysqld-5.0.27-community-max-nt]Explicit or implicit commit is not allowed in stored function or trigger.

The sequence of events is as follows (the two execution paths are T1 and T2):
T1: begin transaction
T1: insert data into data_staging table
T1: sleep for 10 seconds
T2: begin transaction
T2: insert data into data_staging table
T1: insert data into data_staging table
T1: commit
T2: Explicit or implicit commit is not allowed in stored function or trigger.

How to repeat:
See attached files.  Restore the schema using "schema.sql" (InnoDB tables) into an empty database called "rob".  Open two shell windows - in one, run "php odbc1.php", then right away, run "php odbc2.php" in the other window.  The error message should appear in the second window.

In PHP, the problem happens if I use the native "odbc" functions (e.g. odbc_connect), but the problem DOES NOT happen if I use mysql_connect or the PDO functions (PDO_ODBC driver).

The problem happens additionally in my c# program (equivalent to the PHP files, except multi-threaded), which uses the ODBC connector.  However, if I set the transaction isolation level to IsolationLevel.Snapshot, the problem goes away.  I don't know what this option means for MySQL, so I'm hesitant to use it.

Files for replication

Attachment: 24989.tar.gz (application/x-gzip, text), 1.41 KiB.

Note: I changed my c# program to use the MySQL Connector/Net 1.0, and I get the same problem.

This issue is causing us serious problems - any help would be greatly appreciated.

Thank you for a problem report. Looks like your problem is caused by AUTOCOMMIT (see http://dev.mysql.com/doc/refman/5.0/en/innodb-and-autocommit.html) default (ON) setting in ODBC driver (http://msdn.microsoft.com/library/en-us/odbc/htm/odbcsqlsetconnectattr.asp?frame=true). Please, check.

I can't see how this is the problem, since I start all transactions with the "START TRANSACTION" command.   According to your first link, this allows me to perform a multiple-statement transaction even if autocommit is enabled.

Am I missing something here?

Thanks.

Same thing happens in 5.1.14-beta-community-nt

Sorry for a delay with this bug. I've got a crash with latest 5.0.36-BK (-debug build) on Linux when tried your test case:

In T1:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

Emysql> INSERT INTO data_staging (sensor_id, timestamp, value)
    -> VALUES (1, NOW() + INTERVAL FLOOR(RAND() * 10000) MINUTE, 1);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT sleep(10);

In T2:

openxs@suse:~/dbs/5.0> bin/mysql -uroot bug24989
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.36-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> INSERT INTO data_staging (sensor_id, timestamp, value)
    -> VALUES (1, NOW() + INTERVAL FLOOR(RAND() * 10000) MINUTE, 1);

In T1 (as soon as sleep(10) returned):

+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.01 sec)

mysql> INSERT INTO data_staging (sensor_id, timestamp, value)
    -> VALUES (1, NOW() + INTERVAL FLOOR(RAND() * 10000) MINUTE, 1);
Query OK, 1 row affected (0.02 sec)

mysql>
Number of processes running now: 0
070116 08:23:20  mysqld restarted

In error log:

Stack range sanity check OK, backtrace follows:
0x81d9c04
0xffffe410
0x401b6b75
0x401ae903
0x82acc93
0x82b8f00
0x82bc3eb
0x8250e24
0x824f982
0x81f3d82
0x834167d
0x83412ff
0x834151f
0x833e0f3
0x833e8c1
0x834fa5b
0x822292e
0x824f7f6
0x81f3d82
0x81f99d7
0x81f0335
0x81efb86
0x81eed72
0x40050aa7
0x40247c2e
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow
instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8d3e598 = insert into _arrays (username, array_name) values ( li
brary_user(), tocal_ids_n )
thd->thread_id=2

Resolved stack trace:

openxs@suse:~/dbs/5.0> bin/resolve_stack_dump  -s /tmp/mysqld50.sym -n 24989.st
ack
0x81d9c04 handle_segfault + 412
0xffffe410 _end + -142011248
0x401b6b75 _end + 933534709
0x401ae903 _end + 933501315
0x82acc93 _Z17ha_rollback_transP3THDb + 209
0x82b8f00 _Z27convert_error_code_to_mysqliP3THD + 106
0x82bc3eb _ZN11ha_innobase9write_rowEPc + 1409
0x8250e24 _Z12write_recordP3THDP8st_tableP12st_copy_info + 1574
0x824f982 _Z12mysql_insertP3THDP13st_table_listR4ListI4ItemERS3_IS5_ES6_S6_15enu
m_duplicatesb + 2320
0x81f3d82 _Z21mysql_execute_commandP3THD + 8974
0x834167d _ZN13sp_instr_stmt9exec_coreEP3THDPj + 17
0x83412ff _ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr + 385
0x834151f _ZN13sp_instr_stmt7executeEP3THDPj + 287
0x833e0f3 _ZN7sp_head7executeEP3THD + 1157
0x833e8c1 _ZN7sp_head15execute_triggerEP3THDPKcS3_P13st_grant_info + 587
0x834fa5b _ZN19Table_triggers_list16process_triggersEP3THD14trg_event_type20trg_
action_time_typeb + 205
0x822292e _Z36fill_record_n_invoke_before_triggersP3THDR4ListI4ItemES4_bP19Table
_triggers_list14trg_event_type + 76
0x824f7f6 _Z12mysql_insertP3THDP13st_table_listR4ListI4ItemERS3_IS5_ES6_S6_15enu
m_duplicatesb + 1924
0x81f3d82 _Z21mysql_execute_commandP3THD + 8974
0x81f99d7 _Z11mysql_parseP3THDPcj + 475
0x81f0335 _Z16dispatch_command19enum_server_commandP3THDPcj + 1951
0x81efb86 _Z10do_commandP3THD + 526
0x81eed72 handle_one_connection + 982
0x40050aa7 _end + 932068135
0x40247c2e _end + 934128814

We have a serious crashing server bug here.

With 5.0.36 (non-debug) I've got exactly the same result as bug reporter:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO data_staging (sensor_id, timestamp, value)
    -> VALUES (1, NOW() + INTERVAL FLOOR(RAND() * 10000) MINUTE, 1);
ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored functio
n or trigger.

This is still a bug, as there is no commit in triggers or functions.

It is likely a duplicate of bug #19565. At least, it should be re-verified when bug #19565 will be fixed.

Bug #27329 was marked as duplicate of this bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27471

ChangeSet@1.2500, 2007-05-28 17:45:02+04:00, evgen@moonbone.local +4 -0
  Bug#24989: The DEADLOCK error is improperly handled by InnoDB.
  
  When innodb detects a deadlock it calls ha_rollback_trans() to rollback the 
  main transaction. But such action isn't allowed from inside of triggers and
  functions. When it happen the 'Explicit or implicit commit' error is thrown
  even if there is no commit/rollback statements in the trigger/function. This
  leads to the user confusion.
  
  Now the convert_error_code_to_mysql() function doesn't call the 
  ha_rollback_trans() function directly but rather returns an error.
  The sp_rcontext::find_handler() now doesn't allow DEADLOCK errors to be caught
  by the trigger/function error handlers.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27509

ChangeSet@1.2500, 2007-05-29 00:07:22+04:00, evgen@moonbone.local +5 -0
  Bug#24989: The DEADLOCK error is improperly handled by InnoDB.
  
  When innodb detects a deadlock it calls ha_rollback_trans() to rollback the 
  main transaction. But such action isn't allowed from inside of triggers and
  functions. When it happen the 'Explicit or implicit commit' error is thrown
  even if there is no commit/rollback statements in the trigger/function. This
  leads to the user confusion.
  
  Now the convert_error_code_to_mysql() function doesn't call the 
  ha_rollback_trans() function directly but rather returns an error.
  The sp_rcontext::find_handler() now doesn't allow DEADLOCK errors to be caught
  by the trigger/function error handlers.
  Now the dispatch_command() function rolls back the main transaction if
  the DEADLOCK error occur during statement execution.

A code review sent by email

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29205

ChangeSet@1.2500, 2007-06-20 21:33:53+04:00, evgen@moonbone.local +8 -0
  Bug#24989: The DEADLOCK error is improperly handled by InnoDB.
  
  When innodb detects a deadlock it calls ha_rollback_trans() to rollback the 
  main transaction. But such action isn't allowed from inside of triggers and
  functions. When it happen the 'Explicit or implicit commit' error is thrown
  even if there is no commit/rollback statements in the trigger/function. This
  leads to the user confusion.
  
  Now the convert_error_code_to_mysql() function doesn't call the 
  ha_rollback_trans() function directly but rather returns an error
  and sets the thd->transaction_rollback_request and the
  thd->is_fatal_sub_stmt_error flags.
  The sp_rcontext::find_handler() now doesn't allow errors to be caught by the
  trigger/function error handlers when the thd->is_fatal_sub_stmt_error flag
  is set.
  The transaction_rollback_request and the is_fatal_sub_stmt_error flags are 
  added to the THD class. The are initialized by the THD class constructor.
  The  function now resets 
  The is_fatal_sub_stmt_error flag is reset by the reset_sub_statement_state
  function and the transaction_rollback_request - by the
  mysql_reset_thd_for_next_command and the ha_autocommit_or_rollback functions.
  Now the ha_autocommit_or_rollback function rolls back main transaction
  when not in a sub statement and the thd->transaction_rollback_request
  is set.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30327

ChangeSet@1.2500, 2007-07-04 23:23:58+04:00, evgen@moonbone.local +9 -0
  Bug#24989: The DEADLOCK error is improperly handled by InnoDB.
  
  When innodb detects a deadlock it calls ha_rollback_trans() to rollback the 
  main transaction. But such action isn't allowed from inside of triggers and
  functions. When it happen the 'Explicit or implicit commit' error is thrown
  even if there is no commit/rollback statements in the trigger/function. This
  leads to the user confusion.
  
  Now the convert_error_code_to_mysql() function doesn't call the 
  ha_rollback_trans() function directly but rather calls the
  mark_transaction_to_rollback function and returns an error.
  The sp_rcontext::find_handler() now doesn't allow errors to be caught by the
  trigger/function error handlers when the thd->is_fatal_sub_stmt_error flag
  is set. Procedures are still allowed to catch such errors.
  The transaction_rollback_request and the is_fatal_sub_stmt_error flags are 
  added to the THD class. The are initialized by the THD class constructor.
  Now the mysql_execute_command function resets the THD::is_fatal_sub_stmt_error
  flag.
  Now the ha_autocommit_or_rollback function rolls back main transaction
  when not in a sub statement and the thd->transaction_rollback_request
  is set.
  The sp_head::execute function now allows execution of a continue handler after
  fatal error but aborts right after it.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30568

ChangeSet@1.2500, 2007-07-10 00:48:08+04:00, evgen@moonbone.local +9 -0
  Bug#24989: The DEADLOCK error is improperly handled by InnoDB.
  
  When innodb detects a deadlock it calls ha_rollback_trans() to rollback the 
  main transaction. But such action isn't allowed from inside of triggers and
  functions. When it happen the 'Explicit or implicit commit' error is thrown
  even if there is no commit/rollback statements in the trigger/function. This
  leads to the user confusion.
  
  Now the convert_error_code_to_mysql() function doesn't call the 
  ha_rollback_trans() function directly but rather calls the
  mark_transaction_to_rollback function and returns an error.
  The sp_rcontext::find_handler() now doesn't allow errors to be caught by the
  trigger/function error handlers when the thd->is_fatal_sub_stmt_error flag
  is set. Procedures are still allowed to catch such errors.
  The sp_rcontext::find_handler function now accepts a THD handle as a parameter.
  The transaction_rollback_request and the is_fatal_sub_stmt_error flags are 
  added to the THD class. The are initialized by the THD class constructor.
  Now the mysql_execute_command function resets the THD::is_fatal_sub_stmt_error
  flag.
  Now the ha_autocommit_or_rollback function rolls back main transaction
  when not in a sub statement and the thd->transaction_rollback_request
  is set.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30847

ChangeSet@1.2500, 2007-07-13 02:04:05+04:00, evgen@moonbone.local +8 -0
  Bug#24989: The DEADLOCK error is improperly handled by InnoDB.
  
  When innodb detects a deadlock it calls ha_rollback_trans() to rollback the 
  main transaction. But such action isn't allowed from inside of triggers and
  functions. When it happen the 'Explicit or implicit commit' error is thrown
  even if there is no commit/rollback statements in the trigger/function. This
  leads to the user confusion.
  
  Now the convert_error_code_to_mysql() function doesn't call the 
  ha_rollback_trans() function directly but rather calls the
  mark_transaction_to_rollback function and returns an error.
  The sp_rcontext::find_handler() now doesn't allow errors to be caught by the
  trigger/function error handlers when the thd->is_fatal_sub_stmt_error flag
  is set. Procedures are still allowed to catch such errors.
  The sp_rcontext::find_handler function now accepts a THD handle as a parameter.
  The transaction_rollback_request and the is_fatal_sub_stmt_error flags are 
  added to the THD class. The are initialized by the THD class constructor.
  Now the ha_autocommit_or_rollback function rolls back main transaction
  when not in a sub statement and the thd->transaction_rollback_request
  is set.
  The THD::restore_sub_statement_state function now resets the 
  thd->is_fatal_sub_stmt_error flag on exit from a sub-statement.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31088

ChangeSet@1.2500, 2007-07-18 17:28:14+04:00, evgen@moonbone.local +9 -0
  Bug#24989: The DEADLOCK error is improperly handled by InnoDB.
  
  When innodb detects a deadlock it calls ha_rollback_trans() to rollback the 
  main transaction. But such action isn't allowed from inside of triggers and
  functions. When it happen the 'Explicit or implicit commit' error is thrown
  even if there is no commit/rollback statements in the trigger/function. This
  leads to the user confusion.
  
  Now the convert_error_code_to_mysql() function doesn't call the 
  ha_rollback_trans() function directly but rather calls the
  mark_transaction_to_rollback function and returns an error.
  The sp_rcontext::find_handler() now doesn't allow errors to be caught by the
  trigger/function error handlers when the thd->is_fatal_sub_stmt_error flag
  is set. Instead it tries to find a most inner procedure that isn't called
  directly or indirectly from any function/trigger.
  Procedures are still allowed to catch such errors.
  The sp_rcontext::find_handler function now accepts a THD handle as a parameter.
  The transaction_rollback_request and the is_fatal_sub_stmt_error flags are 
  added to the THD class. The are initialized by the THD class constructor.
  Now the ha_autocommit_or_rollback function rolls back main transaction
  when not in a sub statement and the thd->transaction_rollback_request
  is set.
  The THD::restore_sub_statement_state function now resets the 
  thd->is_fatal_sub_stmt_error flag on exit from a sub-statement.

Hi,
We've just run up against this problem using 5.0.38 and a very simplified schema and trigger. (can provide example if required)

We have just noticed that the issue is resolved when the auto-increment part of the table definition is removed. I was wondering if this is as you expect after making the patch or if there may be another issue here.

Is there an expected date when this patch will make it into the next release of 5.0?

Thanks,

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31819

ChangeSet@1.2500, 2007-07-30 17:14:34+04:00, evgen@moonbone.local +8 -0
  Bug#24989: The DEADLOCK error is improperly handled by InnoDB.
  
  When innodb detects a deadlock it calls ha_rollback_trans() to rollback the 
  main transaction. But such action isn't allowed from inside of triggers and
  functions. When it happen the 'Explicit or implicit commit' error is thrown
  even if there is no commit/rollback statements in the trigger/function. This
  leads to the user confusion.
  
  Now the convert_error_code_to_mysql() function doesn't call the 
  ha_rollback_trans() function directly but rather calls the
  mark_transaction_to_rollback function and returns an error.
  The sp_rcontext::find_handler() now doesn't allow errors to be caught by the
  trigger/function error handlers when the thd->is_fatal_sub_stmt_error flag
  is set. Procedures are still allowed to catch such errors.
  The sp_rcontext::find_handler function now accepts a THD handle as a parameter.
  The transaction_rollback_request and the is_fatal_sub_stmt_error flags are 
  added to the THD class. The are initialized by the THD class constructor.
  Now the ha_autocommit_or_rollback function rolls back main transaction
  when not in a sub statement and the thd->transaction_rollback_request
  is set.
  The THD::restore_sub_statement_state function now resets the 
  thd->is_fatal_sub_stmt_error flag on exit from a sub-statement.

Bug #19565 crash in system test, trigger, InnoDB transaction deadlock was marked a duplicate of this bug.

Pushed into 5.1.21-beta

Pushed into 5.0.48

Noted in 5.0.48, 5.1.21 changelogs.

In a stored function or trigger, when InnoDB detected deadlock, it
attempted rollback and displayed an incorrect error message (Explicit
or implicit commit is not allowed in stored function or trigger). Now
InnoDB returns an error under these conditions and does not attempt
rollback.