All things sysadmin

I upgraded a database for one of our customers a while back from 5.1.16-beta to 5.1.22. The developers and myself obviously gave the ChangeLogs a thorough read to ensure that nothing would affect us as far as we could tell. I know the server setup, integration and architecture like the backside of my hand, however, I’m not 100% clued up with the code etc. as that obviously falls on the developers. Presumably the area of the stored procedures fell between chairs, because we did actually overlook one change that did affect us. Only ever so slightly, but even so… Following the upgrade, I tested the application, checksummed the tables (using the excellent tool in Maatkit (formerly known as mysql-toolkit)) and it all seemed to be another glorious feat! Until I checked the logs these messages

1
2
3
4

080130  5:00:19 [Warning] Stored routine ‘db'.'stored_procedure': invalid value in column mysql.proc.collation_connection.
080130  5:00:19 [Warning] Stored routine ‘db'.'stored_procedure': invalid value in column mysql.proc.db_collation.
080130  5:00:41 [Warning] Stored routine ‘db'.'stored_procedure': invalid value in column mysql.proc.collation_connection.
080130  5:00:41 [Warning] Stored routine ‘db'.'stored_procedure': invalid value in column mysql.proc.db_collation.

Luckily, they were only warnings and had no affect on the actual application, other than some slight performance degradation due to the increased disk I/O caused by the log being written to everytime a stored procedure was executed! Googling for these messages, it (at that time) appeared as if we were the only people in the whole wide world with this problem, as the only other hit was a german person asking about it in his blog. Needless to say, he had no replies either. So I went back through the change logs and came to http://dev.mysql.com/doc/refman/5.1/en/news-5-1-21.html and read

The fix for the problems is to store character set information from the object creation context
so that this information is available when the object needs to be used later. The context includes
the client character set, the connection character set and collation, and the collation of the
database with which the object is associated.

As a result of the patch, several tables have new columns:
In the mysql database, the proc and event tables now have these columns:
`character_set_client, collation_connection, db_collation, body_utf8.

A-ha! This was clearly the problem. Dumping all the procedures, and updating them and set the collation and character set to ‘latin1' and collations to ‘latin1_swedish_ci' solved the issue.