711127 - ./manage.py migrate Can't create table 'mozillians4.#sql-2a0_314'

Status: VERIFIED FIXED

(Participation Infrastructure :: Phonebook, defect)

Description

[Austin King [:ozten]]

In a fresh environment, I'm unable to create a valid database.

Steps to Reproduce:
1) In mysql `create database mozillians-fresh`
2) Edit local.py and point to new db
3) ./manage.py syncdb && ./manage.py migrate

Expected:
Commands finish normally, webapp works

Actual:
Errors. Webapp has 'is_vouched' column missing, Django model blows up.

Looking at the output, DROP TABLE profile_groups fails.

... output ...
13:21:1323811313 south:DEBUG south execute "DROP TABLE `profile_groups` CASCADE;" with params "[]" :/home/vagrant/mozillians/vendor/lib/python/south/db/generic.py:145
 ! 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.
... more output...
_mysql_exceptions.OperationalError: (1005, "Can't create table 'mozillians4.#sql-2a0_314' (errno: 150)")

Comment 1

[Austin King [:ozten]]

More debugging:
fuller output http://pastebin.mozilla.org/1404486
show innodb status  http://pastebin.mozilla.org/1404497

Comment 2

[Austin King [:ozten]]

I'm working on preparing a rollback commit, in case we need it for pull request 59.

This bug will block me from testing it. I need help being able to create a fresh db.

Maybe it's just user error?

Comment 3

[Austin King [:ozten]]

I need help here. Or someone to cut a fresh VM. or someone to tell me what I'm doing wrong ;)

I'm going to keep fixing bugs and doing my best to test before committing, but having a fully working env would reduce risk.

Comment 4

[Jen Fong-Adwent [:ednapiranha]]

I suspect this is a foreign key constraint conflict in migrations because the mozillians db is defaulting to innodb (locally) and dropping a table that might have an association causes south to explode. Probably an order of migration operations by defaulting to `manage.py migrate` and 'users' is last on the list

Comment 5

[Jen Fong-Adwent [:ednapiranha]]

this seems to work:

./manage.py syncdb
./manage.py migrate users
./manage.py migrate

Comment 6

[Austin King [:ozten]]

@jfong made some good observiations via IRC

* Why ids mozillians-dev not having 'is_vouched' errors

* The syncdb and migrate steps could work on MyISAM backed tables

I've tested this and you have to go out of your way by putting MyISAM in local.py's DATABASE setting. Otherwise you'll get InnoDB tables. Edit my.cnf doesn't affect the migration.

From mozillians-dev DB:
select * from schema_version;
| version |
+---------+
|       8 |


select * from south_migrationhistory;
+----+-----------+-----------------------------------------------------------------------------+---------------------+
| id | app_name  | migration                                                                   | applied             |
+----+-----------+-----------------------------------------------------------------------------+---------------------+
|  1 | users     | 0001_initial                                                                | 2011-12-05 22:33:29 |
|  2 | users     | 0002_auto__add_field_userprofile_is_vouched__add_field_userprofile_vouched_ | 2011-12-05 22:33:29 |
|  3 | users     | 0003_auto__add_field_userprofile_website                                    | 2011-12-05 22:33:29 |
|  4 | phonebook | 0001_initial                                                                | 2011-12-05 22:34:45 |
|  5 | groups    | 0001_initial                                                                | 2011-12-05 22:35:05 |
|  6 | locations | 0001_initial                                                                | 2011-12-07 23:18:49 |

Comment 7

[Austin King [:ozten]]

(In reply to Austin King [:ozten] from comment #6)

Mid-air, sorry for redundant comment.

There are 6 south migrations in master, so mozillians-dev looks healthy.

Comment 8

[Owen Coutts [:tallOwen]]

This should be fixed by reordring the apps in settings.py (which we did a while ago).

Comment 9

[Matt Brandt [:mbrandt]]

Bumping to verified per the passage of time, the [qa-] nature of the bug, and comment 8.