bk commit into 5.0 tree (dlenev:1.1928) BUG#5894

[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.1928 05/05/30 18:55:56 dlenev@stripped +3 -0
  Added test for bug #5894 "Triggers with altered tables cause corrupt
  databases" and basic handling of errors which happen in triggers.
  (The bug itself was fixed by several previous patches).
  Fixed bug in multi-delete which were exposed by these tests.

  sql/sql_delete.cc
    1.149 05/05/30 18:55:52 dlenev@stripped +2 -2
    multi_delete::send_eof():
      Fixed bug which were exposed by testing of basic handling of errors
      in triggers. Error which happened in multi_delete::do_deletes() 
      was wiped out by thd->clear_error() and thus never sent to client
      (and this stalled client connection). 

  mysql-test/t/trigger.test
    1.11 05/05/30 18:55:52 dlenev@stripped +127 -0
    Added test for bug #5894 "Triggers with altered tables cause corrupt
    databases" and basic handling of errors which happen in triggers.

  mysql-test/r/trigger.result
    1.7 05/05/30 18:55:52 dlenev@stripped +167 -0
    Added test for bug #5894 "Triggers with altered tables cause corrupt
    databases" and basic handling of errors which happen in triggers.

# 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:	brandersnatch.localdomain
# Root:	/home/dlenev/src/mysql-5.0-ttdf

--- 1.148/sql/sql_delete.cc	Tue May 24 22:25:44 2005
+++ 1.149/sql/sql_delete.cc	Mon May 30 18:55:52 2005
@@ -696,11 +696,11 @@
     Note that if we deleted nothing we don't write to the binlog (TODO:
     fix this).
   */
-  if (deleted && (error <= 0 || normal_tables))
+  if (deleted && ((error <= 0 && !local_error) || normal_tables))
   {
     if (mysql_bin_log.is_open())
     {
-      if (error <= 0)
+      if (error <= 0 && !local_error)
         thd->clear_error();
       Query_log_event qinfo(thd, thd->query, thd->query_length,
 			    transactional_tables, FALSE);

--- 1.6/mysql-test/r/trigger.result	Tue May 24 22:19:27 2005
+++ 1.7/mysql-test/r/trigger.result	Mon May 30 18:55:52 2005
@@ -315,3 +315,170 @@
 3	4	3	Fired
 5	6	5	Fired
 drop table t1;
+create table t1 (i int, at int, k int, key(k)) engine=myisam;
+create table t2 (i int);
+insert into t1 values (1, 1, 1);
+insert into t2 values (1), (2), (3);
+create trigger ai after insert on t1 for each row set @a:= new.at;
+create trigger au after update on t1 for each row set @a:= new.at;
+create trigger ad after delete on t1 for each row set @a:= old.at;
+alter table t1 drop column at;
+select * from t1;
+i	k
+1	1
+insert into t1 values (2, 1);
+ERROR 42S22: Unknown column 'at' in 'NEW'
+select * from t1;
+i	k
+1	1
+2	1
+update t1 set k = 2 where i = 2;
+ERROR 42S22: Unknown column 'at' in 'NEW'
+select * from t1;
+i	k
+1	1
+2	2
+delete from t1 where i = 2;
+ERROR 42S22: Unknown column 'at' in 'OLD'
+select * from t1;
+i	k
+1	1
+load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by ''
enclosed by '' (i, k);
+ERROR 42S22: Unknown column 'at' in 'NEW'
+select * from t1;
+i	k
+1	1
+1	2
+insert into t1 select 3, 3;
+ERROR 42S22: Unknown column 'at' in 'NEW'
+select * from t1;
+i	k
+1	1
+1	2
+3	3
+update t1, t2 set k = k + 10 where t1.i = t2.i;
+ERROR 42S22: Unknown column 'at' in 'NEW'
+select * from t1;
+i	k
+1	11
+1	2
+3	3
+update t1, t2 set k = k + 10 where t1.i = t2.i and k < 3;
+ERROR 42S22: Unknown column 'at' in 'NEW'
+select * from t1;
+i	k
+1	11
+1	12
+3	3
+delete t1, t2 from t1 straight_join t2 where t1.i = t2.i;
+ERROR 42S22: Unknown column 'at' in 'OLD'
+select * from t1;
+i	k
+1	12
+3	3
+delete t2, t1 from t2 straight_join t1 where t1.i = t2.i;
+ERROR 42S22: Unknown column 'at' in 'OLD'
+select * from t1;
+i	k
+3	3
+alter table t1 add primary key (i);
+insert into t1 values (3, 4) on duplicate key update k= k + 10;
+ERROR 42S22: Unknown column 'at' in 'NEW'
+select * from t1;
+i	k
+3	13
+replace into t1 values (3, 3);
+ERROR 42S22: Unknown column 'at' in 'NEW'
+select * from t1;
+i	k
+3	3
+alter table t1 add ts timestamp default now();
+replace into t1 (i, k) values (3, 13);
+ERROR 42S22: Unknown column 'at' in 'OLD'
+select * from t1;
+i	k	ts
+drop table t1, t2;
+create table t1 (i int, bt int, k int, key(k)) engine=myisam;
+create table t2 (i int);
+insert into t1 values (1, 1, 1), (2, 2, 2);
+insert into t2 values (1), (2), (3);
+create trigger bi before insert on t1 for each row set @a:= new.bt;
+create trigger bu before update on t1 for each row set @a:= new.bt;
+create trigger bd before delete on t1 for each row set @a:= old.bt;
+alter table t1 drop column bt;
+insert into t1 values (3, 3);
+ERROR 42S22: Unknown column 'bt' in 'NEW'
+select * from t1;
+i	k
+1	1
+2	2
+update t1 set i = 2;
+ERROR 42S22: Unknown column 'bt' in 'NEW'
+select * from t1;
+i	k
+1	1
+2	2
+delete from t1;
+ERROR 42S22: Unknown column 'bt' in 'OLD'
+select * from t1;
+i	k
+1	1
+2	2
+load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by ''
enclosed by '' (i, k);
+ERROR 42S22: Unknown column 'bt' in 'NEW'
+select * from t1;
+i	k
+1	1
+2	2
+insert into t1 select 3, 3;
+ERROR 42S22: Unknown column 'bt' in 'NEW'
+select * from t1;
+i	k
+1	1
+2	2
+update t1, t2 set k = k + 10 where t1.i = t2.i;
+ERROR 42S22: Unknown column 'bt' in 'NEW'
+select * from t1;
+i	k
+1	1
+2	2
+update t1, t2 set k = k + 10 where t1.i = t2.i and k < 2;
+ERROR 42S22: Unknown column 'bt' in 'NEW'
+select * from t1;
+i	k
+1	1
+2	2
+delete t1, t2 from t1 straight_join t2 where t1.i = t2.i;
+ERROR 42S22: Unknown column 'bt' in 'OLD'
+select * from t1;
+i	k
+1	1
+2	2
+delete t2, t1 from t2 straight_join t1 where t1.i = t2.i;
+ERROR 42S22: Unknown column 'bt' in 'OLD'
+select * from t1;
+i	k
+1	1
+2	2
+alter table t1 add primary key (i);
+drop trigger t1.bi;
+insert into t1 values (2, 4) on duplicate key update k= k + 10;
+ERROR 42S22: Unknown column 'bt' in 'NEW'
+select * from t1;
+i	k
+1	1
+2	2
+replace into t1 values (2, 4);
+ERROR 42S22: Unknown column 'bt' in 'NEW'
+select * from t1;
+i	k
+1	1
+2	2
+alter table t1 add ts timestamp default now();
+replace into t1 (i, k) values (2, 11);
+ERROR 42S22: Unknown column 'bt' in 'OLD'
+select * from t1;
+i	k	ts
[phone]:00:00
[phone]:00:00
+drop table t1, t2;

--- 1.10/mysql-test/t/trigger.test	Tue May 24 22:19:28 2005
+++ 1.11/mysql-test/t/trigger.test	Mon May 30 18:55:52 2005
@@ -367,3 +367,130 @@
 select *, @b from t1;
 # This also will drop triggers
 drop table t1;
+
+# Test for bug #5894 "Triggers with altered tables cause corrupt databases"
+# Also tests basic error handling for various kinds of triggers.
+create table t1 (i int, at int, k int, key(k)) engine=myisam;
+create table t2 (i int);
+insert into t1 values (1, 1, 1);
+# We need at least 3 elements in t2 to test multi-update properly
+insert into t2 values (1), (2), (3);
+# Create and then break "after" triggers
+create trigger ai after insert on t1 for each row set @a:= new.at;
+create trigger au after update on t1 for each row set @a:= new.at;
+create trigger ad after delete on t1 for each row set @a:= old.at;
+alter table t1 drop column at;
+# We still should be able select data from tables.
+select * from t1;
+# The following statements changing t1 should fail, but still cause
+# their main effect. This is because operation on the table row is
+# executed before "after" trigger and its effect cannot be rolled back
+# when whole statement fails, because t1 is MyISAM table.
+--error 1054
+insert into t1 values (2, 1);
+select * from t1;
+--error 1054
+update t1 set k = 2 where i = 2;
+select * from t1;
+--error 1054
+delete from t1 where i = 2;
+select * from t1;
+# Should fail and insert only 1 row
+--error 1054
+load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by ''
enclosed by '' (i, k);
+select * from t1;
+--error 1054
+insert into t1 select 3, 3;
+select * from t1;
+# Multi-update working on the fly, again it will update only
+# one row even if more matches
+--error 1054
+update t1, t2 set k = k + 10 where t1.i = t2.i;
+select * from t1;
+# The same for multi-update via temp table
+--error 1054
+update t1, t2 set k = k + 10 where t1.i = t2.i and k < 3;
+select * from t1;
+# Multi-delete on the fly
+--error 1054
+delete t1, t2 from t1 straight_join t2 where t1.i = t2.i;
+select * from t1;
+# And via temporary storage
+--error 1054
+delete t2, t1 from t2 straight_join t1 where t1.i = t2.i;
+select * from t1;
+# Prepare table for testing of REPLACE and INSERT ... ON DUPLICATE KEY UPDATE
+alter table t1 add primary key (i);
+--error 1054
+insert into t1 values (3, 4) on duplicate key update k= k + 10;
+select * from t1;
+--error 1054
+replace into t1 values (3, 3);
+select * from t1;
+# Change table in such way that REPLACE will delete row
+alter table t1 add ts timestamp default now();
+--error 1054
+replace into t1 (i, k) values (3, 13);
+select * from t1;
+# Also drops all triggers
+drop table t1, t2;
+
+create table t1 (i int, bt int, k int, key(k)) engine=myisam;
+create table t2 (i int);
+insert into t1 values (1, 1, 1), (2, 2, 2);
+insert into t2 values (1), (2), (3);
+# Create and then break "before" triggers
+create trigger bi before insert on t1 for each row set @a:= new.bt;
+create trigger bu before update on t1 for each row set @a:= new.bt;
+create trigger bd before delete on t1 for each row set @a:= old.bt;
+alter table t1 drop column bt;
+# The following statements changing t1 should fail and should not
+# cause any effect on table, since "before" trigger is executed 
+# before operation on the table row.
+--error 1054
+insert into t1 values (3, 3);
+select * from t1;
+--error 1054
+update t1 set i = 2;
+select * from t1;
+--error 1054
+delete from t1;
+select * from t1;
+--error 1054
+load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by ''
enclosed by '' (i, k);
+select * from t1;
+--error 1054
+insert into t1 select 3, 3;
+select * from t1;
+# Both types of multi-update (on the fly and via temp table)
+--error 1054
+update t1, t2 set k = k + 10 where t1.i = t2.i;
+select * from t1;
+--error 1054
+update t1, t2 set k = k + 10 where t1.i = t2.i and k < 2;
+select * from t1;
+# Both types of multi-delete
+--error 1054
+delete t1, t2 from t1 straight_join t2 where t1.i = t2.i;
+select * from t1;
+--error 1054
+delete t2, t1 from t2 straight_join t1 where t1.i = t2.i;
+select * from t1;
+# Let us test REPLACE/INSERT ... ON DUPLICATE KEY UPDATE.
+# To test properly code-paths different from those that are used
+# in ordinary INSERT we need to drop "before insert" trigger.
+alter table t1 add primary key (i);
+drop trigger t1.bi;
+--error 1054
+insert into t1 values (2, 4) on duplicate key update k= k + 10;
+select * from t1;
+--error 1054
+replace into t1 values (2, 4);
+select * from t1;
+# Change table in such way that REPLACE will delete row
+alter table t1 add ts timestamp default now();
+--error 1054
+replace into t1 (i, k) values (2, 11);
+select * from t1;
+# Also drops all triggers
+drop table t1, t2;