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

[anonymous]

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.

[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)")

[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.

[Andy Dustman <farcepest@…>]

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

[Andy Dustman <farcepest@…>]

Ticket #1500 has a patch that fixes this issue.

[Andy Dustman <farcepest@…>]

Closing because #1500 fixed this.