diff options
| -rw-r--r-- | mysql-test/r/not_embedded_server.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/sp-dynamic.result | 364 | ||||
| -rw-r--r-- | mysql-test/r/sp-error.result | 16 | ||||
| -rw-r--r-- | mysql-test/r/trigger.result | 2 | ||||
| -rw-r--r-- | mysql-test/t/sp-dynamic.test | 335 | ||||
| -rw-r--r-- | mysql-test/t/sp-error.test | 40 | ||||
| -rw-r--r-- | mysql-test/t/trigger.test | 2 | ||||
| -rw-r--r-- | sql/item_func.cc | 16 | ||||
| -rw-r--r-- | sql/log_event.h | 3 | ||||
| -rw-r--r-- | sql/mysql_priv.h | 17 | ||||
| -rw-r--r-- | sql/share/errmsg.txt | 2 | ||||
| -rw-r--r-- | sql/sp_head.cc | 75 | ||||
| -rw-r--r-- | sql/sp_head.h | 33 | ||||
| -rw-r--r-- | sql/sql_class.cc | 17 | ||||
| -rw-r--r-- | sql/sql_lex.cc | 7 | ||||
| -rw-r--r-- | sql/sql_lex.h | 5 | ||||
| -rw-r--r-- | sql/sql_parse.cc | 135 | ||||
| -rw-r--r-- | sql/sql_prepare.cc | 1253 | ||||
| -rw-r--r-- | sql/sql_yacc.yy | 55 |
19 files changed, 1679 insertions, 700 deletions
diff --git a/mysql-test/r/not_embedded_server.result b/mysql-test/r/not_embedded_server.result index 082ebe72ba4..e471b5a3afa 100644 --- a/mysql-test/r/not_embedded_server.result +++ b/mysql-test/r/not_embedded_server.result @@ -1,5 +1,5 @@ prepare stmt1 from ' show full processlist '; execute stmt1; Id User Host db Command Time State Info -number root localhost test Execute time NULL show full processlist +number root localhost test Query time NULL show full processlist deallocate prepare stmt1; diff --git a/mysql-test/r/sp-dynamic.result b/mysql-test/r/sp-dynamic.result new file mode 100644 index 00000000000..8fe469431cc --- /dev/null +++ b/mysql-test/r/sp-dynamic.result @@ -0,0 +1,364 @@ +create procedure p1() +begin +prepare stmt from "select 1"; +execute stmt; +execute stmt; +execute stmt; +deallocate prepare stmt; +end| +call p1()| +1 +1 +1 +1 +1 +1 +call p1()| +1 +1 +1 +1 +1 +1 +call p1()| +1 +1 +1 +1 +1 +1 +drop procedure p1| +create procedure p1() +begin +execute stmt; +end| +prepare stmt from "call p1()"| +execute stmt| +ERROR HY000: The prepared statement contains a stored routine call that refers to that same statement. It's not allowed to execute a prepared statement in such a recursive manner +execute stmt| +ERROR HY000: The prepared statement contains a stored routine call that refers to that same statement. It's not allowed to execute a prepared statement in such a recursive manner +execute stmt| +ERROR HY000: The prepared statement contains a stored routine call that refers to that same statement. It's not allowed to execute a prepared statement in such a recursive manner +call p1()| +ERROR HY000: Recursive stored routines are not allowed. +call p1()| +ERROR HY000: Recursive stored routines are not allowed. +call p1()| +ERROR HY000: Recursive stored routines are not allowed. +drop procedure p1| +create procedure p1() +begin +prepare stmt from "create procedure p2() begin select 1; end"; +execute stmt; +deallocate prepare stmt; +end| +call p1()| +ERROR HY000: This command is not supported in the prepared statement protocol yet +call p1()| +ERROR HY000: This command is not supported in the prepared statement protocol yet +drop procedure p1| +create procedure p1() +begin +prepare stmt from "drop procedure p2"; +execute stmt; +deallocate prepare stmt; +end| +call p1()| +ERROR HY000: This command is not supported in the prepared statement protocol yet +call p1()| +ERROR HY000: This command is not supported in the prepared statement protocol yet +drop procedure p1| +create procedure p1() +begin +prepare stmt_drop from "drop table if exists t1"; +execute stmt_drop; +prepare stmt from "create table t1 (a int)"; +execute stmt; +insert into t1 (a) values (1); +select * from t1; +deallocate prepare stmt; +deallocate prepare stmt_drop; +end| +call p1()| +a +1 +Warnings: +Note 1051 Unknown table 't1' +call p1()| +a +1 +drop procedure p1| +create procedure p1() +begin +set @tab_name=concat("tab_", replace(curdate(), '-', '_')); +set @drop_sql=concat("drop table if exists ", @tab_name); +set @create_sql=concat("create table ", @tab_name, " (a int)"); +set @insert_sql=concat("insert into ", @tab_name, " values (1), (2), (3)"); +set @select_sql=concat("select * from ", @tab_name); +select @tab_name; +select @drop_sql; +select @create_sql; +select @insert_sql; +select @select_sql; +prepare stmt_drop from @drop_sql; +execute stmt_drop; +prepare stmt from @create_sql; +execute stmt; +prepare stmt from @insert_sql; +execute stmt; +prepare stmt from @select_sql; +execute stmt; +execute stmt_drop; +deallocate prepare stmt; +deallocate prepare stmt_drop; +end| +call p1()| +call p1()| +drop procedure p1| +create procedure p1() +begin +prepare stmt_drop from "drop table if exists t1"; +execute stmt_drop; +prepare stmt from "create table t1 (a int)"; +execute stmt; +deallocate prepare stmt; +deallocate prepare stmt_drop; +end| +drop function if exists f1| +create function f1(a int) returns int +begin +call p1(); +return 1; +end| +select f1(0)| +ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger +select f1(f1(0))| +ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger +select f1(f1(f1(0)))| +ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger +drop function f1| +drop procedure p1| +create procedure p1() +begin +drop table if exists t1; +create table t1 (id integer not null primary key, +name varchar(20) not null); +insert into t1 (id, name) values (1, 'aaa'), (2, 'bbb'), (3, 'ccc'); +prepare stmt from "select name from t1"; +execute stmt; +select name from t1; +execute stmt; +prepare stmt from +"select name from t1 where name=(select name from t1 where id=2)"; +execute stmt; +select name from t1 where name=(select name from t1 where id=2); +execute stmt; +end| +call p1()| +name +aaa +bbb +ccc +name +aaa +bbb +ccc +name +aaa +bbb +ccc +name +bbb +name +bbb +name +bbb +call p1()| +name +aaa +bbb +ccc +name +aaa +bbb +ccc +name +aaa +bbb +ccc +name +bbb +name +bbb +name +bbb +drop procedure p1| +prepare stmt from "select * from t1"| +create procedure p1() +begin +execute stmt; +deallocate prepare stmt; +end| +call p1()| +id name +1 aaa +2 bbb +3 ccc +call p1()| +ERROR HY000: Unknown prepared statement handler (stmt) given to EXECUTE +drop procedure p1| +create procedure p1() +begin +declare a char(10); +set a="sp-variable"; +set @a="mysql-variable"; +prepare stmt from "select 'dynamic sql:', @a, a"; +execute stmt; +end| +call p1()| +ERROR 42S22: Unknown column 'a' in 'field list' +call p1()| +ERROR 42S22: Unknown column 'a' in 'field list' +drop procedure p1| +create procedure p1() +begin +prepare stmt from 'select ? as a'; +execute stmt using @a; +end| +set @a=1| +call p1()| +a +1 +call p1()| +a +1 +drop procedure p1| +drop table if exists t1| +create table t1 (id integer primary key auto_increment, +stmt_text char(35), status varchar(20))| +insert into t1 (stmt_text) values +("select 1"), ("flush tables"), ("handler t1 open as ha"), +("analyze table t1"), ("check table t1"), ("checksum table t1"), +("check table t1"), ("optimize table t1"), ("repair table t1"), +("describe extended select * from t1"), +("help help"), ("show databases"), ("show tables"), +("show table status"), ("show open tables"), ("show storage engines"), +("insert into t1 (id) values (1)"), ("update t1 set status=''"), +("delete from t1"), ("truncate t1"), ("call p1()"), ("foo bar")| +create procedure p1() +begin +declare v_stmt_text varchar(255); +declare v_id integer; +declare done int default 0; +declare c cursor for select id, stmt_text from t1; +declare continue handler for 1295 -- ER_UNSUPPORTED_PS +set @status='not supported'; +declare continue handler for 1064 -- ER_SYNTAX_ERROR +set @status='syntax error'; +declare continue handler for sqlstate '02000' set done = 1; +prepare update_stmt from "update t1 set status=? where id=?"; +open c; +repeat +if not done then +fetch c into v_id, v_stmt_text; +set @id=v_id, @stmt_text=v_stmt_text; +set @status="supported"; +prepare stmt from @stmt_text; +execute update_stmt using @status, @id; +end if; +until done end repeat; +deallocate prepare update_stmt; +end| +call p1()| +select * from t1| +id stmt_text status +1 select 1 supported +2 flush tables not supported +3 handler t1 open as ha not supported +4 analyze table t1 not supported +5 check table t1 not supported +6 checksum table t1 not supported +7 check table t1 not supported +8 optimize table t1 not supported +9 repair table t1 not supported +10 describe extended select * from t1 supported +11 help help not supported +12 show databases supported +13 show tables supported +14 show table status supported +15 show open tables supported +16 show storage engines supported +17 insert into t1 (id) values (1) supported +18 update t1 set status='' supported +19 delete from t1 supported +20 truncate t1 supported +21 call p1() supported +22 foo bar syntax error +drop procedure p1| +drop table t1| +prepare stmt from 'select 1'| +create procedure p1() execute stmt| +call p1()| +1 +1 +call p1()| +1 +1 +drop procedure p1| +create function f1() returns int +begin +deallocate prepare stmt; +return 1; +end| +ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger +create procedure p1() +begin +prepare stmt from 'select 1 A'; +execute stmt; +end| +prepare stmt from 'call p1()'| +execute stmt| +ERROR HY000: The prepared statement contains a stored routine call that refers to that same statement. It's not allowed to execute a prepared statement in such a recursive manner +execute stmt| +ERROR HY000: The prepared statement contains a stored routine call that refers to that same statement. It's not allowed to execute a prepared statement in such a recursive manner +drop procedure p1| +drop table if exists t1, t2| +create procedure p1 (a int) language sql deterministic +begin +declare rsql varchar(100); +drop table if exists t1, t2; +set @rsql= "create table t1 (a int)"; +select @rsql; +prepare pst from @rsql; +execute pst; +set @rsql= null; +set @rsql= "create table t2 (a int)"; +select @rsql; +prepare pst from @rsql; +execute pst; +drop table if exists t1, t2; +end| +set @a:=0| +call p1(@a)| +@rsql +create table t1 (a int) +@rsql +create table t2 (a int) +Warnings: +Note 1051 Unknown table 't1' +Note 1051 Unknown table 't2' +select @a| +@a +0 +call p1(@a)| +@rsql +create table t1 (a int) +@rsql +create table t2 (a int) +Warnings: +Note 1051 Unknown table 't1' +Note 1051 Unknown table 't2' +select @a| +@a +0 +drop procedure if exists p1| diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index 23644f57353..61e931e146c 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -618,7 +618,7 @@ select * from t1| call bug8408_p()| val x select bug8408_f()| -ERROR 0A000: PROCEDURE test.bug8408_p can't return a result set in the given context +ERROR 0A000: Not allowed to return a result set from a function drop procedure bug8408_p| drop function bug8408_f| create function bug8408() returns int @@ -665,20 +665,6 @@ select default(t30.s1) from t30; end| drop procedure bug10969| drop table t1| -prepare stmt from "select 1"; -create procedure p() deallocate prepare stmt; -ERROR 0A000: DEALLOCATE is not allowed in stored procedures -create function f() returns int begin deallocate prepare stmt; -ERROR 0A000: DEALLOCATE is not allowed in stored procedures -create procedure p() prepare stmt from "select 1"; -ERROR 0A000: PREPARE is not allowed in stored procedures -create function f() returns int begin prepare stmt from "select 1"; -ERROR 0A000: PREPARE is not allowed in stored procedures -create procedure p() execute stmt; -ERROR 0A000: EXECUTE is not allowed in stored procedures -create function f() returns int begin execute stmt; -ERROR 0A000: EXECUTE is not allowed in stored procedures -deallocate prepare stmt; create table t1(f1 int); create table t2(f1 int); CREATE PROCEDURE SP001() diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 312a7a90fc9..b305691fa18 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -689,7 +689,7 @@ call bug11587(); set new.c2= '2004-04-02'; end| insert into t1 (c1) values (4),(5),(6); -ERROR 0A000: PROCEDURE test.bug11587 can't return a result set in the given context +ERROR 0A000: Not allowed to return a result set from a trigger select * from t1; c1 c2 1 NULL diff --git a/mysql-test/t/sp-dynamic.test b/mysql-test/t/sp-dynamic.test new file mode 100644 index 00000000000..e9816ee3ef0 --- /dev/null +++ b/mysql-test/t/sp-dynamic.test @@ -0,0 +1,335 @@ +delimiter |; +###################################################################### +# Test Dynamic SQL in stored procedures. ############################# +###################################################################### +# +# A. Basics +# +create procedure p1() +begin + prepare stmt from "select 1"; + execute stmt; + execute stmt; + execute stmt; + deallocate prepare stmt; +end| +call p1()| +call p1()| +call p1()| +drop procedure p1| +# +# B. Recursion. Recusion is disabled in SP, and recursive use of PS is not +# possible as well. +# +create procedure p1() +begin + execute stmt; +end| +prepare stmt from "call p1()"| +--error ER_PS_NO_RECURSION +execute stmt| +--error ER_PS_NO_RECURSION +execute stmt| +--error ER_PS_NO_RECURSION +execute stmt| +--error ER_SP_NO_RECURSION +call p1()| +--error ER_SP_NO_RECURSION +call p1()| +--error ER_SP_NO_RECURSION +call p1()| +drop procedure p1| +# +# C. Create/drop a stored procedure in Dynamic SQL. +# One cannot create stored procedure from a stored procedure because of +# the way MySQL SP cache works: it's important that this limitation is not +# possible to circumvent by means of Dynamic SQL. +# +create procedure p1() +begin + prepare stmt from "create procedure p2() begin select 1; end"; + execute stmt; + deallocate prepare stmt; +end| +--error ER_UNSUPPORTED_PS +call p1()| +--error ER_UNSUPPORTED_PS +call p1()| +drop procedure p1| +create procedure p1() +begin + prepare stmt from "drop procedure p2"; + execute stmt; + deallocate prepare stmt; +end| +--error ER_UNSUPPORTED_PS +call p1()| +--error ER_UNSUPPORTED_PS +call p1()| +drop procedure p1| +# +# D. Create/Drop a table (a DDL that issues a commit) in Dynamic SQL. +# (should work ok). +# +create procedure p1() +begin + prepare stmt_drop from "drop table if exists t1"; + execute stmt_drop; + prepare stmt from "create table t1 (a int)"; + execute stmt; + insert into t1 (a) values (1); + select * from t1; + deallocate prepare stmt; + deallocate prepare stmt_drop; +end| +call p1()| +call p1()| +drop procedure p1| +# +# A more real example (a case similar to submitted by 24/7). +# +create procedure p1() +begin + set @tab_name=concat("tab_", replace(curdate(), '-', '_')); + set @drop_sql=concat("drop table if exists ", @tab_name); + set @create_sql=concat("create table ", @tab_name, " (a int)"); + set @insert_sql=concat("insert into ", @tab_name, " values (1), (2), (3)"); + set @select_sql=concat("select * from ", @tab_name); + select @tab_name; + select @drop_sql; + select @create_sql; + select @insert_sql; + select @select_sql; + prepare stmt_drop from @drop_sql; + execute stmt_drop; + prepare stmt from @create_sql; + execute stmt; + prepare stmt from @insert_sql; + execute stmt; + prepare stmt from @select_sql; + execute stmt; + execute stmt_drop; + deallocate prepare stmt; + deallocate prepare stmt_drop; +end| +--disable_result_log +call p1()| +call p1()| +--enable_result_log +drop procedure p1| +# +# E. Calling a stored procedure with Dynamic SQL +# from a stored function (currently disabled). +# +create procedure p1() +begin + prepare stmt_drop from "drop table if exists t1"; + execute stmt_drop; + prepare stmt from "create table t1 (a int)"; + execute stmt; + deallocate prepare stmt; + deallocate prepare stmt_drop; +end| +--disable_warnings +drop function if exists f1| +--enable_warnings +create function f1(a int) returns int +begin + call p1(); + return 1; +end| + +# Every stored procedure that contains Dynamic SQL is marked as +# such. Stored procedures that contain Dynamic SQL are not +# allowed in a stored function or trigger, and here we get the +# corresponding error message. + +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +select f1(0)| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +select f1(f1(0))| +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +select f1(f1(f1(0)))| +drop function f1| +drop procedure p1| +# +# F. Rollback and cleanup lists management in Dynamic SQL. +# +create procedure p1() +begin + drop table if exists t1; + create table t1 (id integer not null primary key, + name varchar(20) not null); + insert into t1 (id, name) values (1, 'aaa'), (2, 'bbb'), (3, 'ccc'); + prepare stmt from "select name from t1"; + execute stmt; + select name from t1; + execute stmt; + prepare stmt from + "select name from t1 where name=(select name from t1 where id=2)"; + execute stmt; + select name from t1 where name=(select name from t1 where id=2); + execute stmt; +end| +call p1()| +call p1()| +drop procedure p1| +# +# H. Executing a statement prepared externally in SP. +# +prepare stmt from "select * from t1"| +create procedure p1() +begin + execute stmt; + deallocate prepare stmt; +end| +call p1()| +--error ER_UNKNOWN_STMT_HANDLER +call p1()| +drop procedure p1| +# +# I. Use of an SP variable in Dynamic SQL is not possible and +# this limitation is necessary for correct binary logging: prepared +# statements do not substitute SP variables with their values for binlog, so +# SP variables must be not accessible in Dynamic SQL. +# +create procedure p1() +begin + declare a char(10); + set a="sp-variable"; + set @a="mysql-variable"; + prepare stmt from "select 'dynamic sql:', @a, a"; + execute stmt; +end| +--error ER_BAD_FIELD_ERROR +call p1()| +--error ER_BAD_FIELD_ERROR +call p1()| +drop procedure p1| +# +# J. Use of placeholders in Dynamic SQL. +# +create procedure p1() +begin + prepare stmt from 'select ? as a'; + execute stmt using @a; +end| +set @a=1| +call p1()| +call p1()| +drop procedure p1| +# +# K. Use of continue handlers with Dynamic SQL. +# +drop table if exists t1| +create table t1 (id integer primary key auto_increment, + stmt_text char(35), status varchar(20))| +insert into t1 (stmt_text) values + ("select 1"), ("flush tables"), ("handler t1 open as ha"), + ("analyze table t1"), ("check table t1"), ("checksum table t1"), + ("check table t1"), ("optimize table t1"), ("repair table t1"), + ("describe extended select * from t1"), + ("help help"), ("show databases"), ("show tables"), + ("show table status"), ("show open tables"), ("show storage engines"), + ("insert into t1 (id) values (1)"), ("update t1 set status=''"), + ("delete from t1"), ("truncate t1"), ("call p1()"), ("foo bar")| +create procedure p1() +begin + declare v_stmt_text varchar(255); + declare v_id integer; + declare done int default 0; + declare c cursor for select id, stmt_text from t1; + declare continue handler for 1295 -- ER_UNSUPPORTED_PS + set @status='not supported'; + declare continue handler for 1064 -- ER_SYNTAX_ERROR + set @status='syntax error'; + declare continue handler for sqlstate '02000' set done = 1; + + prepare update_stmt from "update t1 set status=? where id=?"; + open c; + repeat + if not done then + fetch c into v_id, v_stmt_text; + set @id=v_id, @stmt_text=v_stmt_text; + set @status="supported"; + prepare stmt from @stmt_text; + execute update_stmt using @status, @id; + end if; + until done end repeat; + deallocate prepare update_stmt; +end| +call p1()| +select * from t1| +drop procedure p1| +drop table t1| +# +# Bug#7115 "Prepared Statements: packet error if execution within stored +# procedure". +# +prepare stmt from 'select 1'| +create procedure p1() execute stmt| +call p1()| +call p1()| +drop procedure p1| +# +# Bug#10975 "Prepared statements: crash if function deallocates" +# Check that a prepared statement that is currently in use +# can't be deallocated. +# +# a) Prepared statements and stored procedure cache: +# +# TODO: add when the corresponding bug (Bug #12093 "SP not found on second +# PS execution if another thread drops other SP in between") is fixed. +# +# b) attempt to deallocate a prepared statement that is being executed +--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG +create function f1() returns int +begin + deallocate prepare stmt; + return 1; +end| + +# b)-2 a crash (#1) spotted by Sergey Petrunia during code review +create procedure p1() +begin + prepare stmt from 'select 1 A'; + execute stmt; +end| +prepare stmt from 'call p1()'| +--error ER_PS_NO_RECURSION +execute stmt| +--error ER_PS_NO_RECURSION +execute stmt| +drop procedure p1| + +# +# Bug#10605 "Stored procedure with multiple SQL prepared statements +# disconnects client" +# +--disable_warnings +drop table if exists t1, t2| +--enable_warnings +create procedure p1 (a int) language sql deterministic +begin + declare rsql varchar(100); + drop table if exists t1, t2; + set @rsql= "create table t1 (a int)"; + select @rsql; + prepare pst from @rsql; + execute pst; + set @rsql= null; + set @rsql= "create table t2 (a int)"; + select @rsql; + prepare pst from @rsql; + execute pst; + drop table if exists t1, t2; +end| +set @a:=0| +call p1(@a)| +select @a| +call p1(@a)| +select @a| +drop procedure if exists p1| + +# End of the test +delimiter ;| diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index 5921d59b284..e289748ba2f 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -875,7 +875,7 @@ create procedure bug8408_p() select * from t1| call bug8408_p()| ---error ER_SP_BADSELECT +--error ER_SP_NO_RETSET select bug8408_f()| drop procedure bug8408_p| @@ -956,39 +956,10 @@ end| drop procedure bug10969| -# -# BUG#NNNN: New bug synopsis -# -#--disable_warnings -#drop procedure if exists bugNNNN| -#--enable_warnings -#create procedure bugNNNN... - - drop table t1| delimiter ;| -# -# Bug#10975, #10605, #7115: Dynamic SQL by means of -# PREPARE/EXECUTE/DEALLOCATE is not supported yet. -# Check that an error message is returned. -# -prepare stmt from "select 1"; ---error ER_SP_BADSTATEMENT -create procedure p() deallocate prepare stmt; ---error ER_SP_BADSTATEMENT -create function f() returns int begin deallocate prepare stmt; ---error ER_SP_BADSTATEMENT -create procedure p() prepare stmt from "select 1"; ---error ER_SP_BADSTATEMENT -create function f() returns int begin prepare stmt from "select 1"; ---error ER_SP_BADSTATEMENT -create procedure p() execute stmt; ---error ER_SP_BADSTATEMENT -create function f() returns int begin execute stmt; -deallocate prepare stmt; - # BUG#9814: Closing a cursor that is not open create table t1(f1 int); create table t2(f1 int); @@ -1114,3 +1085,12 @@ drop function bug11834_1; execute stmt; deallocate prepare stmt; drop function bug11834_2; +# +# BUG#NNNN: New bug synopsis +# +#--disable_warnings +#drop procedure if exists bugNNNN| +#--enable_warnings +#create procedure bugNNNN... + + diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 9920f203c94..a770782e5db 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -723,7 +723,7 @@ begin end| delimiter ;| ---error 1312 +--error ER_SP_NO_RETSET insert into t1 (c1) values (4),(5),(6); select * from t1; diff --git a/sql/item_func.cc b/sql/item_func.cc index 00ce70f6508..22d9fbbad34 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -3834,21 +3834,21 @@ longlong Item_func_get_user_var::val_int() stores this variable and its value in thd->user_var_events, so that it can be written to the binlog (will be written just before the query is written, see log.cc). - + RETURN - 0 OK + 0 OK 1 Failed to put appropriate record into binary log - + */ -int get_var_with_binlog(THD *thd, LEX_STRING &name, - user_var_entry **out_entry) +int get_var_with_binlog(THD *thd, enum_sql_command sql_command, + LEX_STRING &name, user_var_entry **out_entry) { BINLOG_USER_VAR_EVENT *user_var_event; user_var_entry *var_entry; var_entry= get_variable(&thd->user_vars, name, 0); - - if (!(opt_bin_log && is_update_query(thd->lex->sql_command))) + + if (!(opt_bin_log && is_update_query(sql_command))) { *out_entry= var_entry; return 0; @@ -3941,7 +3941,7 @@ void Item_func_get_user_var::fix_length_and_dec() decimals=NOT_FIXED_DEC; max_length=MAX_BLOB_WIDTH; - error= get_var_with_binlog(thd, name, &var_entry); + error= get_var_with_binlog(thd, thd->lex->sql_command, name, &var_entry); if (var_entry) { diff --git a/sql/log_event.h b/sql/log_event.h index 1d8941e65ac..29580589a34 100644 --- a/sql/log_event.h +++ b/sql/log_event.h @@ -1177,9 +1177,6 @@ class Xid_log_event: public Log_event Every time a query uses the value of a user variable, a User_var_log_event is written before the Query_log_event, to set the user variable. - Every time a query uses the value of a user variable, a User_var_log_event is - written before the Query_log_event, to set the user variable. - ****************************************************************************/ class User_var_log_event: public Log_event diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 1ab753e0938..154909a275d 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -589,7 +589,7 @@ bool mysql_change_db(THD *thd,const char *name,bool no_access_check); void mysql_parse(THD *thd,char *inBuf,uint length); bool mysql_test_parse_for_slave(THD *thd,char *inBuf,uint length); bool is_update_query(enum enum_sql_command command); -bool alloc_query(THD *thd, char *packet, ulong packet_length); +bool alloc_query(THD *thd, const char *packet, uint packet_length); void mysql_init_select(LEX *lex); void mysql_reset_thd_for_next_command(THD *thd); void mysql_init_query(THD *thd, uchar *buf, uint length); @@ -848,16 +848,17 @@ int fill_schema_column_privileges(THD *thd, TABLE_LIST *tables, COND *cond); bool get_schema_tables_result(JOIN *join); /* sql_prepare.cc */ -bool mysql_stmt_prepare(THD *thd, char *packet, uint packet_length, - LEX_STRING *name); + +void mysql_stmt_prepare(THD *thd, const char *packet, uint packet_length); void mysql_stmt_execute(THD *thd, char *packet, uint packet_length); -void mysql_sql_stmt_execute(THD *thd, LEX_STRING *stmt_name); -void mysql_stmt_fetch(THD *thd, char *packet, uint packet_length); void mysql_stmt_close(THD *thd, char *packet); +void mysql_sql_stmt_prepare(THD *thd); +void mysql_sql_stmt_execute(THD *thd); +void mysql_sql_stmt_close(THD *thd); +void mysql_stmt_fetch(THD *thd, char *packet, uint packet_length); void mysql_stmt_reset(THD *thd, char *packet); void mysql_stmt_get_longdata(THD *thd, char *pos, ulong packet_length); void reinit_stmt_before_use(THD *thd, LEX *lex); -void init_stmt_after_parse(THD*, LEX*); /* sql_handler.cc */ bool mysql_ha_open(THD *thd, TABLE_LIST *tables, bool reopen); @@ -1361,8 +1362,8 @@ extern int sql_cache_hit(THD *thd, char *inBuf, uint length); /* item_func.cc */ Item *get_system_var(THD *thd, enum_var_type var_type, LEX_STRING name, LEX_STRING component); -int get_var_with_binlog(THD *thd, LEX_STRING &name, - user_var_entry **out_entry); +int get_var_with_binlog(THD *thd, enum_sql_command sql_command, + LEX_STRING &name, user_var_entry **out_entry); /* log.cc */ bool flush_error_log(void); diff --git a/sql/share/errmsg.txt b/sql/share/errmsg.txt index b49b7248021..89ffac33055 100644 --- a/sql/share/errmsg.txt +++ b/sql/share/errmsg.txt @@ -5399,3 +5399,5 @@ ER_DATETIME_FUNCTION_OVERFLOW 22008 eng "Datetime function: %-.32s field overflow" ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG eng "Can't update table '%-.64s' in stored function/trigger because it is already used by statement which invoked this stored function/trigger." +ER_PS_NO_RECURSION + eng "The prepared statement contains a stored routine call that refers to that same statement. It's not allowed to execute a prepared statement in such a recursive manner" diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 59169f45f1b..14956138cbf 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -47,15 +47,30 @@ sp_map_result_type(enum enum_field_types type) } /* - * Returns TRUE if the 'cmd' is a command that might result in - * multiple result sets being sent back. - * Note: This does not include SQLCOM_SELECT which is treated - * separately in sql_yacc.yy. - */ -bool -sp_multi_results_command(enum enum_sql_command cmd) + SYNOPSIS + sp_get_flags_for_command() + + DESCRIPTION + Returns a combination of: + * sp_head::MULTI_RESULTS: added if the 'cmd' is a command that might + result in multiple result sets being sent back. + * sp_head::CONTAINS_DYNAMIC_SQL: added if 'cmd' is one of PREPARE, + EXECUTE, DEALLOCATE. +*/ + +uint +sp_get_flags_for_command(LEX *lex) { - switch (cmd) { + uint flags; + + switch (lex->sql_command) { + case SQLCOM_SELECT: + if (lex->result) + { + flags= 0; /* This is a SELECT with INTO clause */ + break; + } + /* fallthrough */ case SQLCOM_ANALYZE: case SQLCOM_CHECKSUM: case SQLCOM_HA_READ: @@ -90,10 +105,26 @@ sp_multi_results_command(enum enum_sql_command cmd) case SQLCOM_SHOW_TABLES: case SQLCOM_SHOW_VARIABLES: case SQLCOM_SHOW_WARNS: - return TRUE; + flags= sp_head::MULTI_RESULTS; + break; + /* + EXECUTE statement may return a result set, but doesn't have to. + We can't, however, know it in advance, and therefore must add + this statement here. This is ok, as is equivalent to a result-set + statement within an IF condition. + */ + case SQLCOM_EXECUTE: + flags= sp_head::MULTI_RESULTS | sp_head::CONTAINS_DYNAMIC_SQL; + break; + case SQLCOM_PREPARE: + case SQLCOM_DEALLOCATE_PREPARE: + flags= sp_head::CONTAINS_DYNAMIC_SQL; + break; default: - return FALSE; + flags= 0; + break; } + return flags; } @@ -364,9 +395,7 @@ sp_head::operator delete(void *ptr, size_t size) sp_head::sp_head() :Query_arena(&main_mem_root, INITIALIZED_FOR_SP), - m_returns_cs(NULL), m_has_return(FALSE), - m_simple_case(FALSE), m_multi_results(FALSE), m_in_handler(FALSE), - m_is_invoked(FALSE) + m_flags(0), m_returns_cs(NULL) { extern byte * sp_table_key(const byte *ptr, uint *plen, my_bool first); @@ -782,7 +811,7 @@ int sp_head::execute(THD *thd) DBUG_RETURN(-1); } - if (m_is_invoked) + if (m_flags & IS_INVOKED) { /* We have to disable recursion for stored routines since in @@ -802,7 +831,7 @@ int sp_head::execute(THD *thd) my_error(ER_SP_NO_RECURSION, MYF(0)); DBUG_RETURN(-1); } - m_is_invoked= TRUE; + m_flags|= IS_INVOKED; dbchanged= FALSE; if (m_db.length && @@ -889,6 +918,15 @@ int sp_head::execute(THD *thd) /* we should cleanup free_list and memroot, used by instruction */ thd->free_items(); + /* + FIXME: we must free user var events only if the routine is executed + in non-prelocked mode and statement-by-statement replication is used. + But if we don't free them now, the server crashes because user var + events are allocated in execute_mem_root. This is Bug#12637, and when + it's fixed, please add if (thd->options & OPTION_BIN_LOG) here. + */ + if (opt_bin_log) + reset_dynamic(&thd->user_var_events); free_root(&execute_mem_root, MYF(0)); /* @@ -955,7 +993,7 @@ int sp_head::execute(THD *thd) if (! thd->killed) ret= mysql_change_db(thd, olddb, 0); } - m_is_invoked= FALSE; + m_flags&= ~IS_INVOKED; DBUG_RETURN(ret); } @@ -1172,7 +1210,7 @@ int sp_head::execute_procedure(THD *thd, List<Item> *args) thd->spcont= save_spcont; DBUG_RETURN(-1); } - + if (csize > 0 || hmax > 0 || cmax > 0) { Item_null *nit= NULL; // Re-use this, and only create if needed @@ -1349,7 +1387,7 @@ int sp_head::execute_procedure(THD *thd, List<Item> *args) nctx->pop_all_cursors(); // To avoid memory leaks after an error delete nctx; // Does nothing thd->spcont= save_spcont; - + DBUG_RETURN(ret); } @@ -1397,7 +1435,6 @@ sp_head::restore_lex(THD *thd) LEX *sublex= thd->lex; LEX *oldlex= (LEX *)m_lex.pop(); - init_stmt_after_parse(thd, sublex); if (! oldlex) return; // Nothing to restore diff --git a/sql/sp_head.h b/sql/sp_head.h index 1d4bfb514d3..1c54b1a567d 100644 --- a/sql/sp_head.h +++ b/sql/sp_head.h @@ -33,8 +33,8 @@ Item_result sp_map_result_type(enum enum_field_types type); -bool -sp_multi_results_command(enum enum_sql_command cmd); +uint +sp_get_flags_for_command(LEX *lex); struct sp_label; class sp_instr; @@ -107,18 +107,23 @@ class sp_head :private Query_arena MEM_ROOT main_mem_root; public: + /* Possible values of m_flags */ + const static int + HAS_RETURN= 1, // For FUNCTIONs only: is set if has RETURN + IN_SIMPLE_CASE= 2, // Is set if parsing a simple CASE + IN_HANDLER= 4, // Is set if the parser is in a handler body + MULTI_RESULTS= 8, // Is set if a procedure with SELECT(s) + CONTAINS_DYNAMIC_SQL= 16, // Is set if a procedure with PREPARE/EXECUTE + IS_INVOKED= 32; // Is set if this sp_head is being used. int m_type; // TYPE_ENUM_FUNCTION or TYPE_ENUM_PROCEDURE + uint m_flags; // Boolean attributes of a stored routine enum enum_field_types m_returns; // For FUNCTIONs only Field::geometry_type m_geom_returns; CHARSET_INFO *m_returns_cs; // For FUNCTIONs only TYPELIB *m_returns_typelib; // For FUNCTIONs only uint m_returns_len; // For FUNCTIONs only uint m_returns_pack; // For FUNCTIONs only - my_bool m_has_return; // For FUNCTIONs only - my_bool m_simple_case; // TRUE if parsing simple case, FALSE otherwise - my_bool m_multi_results; // TRUE if a procedure with SELECT(s) - my_bool m_in_handler; // TRUE if parser in a handler body uchar *m_tmp_query; // Temporary pointer to sub query string uint m_old_cmq; // Old CLIENT_MULTI_QUERIES value st_sp_chistics *m_chistics; @@ -265,6 +270,19 @@ public: bool add_used_tables_to_table_list(THD *thd, TABLE_LIST ***query_tables_last_ptr); + /* + Check if this stored routine contains statements disallowed + in a stored function or trigger, and set an appropriate error message + if this is the case. + */ + bool is_not_allowed_in_function(const char *where) + { + if (m_flags & CONTAINS_DYNAMIC_SQL) + my_error(ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0), "Dynamic SQL"); + else if (m_flags & MULTI_RESULTS) + my_error(ER_SP_NO_RETSET, MYF(0), where); + return test(m_flags & (CONTAINS_DYNAMIC_SQL|MULTI_RESULTS)); + } private: MEM_ROOT *m_thd_root; // Temp. store for thd's mem_root @@ -290,9 +308,6 @@ private: */ HASH m_sptabs; - /* Used for tracking of routine invocations and preventing recursion. */ - bool m_is_invoked; - int execute(THD *thd); diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 17a48fb4925..2ff0413e05e 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -551,11 +551,6 @@ void THD::cleanup_after_query() } /* Free Items that were created during this execution */ free_items(); - /* - In the rest of code we assume that free_list never points to garbage: - Keep this predicate true. - */ - free_list= 0; } /* @@ -1686,23 +1681,17 @@ Statement_map::Statement_map() : NULL,MYF(0)); } + int Statement_map::insert(Statement *statement) { int rc= my_hash_insert(&st_hash, (byte *) statement); - if (rc == 0) - last_found_statement= statement; if (statement->name.str) { - /* - If there is a statement with the same name, remove it. It is ok to - remove old and fail to insert new one at the same time. - */ - Statement *old_stmt; - if ((old_stmt= find_by_name(&statement->name))) - erase(old_stmt); if ((rc= my_hash_insert(&names_hash, (byte*)statement))) hash_delete(&st_hash, (byte*)statement); } + if (rc == 0) + last_found_statement= statement; return rc; } diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 455c592241a..e579ee9f8bd 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -128,6 +128,7 @@ void lex_start(THD *thd, uchar *buf,uint length) lex->update_list.empty(); lex->param_list.empty(); lex->view_list.empty(); + lex->prepared_stmt_params.empty(); lex->unit.next= lex->unit.master= lex->unit.link_next= lex->unit.return_to= 0; lex->unit.prev= lex->unit.link_prev= 0; @@ -143,6 +144,7 @@ void lex_start(THD *thd, uchar *buf,uint length) lex->describe= 0; lex->subqueries= FALSE; lex->view_prepare_mode= FALSE; + lex->stmt_prepare_mode= FALSE; lex->derived_tables= 0; lex->lock_option= TL_READ; lex->found_semicolon= 0; @@ -568,8 +570,7 @@ int yylex(void *arg, void *yythd) its value in a query for the binlog, the query must stay grammatically correct. */ - else if (c == '?' && ((THD*) yythd)->command == COM_STMT_PREPARE && - !ident_map[yyPeek()]) + else if (c == '?' && lex->stmt_prepare_mode && !ident_map[yyPeek()]) return(PARAM_MARKER); return((int) c); @@ -981,7 +982,7 @@ int yylex(void *arg, void *yythd) { THD* thd= (THD*)yythd; if ((thd->client_capabilities & CLIENT_MULTI_STATEMENTS) && - (thd->command != COM_STMT_PREPARE)) + !lex->stmt_prepare_mode) { lex->safe_to_cache_query= 0; lex->found_semicolon=(char*) lex->ptr; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index d777abca29a..6c91045189c 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -808,6 +808,11 @@ typedef struct st_lex to an .frm file. We need this definition to stay untouched. */ bool view_prepare_mode; + /* + TRUE if we're parsing a prepared statement: in this mode + we should allow placeholders and disallow multistatements. + */ + bool stmt_prepare_mode; bool safe_to_cache_query; bool subqueries, ignore; bool variables_used; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 937fd736282..7d430f2a44a 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -1644,7 +1644,7 @@ bool dispatch_command(enum enum_server_command command, THD *thd, } case COM_STMT_PREPARE: { - mysql_stmt_prepare(thd, packet, packet_length, 0); + mysql_stmt_prepare(thd, packet, packet_length); break; } case COM_STMT_CLOSE: @@ -1664,6 +1664,10 @@ bool dispatch_command(enum enum_server_command command, THD *thd, char *packet_end= thd->query + thd->query_length; mysql_log.write(thd,command,"%s",thd->query); DBUG_PRINT("query",("%-.4096s",thd->query)); + + if (!(specialflag & SPECIAL_NO_PRIOR)) + my_pthread_setprio(pthread_self(),QUERY_PRIOR); + mysql_parse(thd,thd->query, thd->query_length); while (!thd->killed && thd->lex->found_semicolon && !thd->net.report_error) @@ -2220,7 +2224,7 @@ int prepare_schema_table(THD *thd, LEX *lex, Table_ident *table_ident, TRUE error; In this case thd->fatal_error is set */ -bool alloc_query(THD *thd, char *packet, ulong packet_length) +bool alloc_query(THD *thd, const char *packet, uint packet_length) { packet_length--; // Remove end null /* Remove garbage at start and end of query */ @@ -2229,7 +2233,7 @@ bool alloc_query(THD *thd, char *packet, ulong packet_length) packet++; packet_length--; } - char *pos=packet+packet_length; // Point at end null + const char *pos= packet + packet_length; // Point at end null while (packet_length > 0 && (pos[-1] == ';' || my_isspace(thd->charset() ,pos[-1]))) { @@ -2250,8 +2254,6 @@ bool alloc_query(THD *thd, char *packet, ulong packet_length) thd->packet.shrink(thd->variables.net_buffer_length); thd->convert_buffer.shrink(thd->variables.net_buffer_length); - if (!(specialflag & SPECIAL_NO_PRIOR)) - my_pthread_setprio(pthread_self(),QUERY_PRIOR); return FALSE; } @@ -2466,112 +2468,17 @@ mysql_execute_command(THD *thd) } case SQLCOM_PREPARE: { - char *query_str; - uint query_len; - if (lex->prepared_stmt_code_is_varref) - { - /* This is PREPARE stmt FROM @var. */ - String str; - CHARSET_INFO *to_cs= thd->variables.collation_connection; - bool need_conversion; - user_var_entry *entry; - String *pstr= &str; - uint32 unused; - /* - Convert @var contents to string in connection character set. Although - it is known that int/real/NULL value cannot be a valid query we still - convert it for error messages to uniform. - */ - if ((entry= - (user_var_entry*)hash_search(&thd->user_vars, - (byte*)lex->prepared_stmt_code.str, - lex->prepared_stmt_code.length)) - && entry->value) - { - my_bool is_var_null; - pstr= entry->val_str(&is_var_null, &str, NOT_FIXED_DEC); - /* - NULL value of variable checked early as entry->value so here - we can't get NULL in normal conditions - */ - DBUG_ASSERT(!is_var_null); - if (!pstr) - goto error; - } - else - { - /* - variable absent or equal to NULL, so we need to set variable to - something reasonable to get readable error message during parsing - */ - str.set("NULL", 4, &my_charset_latin1); - } - - need_conversion= - String::needs_conversion(pstr->length(), pstr->charset(), - to_cs, &unused); - - query_len= need_conversion? (pstr->length() * to_cs->mbmaxlen) : - pstr->length(); - if (!(query_str= alloc_root(thd->mem_root, query_len+1))) - goto error; - - if (need_conversion) - { - uint dummy_errors; - query_len= copy_and_convert(query_str, query_len, to_cs, - pstr->ptr(), pstr->length(), - pstr->charset(), &dummy_errors); - } - else - memcpy(query_str, pstr->ptr(), pstr->length()); - query_str[query_len]= 0; - } - else - { - query_str= lex->prepared_stmt_code.str; - query_len= lex->prepared_stmt_code.length; - DBUG_PRINT("info", ("PREPARE: %.*s FROM '%.*s' \n", - lex->prepared_stmt_name.length, - lex->prepared_stmt_name.str, - query_len, query_str)); - } - thd->command= COM_STMT_PREPARE; - if (!(res= mysql_stmt_prepare(thd, query_str, query_len + 1, - &lex->prepared_stmt_name))) - send_ok(thd, 0L, 0L, "Statement prepared"); + mysql_sql_stmt_prepare(thd); break; } case SQLCOM_EXECUTE: { - DBUG_PRINT("info", ("EXECUTE: %.*s\n", - lex->prepared_stmt_name.length, - lex->prepared_stmt_name.str)); - mysql_sql_stmt_execute(thd, &lex->prepared_stmt_name); - lex->prepared_stmt_params.empty(); + mysql_sql_stmt_execute(thd); break; } case SQLCOM_DEALLOCATE_PREPARE: { - Statement* stmt; - DBUG_PRINT("info", ("DEALLOCATE PREPARE: %.*s\n", - lex->prepared_stmt_name.length, - lex->prepared_stmt_name.str)); - /* We account deallocate in the same manner as mysql_stmt_close */ - statistic_increment(thd->status_var.com_stmt_close, &LOCK_status); - if ((stmt= thd->stmt_map.find_by_name(&lex->prepared_stmt_name))) - { - thd->stmt_map.erase(stmt); - send_ok(thd); - } - else - { - my_error(ER_UNKNOWN_STMT_HANDLER, MYF(0), - lex->prepared_stmt_name.length, - lex->prepared_stmt_name.str, - "DEALLOCATE PREPARE"); - goto error; - } + mysql_sql_stmt_close(thd); break; } case SQLCOM_DO: @@ -4124,7 +4031,7 @@ end_with_restore_list: } #endif if (lex->sphead->m_type == TYPE_ENUM_FUNCTION && - !lex->sphead->m_has_return) + !(lex->sphead->m_flags & sp_head::HAS_RETURN)) { my_error(ER_SP_NORETURN, MYF(0), name); delete lex->sphead; @@ -4213,15 +4120,31 @@ end_with_restore_list: ha_rows select_limit; /* bits that should be cleared in thd->server_status */ uint bits_to_be_cleared= 0; + /* + Check that the stored procedure doesn't contain Dynamic SQL + and doesn't return result sets: such stored procedures can't + be called from a function or trigger. + */ + if (thd->in_sub_stmt) + { + const char *where= (thd->in_sub_stmt & SUB_STMT_TRIGGER ? + "trigger" : "function"); + if (sp->is_not_allowed_in_function(where)) + goto error; + } #ifndef EMBEDDED_LIBRARY my_bool nsok= thd->net.no_send_ok; thd->net.no_send_ok= TRUE; #endif - if (sp->m_multi_results) + if (sp->m_flags & sp_head::MULTI_RESULTS) { if (! (thd->client_capabilities & CLIENT_MULTI_RESULTS)) { + /* + The client does not support multiple result sets being sent + back + */ my_error(ER_SP_BADSELECT, MYF(0), sp->m_qname.str); #ifndef EMBEDDED_LIBRARY thd->net.no_send_ok= nsok; @@ -4265,7 +4188,7 @@ end_with_restore_list: thd->row_count_func= 0; /* - We never write CALL statements int binlog: + We never write CALL statements into binlog: - If the mode is non-prelocked, each statement will be logged separately. - If the mode is prelocked, the invoking statement will care diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index bf0bea804c4..879ea626494 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -15,9 +15,9 @@ Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ /********************************************************************** -This file contains the implementation of prepare and executes. +This file contains the implementation of prepared statements. -Prepare: +When one prepares a statement: - Server gets the query from client with command 'COM_STMT_PREPARE'; in the following format: @@ -25,21 +25,21 @@ Prepare: - Parse the query and recognize any parameter markers '?' and store its information list in lex->param_list - Allocate a new statement for this prepare; and keep this in - 'thd->prepared_statements' pool. + 'thd->stmt_map'. - Without executing the query, return back to client the total number of parameters along with result-set metadata information (if any) in the following format: [STMT_ID:4] [Column_count:2] [Param_count:2] + [Params meta info (stubs only for now)] (if Param_count > 0) [Columns meta info] (if Column_count > 0) - [Params meta info] (if Param_count > 0 ) (TODO : 4.1.1) -Prepare-execute: +When one executes a statement: - Server gets the command 'COM_STMT_EXECUTE' to execute the - previously prepared query. If there is any param markers; then client - will send the data in the following format: + previously prepared query. If there are any parameter markers, then the + client will send the data in the following format: [COM_STMT_EXECUTE:1] [STMT_ID:4] [NULL_BITS:(param_count+7)/8)] @@ -48,29 +48,29 @@ Prepare-execute: [[length]data] .. [[length]data]. (Note: Except for string/binary types; all other types will not be supplied with length field) - - Replace the param items with this new data. If it is a first execute - or types altered by client; then setup the conversion routines. + - If it is a first execute or types of parameters were altered by client, + then setup the conversion routines. + - Assign parameter items from the supplied data. - Execute the query without re-parsing and send back the results to client -Long data handling: +When one supplies long data for a placeholder: - Server gets the long data in pieces with command type 'COM_STMT_SEND_LONG_DATA'. - The packet recieved will have the format as: [COM_STMT_SEND_LONG_DATA:1][STMT_ID:4][parameter_number:2][data] - - data from the packet is appended to long data value buffer for this + - data from the packet is appended to the long data value buffer for this placeholder. - - It's up to the client to check for read data ended. The server doesn't - care; and also server doesn't notify to the client that it got the - data or not; if there is any error; then during execute; the error - will be returned + - It's up to the client to stop supplying data chunks at any point. The + server doesn't care; also, the server doesn't notify the client whether + it got the data or not; if there is any error, then it will be returned + at statement execute. ***********************************************************************/ #include "mysql_priv.h" #include "sql_select.h" // for JOIN -#include <m_ctype.h> // for isspace() #include "sp_head.h" #include "sp.h" #include "sp_cache.h" @@ -82,7 +82,7 @@ Long data handling: #endif /****************************************************************************** - Prepared_statement: statement which can contain placeholders + Prepared_statement: a statement that can contain placeholders ******************************************************************************/ class Prepared_statement: public Statement @@ -93,6 +93,7 @@ public: Item_param **param_array; uint param_count; uint last_errno; + uint flags; char last_error[MYSQL_ERRMSG_SIZE]; #ifndef EMBEDDED_LIBRARY bool (*set_params)(Prepared_statement *st, uchar *data, uchar *data_end, @@ -104,15 +105,21 @@ public: List<LEX_STRING>& varnames, String *expanded_query); public: - Prepared_statement(THD *thd_arg); + Prepared_statement(THD *thd_arg, Protocol *protocol_arg); virtual ~Prepared_statement(); void setup_set_params(); virtual Query_arena::Type type() const; virtual void close_cursor(); -}; + bool set_name(LEX_STRING *name); + + bool prepare(const char *packet, uint packet_length); + bool execute(String *expanded_query, bool open_cursor); + /* Destroy this statement */ + bool deallocate(); -static void execute_stmt(THD *thd, Prepared_statement *stmt, - String *expanded_query); + /* Possible values of flags */ + static const int IS_IN_USE= 1; +}; /****************************************************************************** Implementation @@ -127,13 +134,30 @@ inline bool is_param_null(const uchar *pos, ulong param_no) enum { STMT_QUERY_LOG_LENGTH= 8192 }; /* - Seek prepared statement in statement map by id: returns zero if statement - was not found, pointer otherwise. + Find a prepared statement in the statement map by id. + + SYNOPSIS + find_prepared_statement() + thd thread handle + id statement id + where the place from which this function is called (for + error reporting). + + DESCRIPTION + Try to find a prepared statement and set THD error if it's not found. + + RETURN VALUE + 0 if the statement was not found, a pointer otherwise. */ static Prepared_statement * find_prepared_statement(THD *thd, ulong id, const char *where) { + /* + To strictly separate namespaces of SQL prepared statements and C API + prepared statements find() will return 0 if there is a named prepared + statement with such id. + */ Statement *stmt= thd->stmt_map.find(id); if (stmt == 0 || stmt->type() != Query_arena::PREPARED_STATEMENT) @@ -148,7 +172,13 @@ find_prepared_statement(THD *thd, ulong id, const char *where) /* - Send prepared stmt info to client after prepare + Send prepared statement id and metadata to the client after prepare. + + SYNOPSIS + send_prep_stmt() + + RETURN VALUE + 0 in case of success, 1 otherwise */ #ifndef EMBEDDED_LIBRARY @@ -193,8 +223,20 @@ static bool send_prep_stmt(Prepared_statement *stmt, /* - Read the length of the parameter data and return back to - caller by positing the pointer to param data. + Read the length of the parameter data and return it back to + the caller. + + SYNOPSIS + get_param_length() + packet a pointer to the data + len remaining packet length + + DESCRIPTION + Read data length, position the packet to the first byte after it, + and return the length to the caller. + + RETURN VALUE + Length of data piece. */ #ifndef EMBEDDED_LIBRARY @@ -239,19 +281,21 @@ static ulong get_param_length(uchar **packet, ulong len) #endif /*!EMBEDDED_LIBRARY*/ /* - Data conversion routines + Data conversion routines. + SYNOPSIS - set_param_xx() - param parameter item - pos input data buffer - len length of data in the buffer + set_param_xx() + param parameter item + pos input data buffer + len length of data in the buffer - All these functions read the data from pos, convert it to requested type - and assign to param; pos is advanced to predefined length. + DESCRIPTION + All these functions read the data from pos, convert it to requested + type and assign to param; pos is advanced to predefined length. - Make a note that the NULL handling is examined at first execution - (i.e. when input types altered) and for all subsequent executions - we don't read any values for this. + Make a note that the NULL handling is examined at first execution + (i.e. when input types altered) and for all subsequent executions + we don't read any values for this. RETURN VALUE none @@ -594,8 +638,35 @@ static void setup_one_conversion_function(THD *thd, Item_param *param, #ifndef EMBEDDED_LIBRARY /* - Update the parameter markers by reading data from client packet - and if binary/update log is set, generate the valid query. + Routines to assign parameters from data supplied by the client. + + DESCRIPTION + Update the parameter markers by reading data from the packet and + and generate a valid query for logging. + + NOTES + This function, along with other _withlog functions is called when one of + binary, slow or general logs is open. Logging of prepared statements in + all cases is performed by means of conventional queries: if parameter + data was supplied from C API, each placeholder in the query is + replaced with its actual value; if we're logging a [Dynamic] SQL + prepared statement, parameter markers are replaced with variable names. + Example: + mysql_stmt_prepare("UPDATE t1 SET a=a*1.25 WHERE a=?") + --> general logs gets [Prepare] UPDATE t1 SET a*1.25 WHERE a=?" + mysql_stmt_execute(stmt); + --> general and binary logs get + [Execute] UPDATE t1 SET a*1.25 WHERE a=1" + If a statement has been prepared using SQL syntax: + PREPARE stmt FROM "UPDATE t1 SET a=a*1.25 WHERE a=?" + --> general log gets + [Query] PREPARE stmt FROM "UPDATE ..." + EXECUTE stmt USING @a + --> general log gets + [Query] EXECUTE stmt USING @a; + + RETURN VALUE + 0 if success, 1 otherwise */ static bool insert_params_withlog(Prepared_statement *stmt, uchar *null_array, @@ -707,6 +778,17 @@ static bool setup_conversion_functions(Prepared_statement *stmt, #else +/* + Embedded counterparts of parameter assignment routines. + + DESCRIPTION + The main difference between the embedded library and the server is + that in embedded case we don't serialize/deserialize parameters data. + Additionally, for unknown reason, the client-side flag raised for + changed types of placeholders is ignored and we simply setup conversion + functions at each execute (TODO: fix). +*/ + static bool emb_insert_params(Prepared_statement *stmt, String *expanded_query) { THD *thd= stmt->thd; @@ -791,7 +873,8 @@ static bool emb_insert_params_withlog(Prepared_statement *stmt, String *query) /* - Set prepared statement parameters from user variables. + Assign prepared statement parameters from user variables. + SYNOPSIS insert_params_from_vars() stmt Statement @@ -829,12 +912,14 @@ static bool insert_params_from_vars(Prepared_statement *stmt, /* Do the same as insert_params_from_vars but also construct query text for binary log. + SYNOPSIS insert_params_from_vars() - stmt Statement + stmt Prepared statement varnames List of variables. Caller must ensure that number of variables in the list is equal to number of statement parameters - query The query with parameter markers replaced with their values + query The query with parameter markers replaced with corresponding + user variables that were used to execute the query. */ static bool insert_params_from_vars_with_log(Prepared_statement *stmt, @@ -845,12 +930,13 @@ static bool insert_params_from_vars_with_log(Prepared_statement *stmt, Item_param **end= begin + stmt->param_count; user_var_entry *entry; LEX_STRING *varname; - DBUG_ENTER("insert_params_from_vars"); - List_iterator<LEX_STRING> var_it(varnames); String buf; const String *val; uint32 length= 0; + + DBUG_ENTER("insert_params_from_vars"); + if (query->copy(stmt->query, stmt->query_length, default_charset_info)) DBUG_RETURN(1); @@ -858,7 +944,8 @@ static bool insert_params_from_vars_with_log(Prepared_statement *stmt, { Item_param *param= *it; varname= var_it++; - if (get_var_with_binlog(stmt->thd, *varname, &entry)) + if (get_var_with_binlog(stmt->thd, stmt->lex->sql_command, + *varname, &entry)) DBUG_RETURN(1); if (param->set_from_user_var(stmt->thd, entry)) @@ -895,16 +982,16 @@ static bool insert_params_from_vars_with_log(Prepared_statement *stmt, } /* - Validate INSERT statement: + Validate INSERT statement. SYNOPSIS mysql_test_insert() - stmt prepared statemen handler - tables global/local table list + stmt prepared statement + tables global/local table list RETURN VALUE - FALSE success - TRUE error, error message is set in THD + FALSE success + TRUE error, error message is set in THD */ static bool mysql_test_insert(Prepared_statement *stmt, @@ -988,13 +1075,13 @@ error: SYNOPSIS mysql_test_update() - stmt prepared statemen handler - tables list of tables queries + stmt prepared statement + tables list of tables used in this query RETURN VALUE - 0 success - 1 error, error message is set in THD - 2 convert to multi_update + 0 success + 1 error, error message is set in THD + 2 convert to multi_update */ static int mysql_test_update(Prepared_statement *stmt, @@ -1068,16 +1155,16 @@ error: /* - Validate DELETE statement + Validate DELETE statement. SYNOPSIS mysql_test_delete() - stmt prepared statemen handler - tables list of tables queries + stmt prepared statement + tables list of tables used in this query RETURN VALUE - FALSE success - TRUE error, error message is set in THD + FALSE success + TRUE error, error message is set in THD */ static bool mysql_test_delete(Prepared_statement *stmt, @@ -1106,21 +1193,24 @@ error: /* Validate SELECT statement. - In case of success, if this query is not EXPLAIN, send column list info - back to client. SYNOPSIS mysql_test_select() - stmt prepared statemen handler - tables list of tables queries + stmt prepared statement + tables list of tables used in the query + + DESCRIPTION + In case of success, if this query is not EXPLAIN, send column list info + back to the client. RETURN VALUE - FALSE success - TRUE error, sent to client + 0 success + 1 error, error message is set in THD + 2 success, and statement metadata has been sent */ -static bool mysql_test_select(Prepared_statement *stmt, - TABLE_LIST *tables, bool text_protocol) +static int mysql_test_select(Prepared_statement *stmt, + TABLE_LIST *tables, bool text_protocol) { THD *thd= stmt->thd; LEX *lex= stmt->lex; @@ -1158,51 +1248,43 @@ static bool mysql_test_select(Prepared_statement *stmt, */ if (unit->prepare(thd, 0, 0, "")) goto error; - if (!text_protocol) + if (!lex->describe && !text_protocol) { - if (lex->describe) - { - if (send_prep_stmt(stmt, 0) || thd->protocol->flush()) - goto error; - } - else - { - /* Make copy of item list, as change_columns may change it */ - List<Item> fields(lex->select_lex.item_list); + /* Make copy of item list, as change_columns may change it */ + List<Item> fields(lex->select_lex.item_list); - /* Change columns if a procedure like analyse() */ - if (unit->last_procedure && - unit->last_procedure->change_columns(fields)) - goto error; + /* Change columns if a procedure like analyse() */ + if (unit->last_procedure && unit->last_procedure->change_columns(fields)) + goto error; - /* - We can use lex->result as it should've been - prepared in unit->prepare call above. - */ - if (send_prep_stmt(stmt, lex->result->field_count(fields)) || - lex->result->send_fields(fields, Protocol::SEND_EOF) || - thd->protocol->flush()) - goto error; - } + /* + We can use lex->result as it should've been prepared in + unit->prepare call above. + */ + if (send_prep_stmt(stmt, lex->result->field_count(fields)) || + lex->result->send_fields(fields, Protocol::SEND_EOF) || + thd->protocol->flush()) + goto error; + DBUG_RETURN(2); } - DBUG_RETURN(FALSE); + DBUG_RETURN(0); error: - DBUG_RETURN(TRUE); + DBUG_RETURN(1); } /* - Validate and prepare for execution DO statement expressions + Validate and prepare for execution DO statement expressions. SYNOPSIS mysql_test_do_fields() - stmt prepared statemen handler - tables list of tables queries - values list of expressions + stmt prepared statement + tables list of tables used in this query + values list of expressions RETURN VALUE - FALSE success - TRUE error, error message is set in THD + FALSE success + TRUE error, error message is set in THD */ static bool mysql_test_do_fields(Prepared_statement *stmt, @@ -1226,13 +1308,13 @@ static bool mysql_test_do_fields(Prepared_statement *stmt, SYNOPSIS mysql_test_set_fields() - stmt prepared statemen handler - tables list of tables queries - values list of expressions + stmt prepared statement + tables list of tables used in this query + values list of expressions RETURN VALUE - FALSE success - TRUE error + FALSE success + TRUE error, error message is set in THD */ static bool mysql_test_set_fields(Prepared_statement *stmt, @@ -1264,9 +1346,9 @@ error: SYNOPSIS select_like_stmt_test() - stmt - prepared statement handler - specific_prepare - function of command specific prepare - setup_tables_done_option - options to be passed to LEX::unit.prepare() + stmt prepared statement + specific_prepare function of command specific prepare + setup_tables_done_option options to be passed to LEX::unit.prepare() NOTE This function won't directly open tables used in select. They should @@ -1275,8 +1357,8 @@ error: "specific_prepare" call (like this happens in case of multi-update). RETURN VALUE - FALSE success - TRUE error, error message is set in THD + FALSE success + TRUE error, error message is set in THD */ static bool select_like_stmt_test(Prepared_statement *stmt, @@ -1300,19 +1382,19 @@ static bool select_like_stmt_test(Prepared_statement *stmt, /* Check internal SELECT of the prepared command (with opening and - locking tables used). + locking of used tables). SYNOPSIS select_like_stmt_test_with_open_n_lock() - stmt - prepared statement handler - tables - list of tables to be opened and locked - before calling specific_prepare function - specific_prepare - function of command specific prepare - setup_tables_done_option - options to be passed to LEX::unit.prepare() + stmt prepared statement + tables list of tables to be opened and locked + before calling specific_prepare function + specific_prepare function of command specific prepare + setup_tables_done_option options to be passed to LEX::unit.prepare() RETURN VALUE - FALSE success - TRUE error + FALSE success + TRUE error */ static bool @@ -1341,12 +1423,12 @@ select_like_stmt_test_with_open_n_lock(Prepared_statement *stmt, SYNOPSIS mysql_test_create_table() - stmt prepared statemen handler - tables list of tables queries + stmt prepared statement + tables list of tables used in this query RETURN VALUE - FALSE success - TRUE error, error message is set in THD + FALSE success + TRUE error, error message is set in THD */ static bool mysql_test_create_table(Prepared_statement *stmt) @@ -1377,17 +1459,17 @@ static bool mysql_test_create_table(Prepared_statement *stmt) /* - Validate and prepare for execution multi update statement + Validate and prepare for execution a multi update statement. SYNOPSIS mysql_test_multiupdate() - stmt prepared statemen handler - tables list of tables queries - converted converted to multi-update from usual update + stmt prepared statement + tables list of tables used in this query + converted converted to multi-update from usual update RETURN VALUE - FALSE success - TRUE error + FALSE success + TRUE error, error message is set in THD */ static bool mysql_test_multiupdate(Prepared_statement *stmt, @@ -1404,16 +1486,16 @@ static bool mysql_test_multiupdate(Prepared_statement *stmt, /* - Validate and prepare for execution multi delete statement + Validate and prepare for execution a multi delete statement. SYNOPSIS mysql_test_multidelete() - stmt prepared statemen handler - tables list of tables queries + stmt prepared statement + tables list of tables used in this query RETURN VALUE - 0 success - 1 error, error message in THD is set. + FALSE success + TRUE error, error message in THD is set. */ static bool mysql_test_multidelete(Prepared_statement *stmt, @@ -1449,10 +1531,11 @@ error: SYNOPSIS mysql_insert_select_prepare_tester() - thd thread handler + thd thread handle - NOTE: we need remove first local tables after open_and_lock_tables, - because mysql_handle_derived use local tables lists + NOTE + We need to remove the first local table after open_and_lock_tables, + because mysql_handle_derived uses local tables lists. */ static bool mysql_insert_select_prepare_tester(THD *thd) @@ -1476,21 +1559,20 @@ static bool mysql_insert_select_prepare_tester(THD *thd) /* - Validate and prepare for execution INSERT ... SELECT statement + Validate and prepare for execution INSERT ... SELECT statement. SYNOPSIS mysql_test_insert_select() - stmt prepared statemen handler - tables list of tables of query + stmt prepared statement + tables list of tables used in this query RETURN VALUE - 0 success - 1 error, sent to client - -1 error, not sent to client + FALSE success + TRUE error, error message is set in THD */ -static int mysql_test_insert_select(Prepared_statement *stmt, - TABLE_LIST *tables) +static bool mysql_test_insert_select(Prepared_statement *stmt, + TABLE_LIST *tables) { int res; LEX *lex= stmt->lex; @@ -1525,7 +1607,7 @@ static int mysql_test_insert_select(Prepared_statement *stmt, SYNOPSIS check_prepared_statement() - stmt prepared statement + stmt prepared statement DESCRIPTION This function @@ -1534,8 +1616,8 @@ static int mysql_test_insert_select(Prepared_statement *stmt, by calling fix_fields. RETURN VALUE - FALSE success, statement metadata is sent to client - TRUE error, error message is set (but not sent) + FALSE success, statement metadata is sent to client + TRUE error, error message is set in THD (but not sent) */ static bool check_prepared_statement(Prepared_statement *stmt, @@ -1582,11 +1664,13 @@ static bool check_prepared_statement(Prepared_statement *stmt, break; case SQLCOM_SELECT: - if ((res= mysql_test_select(stmt, tables, text_protocol))) - goto error; - /* Statement and field info has already been sent */ - DBUG_RETURN(FALSE); - + res= mysql_test_select(stmt, tables, text_protocol); + if (res == 2) + { + /* Statement and field info has already been sent */ + DBUG_RETURN(FALSE); + } + break; case SQLCOM_CREATE_TABLE: res= mysql_test_create_table(stmt); break; @@ -1695,21 +1779,19 @@ static void cleanup_stmt_and_thd_after_use(Statement *stmt, THD *thd) DBUG_VOID_RETURN; } + /* - Given a query string with parameter markers, create a Prepared Statement - from it and send PS info back to the client. + COM_STMT_PREPARE handler. SYNOPSIS mysql_stmt_prepare() - packet query to be prepared - packet_length query string length, including ignored trailing NULL or - quote char. - name NULL or statement name. For unnamed statements binary PS - protocol is used, for named statements text protocol is - used. - RETURN - FALSE OK, statement prepared successfully - TRUE Error + packet query to be prepared + packet_length query string length, including ignored + trailing NULL or quote char. + + DESCRIPTION + Given a query string with parameter markers, create a prepared + statement from it and send PS info back to the client. NOTES This function parses the query and sends the total number of parameters @@ -1722,124 +1804,147 @@ static void cleanup_stmt_and_thd_after_use(Statement *stmt, THD *thd) that a fast and direct retrieval can be made without going through all field items. + RETURN VALUE + none: in case of success a new statement id and metadata is sent + to the client, otherwise an error message is set in THD. */ -bool mysql_stmt_prepare(THD *thd, char *packet, uint packet_length, - LEX_STRING *name) +void mysql_stmt_prepare(THD *thd, const char *packet, uint packet_length) { - LEX *lex; - Statement stmt_backup; - Prepared_statement *stmt= new Prepared_statement(thd); - bool error; + Prepared_statement *stmt= new Prepared_statement(thd, &thd->protocol_prep); + bool rc; DBUG_ENTER("mysql_stmt_prepare"); DBUG_PRINT("prep_query", ("%s", packet)); - /* - If this is an SQLCOM_PREPARE, we also increase Com_prepare_sql. - However, it seems handy if com_stmt_prepare is increased always, - no matter what kind of prepare is processed. - */ - statistic_increment(thd->status_var.com_stmt_prepare, &LOCK_status); - if (stmt == 0) - DBUG_RETURN(TRUE); - - if (name) - { - stmt->name.length= name->length; - if (!(stmt->name.str= memdup_root(stmt->mem_root, (char*)name->str, - name->length))) - { - delete stmt; - DBUG_RETURN(TRUE); - } - } + DBUG_VOID_RETURN; /* out of memory: error is set in Sql_alloc */ if (thd->stmt_map.insert(stmt)) { delete stmt; - DBUG_RETURN(TRUE); + DBUG_VOID_RETURN; /* out of memory */ } - /* - alloc_query() uses thd->memroot && thd->query, so we have to call - both of backup_statement() and backup_item_area() here. - */ - thd->set_n_backup_statement(stmt, &stmt_backup); - thd->set_n_backup_active_arena(stmt, &stmt_backup); - - if (alloc_query(thd, packet, packet_length)) - { - thd->restore_backup_statement(stmt, &stmt_backup); - thd->restore_active_arena(stmt, &stmt_backup); - /* Statement map deletes statement on erase */ - thd->stmt_map.erase(stmt); - DBUG_RETURN(TRUE); - } - - mysql_log.write(thd, thd->command, "[%lu] %s", stmt->id, packet); - - thd->stmt_arena= stmt; - mysql_init_query(thd, (uchar *) thd->query, thd->query_length); + mysql_reset_thd_for_next_command(thd); /* Reset warnings from previous command */ mysql_reset_errors(thd, 0); - lex= thd->lex; - lex->safe_to_cache_query= 0; - sp_cache_flush_obsolete(&thd->sp_proc_cache); sp_cache_flush_obsolete(&thd->sp_func_cache); - error= yyparse((void *)thd) || thd->is_fatal_error || - thd->net.report_error || init_param_array(stmt); - /* - While doing context analysis of the query (in check_prepared_statement) - we allocate a lot of additional memory: for open tables, JOINs, derived - tables, etc. Let's save a snapshot of current parse tree to the - statement and restore original THD. In cases when some tree - transformation can be reused on execute, we set again thd->mem_root from - stmt->mem_root (see setup_wild for one place where we do that). - */ - thd->restore_active_arena(stmt, &stmt_backup); + if (!(specialflag & SPECIAL_NO_PRIOR)) + my_pthread_setprio(pthread_self(),QUERY_PRIOR); - if (!error) - error= check_prepared_statement(stmt, test(name)); + rc= stmt->prepare(packet, packet_length); - /* restore to WAIT_PRIOR: QUERY_PRIOR is set inside alloc_query */ if (!(specialflag & SPECIAL_NO_PRIOR)) my_pthread_setprio(pthread_self(),WAIT_PRIOR); - if (error && thd->lex->sphead) - { - delete thd->lex->sphead; - thd->lex->sphead= NULL; - } - lex_end(lex); - close_thread_tables(thd); - cleanup_stmt_and_thd_after_use(stmt, thd); - thd->restore_backup_statement(stmt, &stmt_backup); - thd->stmt_arena= thd; - if (error) + if (rc) { /* Statement map deletes statement on erase */ thd->stmt_map.erase(stmt); - stmt= NULL; + } + else + mysql_log.write(thd, COM_STMT_PREPARE, "[%lu] %s", stmt->id, packet); + + /* check_prepared_statemnt sends the metadata packet in case of success */ + DBUG_VOID_RETURN; +} + +/* + SYNOPSIS + get_dynamic_sql_string() + lex in main lex + query_len out length of the SQL statement (is set only + in case of success) + + DESCRIPTION + Get an SQL statement text from a user variable or from plain + text. If the statement is plain text, just assign the + pointers, otherwise allocate memory in thd->mem_root and copy + the contents of the variable, possibly with character + set conversion. + + RETURN VALUE + non-zero success, 0 in case of error (out of memory) +*/ + +static const char *get_dynamic_sql_string(LEX *lex, uint *query_len) +{ + THD *thd= lex->thd; + char *query_str= 0; + + if (lex->prepared_stmt_code_is_varref) + { + /* This is PREPARE stmt FROM or EXECUTE IMMEDIATE @var. */ + String str; + CHARSET_INFO *to_cs= thd->variables.collation_connection; + bool needs_conversion; + user_var_entry *entry; + String *pstr= &str; + uint32 unused, len; + /* + Convert @var contents to string in connection character set. Although + it is known that int/real/NULL value cannot be a valid query we still + convert it for error messages to be uniform. + */ + if ((entry= + (user_var_entry*)hash_search(&thd->user_vars, + (byte*)lex->prepared_stmt_code.str, + lex->prepared_stmt_code.length)) + && entry->value) + { + my_bool is_var_null; + pstr= entry->val_str(&is_var_null, &str, NOT_FIXED_DEC); + /* + NULL value of variable checked early as entry->value so here + we can't get NULL in normal conditions + */ + DBUG_ASSERT(!is_var_null); + if (!pstr) + goto end; + } + else + { + /* + variable absent or equal to NULL, so we need to set variable to + something reasonable to get a readable error message during parsing + */ + str.set("NULL", 4, &my_charset_latin1); + } + + needs_conversion= String::needs_conversion(pstr->length(), + pstr->charset(), to_cs, &unused); + + len= needs_conversion ? pstr->length() * to_cs->mbmaxlen : pstr->length(); + if (!(query_str= alloc_root(thd->mem_root, len+1))) + goto end; + + if (needs_conversion) + { + uint dummy_errors; + len= copy_and_convert(query_str, len, to_cs, pstr->ptr(), pstr->length(), + pstr->charset(), &dummy_errors); + } + else + memcpy(query_str, pstr->ptr(), pstr->length()); + query_str[len]= '\0'; + *query_len= len; } else { - stmt->setup_set_params(); - init_stmt_after_parse(thd, stmt->lex); - stmt->state= Query_arena::PREPARED; + query_str= lex->prepared_stmt_code.str; + *query_len= lex->prepared_stmt_code.length; } - DBUG_RETURN(!stmt); +end: + return query_str; } -/* - Init PS/SP specific parse tree members. -*/ +/* Init PS/SP specific parse tree members. */ -void init_stmt_after_parse(THD *thd, LEX *lex) +static void init_stmt_after_parse(LEX *lex) { SELECT_LEX *sl= lex->all_selects_list; /* @@ -1850,6 +1955,66 @@ void init_stmt_after_parse(THD *thd, LEX *lex) sl->uncacheable&= ~UNCACHEABLE_PREPARE; } +/* + SQLCOM_PREPARE implementation. + + SYNOPSIS + mysql_sql_stmt_prepare() + thd thread handle + + DESCRIPTION + Prepare an SQL prepared statement. This is called from + mysql_execute_command and should therefore behave like an + ordinary query (e.g. should not reset any global THD data). + + RETURN VALUE + none: in case of success, OK packet is sent to the client, + otherwise an error message is set in THD +*/ + +void mysql_sql_stmt_prepare(THD *thd) +{ + LEX *lex= thd->lex; + LEX_STRING *name= &lex->prepared_stmt_name; + Prepared_statement *stmt; + const char *query; + uint query_len; + + DBUG_ENTER("mysql_sql_stmt_prepare"); + + DBUG_ASSERT(thd->protocol == &thd->protocol_simple); + if ((stmt= (Prepared_statement*) thd->stmt_map.find_by_name(name))) + { + /* + If there is a statement with the same name, remove it. It is ok to + remove old and fail to insert a new one at the same time. + */ + if (stmt->deallocate()) + DBUG_VOID_RETURN; + } + + if (! (query= get_dynamic_sql_string(lex, &query_len)) || + ! (stmt= new Prepared_statement(thd, &thd->protocol_simple))) + { + DBUG_VOID_RETURN; /* out of memory */ + } + + if (stmt->set_name(name) || thd->stmt_map.insert(stmt)) + { + delete stmt; + DBUG_VOID_RETURN; + } + + if (stmt->prepare(query, query_len+1)) + { + /* Statement map deletes the statement on erase */ + thd->stmt_map.erase(stmt); + } + else + send_ok(thd, 0L, 0L, "Statement prepared"); + + DBUG_VOID_RETURN; +} /* Reinit prepared statement/stored procedure before execution */ @@ -1956,7 +2121,8 @@ void reinit_stmt_before_use(THD *thd, LEX *lex) SYNOPSIS reset_stmt_params() - stmt prepared statement for which parameters should be reset + stmt prepared statement for which parameters should + be reset */ static void reset_stmt_params(Prepared_statement *stmt) @@ -1969,81 +2135,50 @@ static void reset_stmt_params(Prepared_statement *stmt) /* - Executes previously prepared query. - If there is any parameters, then replace markers with the data supplied - from client, and then execute the query. + COM_STMT_EXECUTE handler: execute a previously prepared statement. SYNOPSIS mysql_stmt_execute() - thd Current thread - packet Query string - packet_length Query string length, including terminator character. + thd current thread + packet parameter types and data, if any + packet_length packet length, including the terminator character. + + DESCRIPTION + If there are any parameters, then replace parameter markers with the + data supplied from the client, and then execute the statement. + This function uses binary protocol to send a possible result set + to the client. + + RETURN VALUE + none: in case of success OK packet or a result set is sent to the + client, otherwise an error message is set in THD. */ void mysql_stmt_execute(THD *thd, char *packet, uint packet_length) { ulong stmt_id= uint4korr(packet); ulong flags= (ulong) ((uchar) packet[4]); - Statement stmt_backup; - Cursor *cursor; - /* - Query text for binary log, or empty string if the query is not put into - binary log. - */ + /* Query text for binary, general or slow log, if any of them is open */ String expanded_query; #ifndef EMBEDDED_LIBRARY uchar *packet_end= (uchar *) packet + packet_length - 1; #endif Prepared_statement *stmt; + bool rc; DBUG_ENTER("mysql_stmt_execute"); packet+= 9; /* stmt_id + 5 bytes of flags */ - statistic_increment(thd->status_var.com_stmt_execute, &LOCK_status); if (!(stmt= find_prepared_statement(thd, stmt_id, "mysql_stmt_execute"))) DBUG_VOID_RETURN; DBUG_PRINT("exec_query", ("%s", stmt->query)); DBUG_PRINT("info",("stmt: %p", stmt)); - /* Check if we got an error when sending long data */ - if (stmt->state == Query_arena::ERROR) - { - my_message(stmt->last_errno, stmt->last_error, MYF(0)); - DBUG_VOID_RETURN; - } - - cursor= stmt->cursor; - if (cursor && cursor->is_open()) - stmt->close_cursor(); - - DBUG_ASSERT(thd->free_list == NULL); mysql_reset_thd_for_next_command(thd); - if (flags & (ulong) CURSOR_TYPE_READ_ONLY) - { - if (!stmt->lex->result || !stmt->lex->result->simple_select()) - { - DBUG_PRINT("info",("Cursor asked for not SELECT stmt")); - /* - If lex->result is set in the parser, this is not a SELECT - statement: we can't open a cursor for it. - */ - flags= 0; - my_error(ER_SP_BAD_CURSOR_QUERY, MYF(0)); - goto err; - } - else - { - DBUG_PRINT("info",("Using READ_ONLY cursor")); - if (!cursor && - !(cursor= stmt->cursor= new (stmt->mem_root) Cursor(thd))) - DBUG_VOID_RETURN; - /* If lex->result is set, mysql_execute_command will use it */ - stmt->lex->result= &cursor->result; - stmt->protocol= &cursor->protocol; - thd->lock_id= &cursor->lock_id; - } - } + sp_cache_flush_obsolete(&thd->sp_proc_cache); + sp_cache_flush_obsolete(&thd->sp_func_cache); + #ifndef EMBEDDED_LIBRARY if (stmt->param_count) { @@ -2062,93 +2197,67 @@ void mysql_stmt_execute(THD *thd, char *packet, uint packet_length) if (stmt->param_count && stmt->set_params_data(stmt, &expanded_query)) goto set_params_data_err; #endif - thd->set_n_backup_statement(stmt, &stmt_backup); - thd->stmt_arena= stmt; - reinit_stmt_before_use(thd, stmt->lex); - /* From now cursors assume that thd->mem_root is clean */ - if (expanded_query.length() && - alloc_query(thd, (char *)expanded_query.ptr(), - expanded_query.length()+1)) - { - my_error(ER_OUTOFMEMORY, 0, expanded_query.length()); - goto err; - } - mysql_log.write(thd, thd->command, "[%lu] %s", stmt->id, thd->query); - - thd->protocol= stmt->protocol; // Switch to binary protocol if (!(specialflag & SPECIAL_NO_PRIOR)) my_pthread_setprio(pthread_self(),QUERY_PRIOR); - sp_cache_flush_obsolete(&thd->sp_proc_cache); - sp_cache_flush_obsolete(&thd->sp_func_cache); - mysql_execute_command(thd); + rc= stmt->execute(&expanded_query, + test(flags & (ulong) CURSOR_TYPE_READ_ONLY)); if (!(specialflag & SPECIAL_NO_PRIOR)) my_pthread_setprio(pthread_self(), WAIT_PRIOR); - thd->protocol= &thd->protocol_simple; // Use normal protocol - - if (cursor && cursor->is_open()) - { - /* - It's safer if we grab THD state after mysql_execute_command is - finished and not in Cursor::open(), because currently the call to - Cursor::open is buried deep in JOIN::exec of the top level join. - */ - cursor->init_from_thd(thd); - - if (cursor->close_at_commit) - thd->stmt_map.add_transient_cursor(stmt); - } - else - { - close_thread_tables(thd); - cleanup_stmt_and_thd_after_use(stmt, thd); - reset_stmt_params(stmt); - } + if (rc) + goto err; - log_slow_statement(thd); - /* Prevent from second logging in the end of dispatch_command */ - thd->enable_slow_log= FALSE; + mysql_log.write(thd, COM_STMT_EXECUTE, "[%lu] %s", stmt->id, thd->query); - thd->set_statement(&stmt_backup); - thd->lock_id= &thd->main_lock_id; - thd->stmt_arena= thd; DBUG_VOID_RETURN; set_params_data_err: - reset_stmt_params(stmt); my_error(ER_WRONG_ARGUMENTS, MYF(0), "mysql_stmt_execute"); err: + reset_stmt_params(stmt); DBUG_VOID_RETURN; } /* - Execute prepared statement using parameter values from - lex->prepared_stmt_params and send result to the client using text protocol. + SQLCOM_EXECUTE implementation. + + SYNOPSIS + mysql_sql_stmt_execute() + thd thread handle + + DESCRIPTION + Execute prepared statement using parameter values from + lex->prepared_stmt_params and send result to the client using + text protocol. This is called from mysql_execute_command and + therefore should behave like an ordinary query (e.g. not change + global THD data, such as warning count, server status, etc). + This function uses text protocol to send a possible result set. + + RETURN + none: in case of success, OK (or result set) packet is sent to the + client, otherwise an error is set in THD */ -void mysql_sql_stmt_execute(THD *thd, LEX_STRING *stmt_name) +void mysql_sql_stmt_execute(THD *thd) { + LEX *lex= thd->lex; Prepared_statement *stmt; - /* - Query text for binary log, or empty string if the query is not put into - binary log. - */ + LEX_STRING *name= &lex->prepared_stmt_name; + /* Query text for binary, general or slow log, if any of them is open */ String expanded_query; - Statement stmt_backup; + DBUG_ENTER("mysql_sql_stmt_execute"); - DBUG_ASSERT(thd->free_list == NULL); - /* See comment for statistic_increment in mysql_stmt_prepare */ - statistic_increment(thd->status_var.com_stmt_execute, &LOCK_status); + DBUG_PRINT("info", ("EXECUTE: %.*s\n", name->length, name->str)); - if (!(stmt= (Prepared_statement*)thd->stmt_map.find_by_name(stmt_name))) + if (!(stmt= (Prepared_statement*) thd->stmt_map.find_by_name(name))) { - my_error(ER_UNKNOWN_STMT_HANDLER, MYF(0), stmt_name->length, - stmt_name->str, "EXECUTE"); + my_error(ER_UNKNOWN_STMT_HANDLER, MYF(0), + name->length, name->str, "EXECUTE"); DBUG_VOID_RETURN; } - if (stmt->param_count != thd->lex->prepared_stmt_params.elements) + if (stmt->param_count != lex->prepared_stmt_params.elements) { my_error(ER_WRONG_ARGUMENTS, MYF(0), "EXECUTE"); DBUG_VOID_RETURN; @@ -2156,78 +2265,15 @@ void mysql_sql_stmt_execute(THD *thd, LEX_STRING *stmt_name) DBUG_PRINT("info",("stmt: %p", stmt)); - /* Must go before setting variables, as it clears thd->user_var_events */ - mysql_reset_thd_for_next_command(thd); - thd->set_n_backup_statement(stmt, &stmt_backup); - if (stmt->set_params_from_vars(stmt, - stmt_backup.lex->prepared_stmt_params, + if (stmt->set_params_from_vars(stmt, lex->prepared_stmt_params, &expanded_query)) { my_error(ER_WRONG_ARGUMENTS, MYF(0), "EXECUTE"); - } - thd->command= COM_STMT_EXECUTE; /* For nice messages in general log */ - execute_stmt(thd, stmt, &expanded_query); - thd->set_statement(&stmt_backup); - DBUG_VOID_RETURN; -} - - -/* - Execute prepared statement. - SYNOPSIS - execute_stmt() - thd Current thread - stmt Statement to execute - expanded_query If binary log is enabled, query string with parameter - placeholders replaced with actual values. Otherwise empty - string. - NOTES - Caller must set parameter values and thd::protocol. -*/ - -static void execute_stmt(THD *thd, Prepared_statement *stmt, - String *expanded_query) -{ - DBUG_ENTER("execute_stmt"); - - reinit_stmt_before_use(thd, stmt->lex); - - if (expanded_query->length() && - alloc_query(thd, (char *)expanded_query->ptr(), - expanded_query->length()+1)) - { - my_error(ER_OUTOFMEMORY, MYF(0), expanded_query->length()); DBUG_VOID_RETURN; } - mysql_log.write(thd, thd->command, "[%lu] %s", stmt->id, thd->query); - /* - At first execution of prepared statement we will perform logical - transformations of the query tree (i.e. negations elimination). - This should be done permanently on the parse tree of this statement. - */ - thd->stmt_arena= stmt; - if (!(specialflag & SPECIAL_NO_PRIOR)) - my_pthread_setprio(pthread_self(),QUERY_PRIOR); - mysql_execute_command(thd); - if (!(specialflag & SPECIAL_NO_PRIOR)) - my_pthread_setprio(pthread_self(), WAIT_PRIOR); - /* - 'start_time' is set in dispatch_command, but THD::query will - be freed when we return from this function. So let's log the slow - query here. - */ - log_slow_statement(thd); - /* Prevent from second logging in the end of dispatch_command */ - thd->enable_slow_log= FALSE; - - close_thread_tables(thd); // to close derived tables - cleanup_stmt_and_thd_after_use(stmt, thd); - reset_stmt_params(stmt); - thd->stmt_arena= thd; + (void) stmt->execute(&expanded_query, FALSE); - if (stmt->state == Query_arena::PREPARED) - stmt->state= Query_arena::EXECUTED; DBUG_VOID_RETURN; } @@ -2237,9 +2283,9 @@ static void execute_stmt(THD *thd, Prepared_statement *stmt, SYNOPSIS mysql_stmt_fetch() - thd Thread handler - packet Packet from client (with stmt_id & num_rows) - packet_length Length of packet + thd Thread handle + packet Packet from client (with stmt_id & num_rows) + packet_length Length of packet */ void mysql_stmt_fetch(THD *thd, char *packet, uint packet_length) @@ -2301,8 +2347,8 @@ void mysql_stmt_fetch(THD *thd, char *packet, uint packet_length) Reset a prepared statement in case there was a recoverable error. SYNOPSIS mysql_stmt_reset() - thd Thread handle - packet Packet with stmt id + thd Thread handle + packet Packet with stmt id DESCRIPTION This function resets statement to the state it was right after prepare. @@ -2310,6 +2356,7 @@ void mysql_stmt_fetch(THD *thd, char *packet, uint packet_length) - clear an error happened during mysql_stmt_send_long_data - cancel long data stream for all placeholders without having to call mysql_stmt_execute. + - close an open cursor Sends 'OK' packet in case of success (statement was reset) or 'ERROR' packet (unrecoverable error/statement not found/etc). */ @@ -2339,7 +2386,7 @@ void mysql_stmt_reset(THD *thd, char *packet) /* Delete a prepared statement from memory. - Note: we don't send any reply to that command. + Note: we don't send any reply to this command. */ void mysql_stmt_close(THD *thd, char *packet) @@ -2350,33 +2397,65 @@ void mysql_stmt_close(THD *thd, char *packet) DBUG_ENTER("mysql_stmt_close"); - statistic_increment(thd->status_var.com_stmt_close, &LOCK_status); if (!(stmt= find_prepared_statement(thd, stmt_id, "mysql_stmt_close"))) DBUG_VOID_RETURN; - /* Statement map deletes statement on erase */ - thd->stmt_map.erase(stmt); + /* + The only way currently a statement can be deallocated when it's + in use is from within Dynamic SQL. + */ + DBUG_ASSERT(! (stmt->flags & Prepared_statement::IS_IN_USE)); + (void) stmt->deallocate(); + DBUG_VOID_RETURN; } /* - Long data in pieces from client + SQLCOM_DEALLOCATE implementation. + + DESCRIPTION + Close an SQL prepared statement. As this can be called from Dynamic + SQL, we should be careful to not close a statement that is currently + being executed. + + RETURN VALUE + none: OK packet is sent in case of success, otherwise an error + message is set in THD +*/ + +void mysql_sql_stmt_close(THD *thd) +{ + Prepared_statement* stmt; + LEX_STRING *name= &thd->lex->prepared_stmt_name; + DBUG_PRINT("info", ("DEALLOCATE PREPARE: %.*s\n", name->length, name->str)); + + if (! (stmt= (Prepared_statement*) thd->stmt_map.find_by_name(name))) + { + my_error(ER_UNKNOWN_STMT_HANDLER, MYF(0), + name->length, name->str, "DEALLOCATE PREPARE"); + return; + } + + if (stmt->deallocate() == 0) + send_ok(thd); +} + +/* + Handle long data in pieces from client. SYNOPSIS mysql_stmt_get_longdata() - thd Thread handle - pos String to append - packet_length Length of string + thd Thread handle + packet String to append + packet_length Length of string DESCRIPTION - Get a part of a long data. - To make the protocol efficient, we are not sending any return packages - here. - If something goes wrong, then we will send the error on 'execute' - - We assume that the client takes care of checking that all parts are sent - to the server. (No checking that we get a 'end of column' in the server) + Get a part of a long data. To make the protocol efficient, we are + not sending any return packets here. If something goes wrong, then + we will send the error on 'execute' We assume that the client takes + care of checking that all parts are sent to the server. (No checking + that we get a 'end of column' in the server is performed). */ void mysql_stmt_get_longdata(THD *thd, char *packet, ulong packet_length) @@ -2436,15 +2515,16 @@ void mysql_stmt_get_longdata(THD *thd, char *packet, ulong packet_length) } -Prepared_statement::Prepared_statement(THD *thd_arg) +Prepared_statement::Prepared_statement(THD *thd_arg, Protocol *protocol_arg) :Statement(INITIALIZED, ++thd_arg->statement_id_counter, thd_arg->variables.query_alloc_block_size, thd_arg->variables.query_prealloc_size), thd(thd_arg), - protocol(&thd_arg->protocol_prep), + protocol(protocol_arg), param_array(0), param_count(0), - last_errno(0) + last_errno(0), + flags(IS_IN_USE) { *last_error= '\0'; } @@ -2475,6 +2555,14 @@ void Prepared_statement::setup_set_params() } +/* + DESCRIPTION + Destroy this prepared statement, cleaning up all used memory + and resources. This is called from ::deallocate() to + handle COM_STMT_CLOSE and DEALLOCATE PREPARE or when + THD ends and all prepared statements are freed. +*/ + Prepared_statement::~Prepared_statement() { DBUG_ENTER("Prepared_statement::~Prepared_statement"); @@ -2527,3 +2615,284 @@ void Prepared_statement::close_cursor() reset_stmt_params(this); DBUG_VOID_RETURN; } + + +bool Prepared_statement::set_name(LEX_STRING *name_arg) +{ + name.length= name_arg->length; + name.str= memdup_root(mem_root, (char*) name_arg->str, name_arg->length); + return name.str == 0; +} + +/************************************************************************** + Common parts of mysql_[sql]_stmt_prepare, mysql_[sql]_stmt_execute. + Essentially, these functions do all the magic of preparing/executing + a statement, leaving network communication, input data handling and + global THD state management to the caller. +***************************************************************************/ + +/* + Parse statement text, validate the statement, and prepare it for execution. + + SYNOPSIS + Prepared_statement::prepare() + packet statement text + packet_len + + DESCRIPTION + You should not change global THD state in this function, if at all + possible: it may be called from any context, e.g. when executing + a COM_* command, and SQLCOM_* command, or a stored procedure. + + NOTES + Precondition. + ------------- + The caller must ensure that thd->change_list and thd->free_list + is empty: this function will not back them up but will free + in the end of its execution. + + Postcondition. + -------------- + thd->mem_root contains unused memory allocated during validation. +*/ + +bool Prepared_statement::prepare(const char *packet, uint packet_len) +{ + bool rc; + Statement stmt_backup; + Query_arena *old_stmt_arena; + DBUG_ENTER("Prepared_statement::prepare"); + /* + If this is an SQLCOM_PREPARE, we also increase Com_prepare_sql. + However, it seems handy if com_stmt_prepare is increased always, + no matter what kind of prepare is processed. + */ + statistic_increment(thd->status_var.com_stmt_prepare, &LOCK_status); + + /* + alloc_query() uses thd->memroot && thd->query, so we should call + both of backup_statement() and backup_query_arena() here. + */ + thd->set_n_backup_statement(this, &stmt_backup); + thd->set_n_backup_active_arena(this, &stmt_backup); + + if (alloc_query(thd, packet, packet_len)) + { + thd->restore_backup_statement(this, &stmt_backup); + thd->restore_active_arena(this, &stmt_backup); + DBUG_RETURN(TRUE); + } + + old_stmt_arena= thd->stmt_arena; + thd->stmt_arena= this; + lex_start(thd, (uchar*) thd->query, thd->query_length); + lex->safe_to_cache_query= FALSE; + lex->stmt_prepare_mode= TRUE; + + rc= yyparse((void *)thd) || thd->is_fatal_error || + thd->net.report_error || init_param_array(this); + /* + While doing context analysis of the query (in check_prepared_statement) + we allocate a lot of additional memory: for open tables, JOINs, derived + tables, etc. Let's save a snapshot of current parse tree to the + statement and restore original THD. In cases when some tree + transformation can be reused on execute, we set again thd->mem_root from + stmt->mem_root (see setup_wild for one place where we do that). + */ + thd->restore_active_arena(this, &stmt_backup); + + /* + If called from a stored procedure, ensure that we won't rollback + external changes when cleaning up after validation. + */ + DBUG_ASSERT(thd->change_list.is_empty()); + /* + If the free_list is not empty, we'll wrongly free some externally + allocated items when cleaning up after validation of the prepared + statement. + */ + DBUG_ASSERT(thd->free_list == NULL); + + if (rc == 0) + rc= check_prepared_statement(this, name.str != 0); + + if (rc && thd->lex->sphead) + { + delete thd->lex->sphead; + thd->lex->sphead= NULL; + } + lex_end(lex); + close_thread_tables(thd); + cleanup_stmt_and_thd_after_use(this, thd); + thd->restore_backup_statement(this, &stmt_backup); + thd->stmt_arena= old_stmt_arena; + + if (rc == 0) + { + setup_set_params(); + init_stmt_after_parse(lex); + state= Query_arena::PREPARED; + flags&= ~IS_IN_USE; + } + DBUG_RETURN(rc); +} + +/* + Execute a prepared statement. + + SYNOPSIS + Prepared_statement::execute() + expanded_query A query for binlogging which has all parameter + markers ('?') replaced with their actual values. + open_cursor True if an attempt to open a cursor should be made. + Currenlty used only in the binary protocol. + + DESCRIPTION + You should not change global THD state in this function, if at all + possible: it may be called from any context, e.g. when executing + a COM_* command, and SQLCOM_* command, or a stored procedure. + + NOTES + Preconditions, postconditions. + ------------------------------ + See the comment for Prepared_statement::prepare(). +*/ + +bool Prepared_statement::execute(String *expanded_query, bool open_cursor) +{ + Statement stmt_backup; + Query_arena *old_stmt_arena; + Item *old_free_list; + bool rc= 1; + + statistic_increment(thd->status_var.com_stmt_execute, &LOCK_status); + + /* Check if we got an error when sending long data */ + if (state == Query_arena::ERROR) + { + my_message(last_errno, last_error, MYF(0)); + return 1; + } + if (flags & IS_IN_USE) + { + my_error(ER_PS_NO_RECURSION, MYF(0)); + return 1; + } + /* In case the command has a call to SP which re-uses this statement name */ + flags|= IS_IN_USE; + + if (cursor && cursor->is_open()) + close_cursor(); + + /* + If the free_list is not empty, we'll wrongly free some externally + allocated items when cleaning up after execution of this statement. + */ + DBUG_ASSERT(thd->change_list.is_empty()); + DBUG_ASSERT(thd->free_list == NULL); + if (open_cursor) + { + if (!lex->result || !lex->result->simple_select()) + { + DBUG_PRINT("info",("Cursor asked for not SELECT stmt")); + /* + If lex->result is set in the parser, this is not a SELECT + statement: we can't open a cursor for it. + */ + my_error(ER_SP_BAD_CURSOR_QUERY, MYF(0)); + goto error; + } + + DBUG_PRINT("info",("Using READ_ONLY cursor")); + if (!cursor && !(cursor= new (mem_root) Cursor(thd))) + goto error; + /* If lex->result is set, mysql_execute_command will use it */ + lex->result= &cursor->result; + protocol= &cursor->protocol; + thd->lock_id= &cursor->lock_id; + /* + Currently cursors can be used only from C API, so + we don't have to create an own memory root for them: + the one in THD is clean and can be used. + */ + } + thd->set_n_backup_statement(this, &stmt_backup); + if (expanded_query->length() && + alloc_query(thd, (char*) expanded_query->ptr(), + expanded_query->length()+1)) + { + my_error(ER_OUTOFMEMORY, 0, expanded_query->length()); + goto error; + } + /* + Expanded query is needed for slow logging, so we want thd->query + to point at it even after we restore from backup. This is ok, as + expanded query was allocated in thd->mem_root. + */ + stmt_backup.query= thd->query; + stmt_backup.query_length= thd->query_length; + + /* + At first execution of prepared statement we may perform logical + transformations of the query tree. Such changes should be performed + on the parse tree of current prepared statement and new items should + be allocated in its memory root. Set the appropriate pointer in THD + to the arena of the statement. + */ + old_stmt_arena= thd->stmt_arena; + thd->stmt_arena= this; + reinit_stmt_before_use(thd, lex); + + thd->protocol= protocol; /* activate stmt protocol */ + mysql_execute_command(thd); + thd->protocol= &thd->protocol_simple; /* use normal protocol */ + + if (cursor && cursor->is_open()) + { + /* + It's safer if we grab THD state after mysql_execute_command is + finished and not in Cursor::open(), because currently the call to + Cursor::open is buried deep in JOIN::exec of the top level join. + */ + cursor->init_from_thd(thd); + + if (cursor->close_at_commit) + thd->stmt_map.add_transient_cursor(this); + } + else + { + close_thread_tables(thd); + cleanup_stmt_and_thd_after_use(this, thd); + reset_stmt_params(this); + } + + thd->set_statement(&stmt_backup); + thd->lock_id= &thd->main_lock_id; + thd->stmt_arena= old_stmt_arena; + + if (state == Query_arena::PREPARED) + state= Query_arena::EXECUTED; + + rc= 0; +error: + thd->lock_id= &thd->main_lock_id; + flags&= ~IS_IN_USE; + return rc; +} + + +/* Common part of DEALLOCATE PREPARE and mysql_stmt_close */ + +bool Prepared_statement::deallocate() +{ + /* We account deallocate in the same manner as mysql_stmt_close */ + statistic_increment(thd->status_var.com_stmt_close, &LOCK_status); + if (flags & IS_IN_USE) + { + my_error(ER_PS_NO_RECURSION, MYF(0)); + return TRUE; + } + /* Statement map calls delete stmt on erase */ + thd->stmt_map.erase(this); + return FALSE; +} diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 90066318c60..c9b41cb495b 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -921,16 +921,11 @@ deallocate: { THD *thd=YYTHD; LEX *lex= thd->lex; - if (thd->command == COM_STMT_PREPARE) + if (lex->stmt_prepare_mode) { yyerror(ER(ER_SYNTAX_ERROR)); YYABORT; } - if (lex->sphead) - { - my_error(ER_SP_BADSTATEMENT, MYF(0), "DEALLOCATE"); - YYABORT; - } lex->sql_command= SQLCOM_DEALLOCATE_PREPARE; lex->prepared_stmt_name= $3; }; @@ -946,16 +941,11 @@ prepare: { THD *thd=YYTHD; LEX *lex= thd->lex; - if (thd->command == COM_STMT_PREPARE) + if (lex->stmt_prepare_mode) { yyerror(ER(ER_SYNTAX_ERROR)); YYABORT; } - if (lex->sphead) - { - my_error(ER_SP_BADSTATEMENT, MYF(0), "PREPARE"); - YYABORT; - } lex->sql_command= SQLCOM_PREPARE; lex->prepared_stmt_name= $2; }; @@ -981,16 +971,11 @@ execute: { THD *thd=YYTHD; LEX *lex= thd->lex; - if (thd->command == COM_STMT_PREPARE) + if (lex->stmt_prepare_mode) { yyerror(ER(ER_SYNTAX_ERROR)); YYABORT; } - if (lex->sphead) - { - my_error(ER_SP_BADSTATEMENT, MYF(0), "EXECUTE"); - YYABORT; - } lex->sql_command= SQLCOM_EXECUTE; lex->prepared_stmt_name= $2; } @@ -1324,11 +1309,8 @@ create: YYTHD->client_capabilities |= CLIENT_MULTI_QUERIES; sp->restore_thd_mem_root(YYTHD); - if (sp->m_multi_results) - { - my_error(ER_SP_NO_RETSET, MYF(0), "trigger"); - YYABORT; - } + if (sp->is_not_allowed_in_function("trigger")) + YYABORT; /* We have to do it after parsing trigger body, because some of @@ -1481,11 +1463,9 @@ create_function_tail: LEX *lex= Lex; sp_head *sp= lex->sphead; - if (sp->m_multi_results) - { - my_error(ER_SP_NO_RETSET, MYF(0), "function"); - YYABORT; - } + if (sp->is_not_allowed_in_function("function")) + YYABORT; + if (sp->check_backpatch(YYTHD)) YYABORT; lex->sql_command= SQLCOM_CREATE_SPFUNCTION; @@ -1735,7 +1715,7 @@ sp_decl: sp->add_instr(i); sp->push_backpatch(i, ctx->push_label((char *)"", 0)); - sp->m_in_handler= TRUE; + sp->m_flags|= sp_head::IN_HANDLER; } sp_hcond_list sp_proc_stmt { @@ -1759,7 +1739,7 @@ sp_decl: sp->push_backpatch(i, lex->spcont->last_label()); /* Block end */ } lex->sphead->backpatch(hlab); - sp->m_in_handler= FALSE; + sp->m_flags&= ~sp_head::IN_HANDLER; $$.vars= $$.conds= $$.curs= 0; $$.hndlrs= $6; ctx->add_handlers($6); @@ -1971,12 +1951,7 @@ sp_proc_stmt: LEX *lex= Lex; sp_head *sp= lex->sphead; - if ((lex->sql_command == SQLCOM_SELECT && !lex->result) || - sp_multi_results_command(lex->sql_command)) - { - /* We maybe have one or more SELECT without INTO */ - sp->m_multi_results= TRUE; - } + sp->m_flags|= sp_get_flags_for_command(lex); if (lex->sql_command == SQLCOM_CHANGE_DB) { /* "USE db" doesn't work in a procedure */ my_error(ER_SP_BADSTATEMENT, MYF(0), "USE"); @@ -2026,14 +2001,14 @@ sp_proc_stmt: i= new sp_instr_freturn(sp->instructions(), lex->spcont, $3, sp->m_returns, lex); sp->add_instr(i); - sp->m_has_return= TRUE; + sp->m_flags|= sp_head::HAS_RETURN; } sp->restore_lex(YYTHD); } | IF sp_if END IF {} | CASE_SYM WHEN_SYM { - Lex->sphead->m_simple_case= FALSE; + Lex->sphead->m_flags&= ~sp_head::IN_SIMPLE_CASE; } sp_case END CASE_SYM {} | CASE_SYM @@ -2053,7 +2028,7 @@ sp_proc_stmt: lex->spcont->push_pvar(&dummy, MYSQL_TYPE_STRING, sp_param_in); lex->sphead->add_instr(i); - lex->sphead->m_simple_case= TRUE; + lex->sphead->m_flags|= sp_head::IN_SIMPLE_CASE; lex->sphead->restore_lex(YYTHD); } sp_case END CASE_SYM @@ -2367,7 +2342,7 @@ sp_case: uint ip= sp->instructions(); sp_instr_jump_if_not *i; - if (! sp->m_simple_case) + if (! (sp->m_flags & sp_head::IN_SIMPLE_CASE)) i= new sp_instr_jump_if_not(ip, ctx, $2, lex); else { /* Simple case: <caseval> = <whenval> */ |