Topic: problems with tables in deployment

I have an application that works fine locally but it having an error that I can't track down when I deploy.
I am running rails gem 2.2.0 and I am running edge version 6145.

The page displays a table that shows schedules for soccer teams. It shows a date, two teams, and the field they will play on. Something is failing when it attempts to get the field names based on the foreign key... Again I have all the relationships defined in the models and it works great locally...

here is the 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 'fields' at line 1: SHOW FIELDS FROM fields

Extracted source (around line #96):
[code = ruby]
93: <td width="72" height="22" class="scores2"><%= schedule.home_team.team_name %></td>
94: <td width="20" height="22" class="scores2">vs.</td>
95: <td width="72" height="22" class="scores2"><%= schedule.away_team.team_name %></td>
96: <td width="10" height="22" class="scores2"><%= schedule.field.field_name %></td>
97: <td width="20" height="22" class="scores3">&nbsp;</td>
98:                         
99: <% if logged_in? && current_user.permissions >= 5 %>
[/code]

From the development.log:

Rendering schedules/list
  Schedule Columns (0.000715)   SHOW FIELDS FROM schedules
  Team Columns (0.000515)   SHOW FIELDS FROM teams
  Team Load (0.000456)   SELECT * FROM teams WHERE (teams.id = 19)
  Team Load (0.000443)   SELECT * FROM teams WHERE (teams.id = 17)
  Field Columns (0.000000)   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 'fields' at line 1: SHOW FIELDS FROM fields

There is nothing in the mongrel.log

Any ideas?

Re: problems with tables in deployment

This is happening because 'fields' is a reserved word in MySQL.  There's been some talk (http://dev.rubyonrails.org/ticket/6430) about fixing ActiveRecord so it properly adds backticks to MySQL table names but nothing's been done yet.  For now the suggestion is to not use reserved words for table names.

So the best bet would be to change your table to something like 'fields_table' and add this in your model:

class Fields < ActiveRecord::Base
  set_table_name :fields_table
end

That way you don't have to change any of your code.

Re: problems with tables in deployment

thanks danger!

any idea why this worked locally and not once deployed?

Re: problems with tables in deployment

It might be an issue with different versions of MySQL?  I really don't know.

Re: problems with tables in deployment

ok, thanks, just curious!