On 4/17/06, John W. Long <[EMAIL PROTECTED]> 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.com
There 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
_______________________________________________
Rails-core mailing list
Rails-core@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails-core
