MySQL trigger syntax schema

[michaelirey]

Everything was working great until..

rake db:reset

rake aborted!
Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '% TRIGGER

Was able to hunt the issue down and it turns out this was being generated in the schema.rb file:

  execute(<<-TRIGGERSQL)
CREATE DEFINER = root@% TRIGGER event_users_before_insert_row_tr BEFORE INSERT ON event_users
FOR EACH ROW
BEGIN
    SET NEW.created_at = IFNULL(NEW.created_at, NOW()), NEW.created_at = IF(NEW.created_at = '0000-00-00 00:00:00', NOW(), NEW.created_at), NEW.updated_at = IFNULL(NEW.updated_at, NOW()), NEW.updated_at = IF(NEW.updated_at = '0000-00-00 00:00:00', NOW(), NEW.updated_at);
END
  TRIGGERSQL

The fix is:

  execute(<<-TRIGGERSQL)
CREATE DEFINER = 'root'@'%' TRIGGER event_users_before_insert_row_tr BEFORE INSERT ON event_users
FOR EACH ROW
BEGIN
    SET NEW.created_at = IFNULL(NEW.created_at, NOW()), NEW.created_at = IF(NEW.created_at = '0000-00-00 00:00:00', NOW(), NEW.created_at), NEW.updated_at = IFNULL(NEW.updated_at, NOW()), NEW.updated_at = IF(NEW.updated_at = '0000-00-00 00:00:00', NOW(), NEW.updated_at);
END
  TRIGGERSQL

The only difference is putting quotes around the username and host. I checked the mysql documentation and found this:

http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html

"The DEFINER clause specifies the MySQL account to be used when checking access privileges at trigger activation time. If a user value is given, it should be a MySQL account specified as 'user_name'@'host_name' (the same format used in the GRANT statement), CURRENT_USER, or CURRENT_USER(). The default DEFINER value is the user who executes the CREATE TRIGGER statement. This is the same as specifying DEFINER = CURRENT_USER explicitly."

[michaelirey]

I noticed even when I do:

trigger.security("'root'@'%'").before(:insert)

The quotes are striped out, I we end up with:

  execute(<<-TRIGGERSQL)
CREATE DEFINER = root@% TRIGGER event_users_before_insert_row_tr BEFORE INSERT ON event_users
FOR EACH ROW
BEGIN
    SET NEW.created_at = IFNULL(NEW.created_at, NOW()), NEW.created_at = IF(NEW.created_at = '0000-00-00 00:00:00', NOW(), NEW.created_at), NEW.updated_at = IFNULL(NEW.updated_at, NOW()), NEW.updated_at = IF(NEW.updated_at = '0000-00-00 00:00:00', NOW(), NEW.updated_at);
END
  TRIGGERSQL

[jenseng]

looks like it's due to how mysql returns the Definer when you call SHOW TRIGGERS ... even though you have to quote the user and host when creating a trigger, they are never quoted when you query the triggers from the db, e.g. you just get something like hairtrigger@localhost

so it should be pretty easy to make hairtrigger handle that correctly

that said, it is a little odd that your schema.rb is generating raw CREATE TRIGGER calls (instead of create_trigger). how were these triggers added to the db? model triggers with the rake task? manual create_trigger calls? or execute("CREATE TRIGGER...")?

[michaelirey]

They were added as model triggers with the rake task, as described in the documentation.

Let me know if you would like a code sample.

[michaelirey]

Come to think of it I did get this message in the schema.rb as well: " WARNING: generating adapter-specific definition for #{name} due to a mismatch. either there's a bug in hairtrigger or you've messed up your migrations and/or db :-/"

[jenseng]

that might be a symptom of the definer mismatch. so yeah, if you could send a sample of the model triggers you're using, that would help track it down and hopefully we can solve both problems at once

[michaelirey]

Here is the migration that was generated:

# This migration was auto-generated via `rake db:generate_trigger_migration'.
# While you can edit this file, any changes you make to the definitions here
# will be undone by the next auto-generated trigger migration.

class CreateTriggersEventUsersInsertAndEventUsersUpdate < ActiveRecord::Migration
  def up
    create_trigger("event_users_before_insert_row_tr", :generated => true, :compatibility => 1).
        on("event_users").
        before(:insert) do
      "SET NEW.created_at = IFNULL(NEW.created_at, NOW()), NEW.created_at = IF(NEW.created_at = '0000-00-00 00:00:00', NOW(), NEW.created_at), NEW.updated_at = IFNULL(NEW.updated_at, NOW()), NEW.updated_at = IF(NEW.updated_at = '0000-00-00 00:00:00', NOW(), NEW.updated_at);"
    end

    create_trigger("event_users_before_update_row_tr", :generated => true, :compatibility => 1).
        on("event_users").
        before(:update) do
      "SET NEW.created_at = IFNULL(NEW.created_at, NOW()), NEW.created_at = IF(NEW.created_at = '0000-00-00 00:00:00', NOW(), NEW.created_at), NEW.updated_at = IFNULL(NEW.updated_at, NOW()), NEW.updated_at = IF(NEW.updated_at = '0000-00-00 00:00:00', NOW(), NEW.updated_at);"
    end
  end

  def down
    drop_trigger("event_users_before_insert_row_tr", "event_users", :generated => true)

    drop_trigger("event_users_before_update_row_tr", "event_users", :generated => true)
  end
end

[michaelirey]

And here is a module included in our active record models:

module AutoCreatedUpdated

  extend ActiveSupport::Concern

  included do 

    @trigger_sql = "SET NEW.created_at = IFNULL(NEW.created_at, NOW()), NEW.created_at = IF(NEW.created_at = '0000-00-00 00:00:00', NOW(), NEW.created_at), NEW.updated_at = IFNULL(NEW.updated_at, NOW()), NEW.updated_at = IF(NEW.updated_at = '0000-00-00 00:00:00', NOW(), NEW.updated_at)"

    trigger.before(:insert) { @trigger_sql } 
    trigger.before(:update) { @trigger_sql } 

  end

end

[michaelirey]

mysql version 5.1.70

[michaelirey]

Which created this in the schema.rb

# no candidate create_trigger statement could be found, creating an adapter-specific one
  execute(<<-TRIGGERSQL)
CREATE DEFINER = root@% TRIGGER event_users_before_insert_row_tr BEFORE INSERT ON event_users
FOR EACH ROW
BEGIN
    SET NEW.created_at = IFNULL(NEW.created_at, NOW()), NEW.created_at = IF(NEW.created_at = '0000-00-00 00:00:00', NOW(), NEW.created_at), NEW.updated_at = IFNULL(NEW.updated_at, NOW()), NEW.updated_at = IF(NEW.updated_at = '0000-00-00 00:00:00', NOW(), NEW.updated_at);
END
  TRIGGERSQL

  # no candidate create_trigger statement could be found, creating an adapter-specific one
  execute(<<-TRIGGERSQL)
CREATE DEFINER = root@% TRIGGER event_users_before_update_row_tr BEFORE UPDATE ON event_users
FOR EACH ROW
BEGIN
    SET NEW.created_at = IFNULL(NEW.created_at, NOW()), NEW.created_at = IF(NEW.created_at = '0000-00-00 00:00:00', NOW(), NEW.created_at), NEW.updated_at = IFNULL(NEW.updated_at, NOW()), NEW.updated_at = IF(NEW.updated_at = '0000-00-00 00:00:00', NOW(), NEW.updated_at);
END
  TRIGGERSQL

[michaelirey]

When we added this module to multiple models at the same time we got this:

  # WARNING: generating adapter-specific definition for event_notes_before_insert_row_tr due to a mismatch.
  # either there's a bug in hairtrigger or you've messed up your migrations and/or db :-/
  execute(<<-TRIGGERSQL)
CREATE DEFINER = root@% TRIGGER event_notes_before_insert_row_tr BEFORE INSERT ON event_notes
FOR EACH ROW
BEGIN
    SET NEW.created_at = IFNULL(NEW.created_at, NOW()), NEW.created_at = IF(NEW.created_at = '0000-00-00 00:00:00', NOW(), NEW.created_at), 
          NEW.updated_at = IFNULL(NEW.updated_at, NOW()), NEW.updated_at = IF(NEW.updated_at = '0000-00-00 00:00:00', NOW(), NEW.updated_at);
END
  TRIGGERSQL

  # WARNING: generating adapter-specific definition for event_notes_before_update_row_tr due to a mismatch.
  # either there's a bug in hairtrigger or you've messed up your migrations and/or db :-/
  execute(<<-TRIGGERSQL)
CREATE DEFINER = root@% TRIGGER event_notes_before_update_row_tr BEFORE UPDATE ON event_notes
FOR EACH ROW
BEGIN
    SET NEW.created_at = IFNULL(NEW.created_at, NOW()), NEW.created_at = IF(NEW.created_at = '0000-00-00 00:00:00', NOW(), NEW.created_at), 
          NEW.updated_at = IFNULL(NEW.updated_at, NOW()), NEW.updated_at = IF(NEW.updated_at = '0000-00-00 00:00:00', NOW(), NEW.updated_at);
END
  TRIGGERSQL