Error performing initial data migration with MySQL

[ma...@brunoduarte.com]

Hey all,

I'm having a problem performing the initial data migration of the installation (after syncdb) with MySQL v5.1.72. 

The error is: django.db.utils.OperationalError: (1025, "Error on rename of './django/#sql-617_1cfe7' to './django/partner_stockrecord' (errno: 150)"

See http://pastebin.com/zKgbQHHK . I couldn't find a solution for this on the South ticket system and forums. Any idea?

Thanks,

Bruno

[Izidor Matušov]

Hi Bruno,

Have you tried to do migration on SQLite? (Just to be sure migrations
are not corrupted)

There seems to be mention of similar looking error in Django doc:
https://docs.djangoproject.com/en/dev/ref/databases/#storage-engines

<snippet>
Specifically, if tables that have a ForeignKey between them use
different storage engines, you may see an error like the following when
running migrate:

_mysql_exceptions.OperationalError: (
1005, "Can't create table '\\db_name\\.#sql-4a8_ab' (errno: 150)"
)
</snippet>

MySQL setup might have issues.

Izidor

[Bruno Duarte]

Thanks Izidor. I did try a migration from SQLite but South threw some errors. iirc, http://south.aeracode.org/ticket/697. For now, I’ve decided to run PostreSQL.

Bruno

[Maik Hoepfel]

Bruno, just to confirm, are we talking about Oscar migrations or ones
you created yourself? If Oscar's, which one is failing?

Most people seem to end up with using PostgreSQL. But I do know that the
migrations get run on Travis against MySQL as well; but I don't know
what version they're running.

Regards,

Maik

On 14/01/14 22:41, ma...@brunoduarte.com wrote:

> --
> https://github.com/tangentlabs/django-oscar
> http://django-oscar.readthedocs.org/en/latest/
> https://twitter.com/django_oscar
> ---
> You received this message because you are subscribed to the Google
> Groups "django-oscar" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to django-oscar...@googlegroups.com.
> Visit this group at http://groups.google.com/group/django-oscar.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-oscar/e7c26d37-3101-401b-b0ad-3510bf06ea57%40googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.

[Bruno Duarte]

I also had problems migration from an existing SQLite DB in the past but the error below was for an “empty” Oscar migration (i.e., the very first one after syncdb). The error was with the partner fixtures (see paste bin).

B

> To view this discussion on the web visit https://groups.google.com/d/msgid/django-oscar/52D68DEB.7080602%40tangentsnowball.com.

[Sergei G]

I have a very similar migration issue. I had little success with South and SQLite as a dev setup.

I have

DATABASES = {

    'default': {

        'ENGINE': 'django.db.backends.sqlite3',

        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),

        'ATOMIC_REQUESTS': True,

    }

}

I create db with command:

    ./manage.py syncdb --noinput

It reported that the following apps are not synced and recommended to use migrations:

 - oscar.apps.analytics

 - oscar.apps.checkout

 - oscar.apps.address

 - oscar.apps.shipping

 - oscar.apps.catalogue

 - oscar.apps.partner

 - oscar.apps.basket

 - oscar.apps.payment

 - oscar.apps.offer

 - oscar.apps.order

 - oscar.apps.promotions

 - oscar.apps.voucher

 - oscar.apps.wishlists

 - oscar.apps.dashboard.catalogue

 - oscar.apps.dashboard.ranges

 - oscar.apps.customer

I then run initial migration for each app listed above like this:

./manage.py oscar.apps.analytics --initial

Then I run migrate and get an error message:

(zinnia-dev) SergeiMBP:rslp sergei$ ./manage.py migrate

Running migrations for analytics:

 - Migrating forwards to 0002_initial.

 > customer:0001_initial

 > catalogue:0001_initial

 > analytics:0001_initial

 > analytics:0002_initial

FATAL ERROR - The following SQL query failed: CREATE TABLE "analytics_productrecord" ("id" integer NOT NULL PRIMARY KEY, "product_id" integer NOT NULL UNIQUE, "num_views" integer unsigned NOT NULL, "num_basket_additions" integer unsigned NOT NULL, "num_purchases" integer unsigned NOT NULL, "score" real NOT NULL)

The error was: table "analytics_productrecord" already exists

 ! 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:   = DROP TABLE "analytics_productrecord"; []

   = DROP TABLE "analytics_userrecord"; []

   = DROP TABLE "analytics_userproductview"; []

   = DROP TABLE "analytics_usersearch"; []

 ! The South developers regret this has happened, and would

 ! like to gently persuade you to consider a slightly

 ! easier-to-deal-with DBMS (one that supports DDL transactions)

 ! NOTE: The error which caused the migration to fail is further up.

Error in migration: analytics:0002_initial

Traceback (most recent call last):

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/Django-1.6.1-py3.3.egg/django/db/backends/util.py", line 53, in execute

    return self.cursor.execute(sql, params)

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/Django-1.6.1-py3.3.egg/django/db/backends/sqlite3/base.py", line 450, in execute

    return Database.Cursor.execute(self, query, params)

sqlite3.OperationalError: table "analytics_productrecord" already exists

The above exception was the direct cause of the following exception:

Traceback (most recent call last):

  File "./manage.py", line 10, in <module>

    execute_from_command_line(sys.argv)

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/Django-1.6.1-py3.3.egg/django/core/management/__init__.py", line 399, in execute_from_command_line

    utility.execute()

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/Django-1.6.1-py3.3.egg/django/core/management/__init__.py", line 392, in execute

    self.fetch_command(subcommand).run_from_argv(self.argv)

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/Django-1.6.1-py3.3.egg/django/core/management/base.py", line 242, in run_from_argv

    self.execute(*args, **options.__dict__)

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/Django-1.6.1-py3.3.egg/django/core/management/base.py", line 285, in execute

    output = self.handle(*args, **options)

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/South-0.8.4-py3.3.egg/south/management/commands/migrate.py", line 111, in handle

    ignore_ghosts = ignore_ghosts,

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/South-0.8.4-py3.3.egg/south/migration/__init__.py", line 220, in migrate_app

    success = migrator.migrate_many(target, workplan, database)

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/South-0.8.4-py3.3.egg/south/migration/migrators.py", line 254, in migrate_many

    result = migrator.__class__.migrate_many(migrator, target, migrations, database)

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/South-0.8.4-py3.3.egg/south/migration/migrators.py", line 329, in migrate_many

    result = self.migrate(migration, database)

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/South-0.8.4-py3.3.egg/south/migration/migrators.py", line 133, in migrate

    result = self.run(migration, database)

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/South-0.8.4-py3.3.egg/south/migration/migrators.py", line 114, in run

    return self.run_migration(migration, database)

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/South-0.8.4-py3.3.egg/south/migration/migrators.py", line 84, in run_migration

    migration_function()

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/South-0.8.4-py3.3.egg/south/migration/migrators.py", line 60, in <lambda>

    return (lambda: direction(orm))

  File "/Users/sergei/Sites/zinnia-dev/ext/django-oscar/oscar/apps/analytics/migrations/0002_initial.py", line 18, in forwards

    ('score', self.gf('django.db.models.fields.FloatField')(default=0.0)),

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/South-0.8.4-py3.3.egg/south/db/generic.py", line 47, in _cache_clear

    return func(self, table, *args, **opts)

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/South-0.8.4-py3.3.egg/south/db/generic.py", line 361, in create_table

    "columns": ', '.join([col for col in columns if col]),

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/South-0.8.4-py3.3.egg/south/db/generic.py", line 282, in execute

    cursor.execute(sql, params)

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/Django-1.6.1-py3.3.egg/django/db/backends/util.py", line 69, in execute

    return super(CursorDebugWrapper, self).execute(sql, params)

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/Django-1.6.1-py3.3.egg/django/db/backends/util.py", line 53, in execute

    return self.cursor.execute(sql, params)

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/Django-1.6.1-py3.3.egg/django/db/utils.py", line 99, in __exit__

    six.reraise(dj_exc_type, dj_exc_value, traceback)

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/Django-1.6.1-py3.3.egg/django/utils/six.py", line 490, in reraise

    raise value.with_traceback(tb)

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/Django-1.6.1-py3.3.egg/django/db/backends/util.py", line 53, in execute

    return self.cursor.execute(sql, params)

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/Django-1.6.1-py3.3.egg/django/db/backends/sqlite3/base.py", line 450, in execute

    return Database.Cursor.execute(self, query, params)

django.db.utils.OperationalError: table "analytics_productrecord" already exists

[Sergei G]

I installed Postgre SQL. I made sure I dropped and created empty database. Here is the log of exactly what I did on command line:

(zinnia-dev) SergeiMBP:rslp sergei$ ./manage.py syncdb --noinput

Syncing...

Creating tables ...

Creating table django_admin_log

Creating table auth_permission

Creating table auth_group_permissions

Creating table auth_group

Creating table auth_user_groups

Creating table auth_user_user_permissions

Creating table auth_user

Creating table django_content_type

Creating table django_session

Creating table django_flatpage_sites

Creating table django_flatpage

Creating table django_site

Creating table django_comments

Creating table django_comment_flags

Creating table tagging_tag

Creating table tagging_taggeditem

Creating table south_migrationhistory

Creating table reviews_productreview

Creating table reviews_vote

Creating table thumbnail_kvstore

Installing custom SQL ...

Installing indexes ...

Installed 0 object(s) from 0 fixture(s)

Synced:

 > django.contrib.admin

 > django.contrib.auth

 > django.contrib.contenttypes

 > django.contrib.sessions

 > django.contrib.messages

 > django.contrib.staticfiles

 > django.contrib.flatpages

 > django.contrib.sites

 > django.contrib.comments

 > tagging

 > mptt

 > compressor

 > south

 > oscar

 > oscar.apps.catalogue.reviews

 > oscar.apps.dashboard

 > oscar.apps.dashboard.reports

 > oscar.apps.dashboard.offers

 > oscar.apps.dashboard.reviews

 > oscar.apps.dashboard.communications

 > haystack

 > treebeard

 > sorl.thumbnail

Not synced (use migrations):

 - oscar.apps.analytics

 - oscar.apps.checkout

 - oscar.apps.address

 - oscar.apps.shipping

 - oscar.apps.catalogue

 - oscar.apps.partner

 - oscar.apps.basket

 - oscar.apps.payment

 - oscar.apps.offer

 - oscar.apps.order

 - oscar.apps.promotions

 - oscar.apps.voucher

 - oscar.apps.wishlists

 - oscar.apps.dashboard.catalogue

 - oscar.apps.dashboard.ranges

 - oscar.apps.customer

 - zinnia

(use ./manage.py migrate to migrate these)

(zinnia-dev) SergeiMBP:rslp sergei$ ./manage.py migrate

Running migrations for analytics:

 - Migrating forwards to 0007_initial.

 > customer:0001_initial

 > catalogue:0001_initial

 > analytics:0001_initial

 > analytics:0002_initial

FATAL ERROR - The following SQL query failed: CREATE TABLE "analytics_productrecord" ("id" serial NOT NULL PRIMARY KEY, "product_id" integer NOT NULL UNIQUE, "num_views" integer CHECK ("num_views" >= 0) NOT NULL, "num_basket_additions" integer CHECK ("num_basket_additions" >= 0) NOT NULL, "num_purchases" integer CHECK ("num_purchases" >= 0) NOT NULL, "score" double precision NOT NULL)

The error was: relation "analytics_productrecord" already exists

Error in migration: analytics:0002_initial

Traceback (most recent call last):

  File "/Users/sergei/Sites/zinnia-dev/lib/python3.3/site-packages/Django-1.6.1-py3.3.egg/django/db/backends/util.py", line 53, in execute

    return self.cursor.execute(sql, params)

psycopg2.ProgrammingError: relation "analytics_productrecord" already exists

django.db.utils.ProgrammingError: relation "analytics_productrecord" already exists

I have tried variation on 

./manage.py schemamigration each_app_name --initial

./manage.py migrate 

with the same results.

[Sergei G]

I figured it out.

Command 

    find .. -name migrations

revealed that I had around 9 migration files per oscar application. I think this was caused by me running schema migration multiple times.

running

  rm -r ../path/to/migrations/

cleared that issue.

[Allan Porras]

I have the same problem. Someone resolved it?

[Allan Porras]

I'm attaching my screenshot with error when I try "manage.py migrate".   I think Oscar's tables doesn't created.  What I need to do?

Thanks,

On Tuesday, January 14, 2014 4:41:09 PM UTC-6, ma...@brunoduarte.com wrote:

[Chris Hawkes]

which version of oscar and which version of django?   I just got through a setup process using mysql and oscar and it required some individual initial schemamigrations from a handful of modules included in south, but after going through each one it's working fine.  I'm running django 1.6 and the latest Oscar on MySQL

[Maik Hoepfel]

If you don't override apps, you really shouldn't need to have "individual schema migrations". Chris, can you describe what the problem you were running into was?

Allan, I'll need a bit more information to try to recreate your problem. What's the Oscar version, Django version, MySQL version? We test the migrations against MySQL on Travis, so they should generally work.

Cheers,

Maik

[Allan Porras]

Hi there and thank you very much for your reply.  

I have Django-oscar 0.7.2 and Django 1.6.5, but I have solved this issues. It was a schemamigration mistake on local.

Thanks. Regards, 

On Tuesday, January 14, 2014 4:41:09 PM UTC-6, ma...@brunoduarte.com wrote: