diff options
Diffstat (limited to 'mysql-test/main/sp-destruct.result')
| -rw-r--r-- | mysql-test/main/sp-destruct.result | 230 |
1 files changed, 230 insertions, 0 deletions
diff --git a/mysql-test/main/sp-destruct.result b/mysql-test/main/sp-destruct.result new file mode 100644 index 00000000000..a5f96882a37 --- /dev/null +++ b/mysql-test/main/sp-destruct.result @@ -0,0 +1,230 @@ +call mtr.add_suppression("Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted"); +call mtr.add_suppression("Stored routine .test...bug14233_[123].: invalid value in column mysql.proc"); +flush table mysql.proc; +use test; +drop procedure if exists bug14233; +drop function if exists bug14233; +drop table if exists t1; +drop view if exists v1; +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(); +alter table mysql.proc drop security_type; +call bug14233(); +ERROR HY000: Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted +create view v1 as select bug14233_f(); +ERROR HY000: Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted +insert into t1 values (0); +ERROR HY000: Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted +show procedure status; +ERROR HY000: Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted +flush table mysql.proc; +call bug14233(); +ERROR HY000: Incorrect information in file: './mysql/proc.frm' +create view v1 as select bug14233_f(); +ERROR HY000: Incorrect information in file: './mysql/proc.frm' +insert into t1 values (0); +ERROR HY000: Incorrect information in file: './mysql/proc.frm' +flush table mysql.proc; +call bug14233(); +ERROR 42S02: Table 'mysql.proc' doesn't exist +create view v1 as select bug14233_f(); +ERROR 42S02: Table 'mysql.proc' doesn't exist +insert into t1 values (0); +ERROR 42S02: Table 'mysql.proc' doesn't exist +flush table mysql.proc; +flush privileges; +delete from mysql.proc where name like 'bug14233%'; +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, character_set_client, collation_connection, db_collation, +body_utf8 +) +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', '', '', +'', '', '', +'select count(*) from mysql.user' +), +( +'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', '', '', +'', '', '', +'begin declare x int; select count(*) into x from mysql.user; end' +), +( +'test', 'bug14233_3', 'PROCEDURE', 'bug14233_3', 'SQL', 'READS_SQL_DATA','NO', +'DEFINER', '', '', +'alksj wpsj sa ^#!@ ', +'root@localhost', NOW() , '0000-00-00 00:00:00', '', '', +'', '', '', +'alksj wpsj sa ^#!@ ' +); +select bug14233_1(); +ERROR HY000: Failed to load routine test.bug14233_1. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) +create view v1 as select bug14233_1(); +ERROR HY000: Failed to load routine test.bug14233_1. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) +select bug14233_2(); +ERROR HY000: Failed to load routine test.bug14233_2. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) +create view v1 as select bug14233_2(); +ERROR HY000: Failed to load routine test.bug14233_2. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) +call bug14233_3(); +ERROR HY000: Failed to load routine test.bug14233_3. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) +drop trigger t1_ai; +create trigger t1_ai after insert on t1 for each row call bug14233_3(); +insert into t1 values (0); +ERROR HY000: Failed to load routine test.bug14233_3. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) +drop trigger t1_ai; +drop table t1; +drop function bug14233_1; +drop function bug14233_2; +drop procedure bug14233_3; +show procedure status where db=DATABASE(); +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +show function status where db=DATABASE(); +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +DROP TABLE IF EXISTS proc_backup; +DROP PROCEDURE IF EXISTS p1; +# Backup the proc table +RENAME TABLE mysql.proc TO proc_backup; +CREATE TABLE mysql.proc LIKE proc_backup; +FLUSH TABLE mysql.proc; +# Test with a valid table. +CREATE PROCEDURE p1() +SET @foo = 10; +CALL p1(); +SHOW PROCEDURE STATUS; +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +test p1 PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci +# Modify a field of the table. +ALTER TABLE mysql.proc MODIFY comment CHAR (32); +CREATE PROCEDURE p2() +SET @foo = 10; +ERROR HY000: Cannot load from mysql.proc. The table is probably corrupted +# Procedure loaded from the cache +CALL p1(); +SHOW PROCEDURE STATUS; +ERROR HY000: Cannot load from mysql.proc. The table is probably corrupted +DROP TABLE mysql.proc; +RENAME TABLE proc_backup TO mysql.proc; +FLUSH TABLE mysql.proc; +# +# Bug#51376 Assert `! is_set()' failed in +# Diagnostics_area::set_ok_status on DROP FUNCTION +# +DROP FUNCTION IF EXISTS f1; +CREATE FUNCTION f1() RETURNS INT RETURN 1; +# Backup the procs_priv table +RENAME TABLE mysql.procs_priv TO procs_priv_backup; +FLUSH TABLE mysql.procs_priv; +DROP FUNCTION f1; +SHOW WARNINGS; +Level Code Message +# Restore the procs_priv table +RENAME TABLE procs_priv_backup TO mysql.procs_priv; +FLUSH TABLE mysql.procs_priv; +# +# Bug #56137 "Assertion `thd->lock == 0' failed on upgrading from +# 5.1.50 to 5.5.6". +# +drop database if exists mysqltest; +# Backup mysql.proc. +flush table mysql.proc; +create database mysqltest; +# Corrupt mysql.proc to make it unusable by current version of server. +alter table mysql.proc drop column security_type; +# The below statement should not cause assertion failure. +drop database mysqltest; +Warnings: +Error 1805 Column count of mysql.proc is wrong. Expected 21, found 20. The table is probably corrupted +# Restore mysql.proc. +drop table mysql.proc; +# +# Bug#58414 mysql_upgrade fails on dump upgrade between 5.1.53 -> 5.5.8 +# +DROP TABLE IF EXISTS proc_backup; +DROP DATABASE IF EXISTS db1; +# Backup the proc table +RENAME TABLE mysql.proc TO proc_backup; +CREATE TABLE mysql.proc LIKE proc_backup; +CREATE DATABASE db1; +CREATE PROCEDURE db1.p1() SET @foo = 10; +# Modify a field of the table. +ALTER TABLE mysql.proc MODIFY comment CHAR (32); +DROP DATABASE db1; +Warnings: +Error 1728 Cannot load from mysql.proc. The table is probably corrupted +# Restore mysql.proc +DROP TABLE mysql.proc; +RENAME TABLE proc_backup TO mysql.proc; +create database mysqltest1; +create procedure mysqltest1.foo() select "foo"; +update mysql.proc set name='' where db='mysqltest1'; +drop database mysqltest1; +# +# MDEV-15444 Querying I_S.PARAMETERS can crash with a corrupted mysql.proc +# +CREATE OR REPLACE FUNCTION f1 (a INT) RETURNS INT RETURN 10; +CREATE OR REPLACE FUNCTION f2 (a INT) RETURNS INT RETURN 10; +SELECT +@type0:=COLUMN_TYPE AS t0, +@type1:=REPLACE(COLUMN_TYPE,')',',''XXX'')') AS t1 +FROM INFORMATION_SCHEMA.COLUMNS +WHERE table_schema='mysql' AND table_name='proc' AND column_name='type'; +t0 enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') +t1 enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY','XXX') +EXECUTE IMMEDIATE CONCAT('ALTER TABLE mysql.proc MODIFY type ', @type1); +SHOW COLUMNS IN mysql.proc LIKE 'type'; +Field Type Null Key Default Extra +type enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY','XXX') NO PRI NULL +UPDATE mysql.proc SET type='XXX' WHERE name='f1' AND db='test'; +SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_SCHEMA='test'; +SPECIFIC_CATALOG def +SPECIFIC_SCHEMA test +SPECIFIC_NAME f2 +ORDINAL_POSITION 0 +PARAMETER_MODE NULL +PARAMETER_NAME NULL +DATA_TYPE int +CHARACTER_MAXIMUM_LENGTH NULL +CHARACTER_OCTET_LENGTH NULL +NUMERIC_PRECISION 10 +NUMERIC_SCALE 0 +DATETIME_PRECISION NULL +CHARACTER_SET_NAME NULL +COLLATION_NAME NULL +DTD_IDENTIFIER int(11) +ROUTINE_TYPE FUNCTION +SPECIFIC_CATALOG def +SPECIFIC_SCHEMA test +SPECIFIC_NAME f2 +ORDINAL_POSITION 1 +PARAMETER_MODE IN +PARAMETER_NAME a +DATA_TYPE int +CHARACTER_MAXIMUM_LENGTH NULL +CHARACTER_OCTET_LENGTH NULL +NUMERIC_PRECISION 10 +NUMERIC_SCALE 0 +DATETIME_PRECISION NULL +CHARACTER_SET_NAME NULL +COLLATION_NAME NULL +DTD_IDENTIFIER int(11) +ROUTINE_TYPE FUNCTION +UPDATE mysql.proc SET type='FUNCTION' WHERE name='f1' AND db='test'; +EXECUTE IMMEDIATE CONCAT('ALTER TABLE mysql.proc MODIFY type ', @type0); +SHOW COLUMNS IN mysql.proc LIKE 'type'; +Field Type Null Key Default Extra +type enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') NO PRI NULL +DROP FUNCTION f1; +DROP FUNCTION f2; |