Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22699

Strange effect when creating&calling a procedure after changing character_set_database

    XMLWordPrintable

    Details

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

        Attachments

          Activity

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            hholzgra Hartmut Holzgraefe
            Votes:
            2 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated: