manage.py syncdb fails following The flatpages app docs

[creecode]

I was following along with the The flatpages app docs ( ​http://www.djangoproject.com/documentation/flatpages ) and when I got to the manage.py syncdb step I got the following error.

# manage.py syncdb
Creating table django_flatpage
Traceback (most recent call last):
  File "[stuff deleted]/manage.py", line 11, in <module>
    execute_manager(settings)
  File "/usr/lib/python2.5/site-packages/django/core/management/__init__.py", line 272, in execute_manager
    utility.execute()
  File "/usr/lib/python2.5/site-packages/django/core/management/__init__.py", line 219, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/lib/python2.5/site-packages/django/core/management/base.py", line 72, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/usr/lib/python2.5/site-packages/django/core/management/base.py", line 86, in execute
    output = self.handle(*args, **options)
  File "/usr/lib/python2.5/site-packages/django/core/management/base.py", line 168, in handle
    return self.handle_noargs(**options)
  File "/usr/lib/python2.5/site-packages/django/core/management/commands/syncdb.py", line 91, in handle_noargs
    cursor.execute(statement)
  File "/usr/lib/python2.5/site-packages/MySQLdb/cursors.py", line 166, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.5/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1005, "Can't create table './[stuff deleted]/#sql-48d_41.frm' (errno: 150)")

After some investigation I discovered this error occurs because I use...

DATABASE_OPTIONS = { 'init_command' : 'SET storage_engine = INNODB' }

...in my project settings file, I'm using MySQL for my database. I need to use INNODB files because I am using the binary log feature of MySQL for data recovery in case of disaster. I am using rev 7568 of the development trunk.

Any advice on a work-around welcome.

[Karen Tracey <kmtracey@…>]

I cannot recreate on r8277. Specifying InnoDB as the storage engine in DATABAE_OPTIONS via the init_command seems to work fine. I can add the flatpages app as described in the docs and the tabes get created with no errors.

You might try taking the SQL Django is generating (available from python manage.py sql flatpages) and running the commands individually in a mysql shell to see which one specifically is failing, that might give a clue. (Remember to first set storage_engine=innodb also.)

For reference, this is the SQL Django is generated for me on r8277:

BEGIN;
CREATE TABLE `django_flatpage` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `url` varchar(100) NOT NULL,
    `title` varchar(200) NOT NULL,
    `content` longtext NOT NULL,
    `enable_comments` bool NOT NULL,
    `template_name` varchar(70) NOT NULL,
    `registration_required` bool NOT NULL
)
;
CREATE TABLE `django_flatpage_sites` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `flatpage_id` integer NOT NULL,
    `site_id` integer NOT NULL,
    UNIQUE (`flatpage_id`, `site_id`)
)
;
ALTER TABLE `django_flatpage_sites` ADD CONSTRAINT flatpage_id_refs_id_3f17b0a6 FOREIGN KEY (`flatpage_id`) REFERENCES ` django_flatpage` (`id`);
ALTER TABLE `django_flatpage_sites` ADD CONSTRAINT site_id_refs_id_4e3eeb57 FOREIGN KEY (`site_id`) REFERENCES django_site` (`id`);
COMMIT;

[Karen Tracey <kmtracey@…>]

Actually I just figured out how to get your error. I bet you neglected to also include "django.contrib.sites" in your INSALLED_APPS. If I add just flatpages, and not sites, I get the same errno 150 OperationalError.

[creecode]

Replying to Karen Tracey <kmtracey@gmail.com>:

I bet you neglected to also include "django.contrib.sites" in your INSALLED_APPS.

Sites "django.contrib.sites" was/is there. Additional info, if I comment out the DATABASE_OPTIONS, all works as expected.

[Malcolm Tredinnick]

(Fixed formatting errors in description.)

[Karen Tracey <kmtracey@…>]

Replying to creecode:

Replying to Karen Tracey <kmtracey@gmail.com>:

I bet you neglected to also include "django.contrib.sites" in your INSALLED_APPS.

Sites "django.contrib.sites" was/is there. Additional info, if I comment out the DATABASE_OPTIONS, all works as expected.

Oh well, the cause of your error is not missing sites, I guess that was too easy. For some reason the InnoDB engine doesn't like something about the SQL Django is generating. That's about as much as I get from "errno 150". Without specifics on which SQL statement, exactly, InnoDB doesn't like I don't know how to get any further. That's why I suggested running the sqlall output in a mysql command prompt, and/or comparing it to the output that I get, which runs fine for my DB's InnoDB engine.

[creecode]

I believe the problem was related to using mixed table types. django_site was MyISAM and of course the flatpages dbs are INNODB. The second ALTER command

ALTER TABLE `django_flatpage_sites` ADD CONSTRAINT site_id_refs_id_4e3eeb57 FOREIGN KEY (`site_id`) REFERENCES django_site` (`id`);

was the one that MySQL choked on prior to me changing django_site to INNODB.

Thank you for your assistance.