Opened 11 years ago

Closed 11 years ago

Last modified 10 years ago

#1392 closed defect (fixed)

[magic-removal] "Can't create table '.\\project\\#sql-f6c_e.frm' (errno: 150)")

Reported by: anonymous Owned by: Adrian Holovaty
Component: Database layer (models, ORM) Version: magic-removal
Severity: major Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

I get the above error, when trying to init the database after creating a new project.
revision 2376 of the django magic-removal branch.
if it helps using mysql 5.0.18 on windows xp.

Change History (6)

comment:1 Changed 11 years ago by anonymous

just updated to revision 2402 (which replaced init with syncdb)

i get the following when i run manage.py syncdb

Creating table auth_message
Creating table auth_group
Creating table auth_user
Creating table auth_permission
Creating table auth_group_permissions
Creating table auth_user_groups
Creating table auth_user_user_permissions
Creating table django_content_type
Creating table django_package
Creating table django_session
Creating table django_site
Creating table django_flatpage
Creating table django_flatpage_sites
Creating table django_admin_log
Traceback (most recent call last):

File "C:\django_projects\mittance\manage.py", line 11, in ?

execute_manager(settings)

File "C:\Python24\Lib\site-packages\django\core\management.py", line 1249, in execute_ma

nager

execute_from_command_line(action_mapping)

File "C:\Python24\Lib\site-packages\django\core\management.py", line 1169, in execute_fr

om_command_line

action_mapping[action]()

File "C:\Python24\Lib\site-packages\django\core\management.py", line 472, in syncdb

cursor.execute(sql)

File "C:\Python24\Lib\site-packages\django\db\backends\util.py", line 11, in execute

result = self.cursor.execute(sql, params)

File "C:\Python24\Lib\site-packages\django\db\backends\mysql\base.py", line 31, in execu

te

return self.cursor.execute(sql, params)

File "C:\Python24\Lib\site-packages\MySQLdb\cursors.py", line 137, in execute

self.errorhandler(self, exc, value)

File "C:\Python24\Lib\site-packages\MySQLdb\connections.py", line 33, in defaulterrorhan

dler

raise errorclass, errorvalue

_mysql_exceptions.OperationalError: (1005, "Can't create table '.
mittance
#sql-644_8.fr
m' (errno: 150)")

comment:2 Changed 11 years ago by ian@…

this is a DB schema issue. try just running the SQL manually via the mysql command prompt.

have a look at http://www.faqts.com/knowledge_base/view.phtml/aid/36443/fid/183
or do a google search on the mysql error code, but it sounds like a foreign key issue, not a 'django' problem directly.

comment:3 Changed 11 years ago by Adrian Holovaty

Resolution: invalid
Status: newclosed

comment:4 Changed 11 years ago by Andy Dustman <farcepest@…>

Resolution: invalid
Status: closedreopened

These "errno: 150" messages are a little obscure, but they refer some error condition in InnoDB. Running the SQL directly doesn't change anything and here's why:

$ python manage.py sql auth
BEGIN;
CREATE TABLE `auth_message` (
    `id` mediumint(9) unsigned auto_increment NOT NULL PRIMARY KEY,
    `user_id` integer NOT NULL,
    `message` longtext NOT NULL
);
CREATE TABLE `auth_group` (
    `id` mediumint(9) unsigned auto_increment NOT NULL PRIMARY KEY,
    `name` varchar(80) NOT NULL UNIQUE
);
CREATE TABLE `auth_user` (
    `id` mediumint(9) unsigned auto_increment NOT NULL PRIMARY KEY,
    `username` varchar(30) NOT NULL UNIQUE,
    `first_name` varchar(30) NOT NULL,
    `last_name` varchar(30) NOT NULL,
    `email` varchar(75) NOT NULL,
    `password` varchar(128) NOT NULL,
    `is_staff` bool NOT NULL,
    `is_active` bool NOT NULL,
    `is_superuser` bool NOT NULL,
    `last_login` datetime NOT NULL,
    `date_joined` datetime NOT NULL
);
ALTER TABLE `auth_message` ADD CONSTRAINT `user_id_referencing_auth_user_id` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`);

This is the point at which it dies. One of the requirements of FOREIGN KEY references in MySQL is that both columns of the constraint must the same column definition. auth_message.user_id is integer NOT NULL but auth_user.id is mediumint(9) unsigned auto_increment NOT NULL. auth_message.user_id needs to be mediumint(9) unsigned NOT NULL.

mysql> ALTER TABLE `auth_message` MODIFY COLUMN `user_id` mediumint(9) unsigned NOT NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `auth_message` ADD CONSTRAINT `user_id_referencing_auth_user_id` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Also see http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

comment:5 Changed 11 years ago by Andy Dustman <farcepest@…>

Ticket #1500 has a patch that fixes this issue.

comment:6 Changed 11 years ago by Andy Dustman <farcepest@…>

Resolution: fixed
Status: reopenedclosed

Closing because #1500 fixed this.

Note: See TracTickets for help on using tickets.
Back to Top