[DOCU-250] MySQL InnoDB storage engine Created: 07/Feb/12  Updated: 03/Nov/15

Status: Closed
Project: Documentation
Component/s: content
Affects Version/s: None
Fix Version/s: None

Type: Task Priority: Neutral
Reporter: Antti Hietala Assignee: Antti Hietala
Resolution: Unresolved Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Template:
Acceptance criteria:
Empty
Task DoR:
Empty
Date of First Response:

 Description   

When using a MySQL database as persistent storage, you must use the InnoDB storage engine. InnoDB supports transactions. The other popular storage engine, MyISAM, is not transactional and won't work with Magnolia CMS. We don't stress this fact enough. Users often go with the default MyISAM storage engine, which leads to problems.

Document how to select the InnoDB storage engine using MySQL GUI tools and using the command line. Try to find existing procedures on the Web, ideally in official MySQL documentation, to avoid rewriting.

Link to the engine selection procedures from official Magnolia documentation and wiki articles such as Certified stack and Setting up Jackrabbit persistence manager. Look for other docs where MySQL setup is discussed. Make the reason and links prominent, for example with an info box.



 Comments   
Comment by Matt Dertinger [ 07/Feb/12 ]

Hi,

In addition to documenting how to select the InnoDB storage engine when first installing Magnolia CMS, it may be helpful to document how to convert an existing installation that's using MyISAM to InnoDB. It should also be mentioned that InnoDB is now the default storage engine as of MySQL 5.5.

A handy way to convert existing tables from MyISAM to InnoDB is to do the following:

  1. Backup your database.
  2. Create the script. The following command will create a mysql dump, then replace all the Storage ENGINE=MyISAM with ENGINE=InnoDB, then write the output to <DATABASE_NAME>_innodb.sql. Here’s the command. Be sure to change <DATABASE_NAME> as it fits.
    $ mysqldump <DATABASE_NAME> | sed -e 's/^) ENGINE=MyISAM/) ENGINE=InnoDB/' > <DATABASE_NAME>_innodb.sql
    
  3. Run the script. This command will load the dump file we created in the last step.
    $ mysql -e "source <DATABASE_NAME>_innodb.sql" <DATABASE_NAME>
    
  4. Verify it by running this command in mysql:
    mysql> show table status;
    

Some helpful resources for converting existing tables from MyISAM to InnoDB can be found here:

Cheers,
Matt

Comment by Antti Hietala [ 08/Jan/13 ]

Linked to Setting the Storage Engine from:

Generated at Mon Feb 12 01:07:21 CET 2024 using Jira 9.4.2#940002-sha1:46d1a51de284217efdcb32434eab47a99af2938b.