thr3ads.net - Rails - [Rails] Creating mysql triggers with migrations blows up [Aug 2006]

If this information is useful, please help other people find it:
Share via:

Mitch Kuppinger

2006-Aug-22 17:26 UTC

[Rails] 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?

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Jeremy Kemper

2006-Aug-22 19:50 UTC

head link

[Rails] Re: Creating mysql triggers with migrations blows up

On 8/22/06, Mitch Kuppinger
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:>
>
>   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

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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Mitch Kuppinger

2006-Aug-22 21:35 UTC

head link

[Rails] Re: Creating mysql triggers with migrations blows up

Jeremy Kemper wrote:> On 8/22/06, Mitch Kuppinger
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> 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.


-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Jeremy Kemper

2006-Aug-22 22:08 UTC

head link

[Rails] Re: Creating mysql triggers with migrations blows up

On 8/22/06, Mitch Kuppinger
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:>
>
> Jeremy Kemper wrote:
> > On 8/22/06, Mitch Kuppinger
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> 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.
>
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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Mitch Kuppinger

2006-Aug-23 00:05 UTC

head link

[Rails] Re: Creating mysql triggers with migrations blows up

Jeremy Kemper wrote:> On 8/22/06, Mitch Kuppinger
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> 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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Mitch Kuppinger

2006-Aug-23 01:49 UTC

head link

[Rails] Re: Creating mysql triggers with migrations blows up

BTW. Have tested it. This works. ;)

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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Reasonably Related Threads

Search for more maybe matching threads

Rails - Aug 2006 - Creating mysql triggers with migrations blows up

[Rails] Creating mysql triggers with migrations blows up

[Rails] Re: Creating mysql triggers with migrations blows up

[Rails] Re: Creating mysql triggers with migrations blows up

[Rails] Re: Creating mysql triggers with migrations blows up

[Rails] Re: Creating mysql triggers with migrations blows up

[Rails] Re: Creating mysql triggers with migrations blows up

Reasonably Related Threads