What should have been a simple exercise turned into a bit of a nightmare. Upgrading mySQL 4.0 to 4.1 bit a chunk out of my virtual leg. Here are a couple of tricks for new players.
Client does not support authentication protocol requested by server; consider upgrading MySQL client
This problem is relatively well publicised and luckily easy to fix. If you are running CF you'll definitely need to consider this as part of your upgrade plans. mySQL have updated the way they manage authentication -- CF expects the old format. The fix is a simple update to the relevant users on your database:
UPDATE mysql.user
SET password=OLD_PASSWORD
WHERE user='someuser'
AND host='somehost';
The other problems we experienced may have been a result of the way we migrated data from the old 4.0 generation database to the new 4.1 version. We simply copied the data directories from one server to the next. Though strictly speaking not kosher, there was certainly nothing that suggested this very common practice was going to cause us any headaches.
All seemed well on the surface... but it appears the data transfer modified the column datatypes. For example, varchar(50) columns were magically converted to varchar(16). The real party trick was that no data was lost and data READs were normal. It was only when we started making UPDATEs and INSERTs that everything got all funky. We then went through the laborious task of writing scripts to update the multitude of tables to their correct data types.
Posted by modius at 11:06 AM | Permalink
Trackback: http://blog.daemon.com.au/cgi-bin/dmblog/mt-tb.cgi/274
