History: Migrate from MyISAM to InnoDB

• «
• »

            Context: ((InnoDB)) and ((MyISAM)) are [https://en.wikipedia.org/wiki/Database_engine|storage engines] for the databases supported by Tiki: ((MariaDB)), ((MySQL)) and ((Percona Server for MySQL)).

Tiki uses the database for two types of data:
# The actual data (wiki pages, blog posts, etc.)
** For Tiki data, MyISAM has been used since the beginning of Tiki (2002). In 2017, the default storage engine became ((InnoDB)).
# The ((Unified Index Comparison|Unified Index)): a copy of your data in format optimized for search
** For the unified index, MyISAM has been used since the beginning of Tiki (2002). In 2024 ((Tiki28)), it becomes((InnoDB)). It's not optional.


MyISAM still works just fine and will for many years. And you can easily convert later.

Migration steps:

# Make sure you are not using the deprecated tiki-searchresults.php feature (preference name feature_search_fulltext and removed after ((Tiki26))), but instead use tiki-searchindex.php (preference name: feature_search)
# Do a full backup of your current database/installation
# Alter the database engine for all tables to InnoDB using db/tiki_convert_myisam_to_innodb.sql

Performing the conversion with phpMyAdmin may cause a PHP timeout. Running the script with the mysql command avoids that:
{CODE(colors="shell" theme="default")}mysql -u userName -p databaseName < db\tiki_convert_myisam_to_innodb.sql{CODE}
Nevertheless, step 4 may fail converting the tiki_files table if innodb_log_file_size is not high enough. In one case, 25 MB was insufficient (80 MB sufficed).

The source code lives here: https://gitlab.com/tikiwiki/tiki/-/blob/master/db/tiki_convert_myisam_to_innodb.sql (This is for master, so you may want to look for corresponding file for your Tiki version)

In a future version, we will have a ((Console)) script: https://gitlab.com/tikiwiki/tiki/-/merge_requests/4915