Category Archives: SQL

MySQL Triggers w/Rails

I recently incorporated db-triggers with a Rails app to maintain some counts that were otherwise fairly expensive to retrieve.  Rails wasn’t super-pumped about the idea (what with the “keep all the logic in the app” approach and all), but sometimes… you know… you know better than your framework.

Some things I was aiming for:

  1. Set them up with normal migrations.
  2. Test them with the normal test suite/normal fixtures.
  3. Make recovery/reset simple for when the table (inevitably) is somehow out of sync.

The “frequent counts” table

I’ll have multiple counts but not TOO many — enough that I don’t want to have a column per count but not enough that I mind using “LIKE” to lookup patterns, so my table has: id, code, current_count.

Code will be a unique key (important later) and be formatted like “style_ABC_size_456”.

So, when a new item is added it’ll be associated with a style and some sizes – each combination will either need to be setup (with a current_count = 1) or an existing combo will be found and +=1.

The FrequentCount class has the fairly straightforward finders that you’d expect + methods to reset each of the counts that it contains.  The reset methods follow the pattern “reset_frequent_count_COUNT_NAME” -> they clear the existing counts that they maintain before repopulating them.

I also threw in a reset_all method that looks for anything on the class following the “reset_frequent_count_COUNT_NAME” pattern and runs them.

The trigger-SQL

The SQL for creating the triggers will be needed by the migration as well as the test suite.  In fact, the test suite will need to run them somewhat often due to the way the standard tests “prepare” the database.

I ended up throwing it in lib/trigger_sql.rb.  Methods there are named with the pattern “sql_for_TABLE_OPERATION_TRIGGER_NAME” ex: sql_for_items_insert_style_and_size

Many of the triggers could not rely on pre-existing rows.  i.e. a new style/size combination needs to INSERT where an existing combo could update ( +=1 ).  To get around this, I relied on the unique key setup earlier on the “code” field for the frequent counts table.  <– that allowed me to lean on insert statements with “ON DUPLICATE KEY” clauses with update statements.  Something like this…
create trigger items_insert_style_and_size after insert on items
for each row
insert into frequent_counts(code, current_count)
values (concat(‘style_’,, ‘_size_’, new.size), 1)
on duplicate key update current_count = current_count + 1;
The Migration

I’ve already given away most of the fun stuff about the migration.  It just needs to run through the triggers that are being setup at this specific time, doing things like:
and then make sure to populate it all (with that reset_all) method when we’re done. <– next time out I may want to call specific methods to reset just the ones I care about but this first time, I can just do the whole table.

Testing with Fixtures
Rails goes a little too far when running the default test tasks for us – it ends up nuking the triggers on us, but not to fear: it’s a quick hack in the Rakefile.
I’m going to spare you some details (drop me a line if you want them) but I basically overrode the db:test:prepare method to call a special version of the clone_structure task.  My version has a dependent task that does:
# find methods that follow our pattern of “methods providing trigger sql” and execute the contents of each{ |m| m =~ /sql_for_.+/ }.each do |method_name|
As you see there, it’s leaning on that naming convention “sql_for_TABLE_OPERATION_TRIGGER_NAME” to find the sql to (re)apply.
That’s it!
Migrations set them up and share the code to do so with the fixtures that can repeat the tests whenever we need.  Those reset methods also come in handy not only for the initial population (by the migration) but we can call them manually should we need them.

Leave a comment

Filed under deployment, rails, Ruby, SQL, Test Driven Development (TDD)

Avoiding Insert Locking With Insert Into

Ran into an issue where shared locks were being applied (for InnoDB) on a statement with this format:

insert into a select * from b;

While that statement is running, not only does a have some locks applied, but so does b! problem is… my statement was not exactly that simple. Instead, the select statement involved multiple tables, several sub queries, and more fun… The query was tuned to be as efficient as possible but still was running long enough that having locks on the tables selected from was unacceptable.

The solution? I’m getting around the issue by writing the results of the select to a flat file – then immediately turning around and loading it into the destination table.

select * from a into outfile a.txt;
load data infile a.txt into b;

Not too bad…

A few little catches:

* You can’t overwrite the flat file with select into outfile – maybe a good thing overall, but a bit annoying in my case. No prob though – just modified the script a bit to change the name in a predictable – yet not repeatable way.

* The user you’re running as (against mysql) will need to have file privileges which isn’t automatically given as part of the grant all statement that you probably set yourself up with immediately:
grant file ON *.* TO my_user@localhost;

* Obviously, your user will need to have access to the destination directory that you’re writing your flat file to. So, make sure wherever you’re headed – you’ve taken care of making it writeable for that user.

* I personally was a bit nervous about what would be used as a seperator if I didn’t ‘do it myself’, so I did. Very simple, you just need character(s) that you know will be unique. Here’s the csv example:

select * from a into outfile ‘a.txt’ fields terminated by ‘,’ lines terminated by ‘\n’;
load data infile ‘a.txt’ into table b fields terminated by ‘,’ lines terminated by ‘\n’;

* My table has an auto_increment id field on it. So, I don’t want to have it in my txt – I want it to actually auto increment! Again, nice and easy: Just provide a field list that doesn’t have it:
load data infile ‘a.txt’ into table b fields (field_1, field_2)

* and to combine the last two ideas:
load data infile ‘a.txt’ into table b fields terminated by ‘,’ lines terminated by ‘\n’ (field_1, field_2);

Not bad! No more locks and negligible runtime difference. The extra moving pieces are less than ideal, but it’s not a whole lot of extra complication.

Leave a comment

Filed under SQL

The Future, Conan?

Why aren’t my fixtures properly loading their ‘registered’ timestamps correctly?

Ah, the dreaded Year 2038 issue

loads fine: user_registered: ‘2038-01-18 21:14:07’

not happy: user_registered: ‘2038-01-18 21:14:08’

So, that fixture that I just wanted to have ‘a date way out in the future’ will need to be knocked back a few years I guess.

Leave a comment

Filed under rails, Ruby, SQL


So far so good. In a fairly short amount of time I have an application with some basic functionality + user management (register, signin, email confirmations, etc). Very simple = very nice.

So far I’ve been leaning pretty heavily on:
Pick Axe – Especially Chapters 27 (Built-in Classes and Modules) & Chapter 28 (Standard Library). Those have been an _incredibly_ useful resource for finding that method that you just know must exist, but what’s the syntax?

Agile Web Development w/ RoR – Especially Chapter 14 & 15 (Active Record) and a little Chapter 19 (Action Mailer).

… and there’s one other, but I’ll save that for the grand finale…

Now that this things working, it’s time to break it. In my very first iteration I created the app with a single db, but in reality – it’s going to span two. Today I needed to figure out exactly how this is done. From what I read, this is something that is quite possible… but just isn’t all that straight forward.

Maybe saying that it “isn’t straight forward” isn’t really fair. What’s really the case is more that: It’s just so bloomin easy to work with a single database that you never actually stop to really learn how you’re using the single database. Instead you just change your _outrageously_ straight forward database.yml file and BAM, you’re running.

I’ve found multiple different solutions out there. Some of them really don’t sound appealing – or don’t really fit my needs. Some seem to be exactly what I want… then didn’t work for me, for one reason or another.

I’m to the point now where it’s working and it’s really quite clean. The only flaw (if it really is one) is that the model class is naming the database that it wants to use. How bad is that?… I really don’t mind it at all to tell the truth. I can certainly see an argument for moving it into the config though – which is possible as well… we’ll see if we take that route or not.

Where did I come across this solution, you might ask? I know the suspense has been killing you regarding my other resource… why, Rails Recipes of course.


Filed under Agile Software Dev, rails, Ruby, SQL

What Time(stamp) is it?

The app I am currently developing (in Java, not the Ruby tutorial I’ve been talking about) relies on a timestamp to report only the most recent activity to it’s clients. So, why was it not properly reporting our test data?

After some deliberation I ran the simple statement:
select distinct trim(current_timestamp) from xxx.yyy

Why the ‘trim’? Well because for some reason WinSQL (on my machine) won’t properly retrieve a dataset containing a timestamp for me otherwise (but that’s another story).

Anyway, at 10:46AM it reports that it is currently 2:46PM. So, simple timezone issue, right? Well… maybe.

select dbtimezone from xxx.yyy

This gives me a ‘-6’. So, it does seem to know where we are.

Maybe it’s another WinSQL timestamp issue? Nope, I hack my app to report the timestamp to me – also seems to think it’s 2 this afternoon.

I have my buddy copy/paste my timestamp retrieval (against the same db): he gets 10:46AM! Ok, now this is quickly becoming a pain.

Checking the internet… Some rants are close but not much out there.

After much effort I get a statement mocked up to tell me what it looks like out on the development server. How long does it take for the “automated” deployment tool to get everything out on the dev server and restarted for me to do this? Why, a meer 15 minutes – no, I am serious; I literally timed it with my watch 14:47. Anyway, it comes up with the same (incorrect) stamp that I have locally!

After some effort I’ve found that systimestamp (as opposed to current_timestamp) will report consistenly for me. According to the documentation this is the same as saying “TIMESTAMP WITH TIME ZONE” – but now I don’t even have to worry about what the proper timezone is. Not that the server’s going to be crossing any statelines in the near future, but hey: it works!

The big mystery though is why the timestamp is applied to current_timestamp on some machines (seemingly) by default, while on others: it is not. Softaware? Configuration? Who will solve this mystery? Not me! It works and it’s 5PM on Friday!

Leave a comment

Filed under deployment, SQL