Ruby on Rails | Screencasts | Download | Documentation | Weblog | Community | Source

Ticket #10903 (closed defect: untested)

Opened 6 months ago

Last modified 2 months ago

[PATCH] Cannot rename columns if column name is reserved word. (MySQL)

Reported by: jfranklin Assigned to: wilsondealmeida
Priority: normal Milestone:
Component: ActiveRecord Version: 1.2.6
Severity: normal Keywords:
Cc:

Description

Attempting to rename a column that is a reserved word fails in MySQL (and other DBs?). The migration:

rename_column :configurations, :group, :business_unit

results in the following 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 'group business_unit varchar(255)' at line 1: ALTER TABLE configurations CHANGE group business_unit varchar(255)

The migration should instead create the SQL statement:

ALTER TABLE configurations CHANGE configurations.group business_unit varchar(255)

Attachments

rename_columns_if_column_name_is_reserved_word_mysql.diff (460 bytes) - added by cnxs on 01/26/08 02:18:10.
rename columns if column name is reserved word in mysql
rename_columns_if_column_name_is_reserved_word_mysql.2.diff (2.9 kB) - added by wilsondealmeida on 01/26/08 22:06:12.
rename columns if column name is reserved word in mysql

Change History

01/26/08 02:18:10 changed by cnxs

  • attachment rename_columns_if_column_name_is_reserved_word_mysql.diff added.

rename columns if column name is reserved word in mysql

01/26/08 22:06:12 changed by wilsondealmeida

  • attachment rename_columns_if_column_name_is_reserved_word_mysql.2.diff added.

rename columns if column name is reserved word in mysql

01/26/08 22:06:59 changed by wilsondealmeida

  • owner changed from core to wilsondealmeida.

To rename a column, that is a reserved word in MySql, is quoting the table, column old name and column new name. Another good practice is specify the table name before the column name.

# ALTER TABLE `configurations` CHANGE `configurations`.`group` `configurations`.`business_unit` varchar(255)

Edge release in /trunk/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb uses a method to quote the table name:

# /tags/rel_1-2-6/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb
def quote_table_name(name) #:nodoc:
  quote_column_name(name).gsub('.', '`.`')
end

So rails 1.2.6 can insert that method and change the method's implementation rename_column(table_name, column_name, new_column_name) to:

# /tags/rel_1-2-6/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb
def rename_column(table_name, column_name, new_column_name) #:nodoc:
  current_type = select_one("SHOW COLUMNS FROM #{table_name} LIKE '#{column_name}'")["Type"]
  execute "ALTER TABLE #{quote_table_name(table_name)} CHANGE #{quote_table_name(table_name)}.#{quote_column_name(column_name)} #{quote_table_name(table_name)}.#{quote_column_name(new_column_name)} #{current_type}"
end

Test it:

# /tags/rel_1-2-6/activerecord/test/migration_test.rb
def test_rename_column_with_reserved_word
  Person.delete_all      
  begin
    Person.connection.add_column "people", "group", :string
    Person.connection.rename_column "people", "group", "business_unit"
    Person.reset_column_information
    assert Person.column_names.include?("business_unit")
  ensure
    Person.connection.remove_column("people", "business_unit")
  end
end

This test fails on PostgreSql, so Rails 1.2.6 needs to quote the table and column name on ADD COLUMN too:

# /tags/rel_1-2-6/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
def quote_table_name(name)
  %("#{name}")
end

and change the PostgreSQLAdapter add_column(table_name, column_name, type, options = {}) piece of implementation:

# Add the column.
execute("ALTER TABLE #{quote_table_name(table_name)} ADD COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit])}")

Real world test to Mysql and Postgresql:

class CreateConfigurations < ActiveRecord::Migration
  def self.up
    create_table :configurations do |t|
      t.column :name, :string
    end
    add_column :configurations, :group, :string
    rename_column :configurations, :group, :business_unit
  end

  def self.down    
    rename_column :configurations, :business_unit, :group
    remove_column :configurations, :group
    drop_table :configurations
  end
end

(in reply to: ↑ description ) 01/26/08 22:24:55 changed by wilsondealmeida

  • summary changed from Cannot rename columns if column name is reserved word. (MySQL) to [PATCH] Cannot rename columns if column name is reserved word. (MySQL).

01/26/08 22:27:14 changed by wilsondealmeida

  • status changed from new to closed.
  • resolution set to untested.
  • milestone deleted.

05/14/08 16:26:04 changed by dibistore

The last code example works without errors on rails trunk.