mysql and wordpressing

This morning I checked my email, and I'd had a reply from Phil Harvey about to fix the problem with Capture NX saving two XMP blocks in JPEGs. In case anyone else is having the same problem and manages to find this post, here's the solution Phil gave:

So it is Capture NX that writes XMP to the wrong location in the JPEG. This isn't too unexpected. To fix this is a bit tricky using exiftool because normally it doesn't edit the EXIF segment if you are only writing XMP, so you must use a trick to force the XMP to be processed:

exiftool -xmp= -exif:artist-=nothing FILE.jpg

After doing this, the XMP will be removed from the incorrect location. Then you can use exiftool to copy it from the NEF into the proper location:

exiftool -tagsfromfile FILE.nef -xmp FILE.jpg

After checking my email, I got on with upgrading my wordpress installation. After unpacking the zip, I copied across the wp-config file, the theme, and plugins from my old installation. After doing this I replaced the instances of », «, and   with their numeric equivalents (», «, and   respectively), so as to make the generated html XML/XHTML compatible.

I then loaded up the site in the browser, but it was using the default wordpress theme, rather than my custom theme. So I logged into the wordpress admin panel, and got a message that the wordpress database needed to be 'upgraded'. I clicked OK, then was logged into the admin panel. Going to the appearance section of the admin panel, I could see that my theme wasn't being loaded because Wordpress now insists that your theme must include a stylesheet.

My theme does include a stylesheet, but the stylesheet's location is outside of the wordpress root. I can understand why Wordpress would require all files to be in the theme's folder, but it seems to me it might be more sensible to still allow a theme without a stylesheet to be loaded, just display a warning or something. To fix it so I could use my theme, I just created an empty style.css file inside the theme's directory.

After installing the latest versions of the various plugins I'm using, updating the xmlrpc.php file to make pingbacks work with gravatars, and modifying the latest smart youtube plugin slightly, I checked everything was still working okay, and it was.

So I uploaded the blog directory to the webserver, and told it to overwrite the existing files. This took about 15 minutes, and then I found my blog wasn't working properly. In the admin panel it had an error about a function couldn't be found, and on the actual blog it had a similar error (and due to my use of xhtml this makes the whole page unviewable as the error makes the XML invalid).

Unfortunately because I'd overwritten all the files, I couldn't just go back to the previous installation. So I uploaded the whole blog directory to the web server again, but to a different location where there wouldn't be any existing files to be overwritten, thus ensuring that the upload would be an exact replica of what I had on my local system.

This upload took ages, probably 20 minutes, but I went for lunch part way through, so I'm not sure exactly how long it took. When it was done I ssh'd into the server and renamed the current blog folder and moved the new upload to replace the broken blog.

But now the situation was even worse, I was just getting a big 'Error establishing a database connection' message, with nothing else. Following the advice given at WordPress installation - Error establishing a database connection, I added in

echo mysql_error();

after

function wpdb($dbuser, $dbpassword, $dbname, $dbhost) {
  return $this->__construct($dbuser, $dbpassword, $dbname, $dbhost); ();

But this didn't do anything, still I just got the unhelpful error message.

So I started debugging the problem by putting exit('gh');(first in the index.php file), I'd put it before an include, upload the modified file, see if I got 'gh' or the error. Then I'd put the exit('gh'); after the include, upload the modified file, and see if I got 'gh' or the error. If I got 'gh' that meant the error occured after where I'd put the exit('gh');, so I'd move the exit('gh'); further down the code and repeat. If I got 'gh' with the exit before the include, but got the error with the exit after the include, this meant the problem was in the include file, so I'd then repeat the process in the included file.

This took quite a while, and I must have gone through at least 5 includes (and lots of uploading slightly modified files) before I found the problem. The solution of debugging was actually what was given at WordPress installation - Error establishing a database connection, the reason why my debugging didn't work before was that I had placed the echo mysql_error(); within the wpdb function, whereas actually it should go in the wpdb class constructor (in wordpress 2.9.2 at least), after the line $this->dbh = @mysql_connect($dbhost, $dbuser, $dbpassword, true);.

Placing echo mysql_error(); after this line, I finally got a sensible error message. The problem was that wordpress was using the wrong socket to connect to the database. I tried modifying the mysql_connect line, passing null as the host argument, which meant it should connect using the default socket specified in php.ini (which I thought was the correct socket).

But I still got the same error, so I just manually entered the correct socket path to mysql for the host, and now I could finally connect okay. So I reverted my changes, and put the correct socket path as the hostname in wp-config.php, where it should be. I checked wordpress was working okay now, and it was.

I think in future when upgrading Wordpress, what I'll do is:

• Get it working on my local system first (same as I did this time)
• Zip it up and upload the zip to the web server - should be much faster to upload than the indivdual files - then unzip it somewhere temporary
• Rename the old blog files and the new blog files to make the new ones operative, but keep a backup, e.g. mv ~/path/to/site/blog ~/path/to/site/blog-old; mv ~/path/to/site/blog-new ~/path/to/site/blog - that should keep the downtime between switching from the old wordpress installation to the newest to less than a second, and makes it easy to switch back to the old installation if needs be.

I wrote most of this blog post, and found this cool blog where you can download lots of old country and bluegrass music that the guy's ripped from his record collection: Lonesome Lefty's Scratchy Attic. I received my Groovegrass CD in the post, so I ripped that and listened to it.

I tried to work out why the wordpress installation on my local machine was managing to connect to mysql on the correct socket. Looking at my local phpMyAdmin I could see it said

MySQL client version: mysqlnd 5.0.5-dev - 081106 - $Revision: 1.3.2.27 $

So I thought that my local Wordpress database must have been installed on the main instance of MySQL instead of my installation of MySQL 5.4, which mirrors what I have on the server. But then looking more closely, I noticed that this was the version of the mysql native driver for PHP, not the actual MySQL version, this was stated by phpMyAdmin as 5.4, which meant I was connected to the correct MySQL instance locally.

So I checked the php.ini files locally and on the webserver, and found the problem wa that I hadn't specified the default socket for mysql connections in the php.ini that was on the server - I'd only specified the default socket for mysqli connections. So I added that into the webserver's php.ini, restarted PHP, changed the host in wp-config.php back to 'localhost', and tried wordpress again, and it was still working okay. Weird though how it was working before the upgrade.

Next I tried to find out why one of my tables had a lot of duplicate records in it. The table contained columns with NULL, and normally NULL doesn't count as a unique value, so what I had done was to create a BEFORE INSERT trigger that checks whether the value to be inserted (including where columns are NULL) already exists or not. This was meant to stop duplicate records being inserted, but obviously it wasn't working.

It took me a long time, but eventually I figured out what the problem was - in the trigger I was checking if the id of the row to be inserted, and only doing the unique checking if an id wasn't specified (i.e. it was NULL). But it turned out that actually if you don't specify an id to be inserted, it is not NULL but actually 0. So since the id of the row to be inserted was never NULL, this meant my unique check would never happen, and I ended up with lots of duplicate rows.

Obviously the answer was to check whether the id of the row to be inserted was 0 instead of NULL. After doing this, I thought that the trigger should set LAST_INSERT_ID() to the correct id if the row already existed. It would also need to abort the INSERT since the row already exists.

Searching on how to do this, I found that that you abort the INSERT from within the trigger by throwing an error. You should use SIGNAL to do this, but this isn't supported by MySQL, so instead you have to kludge around this and cause an error e.g. by inserting into a table that doesn't exist.

The articles I got this info from were from about 2007, so I thought that maybe MySQL had implemented SIGNAL by now. I found they had, but it was in 5.5, and I'm using 5.4 beta 2.

So I downloaded and installed MySQL 5.5.1-m2. I couldn't work out how I was meant to get the data from the old installation into the new installation. I had done a mysql dump from the previous version, but couldn't start up the new version of mysql to import the dump since the new version didn't have an existing data directory (and so wouldn't start).

Unfortunately none of the MySQL documentation seems to cover how you actually perform an upgrade, it just seems to cover what you need to do before and after performing an upgrade.

What I did was to copy the data directory from the old mysql installation to the new installation. I could now start mysqld okay, and so ran mysql_upgrade as is advised on Upgrading from MySQL 5.4 to 5.5. But I got a lot of errors for all tables using the InnoDB engine like so:

xoogu.shortURL
Error    : Unknown table engine 'InnoDB'
error    : Corrupt

So after googling about this error and not really finding much useful, I logged into mysql and ran SHOW ENGINES, which predictably showed that InnoDB wasn't listed. I checked ./configure --help in the source directory in case --with-plugins=innobase wasn't the way to build mysql with InnoDB any more and they had decided not to show it as an error if you try and configure with an invalid option. Seems rather unlikely, but worth checking. Of course --with-plugins=innobase, was still the correct way to build mysql with InnoDB.

Next I checked the my.cnf file, which I had copied over from my previous mysql installation, just in case something weird had happened to it and a line had been added to skip-innodb. It hadn't, so finally I remembered the error logs, and I had a look in there. I could see the problem was that the Innodb log file was different to what it was expecting.

Luckily, in my earlier search I had come across a post about removing the InnoDB logs, though I had dismissed it as unlikely to be the cause of MySQL not loading InnoDB as a storage engine. So I went back to that post, and followed the advice there.

Unfortunately, I was now getting the following in the error log instead:

100217 21:39:00 mysqld_safe Starting mysqld daemon with databases from /home/djeyewater/webapps/mysql/data
Could not open required defaults file: /home/djeyewater/webapps/mysql/my.cnf
Fatal error in defaults handling. Program aborted
100217 21:39:00 [ERROR] Can't find messagefile '/home/djeyewater/webapps/mysql/share/mysql/english/errmsg.sys'
100217 21:39:00 [Warning] Can't create test file /home/djeyewater/webapps/mysql/data/rusty-ubuntu.lower-test
100217 21:39:00 [Warning] Can't create test file /home/djeyewater/webapps/mysql/data/rusty-ubuntu.lower-test
100217 21:39:00 [Note] Plugin 'FEDERATED' is disabled.
 /usr/sbin/mysqld: Unknown error 1017
100217 21:39:00 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
100217 21:39:00  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.
100217 21:39:00 mysqld_safe mysqld from pid file /home/djeyewater/webapps/mysql/data/rusty-ubuntu.pid ended

Searching for 'Operating system error number 13 in a file operation' and 'Could not open required defaults file' I couldn't find anything that seemed relevant apart from this post, which isn't much help as they said they just left it overnight, then the next day mysql magically started working.

I checked the permissions for my.cnf, and they were the same as they were in the old installation. SELinux or AppArmor I didn't think would have any effect either, since the directory and filepath was the same as it was the old version of mysql, which had been working fine.

So I think I'll delete the new mysql installation and then try again tomorrow.

Also in the evening I watched Lost with Mauser and Bo.

The weather was overcast all day and it sleeted for a bit in the morning.

Food
Breakfast: Strawberry Jam Toast Sandwich; Cup o' Tea.
Lunch: Sliced Chicken with Mayonnaise, Sliced Cherry Tomatoes, and Crunchy Salad Sandwich; Packet of Barbecue flavour Crisps; Rocky; Cup o' tea.
Dinner: Shepherd's Pie; Parsnips; Ground Black Pepper; Grated Mature Cheddar Cheese; Gravy; Mixed Veg. Pudding was a Passion Fruit Yoghurt with 2x Chocolate Wafer Biscuits and a Shortbread Finger. Coffee; Piece of Sainsbury's Truffle Chocolate; 2x pieces of Sainsbury's Mint Creme Chocolate.