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

1 files changed, 84 insertions, 0 deletions

diff --git a/mysql-test/r/trigger-trans.result b/mysql-test/r/trigger-trans.result
new file mode 100644
index 00000000000..b56abf1f59a
--- /dev/null
+++ b/mysql-test/r/trigger-trans.result
@@ -0,0 +1,84 @@
+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;