CODEWITHSUNDEEP
mysqlruby-on-railsmigration
Abhi
Abhi

Reputation: 4261

Creating mysql function from rails migration

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

Upvotes: 4

Views: 2345

Answers (2)

Abhi
Abhi

Reputation: 4261

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

Upvotes: -2

Tony Vincent
Tony Vincent

Reputation: 14322

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

Upvotes: 9

Related Questions