sp_notembedded.test

# The include statement below is a temp one for tests that are yet to
#be ported to run with InnoDB,
#but needs to be kept for tests that would need MyISAM in future.
--source include/force_myisam_default.inc

# Can't test with embedded server
-- source include/not_embedded.inc
# Disable concurrent inserts to avoid test failures
set @old_concurrent_insert= @@global.concurrent_insert;
set @@global.concurrent_insert= 0;

# Save the initial number of concurrent sessions
--source include/count_sessions.inc

--disable_warnings
drop table if exists t1,t3;
--enable_warnings
delimiter |;


#
# Bug#4902 Stored procedure with SHOW WARNINGS leads to packet error
#
# Added tests for show grants command
--disable_warnings
drop procedure if exists bug4902|
--enable_warnings
create procedure bug4902()
begin
  show grants for 'root'@'localhost';
end|
--disable_parsing
show binlog events|
show storage engines|
show master status|
show slave hosts|
show slave status|
--enable_parsing

call bug4902()|
call bug4902()|

drop procedure bug4902|

# We need separate SP for SHOW PROCESSLIST  since we want use replace_column
--disable_warnings
drop procedure if exists bug4902_2|
--enable_warnings
create procedure bug4902_2()
begin
  show processlist;
end|
--disable_result_log
call bug4902_2()|
--enable_result_log
show warnings|
--disable_result_log
call bug4902_2()|
--enable_result_log
show warnings|
drop procedure bug4902_2|

#
# Bug#3583 query cache doesn't work for stored procedures
#
--disable_warnings
drop table if exists t1|
--enable_warnings
create table t1 (
    id   char(16) not null default '',
    data int not null
)|
--disable_warnings
drop procedure if exists bug3583|
--enable_warnings
--disable_warnings
drop procedure if exists bug3583|
--enable_warnings
create procedure bug3583()
begin
  declare c int;

  select * from t1;
  select count(*) into c from t1;
  select c;
end|

insert into t1 values ("x", 3), ("y", 5)|
set @x = @@query_cache_size|
set global query_cache_size = 10*1024*1024|

flush status|
flush query cache|
show status like 'Qcache_hits'|
call bug3583()|
show status like 'Qcache_hits'|
call bug3583()|
call bug3583()|
show status like 'Qcache_hits'|

set global query_cache_size = @x|
flush status|
flush query cache|
delete from t1|
drop procedure bug3583|
drop table t1|


#
# Bug#6807 Stored procedure crash if CREATE PROCEDURE ... KILL QUERY
#
--disable_warnings
drop procedure if exists bug6807|
--enable_warnings
create procedure bug6807()
begin
  declare id int;

  set id = connection_id();
  kill query id;
  select 'Not reached';
end|

--error ER_QUERY_INTERRUPTED
call bug6807()|
--error ER_QUERY_INTERRUPTED
call bug6807()|

drop procedure bug6807|


#
# Bug#10100 function (and stored procedure?) recursivity problem
#
SET sql_mode = 'NO_ENGINE_SUBSTITUTION'|
--disable_warnings
drop function if exists bug10100f|
drop procedure if exists bug10100p|
drop procedure if exists bug10100t|
drop procedure if exists bug10100pt|
drop procedure if exists bug10100pv|
drop procedure if exists bug10100pd|
drop procedure if exists bug10100pc|
--enable_warnings
# routines with simple recursion
create function bug10100f(prm int) returns int
begin
  if prm > 1 then
    return prm * bug10100f(prm - 1);
  end if;
  return 1;
end|
create procedure bug10100p(prm int, inout res int)
begin
  set res = res * prm;
  if prm > 1 then
    call bug10100p(prm - 1, res);
  end if;
end|
create procedure bug10100t(prm int)
begin
  declare res int;
  set res = 1;
  call bug10100p(prm, res);
  select res;
end|

# a procedure which use tables and recursion
create table t3 (a int)|
insert into t3 values (0)|
create view v1 as select a from t3;
create procedure bug10100pt(level int, lim int)
begin
  if level < lim then
    update t3 set a=level;
    FLUSH TABLES;
    call bug10100pt(level+1, lim);
  else
    select * from t3;
  end if;
end|
# view & recursion
create procedure bug10100pv(level int, lim int)
begin
  if level < lim then
    update v1 set a=level;
    FLUSH TABLES;
    call bug10100pv(level+1, lim);
  else
    select * from v1;
  end if;
end|
# dynamic sql & recursion
prepare stmt2 from "select * from t3;";
create procedure bug10100pd(level int, lim int)
begin
  if level < lim then
    select level;
    prepare stmt1 from "update t3 set a=a+2";
    execute stmt1;
    FLUSH TABLES;
    execute stmt1;
    FLUSH TABLES;
    execute stmt1;
    FLUSH TABLES;
    deallocate prepare stmt1;
    execute stmt2;
    select * from t3;
    call bug10100pd(level+1, lim);
  else
    execute stmt2;
  end if;
end|
# cursor & recursion
create procedure bug10100pc(level int, lim int)
begin
  declare lv int;
  declare c cursor for select a from t3;
  open c;
  if level < lim then
    select level;
    fetch c into lv;
    select lv;
    update t3 set a=level+lv;
    FLUSH TABLES;
    call bug10100pc(level+1, lim);
  else
    select * from t3;
  end if;
  close c;
end|

# end of the stack checking
set @@max_sp_recursion_depth=255|
set @var=1|
# disable log because error about stack overrun contains numbers which
# depend on a system
-- disable_result_log
-- error ER_STACK_OVERRUN_NEED_MORE
call bug10100p(255, @var)|
-- error ER_STACK_OVERRUN_NEED_MORE
call bug10100pt(1,255)|
-- error ER_STACK_OVERRUN_NEED_MORE
call bug10100pv(1,255)|
-- error ER_STACK_OVERRUN_NEED_MORE
call bug10100pd(1,255)|
-- error ER_STACK_OVERRUN_NEED_MORE
call bug10100pc(1,255)|
-- enable_result_log
set @@max_sp_recursion_depth=0|

deallocate prepare stmt2|

drop function bug10100f|
drop procedure bug10100p|
drop procedure bug10100t|
drop procedure bug10100pt|
drop procedure bug10100pv|
drop procedure bug10100pd|
drop procedure bug10100pc|
drop view v1|
drop table t3|

delimiter ;|
SET sql_mode = default;


#
# Bug#15298 SHOW GRANTS FOR CURRENT_USER: Incorrect output in DEFINER context
#
--disable_warnings
drop procedure if exists bug15298_1;
drop procedure if exists bug15298_2;
--enable_warnings
set @orig_sql_mode= @@sql_mode;
set sql_mode= (select replace(@@sql_mode,'NO_AUTO_CREATE_USER',''));
grant all privileges on test.* to 'mysqltest_1'@'localhost';
set sql_mode= @orig_sql_mode;
create procedure 15298_1 () sql security definer show grants for current_user;
create procedure 15298_2 () sql security definer show grants;

connect (con1,localhost,mysqltest_1,,test);
call 15298_1();
call 15298_2();

connection default;
disconnect con1;
drop user mysqltest_1@localhost;
drop procedure 15298_1;
drop procedure 15298_2;

#
# Bug#29936 Stored Procedure DML ignores low_priority_updates setting
#

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

create table t1 (value varchar(15));
create procedure p1() update t1 set value='updated' where value='old';

# load the procedure into sp cache and execute once
call p1();

insert into t1 (value) values ("old");

connect (rl_holder, localhost, root,,);
connect (rl_acquirer, localhost, root,,);
connect (rl_contender, localhost, root,,);
connect (rl_wait, localhost, root,,);

connection rl_holder;
select get_lock('b26162',120);

connection rl_acquirer;
--send select 'rl_acquirer', value from t1 where get_lock('b26162',120);

# we must wait till this select opens and locks the tables
connection rl_wait;
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "User lock" and
  info = "select 'rl_acquirer', value from t1 where get_lock('b26162',120)";
--source include/wait_condition.inc

connection default;
set session low_priority_updates=on;
--send call p1();

connection rl_wait;
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "Waiting for table level lock" and
  info = "update t1 set value='updated' where value='old'";
--source include/wait_condition.inc

connection rl_contender;
select 'rl_contender', value from t1;

connection rl_holder;
select release_lock('b26162');

connection rl_acquirer;
--reap
connection default;
--reap

disconnect rl_holder;
disconnect rl_acquirer;
disconnect rl_wait;
disconnect rl_contender;
drop procedure p1;
drop table t1;
set session low_priority_updates=default;

#
# Bug#44798 MySQL engine crashes when creating stored procedures with execute_priv=N
#
INSERT INTO mysql.user (Host, User, Select_priv, Insert_priv, Update_priv,
Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv,
Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv,
Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv,
Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv,
Create_user_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions,
max_updates, max_connections, max_user_connections) 
VALUES('%', 'mysqltest_1', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N',
'N', 'N', 'N', 'Y', 'Y', 'N', 'N', 'Y', 'Y', 'N', 'N', 'N', 'N', 'N', 'Y', 'Y', 'N', '',
'', '', '', '0', '0', '0', '0');
FLUSH PRIVILEGES;

connect (con1, localhost, mysqltest_1,,);
connection con1;
CREATE PROCEDURE p1(i INT) BEGIN END;
disconnect con1;
connection default;
DROP PROCEDURE p1;

DELETE FROM mysql.user WHERE User='mysqltest_1';
FLUSH PRIVILEGES;


--echo #
--echo # Bug#44521 Prepared Statement: CALL p() - crashes: `! thd->main_da.is_sent' failed et.al.
--echo #
SELECT GET_LOCK('Bug44521', 0);
--connect (con1,localhost,root,,)
--echo ** Connection con1
delimiter $;
CREATE PROCEDURE p()
BEGIN
  SELECT 1;
  SELECT GET_LOCK('Bug44521', 100);
  SELECT 2;
END$
delimiter ;$
--send CALL p();
--connection default
--echo ** Default connection
let $wait_condition=
  SELECT count(*) = 1 FROM information_schema.processlist
  WHERE state = "User lock" AND info = "SELECT GET_LOCK('Bug44521', 100)";
--source include/wait_condition.inc
let $conid =
  `SELECT id FROM information_schema.processlist
   WHERE state = "User lock" AND info = "SELECT GET_LOCK('Bug44521', 100)"`;
dirty_close con1;
SELECT RELEASE_LOCK('Bug44521');
let $wait_condition=
  SELECT count(*) = 0 FROM information_schema.processlist
  WHERE id = $conid;
--source include/wait_condition.inc 
DROP PROCEDURE p;

#
# Bug#47736 killing a select from a view when the view is processing a function, asserts
#
CREATE TABLE t1(a int);
INSERT INTO t1 VALUES (1);
CREATE FUNCTION f1 (inp TEXT) RETURNS INT NO SQL RETURN GET_LOCK('Bug47736', 200);
CREATE VIEW v1 AS SELECT f1('a') FROM t1;

SELECT GET_LOCK('Bug47736', 0);

--connect (con1, localhost, root,,)
--let $ID_1= `SELECT connection_id()`
--send SELECT * FROM v1;

--connection default
let $wait_condition=
  SELECT count(*) = 1 FROM information_schema.processlist
  WHERE state = "User lock" AND info = "SELECT * FROM v1";
--source include/wait_condition.inc
--replace_result $ID_1 ID
--eval KILL QUERY $ID_1

--connection con1
--error ER_QUERY_INTERRUPTED
--reap

--connection default
DROP VIEW v1;
DROP TABLE t1;
DROP FUNCTION f1;
--disconnect con1

--echo # ------------------------------------------------------------------
--echo # -- End of 5.1 tests
--echo # ------------------------------------------------------------------

--echo #
--echo # Test for bug#11763757 "56510: ERROR 42000: FUNCTION DOES NOT EXIST
--echo # IF NOT-PRIV USER RECONNECTS ".
--echo #
--echo # The real problem was that server was unable handle properly stored
--echo # functions in databases which names contained dot.
--echo #

connection default;

--disable_warnings
DROP DATABASE IF EXISTS `my.db`;
--enable_warnings

create database `my.db`;
use `my.db`;

CREATE FUNCTION f1(a int) RETURNS INT RETURN a;

--echo # Create new connection.
connect (addcon, localhost, root,,);
connection addcon;
USE `my.db`;
SELECT f1(1);
SELECT `my.db`.f1(2);

--echo # Switching to default connection.
connection default;
disconnect addcon;
DROP DATABASE `my.db`;
USE test;


--echo #
--echo # Bug#11763507 - 56224: FUNCTION NAME IS CASE-SENSITIVE
--echo #
SET @@SQL_MODE = '';
DELIMITER $;

CREATE EVENT teste_bug11763507 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR 
DO SELECT 1 $

DELIMITER ;$
# EVENTS
--replace_column 6 # 7 # 8 # 9 #
SHOW EVENTS LIKE 'teste_bug11763507';
--replace_column 6 # 7 # 8 # 9 #
SHOW EVENTS LIKE 'TESTE_bug11763507';

--replace_column 4 # 
SHOW CREATE EVENT teste_bug11763507;
--replace_column 4 # 
SHOW CREATE EVENT TESTE_bug11763507;

DROP EVENT teste_bug11763507;
--echo #END OF BUG#11763507 test.

--echo
--echo # WL#4179: Stored programs: validation of stored program statements
--echo #
--echo # Test handle of metadata changes with events.
--echo

CREATE TABLE t1 (a INT, b INT);
CREATE TABLE t2 (a INT, b INT);
CREATE TABLE t3 (a INT);

INSERT INTO t1 VALUES (1, 2);

SET GLOBAL EVENT_SCHEDULER = ON;

SELECT GET_LOCK('e1_lock', 60);

delimiter |;

CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND STARTS NOW() DO
BEGIN
  DECLARE EXIT HANDLER FOR 1136 BEGIN
    INSERT INTO t3 VALUES (1);
  END; -- ER_WRONG_VALUE_COUNT_ON_ROW

  SELECT GET_LOCK('e1_lock', 60);
  SELECT RELEASE_LOCK('e1_lock');

  INSERT INTO t2 SELECT * FROM t1;
END|

delimiter ;|

SELECT RELEASE_LOCK('e1_lock');

let $wait_condition = SELECT COUNT(*) >= 3 FROM t2;
--source include/wait_condition.inc

SELECT GET_LOCK('e1_lock', 60);

ALTER TABLE t1 ADD COLUMN (c INT);

SELECT RELEASE_LOCK('e1_lock');

--echo # Wait for new rows in t3. That means, the even has been executed,
--echo # and INSERT INTO t2 failed because now t1 has 3 columns.
let $wait_condition = SELECT COUNT(*) > 0 FROM t3;
--source include/wait_condition.inc

DROP EVENT e1;
DROP TABLE t1, t2, t3;

SET GLOBAL EVENT_SCHEDULER = OFF;


#
# Restore global concurrent_insert value. Keep in the end of the test file.
#

set @@global.concurrent_insert= @old_concurrent_insert;

# Wait till all disconnects are completed
--source include/wait_until_count_sessions.inc


--echo #
--echo # WL#2284: Increase the length of a user name
--echo #

CREATE DATABASE test1;
CREATE TABLE test1.t1 (
  int_field INTEGER UNSIGNED NOT NULL,
  char_field CHAR(10),
  INDEX(`int_field`)
);

CREATE USER user_name_len_16@localhost;
CREATE USER user_name_len_22_01234@localhost;
CREATE USER user_name_len_32_012345678901234@localhost;

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_name_len_16@localhost;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user_name_len_22_01234@localhost;

--echo # Check that user_name_len16 has no SELECT permission ON t1
connect (con_user_16,localhost,user_name_len_16,,test);
--error ER_TABLEACCESS_DENIED_ERROR
SELECT * FROM test1.t1;

--echo # Check that user_name_len_22_01234 has no SELECT permission ON t1
connect (con_user_22,localhost,user_name_len_22_01234,,test);
--error ER_TABLEACCESS_DENIED_ERROR
SELECT * FROM test1.t1;

connection default;
GRANT SELECT ON test1.t1 TO user_name_len_22_01234@localhost;

--echo # Check that user_name_len_22_01234 has *now* SELECT permission ON t1
connection con_user_22;
SELECT * FROM test1.t1;

connection default;

CREATE DEFINER=user_name_len_22_01234@localhost PROCEDURE test1.p1_len22()
  SELECT * FROM test1.t1;

GRANT EXECUTE ON PROCEDURE test1.p1_len22 TO user_name_len_22_01234@localhost;
GRANT EXECUTE ON PROCEDURE test1.p1_len22 TO user_name_len_16@localhost;

--echo # Now user_name_len_16 should be able to SELECT FROM t1 by calling
--echo # p1_len22 procedure

connection con_user_16;
CALL test1.p1_len22();

--error ER_WRONG_STRING_LENGTH
CREATE DEFINER=user_name_len_33_0123456789012345@localhost PROCEDURE test1.p1_len33()
  SELECT * FROM test1.t1;

connection default;

--echo # REVOKE the SELECT permission from user_name_len_22_01234
REVOKE SELECT ON test1.t1 FROM user_name_len_22_01234@localhost;

--echo # and GRANT it to user_name_len_32_012345678901234
GRANT SELECT ON test1.t1 TO user_name_len_32_012345678901234@localhost;

CREATE DEFINER = user_name_len_32_012345678901234@localhost FUNCTION test1.f1_len32() RETURNS INT
 RETURN (SELECT COUNT(*) FROM test1.t1);

GRANT EXECUTE ON FUNCTION test1.f1_len32 TO user_name_len_32_012345678901234@localhost;
GRANT EXECUTE ON FUNCTION test1.f1_len32 TO user_name_len_22_01234@localhost;

GRANT SELECT ON test1.t1 TO user_name_len_22_01234@localhost;
--echo # Now user_name_len_22_01234 should be able to SELECT COUNT(*) FROM t1
--echo # by calling f1_len32 function
connection con_user_22;
SELECT test1.f1_len32();

connection default;

--echo # Recreate test1.p1_len22, this time with Security Context set to INVOKER
DROP PROCEDURE test1.p1_len22;
--echo # Make sure user_name_len_22_01234 has SELECT privileges on test.t1
connection con_user_22;
SELECT * FROM test1.t1;
connection default;

CREATE DEFINER=user_name_len_22_01234@localhost PROCEDURE test1.p1_len22()
SQL SECURITY INVOKER
  SELECT * FROM test1.t1;

GRANT EXECUTE ON PROCEDURE test1.p1_len22 TO user_name_len_22_01234@localhost;
GRANT EXECUTE ON PROCEDURE test1.p1_len22 TO user_name_len_16@localhost;

--echo # Now user_name_len_16 should *NOT* be able to SELECT COUNT(*) FROM t1
--echo # by calling f1_len22 function as this function is using the INVOKER
--echo # security context

connection con_user_16;
--error ER_TABLEACCESS_DENIED_ERROR
CALL test1.p1_len22();

connection default;
--error ER_WRONG_STRING_LENGTH
CREATE DEFINER=user_name_len_33_0123456789012345@localhost FUNCTION test1.f1_len33() RETURNS INT
  RETURN (SELECT COUNT(*) FROM test1.t1);

# Cleanup
disconnect con_user_16;
disconnect con_user_22;

DROP DATABASE test1;

DROP USER user_name_len_16@localhost;
DROP USER user_name_len_22_01234@localhost;
DROP USER user_name_len_32_012345678901234@localhost;