When moving from MySQL 5.0.x to a newer version (such as 5.1.x or 5.5.x) what's the best way to proactively find backwards compatibility issues? There is lots of documentation out there such as official guides on moving from one release to another and changelogs, but does anyone know of any script out there which has been created to identify possible issues? Other than mysql_upgrade (but see below for note on that) I wasn't able to find one.
It sounds like using mysql_upgrade will be able to find some issues with tables, but I don’t think it will find all possible issues (such as ones with queries) - for example, we've run into an issue in the past where we had a query that was setting a MySQL variable (max_allowed_packet). This used to be permitted, but when we upgraded from to 5.0.95 that query started failing because MySQL changed it to be readonly as of 5.0.84.
To catch compatibility issues in queries themselves is there something which can analyze the queries from the general query log (assuming this was turned this on for a period of time to capture queries)? It would be nice to identify as much as possible globally on the sysadmin side rather than leaving the developers to try to find issues. Even if wading through all the changelogs and guides, there's still the step of seeing what queries need to be changed, which would be ideal to have a script do if one already exists to reduce manual / semi-manual checking.