# # Destructive stored procedure tests # # We do horrible things to the mysql.proc table here, so any unexpected # failures here might leave it in an undetermined state. # # In the case of trouble you might want to skip this. # # embedded server returns different paths in error messages # in lines like 'call bug14233();' # mysqltest should be fixed to allow REPLACE_RESULT in error message -- source include/not_embedded.inc # We're using --system things that probably doesn't work on Windows. --source include/not_windows.inc # Backup proc table --system rm -rf $MYSQLTEST_VARDIR/master-data/mysql/backup --system mkdir $MYSQLTEST_VARDIR/master-data/mysql/backup --system cp $MYSQLTEST_VARDIR/master-data/mysql/proc.* $MYSQLTEST_VARDIR/master-data/mysql/backup/ use test; --disable_warnings drop procedure if exists bug14233; drop function if exists bug14233; drop table if exists t1; drop view if exists v1; --enable_warnings create procedure bug14233() set @x = 42; create function bug14233_f() returns int return 42; create table t1 (id int); create trigger t1_ai after insert on t1 for each row call bug14233(); # Unsupported tampering with the mysql.proc definition alter table mysql.proc drop type; --replace_result $MYSQL_TEST_DIR . --error ER_SP_PROC_TABLE_CORRUPT call bug14233(); --replace_result $MYSQL_TEST_DIR . --error ER_SP_PROC_TABLE_CORRUPT create view v1 as select bug14233_f(); --replace_result $MYSQL_TEST_DIR . --error ER_SP_PROC_TABLE_CORRUPT insert into t1 values (0); flush table mysql.proc; # Thrashing the .frm file --system echo 'saljdlfa' > $MYSQLTEST_VARDIR/master-data/mysql/proc.frm --error ER_NOT_FORM_FILE call bug14233(); --error ER_NOT_FORM_FILE create view v1 as select bug14233_f(); --error ER_NOT_FORM_FILE insert into t1 values (0); flush table mysql.proc; # Drop the mysql.proc table --system rm $MYSQLTEST_VARDIR/master-data/mysql/proc.* --error ER_NO_SUCH_TABLE call bug14233(); --error ER_NO_SUCH_TABLE create view v1 as select bug14233_f(); --error ER_NO_SUCH_TABLE insert into t1 values (0); # Restore mysql.proc --system mv $MYSQLTEST_VARDIR/master-data/mysql/backup/* $MYSQLTEST_VARDIR/master-data/mysql/ --system rmdir $MYSQLTEST_VARDIR/master-data/mysql/backup flush table mysql.proc; flush privileges; delete from mysql.proc where name like 'bug14233%'; # Unsupported editing of mysql.proc, circumventing checks in "create ..." insert into mysql.proc ( db, name, type, specific_name, language, sql_data_access, is_deterministic, security_type, param_list, returns, body, definer, created, modified, sql_mode, comment ) values ( 'test', 'bug14233_1', 'FUNCTION', 'bug14233_1', 'SQL', 'READS_SQL_DATA', 'NO', 'DEFINER', '', 'int(10)', 'select count(*) from mysql.user', 'root@localhost', NOW() , '0000-00-00 00:00:00', '', '' ), ( 'test', 'bug14233_2', 'FUNCTION', 'bug14233_2', 'SQL', 'READS_SQL_DATA', 'NO', 'DEFINER', '', 'int(10)', 'begin declare x int; select count(*) into x from mysql.user; end', 'root@localhost', NOW() , '0000-00-00 00:00:00', '', '' ), ( 'test', 'bug14233_3', 'PROCEDURE', 'bug14233_3', 'SQL', 'READS_SQL_DATA','NO', 'DEFINER', '', '', 'alksj wpsj sa ^#!@ ', 'root@localhost', NOW() , '0000-00-00 00:00:00', '', '' ); --error ER_SP_PROC_TABLE_CORRUPT select bug14233_1(); --error ER_SP_PROC_TABLE_CORRUPT create view v1 as select bug14233_1(); --error ER_SP_PROC_TABLE_CORRUPT select bug14233_2(); --error ER_SP_PROC_TABLE_CORRUPT create view v1 as select bug14233_2(); --error ER_SP_PROC_TABLE_CORRUPT call bug14233_3(); drop trigger t1_ai; create trigger t1_ai after insert on t1 for each row call bug14233_3(); --error ER_SP_PROC_TABLE_CORRUPT insert into t1 values (0); # Clean-up delete from mysql.proc where name like 'bug14233%'; drop trigger t1_ai; drop table t1;