#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 , 13 years ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:2 by , 13 years ago
Resolution: | → wontfix |
---|---|
Status: | new → closed |
comment:3 by , 13 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 , 13 years ago
Resolution: | wontfix |
---|---|
Status: | closed → reopened |
comment:5 by , 13 years ago
Resolution: | → wontfix |
---|---|
Status: | reopened → closed |
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.
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:
(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/