The rest of this page is only kept for posterity.
Introduction
[+]The plan is to add InnoDB support in Tiki.
This will be done keeping MyISAM as the default engine, with minimal impact on today's implementation and update practices. InnoDB will only be supported for clean installs, i.e a fresh install or a re-install. No conversion support will be added in the GUI. However, there is a procedure to convert existing Tiki installations. Please see below for details.
The advantages of InnoDB are
- Crash proof. The database will automatically recover errors upon restart
- Row level locking, i.e. updates, inserts and deletes will no longer lock the whole file/table.
InnoDB also supports referential integrity by the use of foreign key and (multi-statment) transactions, but these features will not be included in the initial integration.
Rules for the installer scripts
- No FKs will be allowed in the tiki.sql script.
- Fulltext index definitions are placed in the tiki_myisam.sql file. InnoDB specific definitions are placed in the tiki_innodb.sql file.
- The word MyISAM must only be used in the engine specification and is not allowed in attribute names or other definitions.
The installer will
- GUI will get an option so the user can choose which engine type to install. MyISAM is pre-selected
- Check if InnoDB is available and only present the option if it is.
- Replace "MyISAM" strings in the installer script with "InnoDB".
Thus the tiki.sql script must (keep on) specifying MyISAM as the engine. It is important that all table definitions include the engine statement.
- After the main install script (tiki.sql), run tiki_myisam.sql or tiki_innodb.sql for the respective installation. tiki_myisam.sql installs the fulltext indexes. tiki_innodb.sql is empty, but added for completeness.
- Updates will use the same replace procedure. Will not check for FULLTEXT index definitions and possible other incompatibilities. MyISAM specific features must be added to the tiki_myisam.sql file.
The application will
- Display the DB engine type in the general features panel along with the Tiki version info
- If the current MySQL engine does not support fulltext search (i.e. InnoDB is used), the section "MySQL Search (legacy)" in the search admin panel will display "Your MySQL engine does not support MySQL fulltext search" instead of the options.
Wishes
Open
Pending
Fixed
Interested community members
Database upgrades
When adding an engine dependent patch, the engine independent SQL must be put in the YYYYMMDD_description_tiki.sql file. The engine dependent parts must be put in an accompanying php file, specifying a post_YYYYMMDD_description_tiki function.
Example: Add a table with a fulltext index
DROP TABLE IF EXISTS `tiki_test`; CREATE TABLE `tiki_test` ( `title` varchar(255) default NULL, KEY `title` (`title`) ) ENGINE=MyISAM;
and a PHP file specifying the engine dependent part
function post_20110918_tiki_test_tiki( $installer ) { if($installer->isMySQLFulltextSearchSupported()) { $installer->query( "CREATE FULLTEXT INDEX ft_test ON tiki_test(`title`);"); } }
ToDo
Remove "MySQL Full-Text Search" in config search
If the config search "Preference Filters" is set to Advanced, "MySQL Full-Text Search" is still displayed in the results. It shouldn't be.
Migrating existing databases to InnoDB
Migration steps
- Make sure InnoDB is integrated in the standard/SVN Tiki release. Otherwise you may be unable to do any upgrades later.
- Do a full backup of your current database/installation
- Upgrade to the latest Tiki version (8.0 SVN or later).
- Disable MySQL fulltext search in Tiki. It's not supported by InnoDB, and you won't be able to disable it from the GUI after the conversion
- Drop all MyISAM fulltext indexes
- Alter the database engine for all tables to InnoDB
Steps 5 and 6 are defined in the file db/tiki_convert_myisam_to_innodb.sql.
To be tested
- TRIM (would be nice that it works here too...)
;){kind=link}
;){kind=link}