Below is the list of changes that have just been committed into a local
5.0 repository of marcsql. When marcsql 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@stripped, 2006-10-05 13:05:13-07:00, malff@weblab.(none) +7 -0
Bug#23001 ('traditional' mode + triggers/stored functions result in
inconsistent behavior)
As per the definition of SQL_MODE='TRADITIONAL', execution of an INSERT or a
DELETE statement must fail with an error if a warning is raised during the
statement execution.
The code covered by this behavior includes the logic present in INSERT or
UPDATE TRIGGERS, if any.
At the code level, the traditional mode is implemented using
THD::abort_on_warning.
Before this fix, any statement that is sensitive to SQL_MODE='TRADITIONAL'
would set THD::abort_on_warning, execute protected code, and reset
THD::abort_on_warning to 0.
This in fact destroyed the THD::abort_on_warning property for nested
statements, because the code is recursive in nature with triggers.
After this fix, THD::abort_on_warning is restored to it's former value
instead, so that execution of the calling statement can resume with the
correct execution context.
A test case has been added to exibit the bugs, and shows all the statements
that are involved.
LOAD DATA INTO TABLE also affects the THD::abort_on_warning property and
has been fixed for consistency, even if this statement is not allowed
inside a trigger.
mysql-test/r/trigger.result@stripped, 2006-10-05 12:47:14-07:00, malff@weblab.(none) +50 -0
Test cases for Bug#23001
mysql-test/t/trigger.test@stripped, 2006-10-05 12:47:14-07:00, malff@weblab.(none) +73 -0
Test cases for Bug#23001
sql/sql_class.h@stripped, 2006-10-05 12:47:15-07:00, malff@weblab.(none) +3 -0
THD::abort_on_warning is a stack, not a flag.
sql/sql_insert.cc@stripped, 2006-10-05 12:47:15-07:00, malff@weblab.(none) +9 -4
THD::abort_on_warning is a stack, not a flag.
sql/sql_load.cc@stripped, 2006-10-05 12:47:15-07:00, malff@weblab.(none) +3 -1
THD::abort_on_warning is a stack, not a flag.
sql/sql_table.cc@stripped, 2006-10-05 12:47:15-07:00, malff@weblab.(none) +3 -1
THD::abort_on_warning is a stack, not a flag.
sql/sql_update.cc@stripped, 2006-10-05 12:47:15-07:00, malff@weblab.(none) +7 -3
THD::abort_on_warning is a stack, not a flag.
# 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: malff
# Host: weblab.(none)
# Root: /home/marcsql/TREE/mysql-5.0-23001
--- 1.301/sql/sql_class.h 2006-10-05 13:05:19 -07:00
+++ 1.302/sql/sql_class.h 2006-10-05 13:05:19 -07:00
@@ -1807,6 +1807,9 @@ class select_insert :public select_resul
bool send_eof();
/* not implemented: select_insert is never re-used in prepared statements */
void cleanup();
+
+protected:
+ int save_abort_on_warning;
};
--- 1.204/sql/sql_insert.cc 2006-10-05 13:05:19 -07:00
+++ 1.205/sql/sql_insert.cc 2006-10-05 13:05:19 -07:00
@@ -338,6 +338,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
#endif
thr_lock_type lock_type = table_list->lock_type;
Item *unused_conds= 0;
+ int save_abort_on_warning;
DBUG_ENTER("mysql_insert");
/*
@@ -501,6 +502,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
table->file->start_bulk_insert(values_list.elements);
thd->no_trans_update= 0;
+ save_abort_on_warning= thd->abort_on_warning;
thd->abort_on_warning= (!ignore &&
(thd->variables.sql_mode &
(MODE_STRICT_TRANS_TABLES |
@@ -714,7 +716,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
thd->row_count_func= info.copied+info.deleted+info.updated;
::send_ok(thd, (ulong) thd->row_count_func, id, buff);
}
- thd->abort_on_warning= 0;
+ thd->abort_on_warning= save_abort_on_warning;
DBUG_RETURN(FALSE);
abort:
@@ -724,7 +726,7 @@ abort:
#endif
if (!joins_freed)
free_underlaid_joins(thd, &thd->lex->select_lex);
- thd->abort_on_warning= 0;
+ thd->abort_on_warning= save_abort_on_warning;
DBUG_RETURN(TRUE);
}
@@ -2273,7 +2275,8 @@ select_insert::select_insert(TABLE_LIST
bool ignore_check_option_errors)
:table_list(table_list_par), table(table_par), fields(fields_par),
last_insert_id(0),
- insert_into_view(table_list_par && table_list_par->view != 0)
+ insert_into_view(table_list_par && table_list_par->view != 0),
+ save_abort_on_warning(0)
{
bzero((char*) &info,sizeof(info));
info.handle_duplicates= duplic;
@@ -2383,6 +2386,7 @@ select_insert::prepare(List<Item> &value
table->file->extra(HA_EXTRA_RETRIEVE_ALL_COLS);
}
thd->no_trans_update= 0;
+ save_abort_on_warning= thd->abort_on_warning;
thd->abort_on_warning= (!info.ignore &&
(thd->variables.sql_mode &
(MODE_STRICT_TRANS_TABLES |
@@ -2440,7 +2444,7 @@ select_insert::~select_insert()
table->file->reset();
}
thd->count_cuted_fields= CHECK_FIELD_IGNORE;
- thd->abort_on_warning= 0;
+ thd->abort_on_warning= save_abort_on_warning;
DBUG_VOID_RETURN;
}
@@ -2799,6 +2803,7 @@ select_create::prepare(List<Item> &value
if (!thd->prelocked_mode)
table->file->start_bulk_insert((ha_rows) 0);
thd->no_trans_update= 0;
+ save_abort_on_warning= thd->abort_on_warning;
thd->abort_on_warning= (!info.ignore &&
(thd->variables.sql_mode &
(MODE_STRICT_TRANS_TABLES |
--- 1.99/sql/sql_load.cc 2006-10-05 13:05:19 -07:00
+++ 1.100/sql/sql_load.cc 2006-10-05 13:05:19 -07:00
@@ -135,6 +135,7 @@ bool mysql_load(THD *thd,sql_exchange *e
char *tdb= thd->db ? thd->db : db; // Result is never null
ulong skip_lines= ex->skip_lines;
bool transactional_table;
+ int save_abort_on_warning;
DBUG_ENTER("mysql_load");
#ifdef EMBEDDED_LIBRARY
@@ -368,6 +369,7 @@ bool mysql_load(THD *thd,sql_exchange *e
table->copy_blobs=1;
thd->no_trans_update= 0;
+ save_abort_on_warning= thd->abort_on_warning;
thd->abort_on_warning= (!ignore &&
(thd->variables.sql_mode &
(MODE_STRICT_TRANS_TABLES |
@@ -482,7 +484,7 @@ err:
mysql_unlock_tables(thd, thd->lock);
thd->lock=0;
}
- thd->abort_on_warning= 0;
+ thd->abort_on_warning= save_abort_on_warning;
DBUG_RETURN(error);
}
--- 1.322/sql/sql_table.cc 2006-10-05 13:05:19 -07:00
+++ 1.323/sql/sql_table.cc 2006-10-05 13:05:19 -07:00
@@ -3981,6 +3981,7 @@ copy_data_between_tables(TABLE *from,TAB
ha_rows examined_rows;
bool auto_increment_field_copied= 0;
ulong save_sql_mode;
+ int save_abort_on_warning;
DBUG_ENTER("copy_data_between_tables");
/*
@@ -4001,6 +4002,7 @@ copy_data_between_tables(TABLE *from,TAB
/* We can abort alter table for any table type */
thd->no_trans_update= 0;
+ save_abort_on_warning= thd->abort_on_warning;
thd->abort_on_warning= !ignore && test(thd->variables.sql_mode &
(MODE_STRICT_TRANS_TABLES |
MODE_STRICT_ALL_TABLES));
@@ -4129,7 +4131,7 @@ copy_data_between_tables(TABLE *from,TAB
err:
thd->variables.sql_mode= save_sql_mode;
- thd->abort_on_warning= 0;
+ thd->abort_on_warning= save_abort_on_warning;
free_io_cache(from);
*copied= found_count;
*deleted=delete_count;
--- 1.199/sql/sql_update.cc 2006-10-05 13:05:19 -07:00
+++ 1.200/sql/sql_update.cc 2006-10-05 13:05:19 -07:00
@@ -137,6 +137,7 @@ int mysql_update(THD *thd,
READ_RECORD info;
SELECT_LEX *select_lex= &thd->lex->select_lex;
bool need_reopen;
+ int save_abort_on_warning;
DBUG_ENTER("mysql_update");
LINT_INIT(timestamp_query_id);
@@ -429,6 +430,7 @@ int mysql_update(THD *thd,
transactional_table= table->file->has_transactions();
thd->no_trans_update= 0;
+ save_abort_on_warning= thd->abort_on_warning;
thd->abort_on_warning= test(!ignore &&
(thd->variables.sql_mode &
(MODE_STRICT_TRANS_TABLES |
@@ -572,7 +574,7 @@ int mysql_update(THD *thd,
DBUG_PRINT("info",("%d records updated",updated));
}
thd->count_cuted_fields= CHECK_FIELD_IGNORE; /* calc cuted fields */
- thd->abort_on_warning= 0;
+ thd->abort_on_warning= save_abort_on_warning;
free_io_cache(table);
DBUG_RETURN((error >= 0 || thd->net.report_error) ? 1 : 0);
@@ -584,7 +586,7 @@ err:
table->key_read=0;
table->file->extra(HA_EXTRA_NO_KEYREAD);
}
- thd->abort_on_warning= 0;
+ thd->abort_on_warning= save_abort_on_warning;
DBUG_RETURN(1);
}
@@ -901,6 +903,7 @@ bool mysql_multi_update(THD *thd,
SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex)
{
multi_update *result;
+ int save_abort_on_warning;
DBUG_ENTER("mysql_multi_update");
if (!(result= new multi_update(table_list,
@@ -910,6 +913,7 @@ bool mysql_multi_update(THD *thd,
DBUG_RETURN(TRUE);
thd->no_trans_update= 0;
+ save_abort_on_warning= thd->abort_on_warning;
thd->abort_on_warning= test(thd->variables.sql_mode &
(MODE_STRICT_TRANS_TABLES |
MODE_STRICT_ALL_TABLES));
@@ -924,7 +928,7 @@ bool mysql_multi_update(THD *thd,
OPTION_SETUP_TABLES_DONE,
result, unit, select_lex);
delete result;
- thd->abort_on_warning= 0;
+ thd->abort_on_warning= save_abort_on_warning;
DBUG_RETURN(FALSE);
}
--- 1.47/mysql-test/r/trigger.result 2006-10-05 13:05:19 -07:00
+++ 1.48/mysql-test/r/trigger.result 2006-10-05 13:05:19 -07:00
@@ -1185,4 +1185,54 @@ i j
2 2
13 13
drop table t1;
+drop table if exists t1;
+drop table if exists t2;
+drop table if exists t3;
+SET @save_sql_mode=@@sql_mode;
+SET sql_mode='TRADITIONAL'|
+create table t1 (id int(10) not null primary key, v int(10) )|
+create table t2 (id int(10) not null primary key, v int(10) )|
+create table t3 (id int(10) not null primary key, v int(10) )|
+create table t4 (c int)|
+create trigger t4_bi before insert on t4 for each row set @t4_bi_called:=1|
+create trigger t4_bu before update on t4 for each row set @t4_bu_called:=1|
+insert into t1 values(10, 10)|
+set @a:=1/0|
+Warnings:
+Error 1365 Division by 0
+select 1/0 from t1|
+1/0
+NULL
+Warnings:
+Error 1365 Division by 0
+create trigger t1_bi before insert on t1 for each row set @a:=1/0|
+insert into t1 values(20, 20)|
+ERROR 22012: Division by 0
+drop trigger t1_bi|
+create trigger t1_bi before insert on t1 for each row
+begin
+insert into t2 values (new.id, new.v);
+update t2 set v=v+1 where id= new.id;
+replace t3 values (new.id, 0);
+update t2, t3 set t2.v=new.v, t3.v=new.v where t2.id=t3.id;
+create temporary table t5 select * from t1;
+delete from t5;
+insert into t5 select * from t1;
+insert into t4 values (0);
+set @check= (select count(*) from t5);
+update t4 set c= @check;
+drop temporary table t5;
+set @a:=1/0;
+end|
+set @check=0, @t4_bi_called=0, @t4_bu_called=0|
+insert into t1 values(30, 30)|
+ERROR 22012: Division by 0
+select @check, @t4_bi_called, @t4_bu_called|
+@check @t4_bi_called @t4_bu_called
+1 1 1
+SET @@sql_mode=@save_sql_mode;
+drop table t1;
+drop table t2;
+drop table t3;
+drop table t4;
End of 5.0 tests
--- 1.53/mysql-test/t/trigger.test 2006-10-05 13:05:19 -07:00
+++ 1.54/mysql-test/t/trigger.test 2006-10-05 13:05:19 -07:00
@@ -1439,5 +1439,78 @@ update t1 set i= i+ 10 where j > 2;
select * from t1;
drop table t1;
+#
+# Bug#23001 'traditional' mode + triggers/stored functions result in
+# inconsistent behavior
+#
+
+--disable_warnings
+drop table if exists t1;
+drop table if exists t2;
+drop table if exists t3;
+--enable_warnings
+
+SET @save_sql_mode=@@sql_mode;
+
+delimiter |;
+SET sql_mode='TRADITIONAL'|
+create table t1 (id int(10) not null primary key, v int(10) )|
+create table t2 (id int(10) not null primary key, v int(10) )|
+create table t3 (id int(10) not null primary key, v int(10) )|
+create table t4 (c int)|
+
+create trigger t4_bi before insert on t4 for each row set @t4_bi_called:=1|
+create trigger t4_bu before update on t4 for each row set @t4_bu_called:=1|
+
+insert into t1 values(10, 10)|
+set @a:=1/0|
+select 1/0 from t1|
+
+create trigger t1_bi before insert on t1 for each row set @a:=1/0|
+
+-- error ER_DIVISION_BY_ZERO
+insert into t1 values(20, 20)|
+
+# Make sure that the following statements:
+# - INSERT
+# - UPDATE
+# - REPLACE
+# - MULTI-UPDATE
+# - CREATE from SELECT
+# - INSERT from SELECT
+# - nested INSERT trigger
+# - nested UPDATE trigger
+# do not mask the error
+drop trigger t1_bi|
+create trigger t1_bi before insert on t1 for each row
+begin
+ insert into t2 values (new.id, new.v);
+ update t2 set v=v+1 where id= new.id;
+ replace t3 values (new.id, 0);
+ update t2, t3 set t2.v=new.v, t3.v=new.v where t2.id=t3.id;
+ create temporary table t5 select * from t1;
+ delete from t5;
+ insert into t5 select * from t1;
+ insert into t4 values (0);
+ set @check= (select count(*) from t5);
+ update t4 set c= @check;
+ drop temporary table t5;
+
+ set @a:=1/0;
+end|
+
+set @check=0, @t4_bi_called=0, @t4_bu_called=0|
+-- error ER_DIVISION_BY_ZERO
+insert into t1 values(30, 30)|
+select @check, @t4_bi_called, @t4_bu_called|
+
+delimiter ;|
+
+SET @@sql_mode=@save_sql_mode;
+
+drop table t1;
+drop table t2;
+drop table t3;
+drop table t4;
--echo End of 5.0 tests
| Thread |
|---|
| • bk commit into 5.0 tree (malff:1.2275) BUG#23001 | marc.alff | 5 Oct |
