1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
|
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 (internal code -6). For more details, run SHOW WARNINGS
show warnings;
Level Code Message
Warning 1601 Creation context of stored routine `test`.`bug14233_1` is invalid
Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select count(*) from mysql.user' at line 3
Error 1457 Failed to load routine test.bug14233_1 (internal code -6). For more details, run SHOW WARNINGS
create view v1 as select bug14233_1();
ERROR HY000: Failed to load routine test.bug14233_1 (internal code -6). For more details, run SHOW WARNINGS
show warnings;
Level Code Message
Warning 1601 Creation context of stored routine `test`.`bug14233_1` is invalid
Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select count(*) from mysql.user' at line 3
Error 1457 Failed to load routine test.bug14233_1 (internal code -6). For more details, run SHOW WARNINGS
select bug14233_2();
ERROR HY000: Failed to load routine test.bug14233_2 (internal code -6). For more details, run SHOW WARNINGS
show warnings;
Level Code Message
Warning 1601 Creation context of stored routine `test`.`bug14233_2` is invalid
Error 1320 No RETURN found in FUNCTION test.bug14233_2
Error 1457 Failed to load routine test.bug14233_2 (internal code -6). For more details, run SHOW WARNINGS
create view v1 as select bug14233_2();
ERROR HY000: Failed to load routine test.bug14233_2 (internal code -6). For more details, run SHOW WARNINGS
show warnings;
Level Code Message
Warning 1601 Creation context of stored routine `test`.`bug14233_2` is invalid
Error 1320 No RETURN found in FUNCTION test.bug14233_2
Error 1457 Failed to load routine test.bug14233_2 (internal code -6). For more details, run SHOW WARNINGS
call bug14233_3();
ERROR HY000: Failed to load routine test.bug14233_3 (internal code -6). For more details, run SHOW WARNINGS
show warnings;
Level Code Message
Warning 1601 Creation context of stored routine `test`.`bug14233_3` is invalid
Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'wpsj sa ^#!@ ' at line 3
Error 1457 Failed to load routine test.bug14233_3 (internal code -6). For more details, run SHOW WARNINGS
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 (internal code -6). For more details, run SHOW WARNINGS
show warnings;
Level Code Message
Warning 1601 Creation context of stored routine `test`.`bug14233_3` is invalid
Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'wpsj sa ^#!@ ' at line 3
Error 1457 Failed to load routine test.bug14233_3 (internal code -6). For more details, run SHOW WARNINGS
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;
|
