Fix the upgrade process

[auge8472]

Because of introducing errors in the upgrade script for the 2.5-branch and losing the overview, I decided to build a new upgrade script, that contains a complete upgrade path for every starting version or group of versions in case of the same changes for those versions.

That attempt will contain many code doublettes but it makes the code clearer. Every block is a complete upgrade script from tha starting version to the new version and because of that it will have an improved readability (IMHO).

This will fix #649

[auge8472]

Aide memoire: remove the step for version 2.4.99.0 because this version never existed.

edit: The version did exist, but only on my own hard drive.

[cankaygisiz]

Sorry for trying to contact here but I'm having issue with the theme. Default theme is not working and I just recieve plain html on my page. Is there something I missed? I've completed every installation step. Let me know if I'm missing something. Appreciate the whole project. It's beautiful.

[auge8472]

@cankaygisiz see #684 and my answer in the corresponding forum thread.

[auge8472]

For documentation reasons

During thinking about the several parts of the current issue I came along informations I was not aware of.

One of the problems here is the change to make the e-mail-addresses of registered user unique (no multiple use of e-mail addresses in the userdata table) so the upgrade script needs to check for such cases and has to abort the process if finds duplicates. Making this safe for an upgrade that starts with any of the supported versions and not forgetting any of the other necessary steps is a brainfuck in itself.

Another big issue is the size of indexes after altering the table charset to utf8mb4. We got a few reports about problems during upgrade to a version of the 2.5-branch. With the version 2.5 we introduced utf8mb4 for the database tables to be able to store the full range of UTF-8 including the private sections including emojis. If the upgrade stops because of only one of the mentioned problems one is left with a broken database structure and an also broken forum.

Some users were unable to upgrade because they were told that the index could not be created for a few columns. On the other side, other users was able to run the upgrade without any problem and I didn't know the cause of the different behaviour.

To be honest, I was initially satisfied with reducing the size of the affected database columns without looking for other solutions. But – beside the fact, that this was made to late for some cases – while researching the upgrade script overhaul, I became aware of the solution on the database side. With MySQL 5.7(.7), a beta version of the MySQL 5.7-branch, the possible size of text indexes was raised from 767 to a maximum of 3072 Bytes. That made the problem going away on the databaseserver side and this seems to be the reason, why some people never fell into this trap.

The MySQL version 5.7 was released in October 2016. MariaDB released the version 10.2 the year after with a capable InnoDB-engine. That's around seven to eight years ago. Because of that I decided to raise the required database server version to 5.7(.7) respective to MariaDB 10.2(.2). And I have added a check that forces one of these minimal server versions with 8cbfe12b3b9c.

[auge8472]

For documentation reasons

The MySQL documentaion for version 8 states in its page about supported unicode character sets that utf8mb3 is deprecated. Additionally it is described, that utf8 is currently an alias for the deprecated utf8mb3 but will become an alias for utf8mb4 when utf8mb3 is removed. Because of that the documentation recommends to explicitely use utf8mb4 to ensure the trouble-free compatibility of ones tables with future releases of MySQL.

Because of this recommendation all tables are altered to utf8mb4 with this rework of the upgrade script and also in the SQL-script for the installation.

[auge8472]

For the records. After a long time of reading, checking, writing, reading and checking again and finally testing an upgrade from every version (group), that is a possible starting point of the upgrade, the PR is ready for review. The upgrade script still needs to be renamed and the remaining conflicts need to be fixed, then the code can finally be transferred to the repository.

[auge8472]

For documentation reasons

Until this point during an upgrade a forum got deactivated when the upgrade started but was reenabled after the changes to the database were made, but before the new script files were uploaded. With this PR the forum remains deactivated and has to be reenabled manually in the settings. I added a notice to the upgrade script below the list of files to upgrade (and also to the language files).

I also added the new sub array $upgrade['delete'] that will be merged with the array $upgrade['items'] after their sorting. This change was made because an item that is marked to be deleted gets hidden, when the list of items also contains the directory of the file. See therefore 330a93dd97713

An example:

old

$upgrade['items'][] = 'themes/default/images/new-image.png';
$upgrade['items'][] = 'themes/default/images/old-image.png (remove)';
$upgrade['items'][] = 'themes/default/';

// output:
// ...
// themes/default/
// ...

versus new

$upgrade['items'][] = 'themes/default/images/new-image.png';
$upgrade['delete'][] = 'themes/default/images/old-image.png (remove)';
$upgrade['items'][] = 'themes/default/';

// output:
// ...
// themes/default/
// ...
// themes/default/images/old-image.png (remove)

[auge8472]

Added queries for checking for the existence of the indexes akismet_spam and spam_check_status in the table mlf2_akismet_rating. Those might be forgotten in the upgrade script of earlier versions. See therefore also point 7 in a report about database issues in the project forum.

[auge8472]

I'll adding it without a review to the codebase.