Opened 10 years ago

Closed 9 years ago

Last modified 4 years ago

#347 closed enhancement (wontfix)

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

Reported by: jvoorhis@… Owned by: adrian
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 Changed 10 years ago by adrian

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

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

comment:2 Changed 9 years ago by remco@…

  • Resolution wontfix deleted
  • Status changed from closed to reopened
  • Version set to 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 Changed 9 years ago by mir@…

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

comment:4 Changed 9 years ago by adrian

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

See the comment by mir@….

comment:5 Changed 4 years ago by candlerb

  • 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