MySQL driver bug/annoyance?

rubyonrails-core
1

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

2

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.

3

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

4

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.

5

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

Erik

6

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