Rails and MySQL syntax error with multiple SQL statements in an execute block

Question

I have the following code in a Rails migration for an app that uses MySQL:

execute <<-SQL
  ALTER TABLE properties
    ADD name VARCHAR(255) NOT NULL;

  ALTER TABLE properties
    ADD CONSTRAINT fk_properties_name
    FOREIGN KEY (name)
    REFERENCES valid_property_names (property_name);
SQL

When I run the migration, I get the following error:

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 'ALTER TABLE properties

Why am I getting this error and how do I fix it?

Answer 1

The problem here is that the Rails Mysql2 database adapter chokes when there are multiple SQL commands within the same execute block. The following will run fine:

execute <<-SQL
  ALTER TABLE properties
    ADD name VARCHAR(255) NOT NULL;
SQL
execute <<-SQL
  ALTER TABLE properties
    ADD CONSTRAINT fk_properties_name
    FOREIGN KEY (name)
    REFERENCES valid_property_names (property_name);
SQL

This behavior may confuse you if you're coming from using PostgreSQL with Rails since the Postgres adapter doesn't have the same limitation.