Unsupported charset or collation for tables warning after upgrade from 6.0 to 6.4

[yylinnn]

Just upgrade zabbix from 6.0 to 6.4. Everything looks like good.
But there is a warning in System Infomation: Unsupported charset or collation for tables: scim_group, userdirectory, connector, userdirectory_media, userdirectory_saml, connector_tag, userdirectory_ldap, userdirectory_idpgroup.
My mariaDB version is: Server version: 10.9.3-MariaDB-1:10.9.3+maria~ubu2004
setting of collation is:
MariaDB [(none)]> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4 | utf8mb4_general_ci |
+--------------------------+----------------------+
1 row in set (0.000 sec)

and I tried to alter :

[I]alter database zabbix  character set utf8mb4 collate utf8mb4_bin;[/I]

but nothing changed, the @@collation_database is still "utf8mb4_general_ci"

Is anyone has suggestion?

[tim.mooney]

Even the Zabbix documentation that you found isn't very clear. The system variable @@collation_database is updated whenever you connect to a specific database, to reflect the settings for that database. The Zabbix doc you reference doesn't seem to say it currently, but you need to be connected to the 'zabbix' database to get the settings for it. Watch:

MariaDB [(none)]> SELECT @@collation_database;
+----------------------+
| @@collation_database |
+----------------------+
| utf8_unicode_ci      |
+----------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> use zabbix;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [zabbix]> SELECT @@collation_database;
+----------------------+
| @@collation_database |
+----------------------+
| utf8_bin             |
+----------------------+
1 row in set (0.000 sec)

I typically use the "SHOW CREATE" family of commands when I want to get information about how a database or table or whatever actually was created.

In your case, I suspect you're going to find that once you've connected to your 'zabbix' database it is now using the correct collation, so future table creation will inherit that, but you have a few older tables that are using the wrong setting. Altering the database doesn't alter those tables. You need to fix each of them too.

[yylinnn]

thank you very much.
finally , the problem is sloved. I update the table with SQL like
MariaDB [zabbix]> ALTER TABLE host_rtdata CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 0 rows affected (0.005 sec)
Records: 0 Duplicates: 0 Warnings: 0

thanks a lot

[yylinnn]

I found a doc “Repairing Zabbix database character set and collation”

https://www.zabbix.com/documentation/current/en/manual/appendix/install/db_charset_coll

and tried follow the step to

alter database zabbix character set utf8mb4 collate utf8mb4_bin;

but nothing changed, the @@collation_database is still "utf8mb4_general_ci"

[tim.mooney]

Even the Zabbix documentation that you found isn't very clear. The system variable @@collation_database is updated whenever you connect to a specific database, to reflect the settings for that database. The Zabbix doc you reference doesn't seem to say it currently, but you need to be connected to the 'zabbix' database to get the settings for it. Watch:

MariaDB [(none)]> SELECT @@collation_database;
+----------------------+
| @@collation_database |
+----------------------+
| utf8_unicode_ci      |
+----------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> use zabbix;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [zabbix]> SELECT @@collation_database;
+----------------------+
| @@collation_database |
+----------------------+
| utf8_bin             |
+----------------------+
1 row in set (0.000 sec)

I typically use the "SHOW CREATE" family of commands when I want to get information about how a database or table or whatever actually was created.

In your case, I suspect you're going to find that once you've connected to your 'zabbix' database it is now using the correct collation, so future table creation will inherit that, but you have a few older tables that are using the wrong setting. Altering the database doesn't alter those tables. You need to fix each of them too.

[yylinnn]

thank you very much.
finally , the problem is sloved. I update the table with SQL like
MariaDB [zabbix]> ALTER TABLE host_rtdata CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 0 rows affected (0.005 sec)
Records: 0 Duplicates: 0 Warnings: 0

thanks a lot

[yylinnn]

Yes, you are correct. Thank you very much.
I checked the zabbix database, it's already in right collation
MariaDB [zabbix]> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4 | utf8mb4_bin |
+--------------------------+----------------------+
1 row in set (0.000 sec)

MariaDB [zabbix]>

Then ,the next step is to correct these table's collation.
MariaDB [zabbix]> SELECT TABLE_NAME, CCSA.CHARACTER_SET_NAME AS table_charset, CCSA.COLLATION_NAME AS table_collation FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLIC ABILITY` CCSA WHERE CCSA.COLLATION_NAME = T.TABLE_COLLATION AND T.TABLE_SCHEMA = 'zabbix' and table_collation = 'utf8mb4_general_ci';
+------------------------+---------------+--------------------+
| TABLE_NAME | table_charset | table_collation |
+------------------------+---------------+--------------------+
| scim_group | utf8mb4 | utf8mb4_general_ci |
| userdirectory | utf8mb4 | utf8mb4_general_ci |
| changelog | utf8mb4 | utf8mb4_general_ci |
| host_rtdata | utf8mb4 | utf8mb4_general_ci |
| connector | utf8mb4 | utf8mb4_general_ci |
| user_scim_group | utf8mb4 | utf8mb4_general_ci |
| userdirectory_media | utf8mb4 | utf8mb4_general_ci |
| userdirectory_saml | utf8mb4 | utf8mb4_general_ci |
| userdirectory_usrgrp | utf8mb4 | utf8mb4_general_ci |
| event_symptom | utf8mb4 | utf8mb4_general_ci |
| connector_tag | utf8mb4 | utf8mb4_general_ci |
| userdirectory_ldap | utf8mb4 | utf8mb4_general_ci |
| userdirectory_idpgroup | utf8mb4 | utf8mb4_general_ci |
+------------------------+---------------+--------------------+
13 rows in set (0.002 sec)