console core:convert-to-utf8mb does not changes database's character set #17030

After running console core:convert-to-utf8mb to convert the database to character set utf8mb4 it looks like the default character set is still latin.

MariaDB [matomo]> SELECT @<a class='mention' href='https://github.com/character_set_database'>@character_set_database</a>, @<a class='mention' href='https://github.com/collation_database'>@collation_database</a>;
+--------------------------+----------------------+
| @<a class='mention' href='https://github.com/character_set_database'>@character_set_database</a> | @<a class='mention' href='https://github.com/collation_database'>@collation_database</a> |
+--------------------------+----------------------+
| latin1                   | latin1_swedish_ci    |
+--------------------------+----------------------+

A SHOW CREATE TABLE do show that tables are using utf8mb4 (returns a CREATE TABLE statement with DEFAULT CHARSET=utf8mb4) but any CREATE TABLE statement not explicitly specifying DEFAULT CHARSET will create a table with latin1 character set.

I think the console core:convert-to-utf8mb should also change the database default character set, something like:

ALTER DATABASE `matomo`
    DEFAULT CHARACTER SET = 'utf8mb4'
    DEFAULT COLLATE = 'utf8mb4_unicode_ci'

That is actually on purpose. Some people might use a shared database for
Matomo and other software. We can't simply assume that a default utf8mb4
charset would work for everything else as well. As we define the charset
when creating new tables, that shouldn't be a problem at all.
For databases created by Matomo in installation, the charset should be set
to utf8mb4 if available.

I get it. It's true that my change could cause problem with shared databases.

As we define the charset when creating new tables, that shouldn't be a problem at all.

One thing though. If I am not mistaken, plugins that creates tables with foreign keys to string-type SQL columns now have to first detect/check what is the charset of the core Matomo tables in order to adapt their SQL CREATE TABLE statement. Otherwise an errno: 150 "Foreign key constraint is incorrectly formed" SQL error can occur...

@C-Duv this might be true, but we are actually not using any foreign keys in Matomo or any of our plugins. If that is something that should be cared of by a plugin developer, might be worth mentioning it in the developer docs.
Feel free to create a issue or PR there if you think that would be useful: https://github.com/matomo-org/developer-documentation

Using foreign key contraints in plugins might also cause other unexpected issues like
https://github.com/dominik-th/matomo-plugin-LoginOIDC/issues/35