Creating mysql function from rails migration

Question

I'm following This Post and trying to add the levenshtein mysql function through rails migration as follows:

class AddLevenshteinFunctionToMysql < ActiveRecord::Migration
  def self.up
    ActiveRecord::Base.connection.execute <<-SQL
      DELIMITER $$
      CREATE FUNCTION levenshtein( s1 VARCHAR(255), s2 VARCHAR(255) )
      RETURNS INT
      DETERMINISTIC
      .
      .
      .
    SQL
  end

  def self.down
    ActiveRecord::Base.connection.execute <<-SQL
      DROP FUNCTION levenshtein;
    SQL
  end
end

But, I'm getting error like:

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 $$

The exact code runs fine when I try it directly in MySQL console. But I want to add it through rails migration. What is wrong?

EDIT

If I remove the DELIMITERs, then I get following error (line 39 is END$$)

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 '' at line 39

EDIT

I got a nice answer explaining the cause of the error, but not the fix. ANSWER

Just remove DELIMITTERs and $$ – Tony Vincent Dec 14 '16 at 09:32 — Tony Vincent, Dec 14 '16 at 09:32

score 9 · Accepted Answer · answered Dec 14 '16 at 09:26

9

Rails cannot execute DELIMITER because it is a MYSQL command.

ActiveRecord sets up a different end-of-statement flag (not a semi-colon), so you can just write the straight sql, Just remove DELIMITTER and $$

answered Dec 14 '16 at 09:26

Tony Vincent

13,354
7
49
68

score -2 · Answer 2 · edited May 23 '17 at 11:54

-2

Ok, I found my answer. To answer my question, as properly suggested in this POST,

Rails cannot execute DELIMITER because it is a MYSQL command

So, removing DELIMITER $$ and replacing END$$ DELIMITER ; with END, fixed my issue.

So the final code looks like:

class AddLevenshteinFunctionToMysql < ActiveRecord::Migration
  def self.up
    ActiveRecord::Base.connection.execute <<-SQL
      #DELIMITER $$  <-- removed
      CREATE FUNCTION levenshtein( s1 VARCHAR(255), s2 VARCHAR(255) )
      RETURNS INT
      DETERMINISTIC
      .
      .
      .
      END IF;
      RETURN c;
      #END$$  <-- removed
      #DELIMITER ;  <-- removed
      END
    SQL
  end

edited May 23 '17 at 11:54

Community

1
1

answered Dec 14 '16 at 09:30

Abhi

4,123
6
45
77

2

This is exactly what @TonyVincent suggested. Perhaps you should select his answer as correct so he gets some karma. – Joshua Pinter Sep 14 '20 at 22:31
1

@JoshuaPinter I have done bit more additional stuff and gave an working example, which was not suggested by Tony. and for his karma, I had upvoted for him. – Abhi Dec 22 '20 at 12:27
1

Up to you, but generally, as a thank you to the person that took the time to understand your question and provide a correct and helpful answer, you want to select them (instead of yourself) as the correct answer. If additional information can be added, it's best just to edit their correct answer and add the additional information instead of creating your own answer. To me, Tony Vincent's answer is bang on and adding a working example to his answer would be the right way to go. – Joshua Pinter Dec 22 '20 at 16:57
1

@JoshuaPinter Ok – Abhi Mar 28 '22 at 15:54

Creating mysql function from rails migration

2 Answers2