id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
16823	syncdb fails with errno 150 when using MySQL 4.0 with InnoDB	RedsChineseFood	nobody	"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 [http://dev.mysql.com/doc/refman/4.1/en/innodb-foreign-key-constraints.html 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. 

"	Bug	closed	Database layer (models, ORM)	1.3	Normal	wontfix			Accepted	0	0	0	0	0	0