Code

Opened 2 years ago

Closed 18 months ago

Last modified 18 months 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: jokerejoker 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 timo 19 months ago.

Download all attachments as: .zip

Change History (15)

comment:1 Changed 2 years ago by jokerejoker

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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 Changed 2 years ago by jokerejoker

  • Cc hans@… added

comment:3 Changed 2 years ago by 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)

comment:4 Changed 2 years ago by 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 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 2 years ago by anonymous (next)

comment:5 Changed 2 years ago by 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.

comment:6 Changed 2 years ago by ramiro

  • Component changed from ORM aggregation to Database 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 Changed 2 years ago by jokerejoker

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 Changed 2 years ago by akaariai

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 2 years ago by akaariai (previous) (diff)

comment:9 Changed 2 years ago by jokerejoker

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 Changed 2 years ago by ramiro

  • Component changed from Database layer (models, ORM) to Documentation
  • Triage Stage changed from Unreviewed to Accepted

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.

Changed 19 months ago by timo

comment:11 Changed 19 months ago by timo

  • Cc timograham@… added
  • Has patch set

comment:12 Changed 19 months ago by claudep

Looks good, except s/MyISM/MyISAM/

comment:13 Changed 18 months ago by Tim Graham <timograham@…>

  • Resolution set to fixed
  • Status changed from new to closed

In c870cb48cd0ec4b5dfdc5df95e6f0b5f5f8a738b:

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

comment:14 Changed 18 months ago by 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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.