MySQL database charset settings (Upgrade 5.8 - 5.10)

Community Support
1

Hello
I’ve tried to upgrade Opennebula from 5.8 to 5.10 in my test environment.
I did the packages upgrade and run onedb fsck.
According your documentation I got a message “Table and database charset (utf8, latin1) differs” and run the command:
ALTER DATABASE opennebula CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
The error message repeated.
I saw that you mentioted system_attributes table in the documentation and converted it:
ALTER TABLE opennebula."system_attributes" CONVERT TO CHARACTER SET utf8mb4;
Finally onedb upgrade and onedb fsck finished without any errors.

My question: Is it correct to change only table (system_attributes)? Because I had the similar issue during upgrade to 5.8 with vm_pool table

Versions of the related components and OS (frontend, hypervisors, VMs): Opennebula 5.10, Ubuntu 18.04 LTS, Mariadb 10.4.8+maria~bionic

2

Hello @FoxSibirsky,

No, we are assuming that all your tables have the same encoding. So in that case you should change the encoding value for every table. Maybe a note mentioned this can be added in the documentation.

The ALTER DATABASE opennebula CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; should be enough. Did you check that the query was successfully executed by running select default_character_set_name FROM information_schema.SCHEMATA where schema_name = "opennebula" after the change?

3

Thank you for response!
Yes, I checked it
Default_character_set_name is utf8mb4 as expected

Although if we speak about all tables in the database we can see this:

MariaDB [opennebula]> select CCSA.character_set_name,table_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "opennebula";
+--------------------+----------------------------+
| character_set_name | table_name                 |
+--------------------+----------------------------+
| latin1             | marketplace_pool           |
| latin1             | vm_import                  |
| latin1             | user_pool                  |
| latin1             | vmgroup_pool               |
| latin1             | document_pool              |
| latin1             | secgroup_pool              |
| latin1             | host_monitoring            |
| latin1             | template_pool              |
| latin1             | network_vlan_bitmap        |
| latin1             | acl                        |
| latin1             | group_pool                 |
| latin1             | history                    |
| latin1             | vm_showback                |
| latin1             | vm_monitoring              |
| latin1             | cluster_vnc_bitmap         |
| latin1             | local_db_versioning        |
| latin1             | db_versioning              |
| latin1             | zone_pool                  |
| latin1             | vdc_pool                   |
| latin1             | marketplaceapp_pool        |
| latin1             | vrouter_pool               |
| latin1             | pool_control               |
| utf8               | image_pool                 |
| utf8               | vn_template_pool           |
| utf8               | network_pool               |
| utf8               | vm_pool                    |
| utf8mb4            | system_attributes          |
| utf8mb4            | cluster_datastore_relation |
| utf8mb4            | hook_pool                  |
| utf8mb4            | cluster_network_relation   |
| utf8mb4            | datastore_pool             |
| utf8mb4            | cluster_pool               |
| utf8mb4            | logdb                      |
| utf8mb4            | group_quotas               |
| utf8mb4            | user_quotas                |
| utf8mb4            | host_pool                  |
| utf8mb4            | hook_log                   |
+--------------------+----------------------------+

My opennebula database has been living since version 4.2
According to yor advice I’ll convert other tables.

4

Hello again,

I think the problem is that your case is inverse to the one in the documentation, you are seeing:

Table and database charset (utf8, latin1) differs

instead of:

Table and database charset (latin1, utf8mb4) differs

Note the order of the encoding. For that case you have to update the database character set to latin1, something like:

ALTER DATABASE opennebula CHARACTER SET latin1 COLLATE <collation>;

5

Hello again
I’ve compared my database characters setting and default. You use latin1 encoding, but last upgrade I got an error from the MySQL (Maria DB had version 10.4.6)

Mysql2::Error: Incorrect string value: ‘\xD1\x83\xD0\xB4\xD0\xB0…’ for column opennebula . vm_pool . short_body at row 1

I solved it changing character set to utf8:

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE opennebula.vm_pool CONVERT TO CHARACTER SET utf8;

I don’t remember exactly if I changed all tables or not.

Now I realized that your script compares equality “character settings” for the whole database and for the table “system_attributes”.
I’m not sure if converted to latin1 database will work.

May be it would better to use utf8mb4 for the opennebula database?