[#MDEV-22699] Strange effect when creating&calling a procedure after changing character_set

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 ...

[MDEV-22699] Strange effect when creating&calling a procedure after changing character_set_database Created: 2020-05-25 Updated: 2023-04-27
Status:	Confirmed
Project:	MariaDB Server
Component/s:	Character Sets, Stored routines
Affects Version/s:	10.1, 10.1.44, 10.4.13, 10.2, 10.3, 10.4, 10.5
Fix Version/s:	10.4, 10.5

[MDEV-22699] Strange effect when creating&calling a procedure after changing character_set_database Created: 2020-05-25 Updated: 2023-04-27