Hi, after changing a primary key column name, the auto-increment information (MySQL) and sequence (Oracle) are lost. What is the correct way to rename primary keys? Thanks, Gustavo -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Hi Gustavo, You will have to use migrations to make changes to the database...did you migrate the changes or did you change the primary key field directly from the database? On Dec 14, 2010, at 10:00 PM, Gustavo de Sá Carvalho Honorato wrote:> Hi, > > after changing a primary key column name, the auto-increment information (MySQL) and sequence (Oracle) are lost. What is the correct way to rename primary keys? > > Thanks, > Gustavo > > -- > 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. > For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.-- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
"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.> What is the correct way to > rename > primary keys?rename_column :table, :key, :id> > Thanks, > GustavoBest, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Gustavo de Sá Carvalho Honorato
2010-Dec-14 17:24 UTC
Re: Re: Change primary_key column name
On Tue, Dec 14, 2010 at 2:39 PM, Marnen Laibow-Koser <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> 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 | | +----------+--------------+------+-----+---------+-------+> > > > What is the correct way to > > rename > > primary keys? > > rename_column :table, :key, :id > > > > > Thanks, > > Gustavo > > Best, > -- > Marnen Laibow-Koser > http://www.marnen.org > marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org > > -- > 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. > For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en. >-- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
"Gustavo de Sá Carvalho Honorato" <gustavohonorato wrote in post #968351:> On Tue, Dec 14, 2010 at 2:39 PM, Marnen Laibow-Koser > <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> 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. peterv@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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.