Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.1.44, 10.4.13, 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
None
Description
When changing the character_set_database variables value, and then creating a stored procedure with a VARCHAR parameter with COLLATE but without CHARACTER SET, calling the procedure leads to
ERROR 1457 (HY000): Failed to load routine test.p1. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
The mysql.proc table is not corrupted, and there are no messages in the error log.
Test case (assuming that the 'test' database was created with default 'latin1' character set:
SET CHARACTER_SET_DATABASE=utf8;
|
DROP PROCEDURE IF EXISTS p1;
|
DELIMITER //
|
CREATE PROCEDURE `p1` (param1 varchar(100) COLLATE utf8_general_ci)
|
BEGIN
|
SELECT param1;
|
END
|
//
|
DELIMITER ;
|
|
|
CALL p1('foo');
|
ERROR 1457 (HY000): Failed to load routine test.p2. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)
|
|
|
SHOW WARNINGS;
|
+-------+------+-------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-------------------------------------------------------------------------------------------------------------------+
|
| Error | 1253 | COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1' |
|
| Error | 1457 | Failed to load routine test.p2. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) |
|
+-------+------+-------------------------------------------------------------------------------------------------------------------+
|
|
I know that character_set_database should never be changed (that thing should have been a STATUS value, not a VARIABLE, to begin with IMHO), and that COLLATE without CHARACTER SET is rarely a good idea.
Yet the error message, and even the extra line in the warning, can be quite confusing, esp. if CREATE and CALL are not happening closely together ...