# Problems with ever-increasing ID value **URL:** \ **Category:** Rails\ **Created:** [July 31, 2006, 10:27pm UTC](https://www.ruby-forum.com/t/problems-with-ever-increasing-id-value/100755 "2006-07-31T22:27:40Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![chrislo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/5e9695/32.png) [@chrislo](https://www.ruby-forum.com/u/chrislo)\ **Post date:** [July 31, 2006, 10:27pm UTC](https://www.ruby-forum.com/t/problems-with-ever-increasing-id-value/100755/1 "2006-07-31T22:27:40Z")
I have a script that I am using to populate a database from a CSV file: RAILS\_ENV = ‘development’ require File.dirname( **FILE** ) + ‘/…/…/config/environment’ require ‘csv’ # Destroy existing data Residence.destroy\_all # IMPORT DATA FROM apartments.csv db\_file = “#{RAILS\_ROOT}/db/apartments.csv” CSV.open(db\_file, “r”, ‘|’) do |row| r = Residence.new r.name = row[0] r.number = row[1] r.street = row[2] r.zip = row[3] r.save end When I run this script it correctly destroys all of the existing data, but new rows are created with increasingly large ID number – ie the “old” IDs are not reused. Has anybody seen this problem ? Perhaps I am misunderstanding the destroy\_all method ? I would like the new rows to begin with ID = 1 . My “Residence” model has the following line, if it is revelevant: has\_many :comments, :dependent =\> :destroy I would appreciate any suggestions, Chris ---
**Author:** ![chrislo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/5e9695/32.png) [@chrislo](https://www.ruby-forum.com/u/chrislo)\ **Post date:** [July 31, 2006, 10:37pm UTC](https://www.ruby-forum.com/t/problems-with-ever-increasing-id-value/100755/2 "2006-07-31T22:37:47Z")
On Jul 31, 2006, at 9:27 pm, Chris L. wrote: > has\_many :comments, :dependent =\> :destroy > > I would appreciate any suggestions, > > Chris Chris, Your database will deliberately does not re-use IDs. You didn’t say what database server you are using, so I don’t know how to override it, but it will be in the docs. For example, SQL Server has an IDENTITY INSERT option, and I think in Postgres you can reset the sequence number (never actually needed to do that). Don’t know about MySQL. In al cases you will have to call hand-rolled SQL to do this using the execute method. Depending on how much data you are storing, it might not be a problem. A 32-bit int can store positive values up to around 2 billion, so you would need to be doing massive bulk loads on a regular basis to run into the limit any time soon. Ashley ---
**Author:** ![chrislo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/5e9695/32.png) [@chrislo](https://www.ruby-forum.com/u/chrislo)\ **Post date:** [July 31, 2006, 10:42pm UTC](https://www.ruby-forum.com/t/problems-with-ever-increasing-id-value/100755/3 "2006-07-31T22:42:11Z")
Ashley and Peter, > Depending on how much data you are storing, it might not be a > problem. A 32-bit int can store positive values up to around 2 > billion, so you would need to be doing massive bulk loads on a > regular basis to run into the limit any time soon. Thank you both for your prompt help, I understand now that this is a database issue. I am using MySQL . Perhaps I can get some inspiration from the “migrations” code. As you suggest I won’t be running out of IDs soon ! It’s more of an annoyance than anything as my URLs have /show/{some large number} in them … perhaps something I need to fix with routing. Kind regards, Chris ---
**Author:** ![chrislo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/5e9695/32.png) [@chrislo](https://www.ruby-forum.com/u/chrislo)\ **Post date:** [August 1, 2006, 12:40am UTC](https://www.ruby-forum.com/t/problems-with-ever-increasing-id-value/100755/4 "2006-08-01T00:40:05Z")
Chris L. wrote: > When I run this script it correctly destroys all of the existing data, > but new rows are created with increasingly large ID number – ie the > “old” IDs are not reused. Has anybody seen this problem ? Perhaps I am > misunderstanding the destroy\_all method ? I would like the new rows to > begin with ID = 1 . … > I would appreciate any suggestions, Chris, I have encountered this problem during development while using migrations to modify a legacy db. I could not find an easy way to reset the sequence in MySQL so I resorted to dropping the table, recreating it and repopulating. Steven ---
**Author:** ![chrislo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/5e9695/32.png) [@chrislo](https://www.ruby-forum.com/u/chrislo)\ **Post date:** [August 1, 2006, 12:54am UTC](https://www.ruby-forum.com/t/problems-with-ever-increasing-id-value/100755/5 "2006-08-01T00:54:47Z")
A database reusing IDs is bad bad bad, so it’s not an “issue.” If you’re afraid of running out of IDs, use bigints. If you really want to “compact” the IDs, a painstaking export/recreate database/import is possible. Joe ---
**Author:** ![chrislo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/5e9695/32.png) [@chrislo](https://www.ruby-forum.com/u/chrislo)\ **Post date:** [August 1, 2006, 1:02am UTC](https://www.ruby-forum.com/t/problems-with-ever-increasing-id-value/100755/6 "2006-08-01T01:02:00Z")
On Jul 31, 2006, at 9:42 pm, Chris L. wrote: > Thank you both for your prompt help, I understand now that this is a > database issue. I am using MySQL . Perhaps I can get some inspiration > from the “migrations” code. As you suggest I won’t be running out > of IDs > soon ! I did try to get on the MySQL site just, to satisfy my curiosity about how you reset the IDs, but the whole site is running like a dog and I gave up. Must be powered by MySQL ;o) It will explain on there somewhere though. > It’s more of an annoyance than anything as my URLs have /show/{some > large number} in them … perhaps something I need to fix with > routing. I wouldn’t do anything complicated to work round the issue you’ll probably tie yourself up in knots. What you could do is update existing records rather than deleting them and re-inserting, but I’d only do this if they actually represent the same apartment - I can’t think of anything more confusing than a database ID or URL that starts randomly changing it meaning! Or, you could add your own application-generated id column, maybe “url\_id”, that you could increment with every row in the csv, but I personally don’t like that idea much. Ashley ---
**Author:** ![chrislo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/5e9695/32.png) [@chrislo](https://www.ruby-forum.com/u/chrislo)\ **Post date:** [August 1, 2006, 10:23am UTC](https://www.ruby-forum.com/t/problems-with-ever-increasing-id-value/100755/7 "2006-08-01T10:23:01Z")
Thank you everyone for your suggestions. I think this is something I don’t need to get too worried about at the moment, when it comes to production I will make an initial import of all the data, and will have “nice” ID numbers again ! I use migrations a lot, so I’m a little reluctant to drop tables from another script, but I will give the MySQL code a try later. Kind regards, Chris ---
**Author:** ![chrislo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/5e9695/32.png) [@chrislo](https://www.ruby-forum.com/u/chrislo)\ **Post date:** [August 1, 2006, 1:47am UTC](https://www.ruby-forum.com/t/problems-with-ever-increasing-id-value/100755/8 "2006-08-01T01:47:00Z")
On 7/31/06, Chris L. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > Thank you both for your prompt help, I understand now that this is a > database issue. I am using MySQL . Perhaps I can get some inspiration > from the “migrations” code. As you suggest I won’t be running out of IDs > soon ! > > It’s more of an annoyance than anything as my URLs have /show/{some > large number} in them … perhaps something I need to fix with routing. execute(“alter table table\_name auto\_increment = 1”) should do the trick. See [http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html](http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html) for more information. Mark ---
**Author:** ![chrislo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/5e9695/32.png) [@chrislo](https://www.ruby-forum.com/u/chrislo)\ **Post date:** [August 1, 2006, 2:17pm UTC](https://www.ruby-forum.com/t/problems-with-ever-increasing-id-value/100755/9 "2006-08-01T14:17:51Z")
Chris L. wrote: > When I run this script it correctly destroys all of the existing data, > but new rows are created with increasingly large ID number – ie the > “old” IDs are not reused. Has anybody seen this problem ? Perhaps I am > misunderstanding the destroy\_all method ? I would like the new rows to > begin with ID = 1 . Look up the MySQL “TRUNCATE” command. It removes all data from a table and restarts the autoincrement(s) from 1. –Al Evans