search

chb / indivo_server

The Indivo™ Personally Controlled Health Record
http://indivohealth.org
GNU General Public License v3.0
103 stars 40 forks source link

Failed Populating Database with MySQL and South #10

Closed jgkim closed 12 years ago

jgkim commented 12 years ago

As a followup to http://mail.chip.org/pipermail/indivohealth/2012-March/000929.html, I'm opening this issue.

When I run the reset script (utils/reset.py), it fails with the following message:

You have requested a reset of the database.
This will IRREVERSIBLY DESTROY all data currently in the 'indivo' database,
and return each table to its initial state.
Are you sure you want to do this?

    Type 'yes' to continue, or 'no' to cancel: yes
RESETTING DATABASE...
Flushing the Database of existing data...
Database nonexistent or corrupted, or Database drop requeseted. Attempting to drop database...
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'indivo' database [y/N] y
Database "indivo" dropped
Creating the Database...
Syncing and Migrating the database...
 ! Error found during real run of migration! Aborting.

 ! Since you have a database that does not support running
 ! schema-altering statements in transactions, we have had 
 ! to leave it in an interim state between migrations.

! You *might* be able to recover with:   = ALTER TABLE `indivo_lab` DROP COLUMN `normal_range_minimum` CASCADE; []
   = ALTER TABLE `indivo_lab` DROP COLUMN `normal_range_maximum` CASCADE; []
   = ALTER TABLE `indivo_lab` DROP COLUMN `non_critical_range_minimum` CASCADE; []
   = ALTER TABLE `indivo_lab` DROP COLUMN `non_critical_range_maximum` CASCADE; []

 ! The South developers regret this has happened, and would
 ! like to gently persuade you to consider a slightly
 ! easier-to-deal-with DBMS.
 ! NOTE: The error which caused the migration to fail is further up.
Traceback (most recent call last):
  File "utils/reset.py", line 148, in <module>
    management.call_command('migrate', verbosity=0)
  File "/opt/homebrew/Cellar/python/2.7.2/lib/python2.7/site-packages/django/core/management/__init__.py", line 166, in call_command
    return klass.execute(*args, **defaults)
  File "/opt/homebrew/Cellar/python/2.7.2/lib/python2.7/site-packages/django/core/management/base.py", line 220, in execute
    output = self.handle(*args, **options)
  File "/opt/homebrew/Cellar/python/2.7.2/lib/python2.7/site-packages/south/management/commands/migrate.py", line 105, in handle
    ignore_ghosts = ignore_ghosts,
  File "/opt/homebrew/Cellar/python/2.7.2/lib/python2.7/site-packages/south/migration/__init__.py", line 191, in migrate_app
    success = migrator.migrate_many(target, workplan, database)
  File "/opt/homebrew/Cellar/python/2.7.2/lib/python2.7/site-packages/south/migration/migrators.py", line 221, in migrate_many
    result = migrator.__class__.migrate_many(migrator, target, migrations, database)
  File "/opt/homebrew/Cellar/python/2.7.2/lib/python2.7/site-packages/south/migration/migrators.py", line 292, in migrate_many
    result = self.migrate(migration, database)
  File "/opt/homebrew/Cellar/python/2.7.2/lib/python2.7/site-packages/south/migration/migrators.py", line 125, in migrate
    result = self.run(migration)
  File "/opt/homebrew/Cellar/python/2.7.2/lib/python2.7/site-packages/south/migration/migrators.py", line 99, in run
    return self.run_migration(migration)
  File "/opt/homebrew/Cellar/python/2.7.2/lib/python2.7/site-packages/south/migration/migrators.py", line 81, in run_migration
    migration_function()
  File "/opt/homebrew/Cellar/python/2.7.2/lib/python2.7/site-packages/south/migration/migrators.py", line 57, in <lambda>
    return (lambda: direction(orm))
  File "/usr/local/indivo_server/indivo/migrations/0002_lab_schema_update.py", line 12, in forwards
    db.alter_column('indivo_pha', 'principal_ptr_id', self.gf('django.db.models.fields.related.OneToOneField')(to=orm['indivo.Principal'], unique=True))
  File "/opt/homebrew/Cellar/python/2.7.2/lib/python2.7/site-packages/south/db/generic.py", line 397, in alter_column
    field.rel.to._meta.get_field(field.rel.field_name).column
  File "/opt/homebrew/Cellar/python/2.7.2/lib/python2.7/site-packages/south/db/generic.py", line 150, in execute
    cursor.execute(sql, params)
  File "/opt/homebrew/Cellar/python/2.7.2/lib/python2.7/site-packages/django/db/backends/mysql/base.py", line 86, in execute
    return self.cursor.execute(query, args)
  File "/opt/homebrew/Cellar/python/2.7.2/lib/python2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.4-x86_64.egg/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/opt/homebrew/Cellar/python/2.7.2/lib/python2.7/site-packages/MySQL_python-1.2.3-py2.7-macosx-10.4-x86_64.egg/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1005, "Can't create table 'indivo.#sql-7a07_11' (errno: 121)")

The sequence that I took to avoid the error was:

  1. Comment out the line south in settings.py.
  2. python manage.py syncdb
  3. Uncomment the line south in settings.py.
  4. python manage.py syncdb
  5. python utils/reset.py --no-syncdb -b -c
thisisdhaas commented 12 years ago

For whatever reason, it looks like south is having trouble running the migration on MySQL. Is there anything in the DB error logs? By default they should be in /var/lib/mysql/server.hostname.err.

Also, what version are you running of:

I'm unable to reproduce this on:

jgkim commented 12 years ago

The following line is added to the DB error log

120406 18:54:51 [Warning] Invalid (old?) table or database name '#sql-135e_17'

The versions I'm running of are:

jgkim commented 12 years ago

It works without any errors in the following circumstance:

jgkim commented 12 years ago

It seems the problem was due to South 0.7.3, and it works smoothly now with South 0.7.4.

thisisdhaas commented 12 years ago

Interesting. Well it's good to have this as a reference of the incompatibility. Glad you cleared it up.

analytik commented 10 years ago

I'd like to note here, for anyone searching around for this, using

When I work with a project and start adding up migrations, at some point, the database gets reliably messed up. Adding an example would be too much work (contact me if you need a sample app), but basically what happens is that I get a mysql error like

(1061, "Duplicate key name 'fragile_assetdata_asset_id_7f82caf7_uniq'")

Where fragile is django app, assetdata.asset_id is a foreign key to asset.id, which I'm changing from 1:n to 1:1 field.

So currently it happens with this migration

        # Changing field 'AssetData.asset'
        db.alter_column(u'fragile_assetdata', 'asset_id', self.gf('django.db.models.fields.related.OneToOneField')(to=orm['fragile.Asset'], unique=True))
        # Adding unique constraint on 'AssetData', fields ['asset']
        db.create_unique(u'fragile_assetdata', ['asset_id'])

Previously it happened with this migration:

        db.alter_column(u'fragile_currentprogress', 'asset_id',
            self.gf('django.db.models.fields.related.ForeignKey')(to=orm['fragile.Asset'],
                null=True, blank=True))

I'm suspecting the problem is in MySQL itself, but trying a few different versions brought the same result. Restoring the db files from backup did the same.

However, it doesn't seem to be happening when I apply the migrations one by one (i.e. it works when I'm developing a new feature, migrating, developing something else, migrating...). So I'm suspecting some sort of racing condition in MySQL.

My workaround was to flatten the migration, however unpleasant it might be, at least I don't have production code running yet...