rake db:migrate returns "Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds.."

Question

I can't understand what's wrong with my migration syntax, please help. I ran rake db:migrate, database in MySql2

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 'USING CAST(number AS integer) DEFAULT NULL' at line 1: ALTER TABLE seats CHANGE number number integer USING CAST(number AS integer) DEFAULT NULL

below it the syntax:

class ChangeNumberTypeInSeats < ActiveRecord::Migration
  def change
    change_column :seats, :number, 'integer USING CAST(number AS integer)'
  end
end

My guess is that because my partner uses PostgreSQL locally, but pushed the code anyway to out Git. Please help.

@Monica_Agustin May you add your `database.yml` to question? — cnnr, Apr 19 '18 at 12:51

score 1 · Answer 1 · answered Apr 19 '18 at 12:58

The last argument of change_column:

'integer USING CAST(number AS integer)'

is a postgres way to convert column from one type to another (in this case to integer) (see more).

If you want to use database-specific code in migrations, you need to agree what DB you use and do it consistently across the team. Postgres-specific code will not run on Mysql and vice-versa.

It's generally a good idea to have the same DB across the team, and the same one as on production (to keep dev-prod parity).

rake db:migrate returns "Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds.."

1 Answers1