From 2ccaf115d94ad52504dfef708dee1a77339813a6 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 24 Mar 2006 14:58:18 +0300 Subject: Fix for bug #18153 "ALTER/OPTIMIZE/REPAIR on transactional tables corrupt triggers". Applying ALTER/OPTIMIZE/REPAIR TABLE statements to transactional table or to table of any type on Windows caused disappearance of its triggers. Bug was introduced in 5.0.19 by my fix for bug #13525 "Rename table does not keep info of triggers" (see comment for sql_table.cc for more info). . mysql-test/r/trigger.result: Added test-case covering handling of triggers when one does ALTER TABLE which should move table to different database. mysql-test/t/trigger.test: Added test-case covering handling of triggers when one does ALTER TABLE which should move table to different database. sql/sql_table.cc: mysql_alter_table(): Removal of strdup() which is no longer necessary allows us to preserve nice assumption that "(new_db != db || new_table != table_name) indicates that table will be renamed. So now we really can use this condition to avoid updating trigger definitions when table is not renamed. Note that we can't use (alter_info->flags & ALTER_RENAME) condition instead since it can be also true when we do "ALTER TABLE t1 RENAME TO t1". sql/sql_trigger.cc: Table_triggers_list::change_table_name(): Mentioned assumption that subject table is not renamed to itself in method description. Added DBUG_ASSERT() to catch wrong usage of this method. mysql-test/r/trigger-trans.result: New BitKeeper file ``mysql-test/r/trigger-trans.result'' mysql-test/t/trigger-trans.test: New BitKeeper file ``mysql-test/t/trigger-trans.test'' --- mysql-test/r/trigger.result | 31 +++++++++++++++++++++++++++++++ 1 file changed, 31 insertions(+) (limited to 'mysql-test/r/trigger.result') diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index c9a91758336..009cde1b747 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -860,6 +860,37 @@ trigger_schema trigger_name event_object_schema event_object_table action_statem mysqltest t1_bi mysqltest t1 set @a:=new.id drop trigger test.t1_bi; ERROR HY000: Trigger does not exist +alter table t1 rename to test.t1; +ERROR HY000: Trigger in wrong schema +insert into t1 values (103); +select @a; +@a +103 +select trigger_schema, trigger_name, event_object_schema, +event_object_table, action_statement from information_schema.triggers +where event_object_schema = 'test' or event_object_schema = 'mysqltest'; +trigger_schema trigger_name event_object_schema event_object_table action_statement +mysqltest t1_bi mysqltest t1 set @a:=new.id +drop trigger test.t1_bi; +ERROR HY000: Trigger does not exist +alter table t1 rename to test.t1, add column val int default 0; +ERROR HY000: Trigger in wrong schema +insert into t1 values (104); +select @a; +@a +104 +select trigger_schema, trigger_name, event_object_schema, +event_object_table, action_statement from information_schema.triggers +where event_object_schema = 'test' or event_object_schema = 'mysqltest'; +trigger_schema trigger_name event_object_schema event_object_table action_statement +mysqltest t1_bi mysqltest t1 set @a:=new.id +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) default NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop trigger test.t1_bi; +ERROR HY000: Trigger does not exist drop trigger t1_bi; drop table t1; drop database mysqltest; -- cgit v1.2.1