mysql issue warnings / cron

I’ve previously written about MySQL pain in the behind issues involving views with SECURITY DEFINER and bad client collation selection.

For the former problem, I wrote a script that you could call periodically to warn you of potential problems with your views. Now I’ve extended it to warn you about collation issues as well.

Put warn-mysql-issues.sh (view) in your cron tab and run it periodically. It’ll save you from production-time errors that you get when attempting to compare a string of one collation with another.

ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,EXPLICIT) and (utf8_unicode_ci,EXPLICIT) for operation '='

P.S. I’ve found that the option skip-character-set-client-handshake in the MySQL config works better than default-character-set = utf8 because the lack of a default-collation-connection option can get you utf8_general_ci autoselected instead.