# Problem creating table in database **URL:** \ **Category:** Rails\ **Created:** [May 18, 2008, 9:39pm UTC](https://www.ruby-forum.com/t/problem-creating-table-in-database/137953 "2008-05-18T21:39:05Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![df-sean](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/f07891/32.png) [@df-sean](https://www.ruby-forum.com/u/df-sean)\ **Post date:** [May 18, 2008, 9:39pm UTC](https://www.ruby-forum.com/t/problem-creating-table-in-database/137953/1 "2008-05-18T21:39:05Z")
I manually deleted a table in my database through MySQL administrator as well as the db/migrate/foo file that corresponded with it. Now when I try to recreate this table through the terminal I run: ruby/script/generate model Spec, which works then I then put the appropriate information into the db/migrate file. After this when I run rake db:migrate I get an error that says the schema already exists, even though I don’t see the table in MySQL admin. ---
**Author:** ![df-sean](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/f07891/32.png) [@df-sean](https://www.ruby-forum.com/u/df-sean)\ **Post date:** [May 18, 2008, 9:51pm UTC](https://www.ruby-forum.com/t/problem-creating-table-in-database/137953/2 "2008-05-18T21:51:24Z")
If you share the contents of the migration and the exact error message, we’ll be more able to help. Regards, Craig ---
**Author:** ![df-sean](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/f07891/32.png) [@df-sean](https://www.ruby-forum.com/u/df-sean)\ **Post date:** [May 18, 2008, 9:52pm UTC](https://www.ruby-forum.com/t/problem-creating-table-in-database/137953/3 "2008-05-18T21:52:00Z")
Sean, One thing to keep in mind. Migrations create a table called “schema\_info” that tracks the “migration level”. If you manually muck with things, it might get out of sync and then the migration rake task won’t do the right thing. If you have three migrations you’ve run, the schema\_info should say “3”. If not, then things will have trouble. Do a “rake -D” and read up on the rake db:XYZ options (where XYZ are the various namespaced functions you can do). If you can, you might just start the migrations over by migrating back to 0 or even dropping & recreating the db and/or tables. Another thing, if I read correctly… you had, let’s say, 3 migrations, then you manually deleted the stuff from 003\_… and the migration file itself. So really you are back to 002. So go and edit the schema\_info and set it to 2 (or whatever level you need) and try doing a migration again. That might work. -Danimal ---
**Author:** ![df-sean](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/f07891/32.png) [@df-sean](https://www.ruby-forum.com/u/df-sean)\ **Post date:** [May 18, 2008, 10:05pm UTC](https://www.ruby-forum.com/t/problem-creating-table-in-database/137953/4 "2008-05-18T22:05:48Z")
So I decided to recover a backup copy and work from there. Is there a best practice for deleting a table? ---
**Author:** ![df-sean](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/f07891/32.png) [@df-sean](https://www.ruby-forum.com/u/df-sean)\ **Post date:** [May 18, 2008, 10:42pm UTC](https://www.ruby-forum.com/t/problem-creating-table-in-database/137953/5 "2008-05-18T22:42:01Z")
On Sun, May 18, 2008 at 4:05 PM, Sean S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > So I decided to recover a backup copy and work from there. Is there a > best practice for deleting a table? As a rule of thumb, most people don’t edit old migrations, especially if the changes in them are already deployed. Instead, write a simple migration that drops the table. To be honest, though, I’ve never done that (I’ve only worked seriously on one Rails project so far), and an interesting question occurs to me: since I’d be writing the self.up part of the migration to drop the table, would I really try to recreate it in the self.down part? Anyone have any insights? Regards, Craig ---
**Author:** ![df-sean](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/f07891/32.png) [@df-sean](https://www.ruby-forum.com/u/df-sean)\ **Post date:** [May 18, 2008, 10:08pm UTC](https://www.ruby-forum.com/t/problem-creating-table-in-database/137953/6 "2008-05-18T22:08:22Z")
Here is what I was attempting by the way: class CreateSpecs \< ActiveRecord::Migration def self.up create\_table :specs do |t| t.column :user\_id, :integer, :null =\> false t.column :first\_name, :string, :default =\> “” t.column :last\_name, :string, :default =\> “” t.column :gender, :string t.column :birthdate, :date t.column :occupation, :string, :default =\> “” t.column :city, :string, :default =\> “” t.column :state, :string, :default =\> “” t.column :zip\_code, :string, :default =\> “” end end def self.down drop\_table :specs end end SQL (0.000000) Mysql::Error: Table ‘schema\_info’ already exists: CREATE TABLE `schema_info` (version int(11)) SQL (0.000000) Mysql::Error: Table ‘schema\_info’ already exists: CREATE TABLE `schema_info` (version int(11)) SQL (0.000166) SELECT version FROM schema\_info SQL (0.000076) SELECT version FROM schema\_info SQL (0.000075) SELECT version FROM schema\_info SQL (0.000072) SELECT version FROM schema\_info SQL (0.000063) SELECT version FROM schema\_info SQL (0.000122) SELECT \* FROM schema\_info SQL (0.026480) SHOW TABLES SQL (0.011537) SHOW FIELDS FROM `sessions` SQL (0.000749) describe `sessions` SQL (0.023708) SHOW KEYS FROM `sessions` SQL (0.000897) SHOW FIELDS FROM `users` SQL (0.000871) describe `users` SQL (0.000359) SHOW KEYS FROM `users` ---
**Author:** ![df-sean](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/f07891/32.png) [@df-sean](https://www.ruby-forum.com/u/df-sean)\ **Post date:** [May 19, 2008, 4:25am UTC](https://www.ruby-forum.com/t/problem-creating-table-in-database/137953/7 "2008-05-19T04:25:48Z")
Yeah, The whole point of migrations is that they are cumulative. Each migration builds on the previous one(s). So, in essence, you could recreate everything (other than data, usually) by dropping the DB, then remigrating through all of them. And the self.up and self.down is simply to make sure that whatever you “do” in one migration, you “undo” in the self.down, so you can move up or down the migration tree. Think of it as a series of transformations and anti-transformations. If I have 10 migrations, I should be able to migrate back to #6 (rake db:migrate VERSION=6) then pop up to 8 (rake db:migrate VERSION=8) and whatever else. Some other thoughts from one whose done quite a few Rails projects: 1. if it’s only you, it’s less important. Migrations matter a lot more on shared projects. In fact, I’ve seen some patches that suggest that future versions of Rails will include migration modifications to make name collisions less likely. For my part, on projects that I’m the only developer, I only use migrations in a post-launch state. Up until then, I’ll still use migrations temporarily as I generate new models/scaffolds/controllers/ etc., but then I “flatten” them back into the original 001. So that is my only migration and effectively is the “current state” of the app’s schema. I do this by hand… although you could simply copy schema.db into your 001 migration as it’s effectively the same thing. I also built some custom rake tasks to make my life easier. For example, I have “rake db:remigrate” which does the drop table, create table and migrate all in one… saves some typing. And I have a “rake data:load” which cranks through a bootstrap file and loads a bunch of data. I used to just use the fixture data, but I prefer a bootstrap option because it’s just a normal Ruby class (with all the Rails environment, though) and I can do console-like calls. (I know there are options to incorporate this into fixtures, but I decided not to go that route). 1. Once you’ve “launched”, it’s a different world. From that point on, you can’t (or rather… “really don’t want to”) roll back migrations. If you launched with only a 001 migration, that one becomes sacrosanct. So any changes start with 002. Then, I usually do the same thing, but 002 is the “baseline”. I.e. I “flatten” my migrations down to 002 until the changes are pushed live. Then 002 becomes the new baseline. Etc. Again, this only works if you are the only one one developing… or if you work closely and carefully with the rest of the team. Craig: one thing: I found this lovely little snippit that I use: ```auto def self.down drop_created_tables end def self.drop_created_tables File.read( __FILE__ ).scan(/create_table :(\w+)/).each { |table| drop_table table[0].to_sym } end ``` The beauty there is that I don’t have to remember to keep the “self.down” method in sync with the “self.up”. If I were using multiple migrations with lots of add\_columns and the like, this wouldn’t work, but with my “flatten” method of working, it’s nice. My 001 migration is pretty huge on some large projects, but still easy to navigate through. -Danimal On May 18, 2:41 pm, “Craig D.” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![df-sean](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/f07891/32.png) [@df-sean](https://www.ruby-forum.com/u/df-sean)\ **Post date:** [May 19, 2008, 1:22pm UTC](https://www.ruby-forum.com/t/problem-creating-table-in-database/137953/8 "2008-05-19T13:22:43Z")
Note that Rails 2.1 is changing the way that migrations are tracked. Instead of using a sequential number, it uses a utc timestamp of the form yyyymmddhhmm, and instead of a single row schema\_version table there’s a schema\_migrations table which has a row for each migration which is currently in effect. This is quite nice for multi-developer projects, we’ve been using the enhanced\_migrations plugin from revolution healthcare at work for some time which does pretty much the same thing as 2.1 with a different implementation. It’s still possible to do things like collapse multiple migrations down to a checkpoint, but the techniques will be slightly different, and related plugins like automatic\_migrations will probably need to be adapted to the new scheme. – Rick DeNatale My blog on Ruby [http://talklikeaduck.denhaven2.com/](http://talklikeaduck.denhaven2.com/)