EVOLUTION-MANAGER

Edit File: sp-destruct.result

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; create procedure p1() set @foo = 10; alter table mysql.proc drop primary key; drop procedure p1; ERROR HY000: Cannot load from mysql.proc. The table is probably corrupted alter table mysql.proc add primary key (db,name,type); drop procedure p1; # Start of 10.3 tests # # 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;