bk commit into 5.0 tree (dlenev:1.2101) BUG#13525

[dlenev]

Below is the list of changes that have just been committed into a local
5.0 repository of dlenev. When dlenev does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2101 06/03/24 14:42:47 dlenev@stripped +6 -0
  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/t/trigger-trans.test
    1.1 06/03/24 14:42:42 dlenev@stripped +52 -0
    New BitKeeper file ``mysql-test/t/trigger-trans.test''

  mysql-test/r/trigger-trans.result
    1.1 06/03/24 14:42:42 dlenev@stripped +84 -0
    New BitKeeper file ``mysql-test/r/trigger-trans.result''

  sql/sql_trigger.cc
    1.48 06/03/24 14:42:42 dlenev@stripped +4 -0
    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.

  sql/sql_table.cc
    1.299 06/03/24 14:42:42 dlenev@stripped +0 -1
    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".

  mysql-test/t/trigger.test
    1.38 06/03/24 14:42:42 dlenev@stripped +23 -0
    Added test-case covering handling of triggers when one does ALTER TABLE which
    should move table to different database.

  mysql-test/t/trigger-trans.test
    1.0 06/03/24 14:42:42 dlenev@stripped +0 -0
    BitKeeper file /home/dlenev/mysql-5.0-bg18153/mysql-test/t/trigger-trans.test

  mysql-test/r/trigger-trans.result
    1.0 06/03/24 14:42:42 dlenev@stripped +0 -0
    BitKeeper file /home/dlenev/mysql-5.0-bg18153/mysql-test/r/trigger-trans.result

  mysql-test/r/trigger.result
    1.33 06/03/24 14:42:41 dlenev@stripped +31 -0
    Added test-case covering handling of triggers when one does ALTER TABLE which
    should move table to different database.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	dlenev
# Host:	jabberwock.site
# Root:	/home/dlenev/mysql-5.0-bg18153

--- 1.298/sql/sql_table.cc	2006-02-27 15:50:30 +03:00
+++ 1.299/sql/sql_table.cc	2006-03-24 14:42:42 +03:00
@@ -3831,7 +3831,6 @@
       Win32 and InnoDB can't drop a table that is in use, so we must
       close the original table at before doing the rename
     */
-    table_name=thd->strdup(table_name);		// must be saved
     close_cached_table(thd, table);
     table=0;					// Marker that table is closed
   }
--- New file ---
+++ mysql-test/r/trigger-trans.result	06/03/24 14:42:42
drop table if exists t1;
create table t1 (a varchar(16), b int) engine=innodb;
create trigger t1_bi before insert on t1 for each row
begin
set new.a := upper(new.a);
set new.b := new.b + 3;
end|
select trigger_schema, trigger_name, event_object_schema,
event_object_table, action_statement from information_schema.triggers
where event_object_schema = 'test' and event_object_table = 't1';
trigger_schema	trigger_name	event_object_schema	event_object_table	action_statement
test	t1_bi	test	t1	begin
set new.a := upper(new.a);
set new.b := new.b + 3;
end
insert into t1 values ('The Lion', 10);
select * from t1;
a	b
THE LION	13
optimize table t1;
Table	Op	Msg_type	Msg_text
test.t1	optimize	status	OK
select trigger_schema, trigger_name, event_object_schema,
event_object_table, action_statement from information_schema.triggers
where event_object_schema = 'test' and event_object_table = 't1';
trigger_schema	trigger_name	event_object_schema	event_object_table	action_statement
test	t1_bi	test	t1	begin
set new.a := upper(new.a);
set new.b := new.b + 3;
end
insert into t1 values ('The Unicorn', 20);
select * from t1;
a	b
THE LION	13
THE UNICORN	23
alter table t1 add column c int default 0;
select trigger_schema, trigger_name, event_object_schema,
event_object_table, action_statement from information_schema.triggers
where event_object_schema = 'test' and event_object_table = 't1';
trigger_schema	trigger_name	event_object_schema	event_object_table	action_statement
test	t1_bi	test	t1	begin
set new.a := upper(new.a);
set new.b := new.b + 3;
end
insert into t1 values ('Alice', 30, 1);
select * from t1;
a	b	c
THE LION	13	0
THE UNICORN	23	0
ALICE	33	1
alter table t1 rename to t1;
select trigger_schema, trigger_name, event_object_schema,
event_object_table, action_statement from information_schema.triggers
where event_object_schema = 'test' and event_object_table = 't1';
trigger_schema	trigger_name	event_object_schema	event_object_table	action_statement
test	t1_bi	test	t1	begin
set new.a := upper(new.a);
set new.b := new.b + 3;
end
insert into t1 values ('The Crown', 40, 1);
select * from t1;
a	b	c
THE LION	13	0
THE UNICORN	23	0
ALICE	33	1
THE CROWN	43	1
alter table t1 rename to t1, add column d int default 0;
select trigger_schema, trigger_name, event_object_schema,
event_object_table, action_statement from information_schema.triggers
where event_object_schema = 'test' and event_object_table = 't1';
trigger_schema	trigger_name	event_object_schema	event_object_table	action_statement
test	t1_bi	test	t1	begin
set new.a := upper(new.a);
set new.b := new.b + 3;
end
insert into t1 values ('The Pie', 50, 1, 1);
select * from t1;
a	b	c	d
THE LION	13	0	0
THE UNICORN	23	0	0
ALICE	33	1	0
THE CROWN	43	1	0
THE PIE	53	1	1
drop table t1;


--- 1.32/mysql-test/r/trigger.result	2006-03-04 16:54:59 +03:00
+++ 1.33/mysql-test/r/trigger.result	2006-03-24 14:42:41 +03:00
@@ -860,6 +860,37 @@
 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;
--- New file ---
+++ mysql-test/t/trigger-trans.test	06/03/24 14:42:42
# Tests which involve triggers and transactions
# (or just InnoDB storage engine)
-- source include/have_innodb.inc

--disable_warnings
drop table if exists t1;
--enable_warnings

# Test for bug #18153 "OPTIMIZE/ALTER on transactional tables corrupt
#                      triggers/triggers are lost".

create table t1 (a varchar(16), b int) engine=innodb;
delimiter |;
create trigger t1_bi before insert on t1 for each row
begin
 set new.a := upper(new.a);
 set new.b := new.b + 3;
end|
delimiter ;|
select trigger_schema, trigger_name, event_object_schema,
       event_object_table, action_statement from information_schema.triggers
       where event_object_schema = 'test' and event_object_table = 't1';
insert into t1 values ('The Lion', 10);
select * from t1;
optimize table t1;
select trigger_schema, trigger_name, event_object_schema,
       event_object_table, action_statement from information_schema.triggers
       where event_object_schema = 'test' and event_object_table = 't1';
insert into t1 values ('The Unicorn', 20);
select * from t1;
alter table t1 add column c int default 0;
select trigger_schema, trigger_name, event_object_schema,
       event_object_table, action_statement from information_schema.triggers
       where event_object_schema = 'test' and event_object_table = 't1';
insert into t1 values ('Alice', 30, 1);
select * from t1;
# Special tricky cases allowed by ALTER TABLE ... RENAME
alter table t1 rename to t1;
select trigger_schema, trigger_name, event_object_schema,
       event_object_table, action_statement from information_schema.triggers
       where event_object_schema = 'test' and event_object_table = 't1';
insert into t1 values ('The Crown', 40, 1);
select * from t1;
alter table t1 rename to t1, add column d int default 0;
select trigger_schema, trigger_name, event_object_schema,
       event_object_table, action_statement from information_schema.triggers
       where event_object_schema = 'test' and event_object_table = 't1';
insert into t1 values ('The Pie', 50, 1, 1);
select * from t1;
drop table t1;

# End of 5.0 tests


--- 1.37/mysql-test/t/trigger.test	2006-03-04 16:54:59 +03:00
+++ 1.38/mysql-test/t/trigger.test	2006-03-24 14:42:42 +03:00
@@ -1017,6 +1017,29 @@
 # There should be no fantom .TRN files 
 --error ER_TRG_DOES_NOT_EXIST
 drop trigger test.t1_bi;
+# Let us also check handling of this restriction in ALTER TABLE ... RENAME
+--error ER_TRG_IN_WRONG_SCHEMA
+alter table t1 rename to test.t1;
+insert into t1 values (103);
+select @a;
+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';
+# Again there should be no fantom .TRN files 
+--error ER_TRG_DOES_NOT_EXIST
+drop trigger test.t1_bi;
+--error ER_TRG_IN_WRONG_SCHEMA
+alter table t1 rename to test.t1, add column val int default 0;
+insert into t1 values (104);
+select @a;
+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';
+# Table definition should not change
+show create table t1;
+# And once again check for fantom .TRN files 
+--error ER_TRG_DOES_NOT_EXIST
+drop trigger test.t1_bi;
 drop trigger t1_bi;
 drop table t1;
 drop database mysqltest;

--- 1.47/sql/sql_trigger.cc	2006-03-10 03:44:01 +03:00
+++ 1.48/sql/sql_trigger.cc	2006-03-24 14:42:42 +03:00
@@ -1373,6 +1373,7 @@
     This method tries to leave trigger related files in consistent state,
     i.e. it either will complete successfully, or will fail leaving files
     in their initial state.
+    Also this method assumes that subject table is not renamed to itself.
 
   RETURN VALUE
     FALSE  Success
@@ -1393,6 +1394,9 @@
   init_alloc_root(&table.mem_root, 8192, 0);
 
   safe_mutex_assert_owner(&LOCK_open);
+
+  DBUG_ASSERT(my_strcasecmp(table_alias_charset, db, new_db) ||
+              my_strcasecmp(table_alias_charset, old_table, new_table));
 
   if (Table_triggers_list::check_n_load(thd, db, old_table, &table, TRUE))
   {