Upgrading from rt34 (yeah, yeah, yeah) to rt5.0, working on the databases migration.
Dump the rt34 database on the old server
$ mysqldump -u root -p --default-character-set=binary --databases rt34 > just-rt34.sql
$ gzip -9 just-rt34.sql # Almost 6G so gzip it
Restore the rt34 database on the new server
$ zcat just-rt34.sql.gz | mysql -h 127.0.0.1 -u root -p --default-character-set=binary
Apply database scheme migrations
$ etc/upgrade/upgrade-mysql-schema.pl rt34:x.x.x.x root my-password-snipped > queries.sql
$ mysql -h x.x.x.x -u root -p rt34 < queries.sql
ERROR 1366 (22007) at line 20: Incorrect string value: ‘\xC2 > at…’ for column rt34.Attachments.Headers at row 42626
I made the assumption there’s corruption in the Attachments table and I’ve gone back a couple months in my backups. All the backups throw the same error. Did I miss a step from the README’s? Or has the Attachment table been corrupt for a “long time”?
What’s my next step?
Treat this like a database integrity issue and work there or is this something I need to work on in Request Tracker?
Any help would be appreciated.
Check of the Attachments table would indicate the table is OK.
mysql> check table Attachments ;
+------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------+-------+----------+----------+
| rt34.Attachments | check | status | OK |
+------------------+-------+----------+----------+
1 row in set (9 min 23.34 sec)
Here is the SQL that throws the error message
ALTER TABLE Attachments
MODIFY MessageId VARCHAR(160) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY Subject VARCHAR(255) CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY Filename VARCHAR(255) CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY ContentType VARCHAR(80) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY ContentEncoding VARCHAR(80) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY Headers LONGTEXT CHARACTER SET utf8 NULL DEFAULT NULL
--------------
ERROR 1366 (22007) at line 20: Incorrect string value: '\xC2 > at...' for column `rt34`.`Attachments`.`Headers` at row 42626
Editing the queries.sql and removing the problematic SQL I find several more tables that fail the migration.
ALTER TABLE Tickets
MODIFY Type VARCHAR(16) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY Subject VARCHAR(200) CHARACTER SET utf8 NULL DEFAULT '[no subject]',
MODIFY Status VARCHAR(10) CHARACTER SET ascii NULL DEFAULT NULL
--------------
ERROR 1366 (22007) at line 164: Incorrect string value: '\xF0\x9F\x92\xBB\xF0\x9F...' for column `rt34`.`Tickets`.`Subject` at row 64787
ALTER TABLE Transactions
MODIFY Type VARCHAR(20) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY Field VARCHAR(40) CHARACTER SET ascii NULL DEFAULT NULL,
MODIFY OldValue VARCHAR(255) CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY NewValue VARCHAR(255) CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY Data VARCHAR(100) CHARACTER SET utf8 NULL DEFAULT NULL,
MODIFY ObjectType VARCHAR(64) CHARACTER SET ascii NOT NULL DEFAULT '',
MODIFY ReferenceType VARCHAR(255) CHARACTER SET ascii NULL DEFAULT NULL
--------------
ERROR 1366 (22007) at line 173: Incorrect string value: '\xC2' for column `rt34`.`Transactions`.`Data` at row 38690
That looks like the character encoding of the database, table or even column may be different to what is expected (or possibly between the old server and the new one if you’re moving the backup from an old machine to a new one), so you’ve got multi-byte characters that MySQL/MariaDB can’t make sense of in to convert to utf8. These sort of things can take some hunting down.
My first suggestion would probably be to check what the /etc/my.cnf file(s) say on both the old and new server, and make them the same to start with (don’t change the old server obviously - make the new one match it!).
This question has come up before and @knation pointed to a stackoverflow thread about it (as its really a database issue, rather than RT itself).
I think I might be misunderstanding this document.
https://docs.bestpractical.com/rt/5.0.2/UPGRADING.mysql.html
1b. Configure the new MySQL to use Latin1 as the default character set everywhere, not UTF-8. This is necessary so the import in the next step assumes the data is Latin1.
1c. Import the dump made in step 1a into the new MySQL server, using the --default-character-set=binary option on restore. This will ensure that the data is imported as bytes, which will be interpreted as Latin1 thanks to step 1b above.
Followed by
10. If you were upgrading from MySQL 4.0, you may now, if you wish, reconfigure your newer MySQL instance to use UTF-8 as the default character set, as step 7 above adjusted the character sets on all existing tables to contain UTF-8 encoded data, rather than Latin1.
I think the UPGRADING.mysql.html should have documentation (or link) on how to set the new server to Latin1 and how to reconfigure the server default back to UTF-8.
I’ll work through this issue and post a PR.