The upgrade to Rails 1.1 hurt my ability to run tests because it
generated the following MySQL error:
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 ''key ON config (key)'' at line 1: CREATE UNIQUE
INDEX key ON
config (key)
I have a table called `config` with a column `key` which should be
unique. I''m guessing "key" is a MySQL keyword and just needs
to be quoted?
Following rake --trace I got:
** Execute db:schema:load
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 ''key ON config (key)'' at line 1: CREATE UNIQUE
INDEX key ON
config (key)
.../lib/active_record/connection_adapters/abstract_adapter.rb:120:in
`log''
.../lib/active_record/connection_adapters/mysql_adapter.rb:185:in
`execute''
.../lib/active_record/connection_adapters/abstract/schema_statements.r
b:196:in `add_index''
Which led me to the conclusion that the problem was in the add_index
method of the SchemaStatements module. I added this method to the MySQL
connection adapter and changed:
execute "CREATE #{index_type} INDEX #{index_name} ON #{table_name}
(#{Array(column_name).join(", ")})"
To:
execute "CREATE #{index_type} INDEX `#{index_name}` ON #{table_name}
(#{Array(column_name).map{ |e| "`#{e}`"}.join(",
")})"
And the problem went away. Is this just a MySQL issue or would the
problem be common across all databases? I would like to submit a patch
for this if I can. Are there other places that would benefit from
backticks to avoid problems which occur because key words are used as
table or column names?
--
John Long
http://wiseheartdesign.com
On 4/17/06, John W. Long <ng@johnwlong.com> wrote:> The upgrade to Rails 1.1 hurt my ability to run tests because it > generated the following MySQL error: > > 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 ''key ON config (key)'' at line 1: CREATE UNIQUE INDEX key ON > config (key) > > I have a table called `config` with a column `key` which should be > unique. I''m guessing "key" is a MySQL keyword and just needs to be quoted? > > Following rake --trace I got: > > ** Execute db:schema:load > 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 ''key ON config (key)'' at line 1: CREATE UNIQUE INDEX key ON > config (key) > .../lib/active_record/connection_adapters/abstract_adapter.rb:120:in > `log'' > .../lib/active_record/connection_adapters/mysql_adapter.rb:185:in > `execute'' > .../lib/active_record/connection_adapters/abstract/schema_statements.r > b:196:in `add_index'' > > Which led me to the conclusion that the problem was in the add_index > method of the SchemaStatements module. I added this method to the MySQL > connection adapter and changed: > > execute "CREATE #{index_type} INDEX #{index_name} ON #{table_name} > (#{Array(column_name).join(", ")})" > > To: > > execute "CREATE #{index_type} INDEX `#{index_name}` ON #{table_name} > (#{Array(column_name).map{ |e| "`#{e}`"}.join(", ")})" > > And the problem went away. Is this just a MySQL issue or would the > problem be common across all databases? I would like to submit a patch > for this if I can. Are there other places that would benefit from > backticks to avoid problems which occur because key words are used as > table or column names? > > -- > John Long > http://wiseheartdesign.comThere are database-specific quote_column_name methods for the adapters that should handle this. http://rails.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/Quoting.html#M000618 Note: this is the generic one, MysqlAdapter redefines to to backtick columns. -- Rick Olson http://techno-weenie.net
Rick Olson wrote:>> And the problem went away. Is this just a MySQL issue or would the >> problem be common across all databases? I would like to submit a patch >> for this if I can. Are there other places that would benefit from >> backticks to avoid problems which occur because key words are used as >> table or column names? > > There are database-specific quote_column_name methods for the adapters > that should handle this.Brilliant. So I should patch schema_statements.rb to use #quote_column_name in #add_index? -- John Long http://wiseheartdesign.com
On 4/17/06, John W. Long <ng@johnwlong.com> wrote:> Rick Olson wrote: > >> And the problem went away. Is this just a MySQL issue or would the > >> problem be common across all databases? I would like to submit a patch > >> for this if I can. Are there other places that would benefit from > >> backticks to avoid problems which occur because key words are used as > >> table or column names? > > > > There are database-specific quote_column_name methods for the adapters > > that should handle this. > > Brilliant. So I should patch schema_statements.rb to use > #quote_column_name in #add_index? > > -- > John Long > http://wiseheartdesign.comYup, go for it. Post the ticket # and I''ll see about adding it. I''d appreciate any folks using mssql, oracle, etc to try it out too. -- Rick Olson http://techno-weenie.net
Rick Olson wrote:>> Brilliant. So I should patch schema_statements.rb to use >> #quote_column_name in #add_index? > > Yup, go for it. Post the ticket # and I''ll see about adding it. I''d > appreciate any folks using mssql, oracle, etc to try it out too.Done: http://dev.rubyonrails.org/ticket/4764 Thinking about this a little more, I wonder if we should create a new quoting method called "quote_index_name" and use it when quoting indexes for connection adapters. It''s probably premature at this point, but I thought I would mention it. If the mssql, oracle, etc... folks can test this out I''d be grateful. It works for me on mysql. -- John Long http://wiseheartdesign.com
Rick Olson wrote:> Yup, go for it. Post the ticket # and I''ll see about adding it. I''d > appreciate any folks using mssql, oracle, etc to try it out too.Rick, any word on this? -- John Long http://wiseheartdesign.com
> Thinking about this a little more, I wonder if we should create a new > quoting method called "quote_index_name" and use it when quoting indexes > for connection adapters. It''s probably premature at this point, but I > thought I would mention it.I wouldn''t worry about it. If it becomes a big need in the other adapters, it can be added easily enough I suppose. http://dev.rubyonrails.org/changeset/4239 -- Rick Olson http://techno-weenie.net