Improve the database setup on the installation

[jonasraoni]

Describe the problem you would like to solve
The default MySQL charset/collation (utf8/utf8_general_ci) we're using might fail to insert some UTF8 characters.
The solution involves upgrading the database to use utf8mb4.

Considerations:

• When changing the charset, the [database][collation] configuration must match the [i18n][connection_charset] at the config.inc.php.
• The installation page doesn't provide such options, and it must be done with care, as MySQL settings don't fit PostgreSQL.
• We could add a "conversion" tool to help users, or perhaps offer it as a migration script (as it somehow affects the reliability of the software, I think it makes sense to be a forced upgrade):
  • If we offer the migration script it will probably have to be kept forever (to always attempt to upgrade) or perhaps we can bump the minimum MySQL version requirement, which would also let us use newer features.
• The script must ensure the user is using MySQL, and also query which acceptable charsets the MySQL instance has...
  • Newer versions have the collation utf8mb4_0900_ai_ci, which is preferred
  • Not so newer versions have a slightly worse collation: utf8mb4_bin
  • As MySQL allows setting the collation for individual tables and fields, perhaps we should ensure nothing remained using utf8
• About the settings in the installation page
  • We could probably make it transparent for the user, by attempting to create a table using the best charset and fallback to another on failure).
  • Pre-connect to the database and check what's available (will require changes in the interface).
  • Let the user choose...
• Is there any reason to keep the Latin1 encoding? Latin1/ISO8859-1 references will be removed by the Revisit locale file loading #6328

[asmecher]

This will get considerably easier once ADODB is removed; otherwise we need to support all the permutations for both PDO-based (Laravel) and MySQLi-based (ADODB) implementations (plus PostgreSQL equivalents). If you don't mind, unless this is a pressing community need, I'd rather defer until after we're able to remove ADODB (this will be a few more major releases).

[jonasraoni]

No worries, I'm just flagging things as I see them.

[AndrewGearhart]

@asmecher @NateWr ... are we there yet? ;-)

[asmecher]

We can't remove ADODB until we discard the <=3.3.0 upgrade process, unfortunately. Legacy upgrades up until that point use ADODB. I'm currently thinking to remove ADODB entirely when we hit the next LTS release, which may be 3.6.0.