Hello,
We are migrating our old RT 3.6.1 database (MySQL) to RT 5.0.0 (MariaDB).
As per documentation, I did the following:
- created a backup using the following command:
(mysqldump -u root -p<removed> rt3 --databases --tables sessions --no-data --single-transaction --default-character-set=binary --max_allowed_packet=1G; mysqldump -u root -p<removed> rt3 --databases --ignore-table rt3.sessions --single-transaction --default-character-set=binary --max_allowed_packet=1G) | bzip2 > /work/tmp/rt3-`date +%Y%m%d`.sql.bz2
- Installed RT 5.0.0 and configured it to use “latin1”:
# mysqladmin variables | grep -i character_set
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| session_track_system_variables | autocommit,character_set_client,character_set_connection,character_set_results,time_zone
- Restored the “rt3” database on the RT5 host as:
{ echo 'CREATE DATABASE rt3; USE rt3;'; bzip2 -dc /root/rt3-20231010.sql.bz2; } | mysql --max_allowed_packet=1G --default-character-set=binary
- Switched to using “rt3” database in /opt/rt5/etc/RT_SiteConfig.pm
- Ran “make upgrade-database” up to version 3.7.87
- Ran “perl etc/upgrade/upgrade-mysql-schema.pl rt3 > rt3-queries.sql”
- Ran “mysql -u root -p rt3 < rt3-queries.sql”. No errors were observed.
- Continued with “make upgrade-database” starting from version 3.7.87. Some warnings were observed, but no errors.
The problem is, that in the end, the “Subject” column of some tickets with the Cyrillic characters doesn’t display correctly, when I change MariDB server to use “utf8mb4” encoding, starting RT standalone server and looking to the ticket in my browser.
If I check the “Subject” column encoding, it shows utfmb4, as expected:
MariaDB [(none)]> SELECT character_set_name FROM information_schema.`COLUMNS` WHERE table_schema = "rt3" AND table_name = "Tickets" AND column_name = "Subject";
+--------------------+
| character_set_name |
+--------------------+
| utf8mb4 |
+--------------------+
However, the “Subject” of the problem ticket doesn’ look correctly:
# mysqladmin variables | grep -i character_set
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| session_track_system_variables | autocommit,character_set_client,character_set_connection,character_set_results,time_zone
# echo "select Subject from rt3.Tickets where id = 63804;" | mysql --default-character-set=utf8mb4
Subject
невозможно добавить запиÑÑŒ в SEL
But, if I change “mysql” encoding to “latin1”, the Subject is displayed correctly:
# echo "select Subject from rt3.Tickets where id = 63804;" | mysql --default-character-set=latin1
Subject
невозможно добавить запись в SEL
Subsequently, If I configure MariaDB to use latin1, and hack /opt/rt5/lib/RT/Handle.pm to comment out the “SET NAMES ‘utf8mb4’” op, the ticket is displayed correctly in the browser.
It looks like the content of the “Subject” column hasn’t been converted from latin1 to utf8mb4 during migration, is that my assumption correct?
If so, how could I convert my old rt3 database for RT5 with utf8mb4 coding?
Could please someone help?