Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Closed
C-Duv opened this issue Dec 30, 2020 · 4 comments
Closed

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

C-Duv opened this issue Dec 30, 2020 · 4 comments
Labels
answered For when a question was asked and we referred to forum or answered it.

Comments

@C-Duv
Copy link

C-Duv commented Dec 30, 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 @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| 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
Copy link
Member

sgiehl commented Dec 30, 2020 via email

@C-Duv C-Duv mentioned this issue Dec 30, 2020
Closed
@C-Duv
Copy link
Author

C-Duv commented Dec 30, 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
Copy link
Member

sgiehl commented Jan 11, 2021

@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

@sgiehl sgiehl closed this as completed Jan 11, 2021
@sgiehl sgiehl added the answered For when a question was asked and we referred to forum or answered it. label Jan 11, 2021
@Findus23
Copy link
Member

Using foreign key contraints in plugins might also cause other unexpected issues like
dominik-th/matomo-plugin-LoginOIDC#35

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
answered For when a question was asked and we referred to forum or answered it.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@C-Duv @sgiehl @Findus23