MySQL annoyances

I’m working on creating a table in MySQL for keeping track of some votes. I want to limit the data so that someone on a specific IP, with a specific user agent (browser) cannot vote more than once for a specific item per month. The constraint at the table level is just to stop it filling up with false entries submitted by script. There will be client-side constraints too, for regular users.

To set this up I created a table for the votes with the following fields: vote, ip, ua, item_id, created_at.

I decided I could probably do it all with a simple UNIQUE KEY, but half way through writing it I realised that I only wanted part of the created_at field to be taken into account because people should be able to vote for the same item in another month, but not another day in the same month. I wondered if I could include a function in the unique key like this:

UNIQUE KEY `vote_constraint` (`item_id`, `ip`, `ua`, YEAR(`created_at`), MONTH(`created_at`))

Sadly that’s not allowed.

I decided to include year and month columns in the table so that I could stick them in the unique key without using a function. Since the month and year will always be the current month and year I figured I could set up a clever default based on the created_at value, but that didn’t work either. MySQL only allows you to use constants as defaults (barring a couple of half-constant keywords like AUTO_INCREMENT and CURRENT_TIMESTAMP).

I looked into triggers, which I have never used before. The theory is that you can create a trigger that is called when certain events happen. In my case I would want to create a trigger that is called BEFORE INSERT on the table that sets the year and month values to the right thing. This would allow me to insert a row and get values inserted by default. Frustratingly MySQL doesn’t really give a crap about the name of the trigger. BEFORE INSERT actually gets called AFTER checking whether the data you’ve passed is valid or not. So I’d have to pass in a valid value for each field before the trigger (that would create valid data) would even be considered, rendering the entire trigger pointless.

There are many reports of this being an issue over the last 7 years or so (yeah, honestly), and there have been patches submitted to fix this since May last year, but it’s not out there in the current version, and it might not appear for quite a while in the stable MySQL we use at work.

So despite everything it seems like I might as well just create the values in my Perl script and pass them through to MySQL to save it having to think.

MySQL default values should be better

I was creating a table a little while ago that stores a timestamp that is essentially an expiry date. I had wanted this to be a date 2 weeks in advance of the current date and time. With a timestamp field, however, the only non-constant default value available is CURRENT_TIMESTAMP. This, obviously, represents the moment in time that the row is created. I wanted to be able to do something more like the following:

expiry_date TIMESTAMP NOT NULL DEFAULT DATE_ADD(NOW(), INTERVAL 14 DAY)

Unfortunately that’s not allowed at all. Perhaps at some point in the future it will be though, and I look forward to that day.

If you’re doing this yourself and your expiry date is absolutely always going to be the same amount of time into the future then you can use something like the following:

start_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

You could then use something like the following query to fetch all rows where the expiry date has not been reached:

SELECT * FROM table_name WHERE DATE_ADD(start_date, INTERVAL 14 DAY) < NOW();

Oh well, just another little MySQL thing I found a bit interesting while I was poking around. 🙂

MySQL case insensitivity on varchar fields

I created a MySQL table like the following:

CREATE TABLE `links` (
`id` int(11) NOT NULL auto_increment,
`short_id` varchar(255) NOT NULL,
`url` text NOT NULL,
`user_id` int(11) default NULL,
`created_at` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
CONSTRAINT unique_key_1 UNIQUE (`short_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This structure caused an error for me when it came to inserting an upper-case ‘A’ in addition to the lower-case ‘a’ into the short_id field. I removed the unique constraint for the short_id field and this allowed me to create a row with ‘A’ as the short_id.

Unfortunately

SELECT * FROM links WHERE short_id='A';

gave me both rows (‘a’ and ‘A’), with the ‘a’ row being first. To fix this I had to set the short_id field to be BINARY. When I’d done that I realised I could make the short_id field unique again.

To create the table properly I now use the following query:

CREATE TABLE IF NOT EXISTS `links` (
`id` int(11) NOT NULL auto_increment,
`short_id` varchar(255) character set utf8 collate utf8_bin NOT NULL,
`url` text NOT NULL,
`user_id` int(11) default NULL,
`created_at` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
CONSTRAINT unique_key_1 UNIQUE (`short_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

You can just add the BINARY keyword after the field data type if you like, e.g.:

`short_id` varchar(255) BINARY NOT NULL

Hope this helps someone else out there. 🙂