MySQL error (1005, "Can't create table '\\db_name\\.#sql-4a8_98' (errno: 150)") in Django 1.4

[Hans Andersen]

I am getting MySQL (errno: 150) while trying to run syncdb.

I have tested the same model code on Django 1.3.1 and found no problems in running syncdb, so the issue seems to has been introduced in Django 1.4, and is affecting Django 1.4+, as I has also tested on Django SVN.

My system info are:

• Ubuntu 12.04
• Python 2.7.3
• Django 1.4
• MySQL 5.5.22

My models are:

from django.db import models
from django.contrib.auth.models import User
from django.utils.translation import gettext_lazy as _

class Report(models.Model):
        TOPIC_CHOICES = (
        ...
        )

        user = models.ForeignKey(User)
        topic = models.PositiveSmallIntegerField(_('Report topic'), choices=TOPIC_CHOICES, default=None)
        custom_text = models.TextField(_("Further comments"), \
                help_text=_("It is important to give as precise a description, with as many details, of the problem as possible, for our development team to be able properly to determine your problem."))
        fixed = models.BooleanField(_("The reported problem is fixed."), default=False)

class ReportUnit(models.Model):
        TEASING_CHOICES = (
        ...
        )

        report = models.OneToOneField(Report)
        tease = models.PositiveSmallIntegerField(choices=TEASING_CHOICES, default=None)

class ReportAdvertisement(models.Model):
        TEASING_CHOICES = (
        ...
        )

        report = models.OneToOneField(Report)
        tease = models.PositiveSmallIntegerField(choices=TEASING_CHOICES, default=None)

class ReportStatistics(models.Model):
        TEASING_CHOICES = (
        ...
        )

        report = models.OneToOneField(Report)
        tease = models.PositiveSmallIntegerField(choices=TEASING_CHOICES, default=None)

Python traceback:

Creating tables ...
Creating table table_name
Traceback (most recent call last):
  File "/home/user/workspace/project/manage.py", line 25, in <module>
    execute_manager(settings)
  File "/usr/lib/python2.7/dist-packages/django/core/management/__init__.py", line 438, in execute_manager
    utility.execute()
  File "/usr/lib/python2.7/dist-packages/django/core/management/__init__.py", line 379, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/lib/python2.7/dist-packages/django/core/management/base.py", line 191, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/usr/lib/python2.7/dist-packages/django/core/management/base.py", line 220, in execute
    output = self.handle(*args, **options)
  File "/usr/lib/python2.7/dist-packages/django/core/management/base.py", line 351, in handle
    return self.handle_noargs(**options)
  File "/usr/lib/python2.7/dist-packages/django/core/management/commands/syncdb.py", line 101, in handle_noargs
    cursor.execute(statement)
  File "/usr/lib/python2.7/dist-packages/django/db/backends/util.py", line 34, in execute
    return self.cursor.execute(sql, params)
  File "/usr/lib/python2.7/dist-packages/django/db/backends/mysql/base.py", line 86, in execute
    return self.cursor.execute(query, args)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1005, "Can't create table '\\db_name\\.#sql-4a8_ab' (errno: 150)")

[Hans Andersen]

After some further testing it turns out that the issue does also appear in django 1.3.1, it seems to be the new version of MySQL that is the problem.

While testing, the reason why 1.3.1 did not come up at first is because, the first run of syncdb creates the Report table but fails to proceed because of some sort of wrong order in the foreignkey assignment, that apparently does not work the same way as in the older versions of MySQL, the second time I ran syncdb the rest of the tables was created successfully.

[learts92@…]

Same error after upgrading to MySQL 5.5 on Ubuntu 12.04.
Trying to execute the SQL code manually instead of running

 manage.py SYNCDB

reveals that the error occurs when creating the foreign key to the user model of the authentication app: ("book" is my app name)

ALTER TABLE  `book_player` ADD CONSTRAINT  `user_id_refs_id_52bc6c8e` FOREIGN KEY (  `user_id` ) REFERENCES  `auth_user` (  `id` ) ;

The simplest model that raises the error:

from django.db import models
from django.contrib.auth.models import User

class Player(models.Model):
	
	user = models.OneToOneField(User)

[anonymous]

For the reporters experiencing this:

Please make sure all your MySQL tables are using the same storage engine (i.e. MyISM v. InnoDB). Specially tables that have ForeignKeys between them.

If you need more information about MySQL storage engines and how to know which one(s) are you using you will need to read the MySQL documentatio,n although the MySQL notes in our database docs also have some introductory information.

I suspect you had tables created with the MyISAM storage engine (the default for MySQL < 5.5) and since MySQL 5.5 defaults to InnoDB and you created new tables since then, you ended with a mix.

See comment:6:ticket:8200

[learts92@…]

That was my problem, deleting all the django tables and re-running syncdb solved the problem.
Thanks and sorry for not noticing such a simple error.

[Ramiro Morales]

Good to know (sorry, anonymous in comment:4 was me). We need further feedback from OP jokerejoker to decide what to do with this ticket.

[Hans Andersen]

I can confirm the different table types, but I am still a bit wondering why every thing works fine if I am running syncdb twice.

e.g. I first run syncdb and a failure is coming op with a traceback, I can the see that the report table is successfully created. Then I run syncdb again and everything works, no failure and the rest of the tables are created as expected with the first run of syncdb.

The problem it self might not be with Django, but MySQL, but people will be having this exact problem when upgrading there MySQL server and I suspect that the problem can be worked around by switching the order of how the SQL code is generated.

What I am able to conclude is that the current issue will not be happening on our production server since it is running Ubuntu 10.04 with MySQL 5.1.62. However, we are going to upgrade the server in the near future, so it would be nice for this issue to be none existing.

[Anssi Kääriäinen]

The problem is that when the table is created in the first syncdb, Django wont touch it anymore in the second run. So, it will skip creating the foreign keys also. There is no schema migration capabilities in Django, and thus if table exists, it will be left alone in syncdb.

[Hans Andersen]

I can further more tell that the problem does not happen while using south migration, but that would pretty much be expected from comment:8 says.

[Ramiro Morales]

Ok, I'm going to switch the component of the ticket to Documentation. We can add a paragraph about this potential pitfall to the MySQL notes near the "MySQL 5.5 has InnoDB as default storage engine" one.

Thanks.

[Claude Paroz]

Looks good, except s/MyISM/MyISAM/

[Tim Graham <timograham@…>]

In c870cb48cd0ec4b5dfdc5df95e6f0b5f5f8a738b:

Fixed #18256 - Added a potential pitfall when upgrading to MySQL 5.5.5

[Tim Graham <timograham@…>]

In e2dea54efe53fbeb66e684973ed6ad05f63969cc:

[1.4.X] Fixed #18256 - Added a potential pitfall when upgrading to MySQL 5.5.5

Backport of c870cb48cd from master