Problem creating table in database

Rails
1

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.

2

If you share the contents of the migration and the exact error
message, we’ll be more able to help.

Regards,
Craig

3

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

4

So I decided to recover a backup copy and work from there. Is there a
best practice for deleting a table?

5

On Sun, May 18, 2008 at 4:05 PM, Sean S.
[email protected] 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

6

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

7

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:

  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.” [email protected]

8

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/