MySQL driver bug/annoyance?

When creating tables using migrations (and possibly other operations
that result in DB interaction) and using table or column names which
are reserved MySQL keywords, i.e. references, the following error
occurs:

Mysql::Error: #42000You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'references (`id` int(11) DEFAULT NULL
auto_increment PRIMARY KEY, `label` varcha' at line 1: CREATE TABLE
references (`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY,
`label` varchar(255) NOT NULL, `url` varchar(255) NOT NULL)
ENGINE=InnoDB

Shouldn't the Rails MySQL driver escape all identifiers with
backquotes or something equivalent?

Erik

It should, but looks like it fails to do so with the name of the table (“references”) in the CREATE TABLE statement. In fact, if you look at the MySQL adapter, most of the table-related operations use the plain string for the table name, while it carefully escapes column names with quote_column_name.

You can try to patch it with unit tests. It shouldn’t be too difficult, you just have to make sure you have enough test coverage not to miss on some operation.

I discovered a simple workaround for this, instead of:

create_table :references do |t|
end

use:

create_table '`references`' do |t|
end

and, in model Reference, set_table_name to '`references`'.

But it's still ugly...

Erik

Yeah, because the table name is unescaped, you solution works … but it will stop working once the adapter properly escapes it.

Whether are you going to patch it or not, you should still open up a ticket in Trac.

Opened a ticket: http://dev.rubyonrails.org/ticket/8578.

Erik

Check and make sure that a ticket isn't already open first:
http://dev.rubyonrails.org/ticket/4127
http://dev.rubyonrails.org/ticket/4593