syncdb fails with errno 150 when using MySQL 4.0 with InnoDB

[RedsChineseFood]

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.

[Aymeric Augustin]

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/

[eprasetya@…]

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

[Ramiro Morales]

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're-closing this ticket.