Opened 19 years ago

Closed 18 years ago

Last modified 13 years ago

#347 closed enhancement (wontfix)

Add setting for table generation to use a default MySQL table type

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

Description

Allow the MySQL wrapper to create tables with the InnoDB engine, as opposed to just the system default which is typicall MyISAM. MyISAM does not support transactions and does nothing to ensure referential integrity.

Change History (5)

comment:1 by Adrian Holovaty, 18 years ago

Resolution: wontfix
Status: newclosed

This is too MySQL-specific. Feel free to edit the output of django-admin.py sql and run it manually.

comment:2 by remco@…, 18 years ago

Resolution: wontfix
Status: closedreopened
Version: magic-removal

Why not make it part of the DATABASE_ENGINE setting in settings.py?

For example let DATABASE_ENGINE = 'mysql' use MyIsam by default
But also allow:

DATABASE_ENGINE = 'mysql_myisam'
or
DATABASE_ENGINE = 'mysql_innodb'

And use those to change the storage engine used by the database

comment:3 by mir@…, 18 years ago

You can easily set a default engine for mysql. Works great for me ;-)

comment:4 by Adrian Holovaty, 18 years ago

Resolution: wontfix
Status: reopenedclosed

See the comment by mir@….

comment:5 by candlerb, 13 years ago

Cc: candlerb added
Easy pickings: unset
UI/UX: unset

The notes at https://docs.djangoproject.com/en/dev/ref/databases/ suggest using

'OPTIONS': {
   'init_command': 'SET storage_engine=INNODB',
}

This worked fine for me in testing - but it broke our production network upon deployment. Here's the reason:

  • We use mysql replication
  • The mysql slaves happened to have a default storage engine of MyISAM
  • The "SET storage_engine=INNODB" setting is not replicated
  • Hence the tables were created INNODB on the master and MyISAM on the slaves
  • The slaves barfed when the foreign key constraints were replicated

The moral is: it's a bad idea to rely on session attributes, and it would have been much better if Django could have been configured to add the table type to the CREATE TABLE statements.

Other databases need trailing options after CREATE TABLE, so I don't see this as being a Mysql-specific requirement. e.g.

Mysql:

    'OPTIONS': { 'create_table_options': 'ENGINE=InnoDB DEFAULT CHARSET=latin1' }

Oracle:

    'OPTIONS': { 'create_table_options': 'tablespace DATA logging' }
Note: See TracTickets for help on using tickets.
Back to Top