Migrate database charset to utf8mb4 (emoji support) and storage engine to InnoDB

[Deltik]

Motivation

e107 is currently using the obsolete MyISAM storage engine and the undesirable utf8mb3 character set for all tables by default.

Character Set

Many emojis, which are common today, require 4 UTF-8 bytes to store but are not supported by the utf8mb3 character set because it can only support up to 3 bytes per character.

Furthermore, from the MySQL 8.0 documentation about utf8mb3:

The utf8mb3 character set is deprecated and you should expect it to be removed in a future MySQL release. Please use utf8mb4 instead.

Storage Engine

InnoDB, which became the MySQL 5.5.5 default storage engine in 2010, has multiple important advantages over MyISAM:

• Row-level locking – With MyISAM and its table-level locking, queries that involve a table will lock the entire table and force other queries needing the table to wait. InnoDB has row-level locking, which keeps all other rows in a table available for use if one query only affects a subset of rows in the table. InnoDB may improve concurrent query performance.
• Foreign key integrity – MyISAM has no way to ensure that a reference across tables is valid. For example, there is no mechanism linking e107_user.user_id and e107_comments.comment_author_id, so if a user is deleted, there is no way for the comments to handle that, by preventing the user from being deleted, dissociating the comment's author, or deleting the comment as well.
• Transactions – InnoDB can guarantee that a group of queries completes in one atomic unit. One use of transactions is to roll back an action if any query in the action fails, but it is not possible to know beforehand if one of the queries will fail. Another use is to prevent a half-modified state if a connection fails or the MySQL server crashes during a multi-step process like a schema migration/update.
• Data integrity – MyISAM has no journal or transaction log, so if it crashes for whatever reason, data will be inconsistent and corrupt. InnoDB can recover from a crash by replaying a transaction log upon restart and detect corruption by checksumming.
• Replication – MyISAM cannot (reliably) be used in a MySQL cluster due to the aforementioned data integrity drawbacks. InnoDB should be the storage engine used with Galera Cluster.

Proposed Solution

To get started with modernizing the database storage of e107, I propose the following migrations:

• Convert all MyISAM tables to InnoDB.
• Change all tables with the charset utf8mb3 to utf8mb4 and collate utf8mb4_unicode_ci.

For large databases, we may wish to consider splitting up the migration into these steps:

1. Put the site in maintenance mode.
2. Perform whatever migrations possible until the script times out.
3. Keep refreshing until all migrations are done.
4. Leave maintenance mode.

Additional Context

It seems that if I try to save an emoji in any input field, like a page body or tagline, the emoji and everything after it gets wiped out in the database.

[Deltik]

Storage Engine Addendum

Because plugin_sql.php files often explicitly specify CREATE TABLE … (…) ENGINE=MyISAM;, I am in favor of a strict alias of MyISAM to InnoDB. To override the alias, plugins should specify ENGINE=Aria. e107 would then create the table using the Aria storage engine if it is available (on MariaDB) or using MyISAM otherwise.

In fact, I would alias the storage engines in this order of preference:

ENGINE=	Actual Storage Engine
MyISAM	InnoDB, Aria, Maria, MyISAM
Aria	Aria, Maria, MyISAM
InnoDB	InnoDB, XtraDB
XtraDB	XtraDB, InnoDB

[CaMer0n]

Support the idea, I would save this change for after the v2.3.1 release.

[CaMer0n]

Mostly worked well in local tests.

I have plugins with TEXT table fields. After running the recommended update to change the storage engine, they all changed to MEDIUMTEXT, which results in verification errors when comparing with the original xxxx_sql.php file:

[Jimmi08]

@Deltik
Search is not working with InnoDB tables, changing them back to MyISAM solved problem

#5209

[sanslash332]

Hello @Jimmi08
How I can do a easy way to Rebert the change from innoDB to MyISAM on the tables on my DB?
Through phpmiadmin? Or a sql query? (sorry, im a noov with sql)

Just to get back the searrch plugin while the plugin is fixed.

thanks

[Jimmi08]

(sorry, im a noov with sql)

Me too. There are some solutions on google, but they didn't work (or I didn't know how to use them). So I exported the database to file , renamed it and imported back. Or it can be done manually table by table. Online table had this type before. You need to check option Add delete table if exists in export options or reimport to empty/new database.

[Deltik]

@sanslash332 and @Jimmi08: I don't have a solution in code yet, but here are the quick fix queries you can run to repair e107 e_search on InnoDB tables for the core plugins.

Note: The queries assume that the table prefix is e107_; adjust to match your $mySQLprefix value as necessary.

chatbox

ALTER TABLE e107_chatbox ADD FULLTEXT(cb_nick);
ALTER TABLE e107_chatbox ADD FULLTEXT(cb_message);

user

ALTER TABLE e107_user ADD FULLTEXT(user_name);
ALTER TABLE e107_user ADD FULLTEXT(user_signature);

forum

ALTER TABLE e107_forum_thread ADD FULLTEXT(thread_name);
ALTER TABLE e107_forum_post ADD FULLTEXT(post_entry);

_blank

ALTER TABLE e107_blank ADD FULLTEXT(blank_nick);
ALTER TABLE e107_blank ADD FULLTEXT(blank_message);

page

ALTER TABLE e107_page ADD FULLTEXT(page_title);
ALTER TABLE e107_page ADD FULLTEXT(page_text);
ALTER TABLE e107_page ADD FULLTEXT(page_metakeys);
ALTER TABLE e107_page ADD FULLTEXT(page_fields);

news

ALTER TABLE e107_news ADD FULLTEXT(news_title);
ALTER TABLE e107_news ADD FULLTEXT(news_body);
ALTER TABLE e107_news ADD FULLTEXT(news_extended);
ALTER TABLE e107_news ADD FULLTEXT(news_summary);
ALTER TABLE e107_news ADD FULLTEXT(news_meta_keywords);
ALTER TABLE e107_news ADD FULLTEXT(news_meta_description);

download

ALTER TABLE e107_download ADD FULLTEXT(download_name);
ALTER TABLE e107_download ADD FULLTEXT(download_url);
ALTER TABLE e107_download ADD FULLTEXT(download_description);
ALTER TABLE e107_download ADD FULLTEXT(download_author);
ALTER TABLE e107_download ADD FULLTEXT(download_author_website);

faqs

ALTER TABLE e107_faqs ADD FULLTEXT(faq_question);
ALTER TABLE e107_faqs ADD FULLTEXT(faq_answer);
ALTER TABLE e107_faqs ADD FULLTEXT(faq_tags);
ALTER TABLE e107_faqs_info ADD FULLTEXT(faq_info_title);

[sanslash332]

Ho! thanks @Deltik

The solution works like a charm!

Well, wait to implement that direct on the page; but for now, the issue is solved if you do manually these commands :-)
again, thanks