[Solved]-Django's syncdb fails with MySQL errno: 150

by

0👍

✅

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. Using Django’s ORM, this can be done 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.

Also, I created a new Django ticket.

👤RedsChineseFood

15👍

using Django 1.5, the error was that mysql creating tables with Innodb as default engine. Solution was to add the following to settings for the database and this created issues with constraints:

DATABASES = {
'default': {
    'ENGINE': 'django.db.backends.mysql',
    'NAME': 'etc....',
    'OPTIONS': {
           "init_command": "SET storage_engine=MyISAM",
    },
},

}
👤PhoebeB

4👍

MySQL docs say this:

Cannot create table. If the error message refers to error 150, table
creation failed because a foreign key constraint was not correctly
formed. If the error message refers to error –1, table creation
probably failed because the table includes a column name that matched
the name of an internal InnoDB table.

Post your model code here to let us see what can be wrong. If you can’t, and do not know what’s wrong, try bisecting the repository revisions, or bisecting the application: turn off half of applications, see if it syncs correctly, then bisect the part that contains the bad model, and so on.

👤culebrón

3👍

Recently I got this error, too.
In my case, this error occured because the table which contains the foreign key is using a different engine comparing with the table you are going to create.
a lazy way to solve this problem is unify these two tables’ engine using:

ALTER TABLE table_name ENGINE = engine_type;

And it worked for me.

👤Johnny Zhao

Leave a comment