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.

Love SQL

Something I’m working on at the moment requires a rather large collection of data to be moved from several places into one clever little table that I’ve created. It’s going to consolidate functionality and make things much easier to script in a few different environments.

Gathering all of this data together has been made pretty damn easy by using a few choice SQL queries. A few months ago I would probably have written a Perl script to do this instead, but that’s really not necessary.

This first query performs a SELECT on one table and uses the values to populate the INSERT statement values. It’s quite simple because all of the required data is in one table on the same database as the table it’s being copied to. All field, table, and database names have been changed to protect the innocent.

INSERT INTO clever_table (id, person_id, email_address, email_type) SELECT NULL, person_id, email_address_1, email_type FROM prefs WHERE email_address_1 IS NOT NULL;

The second query is a bit more complicated. It performs a SELECT on a table in one database that does an INNER JOIN on a table in another database and then an INSERT to get all of the collated data into a different table in the first database.

INSERT INTO clever_table (id, person_id, email_address, email_type) SELECT NULL, person_id, people.email, email_type FROM prefs INNER JOIN other_db.people ON prefs.person_id=people.id WHERE prefs.password_reminder=1;

I was quite pleased to find this worked first time when I tested. It doesn’t seem complicated to me now, but I know it would have done before I started working where I do now.

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. 🙂