@C-Duv opened this Issue on December 30th 2020

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'
@sgiehl commented on December 30th 2020 Member

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.

@C-Duv commented on December 30th 2020

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...

@sgiehl commented on January 11th 2021 Member

@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

@Findus23 commented on January 11th 2021 Member

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

This Issue was closed on January 11th 2021
Powered by GitHub Issue Mirror