Error near 'DELIMITER $$'

delimiter, mysql, ruby, ruby-on-rails

Solution

The top answer is correct (Rails cannot execute `DELIMITER` because it is a MYSQL command), but @ishandutta2007's follow up question was not answered, so I'll answer that here.

`DELIMITER` is often used to wrap mysql function and procedure bodies; to achieve this in rails simply wrap the procedure body in it's own execute statement.

So for instance code that might read like:

execute <<-SQL
  DROP FUNCTION IF EXISTS MyFunc;
  DELIMITER $$
  CREATE FUNCTION My Func
    . . .
  $$
  DELIMITER ;
SQL

Would instead become the following, with the multiple `execute` calls acting as the 'scoping' intended by redefining the delimiter:

execute 'DROP FUNCTION IF EXISTS MyFunc'
execute <<-SQL
  CREATE FUNCTION My Func
    . . .
SQL

Problem

when I change Delimeter from mysql console or MySQL Workbench I do not get any error, but when I embed the same code in ruby on rails I get error ``` mysql> DELIMITER $$ mysql> ``` gives no error. but ``` ActiveRecord::Base.connection.execute(%Q{ DELIMITER $$ }) ``` gives: ``` ActiveRecord::StatementInvalid: 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 'DELIMITER $$' at line 1: ```

Original source