Django's syncdb fails with MySQL errno: 150

Question

First off, here's my current setup:

Django : version 1.3

MySQL : version 4.0.18 (not my 1st choice...)

When I run syncdb, I get the following error:

Creating tables ...
Creating table auth_permission
Creating table auth_group_permissions
Traceback (most recent call last):
  File "C:\path_to_app\manage.py", line 14, in <module>
    execute_manager(settings)
  File "C:\Python27\lib\site-packages\django\core\management\__init__.py", line 438, in execute_manager
    utility.execute()
  File "C:\Python27\lib\site-packages\django\core\management\__init__.py", line 379, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "C:\Python27\lib\site-packages\django\core\management\base.py", line 191, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "C:\Python27\lib\site-packages\django\core\management\base.py", line 220, in execute
    output = self.handle(*args, **options)
  File "C:\Python27\lib\site-packages\django\core\management\base.py", line 351, in handle
    return self.handle_noargs(**options)
  File "C:\Python27\lib\site-packages\django\core\management\commands\syncdb.py", line 101, in handle_noargs
    cursor.execute(statement)
  File "C:\Python27\lib\site-packages\django\db\backends\util.py", line 34, in execute
    return self.cursor.execute(sql, params)
  File "C:\Python27\lib\site-packages\django\db\backends\mysql\base.py", line 86, in execute
    return self.cursor.execute(query, args)
  File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1005, "Can't create table '.\\database_name\\#sql-d64_e75f2.frm' (errno: 150)")

From what I understand it has something to do with how InnoDB handles foreign keys. Here's what my setting file looks like:

DATABASES = {
    'default': {
        ....
        'OPTIONS':  { 'init_command': 'SET table_type=INNODB;', 'charset': 'latin1'}, 
    },
}

When "SET table_type=INNODB" is not specfied, everything runs smoothly. I've looked around on the net and it seems the InnoDB engine doesn't like something about the SQL Django is generating

For now, the only work around I found, was tho create the tables myself, and use inspectDB to generate the models...

Is there a fix for this? Thanks!

Answer 1

using Django 1.5, the error was that mysql creating tables with Innodb as default engine. Solution was to add the following to settings for the database and this created issues with constraints:

DATABASES = {
'default': {
    'ENGINE': 'django.db.backends.mysql',
    'NAME': 'etc....',
    'OPTIONS': {
           "init_command": "SET storage_engine=MyISAM",
    },
},

}

Answer 2

MySQL docs say this:

Cannot create table. If the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. If the error message refers to error –1, table creation probably failed because the table includes a column name that matched the name of an internal InnoDB table.

Post your model code here to let us see what can be wrong. If you can't, and do not know what's wrong, try bisecting the repository revisions, or bisecting the application: turn off half of applications, see if it syncs correctly, then bisect the part that contains the bad model, and so on.