Websiting

This morning I checked my email, and bythom.com, then refilled the pond with Ben as the water level was getting quite low. The water butt was quite full, and there were mushrooms in the grass, so it must have rained quite a bit here while we were away.

After that I edited my crontab on the webserver to try and start NGinx, spawn-fcgi and MySQL every 30 minutes. I found a good Shell script to restart MySQL server if it is killed or not working. However, I ended up not using that script as a cron job, but rather just trying to start mysqld_safe since that's easier (doesn't require me to edit the shell script at all, which I don't really know anything about, and also WebFaction suggest just trying to start mysqld_safe from a cron job).

After lunch I read dpreview for a bit, then did some more work on my website. I changed my slideshow so that the border round an image would be calculated as a percentage size of the size that the image was being displayed at. After getting that working, I updated the javascript file on the web server. I didn't have any images on the webserver, so I had to upload one (through the site, not FTP).

The image upload went okay, which was good, but when I tried to view the page for the image, I got the error:

directory index of "/path/to/file" is forbidden

I looked at the various Nginx logs for the site, but couldn't really see what the problem was (probably just because I'm not very good at deciphering them). I looked at the .conf file for my site and saw the problem - I had a rewrite rule that didn't start with a forward slash (NginX requires a forward slash at the start of url pattern matches, while apache requires there not to be a forward slash). So I added the forward slash in, restarted Nginx, and the page worked. Yay!

In the afternoon and a bit of the evening, I was trying to work out how I could keep from inserting duplicate records in a table where the columns can be NULL. Reading the comments on the MySQL Manual page for CREATE TRIGGER, it seems that you could do this by checking if the row you want to insert already exists, then if it does try and insert a unique value in a special table that already has that unique value in it. This should fail, and then cause your operation that fired the trigger to fail as well.

I first tried to write my trigger so that if the row already existed, then it would try and insert a row with the id of the row that already existed, and since the id column is a primary key, this would fail and cause the insert not to occur. I thought I would try doing this on the table that I'm trying to insert the row to, to avoid having a special table just to break transactions.

However, the problem with this is that I since I was trying to use the trigger to insert the duplicate key/id into the same table that had the trigger on it, the insertion caused by the trigger would fire the trigger, and it would get into an infinite loop. I didn't actually try this, but did do some googling to check whether this would cause an infinite loop, and it seems that it would: MySQL Trigger: ERROR 1442 (HY000): Cant update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

I was thinking that maybe I should just use a stored procedure or function to insert any data to this particular table, but then I saw the comment on the MySQL Triggers page by S Roberts on June 2 2009 2:34pm, which suggests setting the id/key of the row you are trying to insert to a row that already exists. Trying this in phpMyAdmin, it seemed to be working great! I found that I didn't even need to be inserting/setting the id/key in the INSERT that fired the trigger, setting NEW.id in the trigger to an id that already existed would still cause the INSERT to abort.

Now, my table looked like this:

CREATE TABLE IF NOT EXISTS `location` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `Sublocation` int(10) unsigned default NULL,
  `City` int(10) unsigned default NULL,
  `ProvinceState` int(10) unsigned default NULL,
  `country` int(10) unsigned default NULL,
  `WorldRegion` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=21 ;

INSERT INTO `location` (`id`, `Sublocation`, `City`, `ProvinceState`, `country`, `WorldRegion`) VALUES
(20, NULL, 1, NULL, NULL, NULL),
(19, NULL, NULL, 1, NULL, NULL),
(1, NULL, 1, 1, NULL, NULL);

My trigger was

CREATE TRIGGER locationUniqueCheck BEFORE INSERT ON location
FOR EACH ROW
 SET NEW.id = (SELECT id FROM location WHERE 
  IF(ISNULL(NEW.Sublocation), ISNULL(location.Sublocation), location.Sublocation = NEW.Sublocation) AND
  IF(ISNULL(NEW.City), ISNULL(location.City), location.City = NEW.City) AND
  IF(ISNULL(NEW.ProvinceState), ISNULL(location.ProvinceState), location.ProvinceState = NEW.ProvinceState) AND
  IF(ISNULL(NEW.country), ISNULL(location.country), location.country = NEW.country) AND
  IF(ISNULL(NEW.WorldRegion), ISNULL(location.WorldRegion), location.WorldRegion = NEW.WorldRegion)
  LIMIT 1
 );

I found that when inserting to the table

INSERT INTO location SET City=1 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)

would insert zero rows and return the id of the existing row where City=1 and everything else was NULL

INSERT INTO location SET ProvinceState=1 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)

would insert zero rows and return the id of the existing row where ProvinceState=1 and everything else was NULL

INSERT INTO location SET City=1, ProvinceState=1 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)

would insert zero rows but would return 0 as the id, rather than the id of the row that was matched in the trigger.

The MySQL Manual does say

If you use INSERT IGNORE and the row is ignored, the AUTO_INCREMENT counter is not incremented and LAST_INSERT_ID() returns 0, which reflects that no row was inserted.

But I'm not using INSERT IGNORE, and it seems weird how it works when only inserting one column value, but not two.

Food
Breakfast: Lemon marmalade toast sandwich; cup o' tea.
Lunch: Mature cheddar cheese with iceberg lettuce sandwich; nectarine; slice of Ludlow Food Centre Carrot Cake; Chocolate Brownie bite; cup o' tea.
Dinner: 2x posh cumberland sausages; baked beans; fish & chip shop chips (from yesterday); salt. Pudding was some of Macky's Mackie's ice cream