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.

Leave a Reply

Your email address will not be published. Required fields are marked *