MySQL Bugs: #22510: mysql-test-run trigger.test fails when using InnoDB as the default engine

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

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.

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.

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;

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

Reproducable on 5.0.27 as well.

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).

The test case problem is that for transactional tables the trigger is not invoked because a previous trigger failure causes a rollback.

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.