Opened 12 years ago

Closed 11 years ago

Last modified 11 years ago

#18256 closed Bug (fixed)

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

Reported by: Hans Andersen Owned by: nobody
Component: Documentation Version: 1.4
Severity: Normal Keywords: mysql
Cc: hans@…, timograham@… Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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)")

Attachments (1)

18256.diff (906 bytes ) - added by Tim Graham 11 years ago.

Download all attachments as: .zip

Change History (15)

comment:1 by Hans Andersen, 12 years ago

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.

comment:2 by Hans Andersen, 12 years ago

Cc: hans@… added

comment:3 by learts92@…, 12 years ago

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)

comment:4 by anonymous, 12 years ago

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 documentation although the MySQL notes in our database documentation also has 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

Version 0, edited 12 years ago by anonymous (next)

comment:5 by learts92@…, 12 years ago

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.

comment:6 by Ramiro Morales, 12 years ago

Component: ORM aggregationDatabase layer (models, ORM)

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.

comment:7 by Hans Andersen, 12 years ago

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.

comment:8 by Anssi Kääriäinen, 12 years ago

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.

Last edited 12 years ago by Anssi Kääriäinen (previous) (diff)

comment:9 by Hans Andersen, 12 years ago

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.

comment:10 by Ramiro Morales, 12 years ago

Component: Database layer (models, ORM)Documentation
Triage Stage: UnreviewedAccepted

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.

by Tim Graham, 11 years ago

Attachment: 18256.diff added

comment:11 by Tim Graham, 11 years ago

Cc: timograham@… added
Has patch: set

comment:12 by Claude Paroz, 11 years ago

Looks good, except s/MyISM/MyISAM/

comment:13 by Tim Graham <timograham@…>, 11 years ago

Resolution: fixed
Status: newclosed

In c870cb48cd0ec4b5dfdc5df95e6f0b5f5f8a738b:

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

comment:14 by Tim Graham <timograham@…>, 11 years ago

In e2dea54efe53fbeb66e684973ed6ad05f63969cc:

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

Backport of c870cb48cd from master

Note: See TracTickets for help on using tickets.
Back to Top