"Gustavo de Sá Carvalho Honorato" <gustavohonorato wrote in post
#968351:
> On Tue, Dec 14, 2010 at 2:39 PM, Marnen Laibow-Koser
> <[email protected]> wrote:
>>
>> "Gustavo de S Carvalho Honorato" <gustavohonorato wrote in post
>> #968329:
>> > Hi,
>> >
>> > after changing a primary key column name, the auto-increment information
>> > (MySQL) and sequence (Oracle) are lost.
>>
>> So what? The actual value of the key should never be significant
>> anyway.
>
> Sorry Marnen, I think I didn't make myself clear, let me show one
> example which ilustrates betteer what I'm trying to say. Consider this
> user table, in MySQL database:
>
> +----------+--------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +----------+--------------+------+-----+---------+----------------+
> | id | int(11) | NO | PRI | NULL | auto_increment |
> | username | varchar(255) | YES | | NULL | |
> | password | varchar(255) | YES | | NULL | |
> +----------+--------------+------+-----+---------+----------------+
>
> Look that id column has auto_increment extra.
>
> When I rename :id column using "rename_column :users, :id, :key" my
> new :key column loses its "auto_increment" as bellow:
>
> +----------+--------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +----------+--------------+------+-----+---------+-------+
> | key | int(11) | NO | PRI | 0 | |
> | username | varchar(255) | YES | | NULL | |
> | password | varchar(255) | YES | | NULL | |
> +----------+--------------+------+-----+---------+-------+
I did not get this to work with the default rename_column function
(I believe there is a bug, more details below).
So at first, I resorted to this (based on the standard mysql
documentation for ALTER TABLE/CREATE TABLE):
class RenamePrimaryKeyPayments < ActiveRecord::Migration
def self.up
connection.execute("ALTER TABLE payments CHANGE id `key` INTEGER NOT
NULL AUTO_INCREMENT;")
end
def self.down
connection.execute("ALTER TABLE payments CHANGE `key` id INTEGER NOT
NULL AUTO_INCREMENT;")
end
end
Note: be careful with direct SQL here, you cannot access the `key`
column
without the backticks, maybe a reserved word in SQL ...
mysql> describe payments;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| key | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | NO | | NULL | |
| testing | varchar(255) | YES | | NULL | |
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> select `key`, testing from payments;
+-----+---------+
| key | testing |
+-----+---------+
| 1 | alfa |
| 2 | beta |
| 3 | gamma |
| 4 | delta |
+-----+---------+
4 rows in set (0.00 sec)
mysql> select id, testing from payments;
+----+---------+
| id | testing |
+----+---------+
| 1 | alfa |
| 2 | beta |
| 3 | gamma |
| 4 | delta |
+----+---------+
4 rows in set (0.00 sec)
mysql> describe payments;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | NO | | NULL | |
| testing | varchar(255) | YES | | NULL | |
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql>
The good news is that the AUOT_INCREMENT automatically does the right
thing and starts inserting new records with the primary key 1 higher
than
the existing records. I was concerned how this works out for records
for which a transaction was started, but was rolled back ... or if the
highest record was deleted in the meanwhile).
mysql> DELETE from payments WHERE id = 4;
Query OK, 1 row affected (0.00 sec)
mysql> select id, testing from payments;
+----+---------+
| id | testing |
+----+---------+
| 1 | alfa |
| 2 | beta |
| 3 | gamma |
+----+---------+
3 rows in set (0.00 sec)
mysql> INSERT INTO payments (testing) VALUES ('zeta') ;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select id, testing from payments;
+----+---------+
| id | testing |
+----+---------+
| 1 | alfa |
| 2 | beta |
| 3 | gamma |
| 5 | zeta | # GOOD ! it remembers about the 4 that is consumed
+----+---------+
4 rows in set (0.00 sec)
mysql> DELETE from payments WHERE id = 5;
Query OK, 1 row affected (0.00 sec)
# migrating ...
mysql> select key, testing from payments;
ERROR 1064 (42000): 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, testing from payments' at line 1
mysql> select `key`, testing from payments;
+-----+---------+
| key | testing |
+-----+---------+
| 1 | alfa |
| 2 | beta |
| 3 | gamma |
| 6 | peta | # GOOD !! it still remembers the 4 and 5 consumed
+-----+---------+
4 rows in set (0.00 sec)
mysql> DELETE FROM payments WHERE `key` = 6; # deleting the 6 here
Query OK, 1 row affected (0.00 sec)
Changing the migration to use "rename_column" first and re-add the
AUTO_INCREMENT afterwards:
def self.down
rename_column :payments, :key, :id
connection.execute("ALTER TABLE payments CHANGE id id INTEGER NOT
NULL AUTO_INCREMENT;")
end
which does:
...
SQL (0.1ms) SELECT `schema_migrations`.`version` FROM
`schema_migrations`
SQL (0.3ms) SHOW COLUMNS FROM `payments` LIKE 'key'
SQL (8.3ms) ALTER TABLE `payments` CHANGE `key` `id` int(11) NOT NULL
SQL (5.2ms) ALTER TABLE payments CHANGE id id INTEGER NOT NULL
AUTO_INCREMENT;
AREL (0.5ms) DELETE FROM `schema_migrations` WHERE
`schema_migrations`.`version` = '20101217234428'
...
and then you get:
mysql> select id, testing FROM payments ;
+----+---------+
| id | testing |
+----+---------+
| 1 | alfa |
| 2 | beta |
| 3 | gamma |
+----+---------+
3 rows in set (0.00 sec)
mysql> INSERT INTO payments (testing) VALUES ('peta');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select id, testing FROM payments ;
+----+---------+
| id | testing |
+----+---------+
| 1 | alfa |
| 2 | beta |
| 3 | gamma |
| 4 | peta | # BAD, we forgot the consumed id's 5,6,7
+----+---------+
4 rows in set (0.00 sec)
So, I believe the "direct" migration from the 1 column name to the new
column name, while maintaining the AUTO_INCREMENT is required.
Is it possible to add this as an extra option in the Rails migration ?
I did not immediately find on the api.rubyonrails.org site how you could
give an additional option (as a 4th argument then), to rename_column:
rename_column(table_name, column_name, new_column_name).
Digging deeper ... this could be a bug in Rails 3.0.3 ....
This code in lib/active_record/connection_adapters around line 499,
current_type = select_one("SHOW COLUMNS FROM
#{quote_table_name(table_name)} LIKE '#{column_name}'")["Type"]
rename_column_sql = "ALTER TABLE #{quote_table_name(table_name)} CHANGE
#{quote_column_name(column_name)} #{quote_column_name(new_column_name)}
#{current_type}"
Only takes the "type" into account, but ... when inspecting what comes
back from the database, there is also "extra" info:
I instrumented:
select_one("SHOW COLUMNS FROM #{quote_table_name(table_name)} LIKE
'#{column_name}'").inspect
and got:
{"Default"=>nil, "Extra"=>"auto_increment", "Field"=>"key",
"Key"=>"PRI", "Null"=>"NO", "Type"=>"int(11)"}
And I have the impression the "Extra" field is not used, but is required
by mysql to keep the auto_increment active on that column.
So, with this changed version, the behaviour is actually better, with a
standard "rename_column" and not requiring raw SQL in the migration.
pet...@asus:~/g/activerecord-3.0.3/lib/active_record/connection_adapters$
diff -u mysql_adapter.rb.ORIG mysql_adapter.rb
--- mysql_adapter.rb.ORIG 2010-12-18 02:23:26.000000000 +0100
+++ mysql_adapter.rb 2010-12-18 02:22:47.000000000 +0100
@@ -496,7 +496,9 @@
else
raise ActiveRecordError, "No such column:
#{table_name}.#{column_name}"
end
- current_type = select_one("SHOW COLUMNS FROM
#{quote_table_name(table_name)} LIKE '#{column_name}'")["Type"]
+ column_info = select_one("SHOW COLUMNS FROM
#{quote_table_name(table_name)} LIKE '#{column_name}'")
+ current_type = column_info["Type"]
+ current_type << " AUTO_INCREMENT" if column_info["Extra"] =~
/auto_increment/i
rename_column_sql = "ALTER TABLE
#{quote_table_name(table_name)} CHANGE #{quote_column_name(column_name)}
#{quote_column_name(new_column_name)} #{current_type}"
add_column_options!(rename_column_sql, options)
execute(rename_column_sql)
The migration code (also testing for a column that is not
AUTO_INCREMENT):
class RenamePrimaryKeyPayments < ActiveRecord::Migration
def self.up
rename_column :payments, :id, :key
rename_column :payments, :testing, :greek
end
def self.down
rename_column :payments, :key, :id
rename_column :payments, :greek, :testing
end
end
Up migration log:
SQL (0.1ms) SELECT `schema_migrations`.`version` FROM
`schema_migrations`
SQL (0.4ms) SHOW COLUMNS FROM `payments` LIKE 'id'
SQL (2.9ms) ALTER TABLE `payments` CHANGE `id` `key` int(11)
AUTO_INCREMENT NOT NULL
SQL (0.5ms) SHOW COLUMNS FROM `payments` LIKE 'testing'
SQL (3.4ms) ALTER TABLE `payments` CHANGE `testing` `greek`
varchar(255) DEFAULT NULL
SQL (1.2ms) INSERT INTO `schema_migrations` (`version`) VALUES
('20101217234428')
Down migration:
SQL (0.1ms) SELECT `schema_migrations`.`version` FROM
`schema_migrations`
SQL (0.3ms) SHOW COLUMNS FROM `payments` LIKE 'key'
SQL (3.1ms) ALTER TABLE `payments` CHANGE `key` `id` int(11)
AUTO_INCREMENT NOT NULL
SQL (0.4ms) SHOW COLUMNS FROM `payments` LIKE 'greek'
SQL (2.6ms) ALTER TABLE `payments` CHANGE `greek` `testing`
varchar(255) DEFAULT NULL
AREL (1.5ms) DELETE FROM `schema_migrations` WHERE
`schema_migrations`.`version` = '20101217234428'
If this is relevant, I could try to file bug ticket against Rails 3.0.3.
(and try to add tests).
HTH,
Peter
--
Posted via http://www.ruby-forum.com/.
--
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
