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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com To unsubscribe from this group, send email to rubyonrails-core-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en -~----------~----~----~----~------~----~------~--~---
On 6/5/07, eallik@gmail.com <eallik@gmail.com> wrote:> > > Shouldn''t the Rails MySQL driver escape all identifiers with > backquotes or something equivalent?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. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com To unsubscribe from this group, send email to rubyonrails-core-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en -~----------~----~----~----~------~----~------~--~---
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 On Jun 5, 3:18 pm, "eal...@gmail.com" <eal...@gmail.com> wrote:> 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--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com To unsubscribe from this group, send email to rubyonrails-core-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en -~----------~----~----~----~------~----~------~--~---
On 6/5/07, eallik@gmail.com <eallik@gmail.com> wrote:> > > create_table ''`references`'' do |t| > end > > and, in model Reference, set_table_name to ''`references`''.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. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com To unsubscribe from this group, send email to rubyonrails-core-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en -~----------~----~----~----~------~----~------~--~---
Opened a ticket: http://dev.rubyonrails.org/ticket/8578. Erik On 05/06/07, Mislav Marohnić <mislav.marohnic@gmail.com> wrote:> On 6/5/07, eallik@gmail.com <eallik@gmail.com> wrote: > > > > create_table '`references`' do |t| > > end > > > > and, in model Reference, set_table_name to '`references`'. > > 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. > > > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com To unsubscribe from this group, send email to rubyonrails-core-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en -~----------~----~----~----~------~----~------~--~---
Check and make sure that a ticket isn''t already open first: http://dev.rubyonrails.org/ticket/4127 http://dev.rubyonrails.org/ticket/4593 On Jun 5, 8:09 am, "Erik Allik" <eal...@gmail.com> wrote:> Opened a ticket:http://dev.rubyonrails.org/ticket/8578. > > Erik > > On 05/06/07, Mislav Marohnić <mislav.maroh...@gmail.com> wrote: > > > On 6/5/07, eal...@gmail.com <eal...@gmail.com> wrote: > > > > create_table ''`references`'' do |t| > > > end > > > > and, in model Reference, set_table_name to ''`references`''. > > > 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.--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com To unsubscribe from this group, send email to rubyonrails-core-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en -~----------~----~----~----~------~----~------~--~---