Migration with mysql reserved word


#1

I have a column name called ‘order’ in two of my db tables.
I have just realized that it is causing some problems since ‘order’ is
a reserved word in mysql.
Unfortunately, trying to run a migration that includes…

rename_column :images_piles, :order, :sequence

…results in a mysql error (see below). Is there anyway way to run a
successful migration that forces mysql to treat ‘order’ as a column
name?

== ChangeOrderToSequence: migrating

– rename_column(:images_piles, :order, :sequence)
rake aborted!
Mysql::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 ‘order sequence int(11)’ at line 1: ALTER TABLE images_piles
CHANGE order sequence int(11)


#2

On 7 Mrz., 00:31, “removed_email_address@domain.invalid” removed_email_address@domain.invalid
wrote:

I have a column name called ‘order’ in two of my db tables.

I don’t know how to do it in rails, but when you have access to a
mysql
command-line-client or phpmyadmin installed there is an easy way.

Just write the query like this:

ALTER TABLE images_piles CHANGE order sequence int(11)

Simply put any keyword between backticks and it won’t be recognized
as a keyword any more.

Once you changed the name of the column you can do the rest of the
migration with rails.

HTH
Adrian


#3

i’ve hit this problem before when i had a column named ‘group’ in my
table. i looked into the behavior because i noticed that rails,
specifically ActiveRecord, does add backticks some of the time, but
not all the time.

i even went in and forced it to use the reserved word by creating the
table manually from the cl and even though that worked, when i
attempted to add a new record, it blew up because AR wasn’t adding
backticks on the insert.

in the end, it was just easier to change the column name.

Chris