Opened 13 years ago

Closed 12 years ago

Last modified 9 years ago

#16823 closed Bug (wontfix)

syncdb fails with errno 150 when using MySQL 4.0 with InnoDB

Reported by: RedsChineseFood Owned by: nobody
Component: Database layer (models, ORM) Version: 1.3
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Using MySQL 4.0.18 with InnoDB as the default storage engine, 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_project\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)")

Here's what my setting file looks like:

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

I traced the source of the problem. When creating 2 InnoDB tables with a foreign key relationship, the foreign key column must be indexed explicitly prior to MySQL 4.1.2. I did this by using the db_index=True option in the foreign key field. However, in the Django generated SQL, the CREATE INDEX statement is issued after the foreign key relationship is created. For example, for the following models:

    class Customer(models.Model):
        first_name = models.CharField(max_length=100)
        last_name = models.CharField(max_length=100)
        
    class Order(models.Model):
        customer = models.ForeignKey(Customer, db_index=True)


Django generates the following SQL code:

    BEGIN;
    CREATE TABLE `foo_app_customer` (
        `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
        `first_name` varchar(100) NOT NULL,
        `last_name` varchar(100) NOT NULL
    )
    ;
    CREATE TABLE `foo_app_order` (
        `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
        `customer_id` integer NOT NULL
    )
    ;
    ALTER TABLE `foo_app_order` ADD CONSTRAINT `customer_id_refs_id_27e4f922` FOREIGN KEY (`customer_id`) REFERENCES `foo_app_customer` (`id`);
    CREATE INDEX `foo_app_order_12366e04` ON `foo_app_order` (`customer_id`);
    COMMIT;  

If you try running this code using MySQL 4.0, an errno 150 will occur when trying to execute the ALTER TABLE statement. But if the CREATE INDEX statement is issued first, everything works like a charm. As far as I can tell, the only workaround for this is to create your own table manually and using inspectdb afterwards to generate the models.

Change History (5)

comment:1 by Aymeric Augustin, 13 years ago

Triage Stage: UnreviewedAccepted

I'm accepting the bug because the report is solid and because the fix could be easy (switch the two SQL statements?)

But, to be honest, MySQL 4.0 isn't a priority for Django today. "Extended support" by MySQL for this version ended on December 31, 2008, and our official policy is:

While it may be possible to use 3.23 or 4.0, you'll probably have less trouble if you use 4.1 or 5.0.

(from https://docs.djangoproject.com/en/dev/ref/databases/#mysql-notes)

If you can't upgrade to a more recent version of MySQL, your best chance to see this fixed is to submit a good patch, see https://docs.djangoproject.com/en/dev/internals/contributing/writing-code/submitting-patches/

comment:2 by Ramiro Morales, 12 years ago

Resolution: wontfix
Status: newclosed

comment:3 by eprasetya@…, 12 years ago

Hi, I'm using mysql 5.5.22 that comes with Ubuntu 12.04. We're starting to see this issue after the upgrade.

comment:4 by anonymous, 12 years ago

Resolution: wontfix
Status: closedreopened

comment:5 by Ramiro Morales, 12 years ago

Resolution: wontfix
Status: reopenedclosed

You don't five us any detail about your environment.

Please provide them in #18256 if the workaround detalied there isn't of help.

I'm re-closing this ticket.

Last edited 12 years ago by Ramiro Morales (previous) (diff)
Note: See TracTickets for help on using tickets.
Back to Top