MySQL Bugs: #19565: crash in system test, trigger, InnoDB transaction deadlock

Bug #19565	crash in system test, trigger, InnoDB transaction deadlock
Submitted:	5 May 2006 17:07	Modified:	31 Jul 2007 20:05
Reporter:	Matthias Leich	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S1 (Critical)
Version:	5.0, 5.1	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	bfsm_2007_02_15, rt_q1_2007, trigger

Description:
The system test "system_1" from the extra QA tests runs
normally at least several minutes without problems.
But when I alter the configuration and use InnoDB instead
of MyISAM for the table tb1_eng1, then the server crashes
after less than two minutes test runtime.
The crash is good reproducible and master.err showed in
all cases the same SQL command
Example:
   thd->query at 0x970dd70 = insert into tb1_logs (dt1, entry_dsc, f4)
   values (now(), concat('Insert row  ', new.f1,' ',
new.f2, ' ', new.f3, ' (tb1_eng1)'), new.f1)
Note: tb1_logs is also of type InnoDB

It is in the first moment surprising that the changed 
configuration setting causing the crash affects the table 
tb1_eng1 and the SQL statement within master.err is an insert
into the table tb1_logs.
But there are several triggers defined for tb1_eng1 and these
triggers do inserts into tb1_logs.

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
         Version 5.1 last ChangeSet@1.2381, 2006-05-05
         BUILD/compile-pentium-debug-max
I do not know if MySQL 5.0 or 4.1 show the same problem.

How to repeat:
Please run the QA testsuite "system_1" and set within the 
configuration file system_1.env
    ENG1=innodb   # Engine type used for the first table

stacktrace

Attachment: stacktrace (application/octet-stream, text), 3.97 KiB.

Hi!

The reason is the code below. But InnoDB must be able to resolve a deadlock by a total rollback of the transaction.

The fix may be to simply remove those assertions from handler.cc and test that an InnoDB error HA_ERR_LOCK_DEADLOCK will cause MySQL to return gracefully from the current SQL statement. It makes no sense to continue the processing of the current SQL statement if a total rollback has happened.

Regards,

Heikki

handler.cc:

int ha_commit_trans(THD *thd, bool all)
{
  int error= 0, cookie= 0;
  THD_TRANS *trans= all ? &thd->transaction.all : &thd->transaction.stmt;
  bool is_real_trans= all || thd->transaction.all.nht == 0;
  handlerton **ht= trans->ht;
  my_xid xid= thd->transaction.xid_state.xid.get_my_xid();
  DBUG_ENTER("ha_commit_trans");

  if (thd->in_sub_stmt)
  {
    /*
      Since we don't support nested statement transactions in 5.0,
      we can't commit or rollback stmt transactions while we are inside
      stored functions or triggers. So we simply do nothing now.
      TODO: This should be fixed in later ( >= 5.1) releases.
    */
    if (!all)
      DBUG_RETURN(0);
    /*
      We assume that all statements which commit or rollback main transaction
      are prohibited inside of stored functions or triggers. So they should
      bail out with error even before ha_commit_trans() call. To be 100% safe
      let us throw error in non-debug builds.
    */
    DBUG_ASSERT(0);
    my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
    DBUG_RETURN(2);
  }

###

int ha_rollback_trans(THD *thd, bool all)
{
  int error=0;
  THD_TRANS *trans=all ? &thd->transaction.all : &thd->transaction.stmt;
  bool is_real_trans=all || thd->transaction.all.nht == 0;
  DBUG_ENTER("ha_rollback_trans");
  if (thd->in_sub_stmt)
  {
    /*
      If we are inside stored function or trigger we should not commit or
      rollback current statement transaction. See comment in ha_commit_trans()
      call for more information.
    */
    if (!all)
      DBUG_RETURN(0);
    DBUG_ASSERT(0);
    my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
    DBUG_RETURN(1);
  }

Bug #22390 was marked as duplicate of this bug.

Bug#24329 was marked as duplicate for this bug.

Bug #24989 is likely a duplicate of this one (stack trace for crash of 5.0.36-debug looks really similar).

This bug should be fixed by the fix for Bug#24989: The DEADLOCK error is improperly handled by InnoDB, which should be in the main tree by the end of this week.
Setting the bug to duplicate.
Please reopen the bug if the problem is still present.