Pewnego dnia w pracy postanowiłem zaktualizować mysql do wersji 5.1.28-rc.

Po pierwszej zaktualizowanej maszynie widzę w logach ciekawy warning:

[Warning] Stored routine 'database'.'routine_name': invalid value in column mysql.proc.db_collation.
[Warning] Stored routine 'database'.'routine_name': invalid value in column mysql.proc.character_set_client.
[Warning] Stored routine 'database'.'routine_name': invalid value in column mysql.proc.collation_connection.

W wersji 5.1.28 i nowszych w momencie gdy procedury różnią się kodowaniem od tabel widzimy właśnie taki warning.

Rozwiązaniem jest zmiana kodowania procedur na używane w tabelach.

Przykład błędnego zaimportowania procedury:

mysql (database) > show variables like '%char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | latin1                           |
| character_set_connection | latin1                           |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | latin1                           |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /opt/mysql/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)

mysql (database) > show create procedure exampleG
*************************** 1. row ***************************
           Procedure: example
            sql_mode:
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `example`()
begin select 1; end
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

Baza i tabele są kodowane utf8 tymczasem nasz klient podłączył się z użyciem kodowania latin1.
Nasza procedura przyjęła kodowanie latin1.

Rozwiązanie:
Zmieniamy kodowanie.

mysql (database) > show variables like '%char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /opt/mysql/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

mysql (database) > DROP PROCEDURE example;
Query OK, 0 rows affected (0.00 sec)

mysql (database) > DELIMITER ||
mysql (database) > create procedure example()
 begin
   select 1;
 end ||
Query OK, 0 rows affected (0.01 sec)

mysql (database) > show create procedure exampleG
*************************** 1. row ***************************
           Procedure: example
            sql_mode:
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `example`()
begin select 1; end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

Dodajmy jeszcze do my.cnf domyslne kodowanie dla clienta na utf8.

[client]
default_character_set = utf8

Na koniec:

mysql (database) > flush logs;