Error near 'DELIMITER $$'

Question

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: 

Answer 1

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