Code

Opened 3 years ago

Closed 2 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.

Attachments (0)

Change History (5)

comment:1 Changed 3 years ago by aaugustin

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

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 Changed 2 years ago by ramiro

  • Resolution set to wontfix
  • Status changed from new to closed

comment:3 Changed 2 years ago by 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.

comment:4 Changed 2 years ago by anonymous

  • Resolution wontfix deleted
  • Status changed from closed to reopened

comment:5 Changed 2 years ago by ramiro

  • Resolution set to wontfix
  • Status changed from reopened to 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.

Last edited 2 years ago by ramiro (previous) (diff)

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.