Migrations creating defunct tables

Question

ENVIRONMENT

ruby 1.9.3p194 (2012-04-20 revision 35410) [x86_64-darwin10.8.0]; Rails 3.2.6; OSX 10.6.8

PROBLEM

Even after resetting our database (rake db:reset), migrations are generating erroneous tables, fields, and schemas, featuring tables, fields, limits, indexes and so forth which have been deprecated by earlier migration specifications.

BACKGROUND

I have moved a github-collaborated project back and forth between two OSX 10.6.8 systems. At a previous juncture, migrations were re-begun from scratch by consolidating intended table definitions in a single initial migration (eliminating the substantial confusion and laborious processing of many previous migration specifications).
For months, the revised migrations have faithfully generated tables and schemas.
After pulling our project to my principal development system after synching the public repository from a portable system, further migrations are retaining many tables and fields which in fact are not defined in any of the existing migrations, and which are neither generated by the same migrations in the development environments of other collaborators. In other words, many defunct/deprecated fields and tables are somehow persisting from specifications which were long before removed from our revised migrations. Thus db:migrate produces erroneous tables and schemas on this one system, even after running rake db:reset.
Presumably then, prior prescriptions are retained somewhere in the development environment, and it is necessary somehow either to remove, to revise, or to override prior, no-longer-existing definitions which nonetheless are precipitating in undesirable tables and fields.
In rectifying this problem, it would be very undesirable to be forced to rescind existing work.

QUESTION

What is the proper and effective way then to regenerate migrations, tables, and schemas which are faithful to the migration specifications we have retained in our project?

score 0 · Answer 1 · edited May 23 '17 at 12:21

0

Please refer to these to references:

Basically, you can run

rake db:schema:load

To load your database schema directly from the schema.rb file, but BE CAREFUL doing this in production since this can delete your production data

edited May 23 '17 at 12:21

Community

1
1

answered Sep 09 '12 at 18:09

Amir Rubin

850
7
11

No, db:schema:load just re-creates your database from the errant schema. What I need to do is re-create a schema which abides by the migrations we've created. Somehow, undesirable, orphaned table definitions are persisting in output schema. – mike montagne Sep 09 '12 at 21:26
PS. I see that other comments warn to "be careful" when running db:schema:load in production environments. I think this usual caution however is misleading, because there is no way that any care whatever can prevent a schema load from destroying the production data that particular schema revisions will inevitably destroy. Moreover, there is no reason to run that "risk," which is inherently resolved instead by running db:migrate. Thus db:schema:load should be used restrictively, virtually without exception, in development and testing environments ― for rebuilding from the existing schema. – mike montagne Sep 10 '12 at 19:35

mike montagne · Accepted Answer · 2012-09-10T19:54:44.680

FIX

By deducing the following procedure, I was finally able to generate schemas and tables which are consistent with our migration specifications:

First, I manually deleted all instructions between the do and final end terminator of my schema.rb, and (whether it mattered or not) I further re-defined the version to 0:
```
ActiveRecord::Schema.define(:version => 0) do  
end
```
Next, I ran rake db:drop db:create db:reset (dropping tables first, simply to eliminate any possibility that artifacts might persist from SQL engine processes).
Finally, before re-populating our tables, I ran rake db:migrate (which for whatever reasons, my environment refused to run with the preceding three commands in a single statement [I regularly otherwise run rake db:drop db:create db:schema:load db:fixtures:load to rebuild and re-populate tables for example]).

NOTES

In attempting to diagnose possible contributing factors, project searches found absolutely no references to deprecated tables or fields in any file other than my errant schema. Thus I presumed that for some reason (possibly a handling error?) erroneous existing schema references were persisting in further processes.
In any case, after gutting all instructions from my local schema.rb, a reset and regular migration succeeded in producing a schema which is indeed faithful to our migration specifications, whereas previous efforts produced schemas erroneously retaining redundant tables, fields, etc., which indeed then, seem to have persisted from the former (defunct) local schema.
Quite possibly therefore, if rake db:reset is apparently generating both tables and schemas from a defunct schema which the process has not rebuilt according to existing migration specifications, then these results (which I have reproduced repeatedly now) indicate a logical flaw in db:reset ― which should itself be purging the prior schema, that consequently, db:reset neither retains nor depends erroneously upon the former schema's specifications.
For the safety of folks who might introduce their own inadvertent errors in writing migrations, I would also recommend that before reconstructing a legitimate schema, db:reset first copy the existing (defunct) schema to a timestamped backup, that problematic migrations can be rectified by potentially necessary reliance on the record of the former schema.

I have since verified that indeed, version has to be set to zero (:version => 0) in order to force a veritable rebuild. — mike montagne, Sep 18 '12 at 23:01

Migrations creating defunct tables

2 Answers2