Bug #22510 mysql-test-run trigger.test fails when using InnoDB as the default engine
Submitted: 20 Sep 2006 8:51 Modified: 2 May 2008 17:15
Reporter: Paul McCullagh (Basic Quality Contributor) (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Tests: Server Severity:S7 (Test Cases)
Version:5.1.12 OS:Linux (Linux)
Assigned to: Davi Arnaut CPU Architecture:Any
Tags: errno 1442, innodb, test, trigger

[20 Sep 2006 8:51] Paul McCullagh
Description:
The following code fails to produce the expected error when run using InnoDB or the PBXT engine:

create trigger t1_bu before update on t1
  for each row insert into t1 values (2);
--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
update t1 set f1= 10;

According to the comment in the test:

# Test for bug #11896 "Partial locking in case of recursive trigger
# definitions". Recursion in triggers should not be allowed.
# We also should not allow to change tables which are used in
# statements invoking this trigger.

How to repeat:
Run the following test with the last BK 5.1 tree:

./mysql-test-run --mysqld=--default-storage-engine=innodb --start-from=trigger

TEST                            RESULT
-------------------------------------------------------
trigger-compat                 [ pass ]   
trigger-grant                  [ pass ]   
trigger-trans                  [ pass ]   
trigger                        [ fail ]

Errors are (from /Users/build/Development/mysql-5.1/mysql-5.1.12-beta/mysql-test/var/log/mysqltest-time) :
mysqltest: At line 844: query 'update t1 set f1= 10' succeeded - should have failed with errno 1442...
(the last lines may be the most important ones)

Suggested fix:
I am submitting this as a bug, although I am not sure if the restriction is only intended to apply to non-transactional engines.
[20 Sep 2006 9:41] Valeriy Kravchuk
Thank you for a problem report. Did you ran that test as "root" user? Your report looks like a dupliucate of http://bugs.mysql.com/bug.php?id=21883.
[20 Sep 2006 13:35] Paul McCullagh
The test was not run as user root, but I don't think the bugs are the same. The problem I reported can only be repeated when running the test with InnoDB. MyISAM works as expected.

In addition, Bug #21883, fails in RENAME, expecting error 1 (trigger.test line 1143):

--error 1
rename table t1 to t2;

This bug fails on UPDATE, expecting error 1442 (trigger test line 843):

--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
update t1 set f1= 10;
[20 Sep 2006 14:10] Valeriy Kravchuk
Verified just as described with 5.1.12-BK (ChangeSet@1.2330.1.1, 2006-09-18 17:28:17+02:00) on Linux:

openxs@suse:~/dbs/5.1/mysql-test> ./mysql-test-run --mysqld=--default-storage-e
ngine=innodb trigger
Logging: ./mysql-test-run --mysqld=--default-storage-engine=innodb trigger
Stopping master cluster
Installing Test Databases
Removing Stale Files
Installing Master Databases
running  ../libexec/mysqld --no-defaults --bootstrap --skip-grant-tables --based
ir=.. --datadir=mysql-test/var/master-data --skip-innodb --skip-ndbcluster --tmp
dir=.
../libexec/mysqld: Table 'mysql.plugin' doesn't exist
060920 15:07:48 [ERROR] Can't open the mysql.plugin table. Please run the mysql_
upgrade script to create it.
Installing Master Databases 1
Installing Slave Databases
Loading Standard Test Databases
Starting Tests

TEST                            RESULT
-------------------------------------------------------
trigger                        [ fail ]

Errors are (from /home/openxs/dbs/5.1/mysql-test/var/log/mysqltest-time) :
mysqltest: At line 844: query 'update t1 set f1= 10' succeeded - should have fai
led with errno 1442...
(the last lines may be the most important ones)

Aborting: trigger failed in default mode. To continue, re-run with '--force'.

Ending Tests
Shutting-down MySQL daemon

Master shutdown finished
Slave shutdown finished
[13 Nov 2006 19:45] Kent Stroker
Reproducable on 5.0.27 as well.
[28 Nov 2007 12:28] Matthias Leich
Bug#32790 crash in trigger.test with InnoDB for a table
causes now (2007-11-28) that trigger.test with InnoDB
as default engine crashes the server (5.0,5.1,6.0).
[2 May 2008 15:52] Davi Arnaut
The test case problem is that for transactional tables the trigger is not invoked because a previous trigger failure causes a rollback.
[2 May 2008 17:15] Davi Arnaut
Closing as not a bug because a lot of our test cases do not work properly when the default engine is changed and this is just a particular case that causes a failure when a default transactional is not expected.

The update does not produce a error because there is not data in the table since a previous trigger failure causes a rollback for transactional engines but not for non-transactional ones.