The Usual Tech Ramblings

The "gotchas" of upgrades

On Sunday I wrote up a “brief” little record of my upgrade nightmare. Today, I got an email from Bill reporting his anti-spam comment plugin was broken. So I decided to check in on it. I had assumed there was an issue with the PHP build I had created after the installs. I had to rebuild it 3 times yesterday because the XML functionality was broken, which meant little things like w.bloggar were broken. So that was my first stop. Everything looked okay. I’d assumed it’d probably be using the GD libraries, but I was wrong there.

Looking at the code for SecureImage, it seemed to be calling the ImageMagick convert application. This seemed like the next good point to check on. I don’t remember rebuilding anything that would have affected that application, so I attempted to call it using the same arguments. This turned out harder than I thought as it called for the use of STDIN and STDOUT, both of which I couldn’t really emulate enough quickly, but it wasn’t genearting any basic errors like libraries being missing, so I moved on, keeping it in the back of my mind.

Next step was log files. I checked the apache logs, nothing obvious there, then the php logs, nothing again, last one to try, the mysql logs. Checking in /var/log/mysql/mysqld.err gave me a bit insight into what was wrong…

[Warning] './wp_secureimage' had no or invalid character set, and 
  default character set is multi-byte, so character column sizes 
  may have changed.

This reminded me of some mentions on character changes in the mysql upgrade “gotchas”. About a quarter of the way down the page is this little number:

Incompatible change: MySQL interprets length specifications in character column definitions in characters. (Earlier versions interpret them in bytes.) For example, CHAR(N) means N characters, not N bytes.

What does that mean? It doesn’t mean a whole lot for single character/single byte strings. However if you use multi-byte characters (ujis for example), it means a whole lot. This is where the whole issue was… MySQL had changed the character length of one field from 32 characters down to 10. This caused an issue loading the captcha image from the database because the md5 string for the filename wasn’t found in the DB because it had been truncated down to 10 characters. This was easily resolved using the following statement in SQL:

  alter table wp_secureimage modify img_name varchar(32);

A quick refresh of firefox, and his random images are back up and running again. I’ve now got to go through the log files for SQL and adjust all the others now… weeeee