Creating mysql triggers with migrations blows up

def self.up
execute("delimiter ^ ")
sql = <<-_SQL
CREATE TRIGGER customer_bi BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
SET NEW.sndx = SOUNDEX(NEW.lname) ;
END ^
_SQL
sql.split(’^’).each do |stmt|
execute(stmt) if (stmt.strip! && stmt.length > 0)
end
execute("delimiter ; ")
end

Blows up with this message:

Mysql::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 ‘delimiter ^’ at line 1: delimiter ^

I can execute these commands without problems in the mysql client.
I use MySQL 5.0.22 and Rails 1.1.4

I’ve searched widely without finding any reference to this issue. Does
anyone have any ideas?

On 8/22/06, Mitch K. [email protected] wrote:

  _SQL

use near ‘delimiter ^’ at line 1: delimiter ^

I can execute these commands without problems in the mysql client.
I use MySQL 5.0.22 and Rails 1.1.4

In the client you’re executing a single valid command. Here, you’re
executing a series of invalid commands. Pass a valid SQL to execute,
not
SQL fragments.

jeremy

Jeremy K. wrote:

On 8/22/06, Mitch K. [email protected] wrote:

  _SQL

use near ‘delimiter ^’ at line 1: delimiter ^

I can execute these commands without problems in the mysql client.
I use MySQL 5.0.22 and Rails 1.1.4

In the client you’re executing a single valid command. Here, you’re
executing a series of invalid commands. Pass a valid SQL to execute,
not
SQL fragments.

jeremy

Thanks, Jeremy

The triggers require a change in delimiter so that mysql doesn’t
interpret the ‘;’ as the end of the CREATE TRIGGER statement in the
lines in the BEGIN END block. I expected that execute("delimiter ; ")
would work like it does thru the mysql client. Clearly it does not, but
I’m not sure why. I note that it is mysql complaining rather than rails.
I also can use the
sql = <<-_SQL
Valid SQL statements
_SQL
sql.split(‘^’).each do |stmt|
execute(stmt) if (stmt.strip! && stmt.length > 0)
end
idiom to insert and delete data without problems. It seems to me that
execute does not handle ‘delimiter ^’ correctly. I suspect that this
usage was not contemplated when execute was coded. I don’t see a work
around but would be grateful for any assistance. I am trying to trace
back thru the code for execute but that may take a while.

Jeremy K. wrote:

On 8/22/06, Mitch K. [email protected] wrote:

interpret the ‘;’ as the end of the CREATE TRIGGER statement in the
idiom to insert and delete data without problems. It seems to me that
execute does not handle ‘delimiter ^’ correctly. I suspect that this
usage was not contemplated when execute was coded. I don’t see a work
around but would be grateful for any assistance. I am trying to trace
back thru the code for execute but that may take a while.

Execute just passes the SQL to the database; there is no further
contemplation. Your workaround is the appropriate way to execute
multiple
statements.

By the way, delimiter is a mysql client command, so you’re out of luck
trying to execute it on the server in any case.

jeremy

I ought to have read more carefully. I didn’t realize that the delimiter
command is a mysql client command. Clearly I do not need to use it to
pass the CREATE TRIGGER to MySQL. Reviewing the online MySQL reference
manual I also see that I will have to give the user creating the
triggers the SUPER privilege. I think things will work out now. Thanks,
Jeremy, for the assistance.

mitch

On 8/22/06, Mitch K. [email protected] wrote:

interpret the ‘;’ as the end of the CREATE TRIGGER statement in the
idiom to insert and delete data without problems. It seems to me that
execute does not handle ‘delimiter ^’ correctly. I suspect that this
usage was not contemplated when execute was coded. I don’t see a work
around but would be grateful for any assistance. I am trying to trace
back thru the code for execute but that may take a while.

Execute just passes the SQL to the database; there is no further
contemplation. Your workaround is the appropriate way to execute
multiple
statements.

By the way, delimiter is a mysql client command, so you’re out of luck
trying to execute it on the server in any case.

jeremy

BTW. Have tested it. This works. :wink:

class InitialTriggersSchema < ActiveRecord::Migration
def self.up
sql = <<-_SQL
DROP TRIGGER customer_bi
^
CREATE TRIGGER customer_bi BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
SET NEW.txtid = LPAD(TRIM(NEW.txtid),8,’ ‘);
SET NEW.sndx = SOUNDEX(NEW.lname) ;
END
^
DROP TRIGGER customer_bi
^
CREATE TRIGGER customer_bi BEFORE UPDATE ON customers
FOR EACH ROW
BEGIN
IF NEW.txtid <> OLD.txtid THEN
SET NEW.txtid = LPAD(TRIM(NEW.txtid),8,’ ‘);
END IF;
SET NEW.sndx = SOUNDEX(NEW.lname) ;
END
^
/* Other Triggers */
_SQL
sql.split(’^’).each do |stmt|
execute(stmt) if (stmt.strip! && stmt.length > 0)
end
end

def self.down
sql = <<-_SQL
DROP TRIGGER customer_bi ;
DROP TRIGGER customer_bi ;
_SQL
sql.split(’;’).each do |stmt|
execute(stmt) if (stmt.strip! && stmt.length > 0)
end
end

The split on ‘^’ in migrations has the same effect as changing the
delimiter in the mysql client. Thanks again for pointing me in the right
direction.

mitch