Character set (charset) and collation, double-encoding problems, during migration
Ni! I'm posting this here in case anyone else ever bumps into this problem.
I was forced to migrate my server to a new host. My server's database was from before transition from latin1 to utf8mb4, and it contains posts from 2014 onwards.
As far as I remember, throughout this period the database went through all updates without error, or if there was any I manually fixed and checked. And it worked fine under the previous server, always running on the master branch.
However:
- Its tables showed a mix of encodings/collations
- utf8mb4(_general_ci): propertystorage, push_subscriber, principals, register, rendezvous_groups, rendezvous_markers, rendezvous_members, schedulingobjects, calendarinstances, locks, addressbookchanges, addressbooks, calendarchanges, listeners, pchan, users, calendarsubscriptions, cards, groupmembers, calendarobjects, calendars
- latin1(_swedish_ci): all
oauth_*
tables - utf8(_general_ci): all other tables
- Its char fields still used type
char(255)
. - It used the old engine MyISAM
I dumped and imported it into the new server. So, assuming the dump+import exactly replicated the database, the only difference between source and target are the server and database character sets which changed from latin1(_swedish_ci)
to utf8mb4(_general_ci)
.
However, in the new server, a bunch of things were broken, among which:
- non-ASCII text from any column no longer displayed correctly.
- I couldn't post anymore due to encoding issues in updates and collation mismatching in comparisons.
The hints I had about this encoding issue were:
- on the mariadb command line, text appears garbled both on the old and new servers.
- when I run the following on the mariadb command line, supposed to fix a "double-encoding" problem, the text is displayed correctly on both servers:
CONVERT(BINARY(CONVERT(some_text_column USING latin1)) USING utf8mb4)
.
Given these hints, and reading this and this, my suspicion is that Hubzilla does not properly sets the dialect of the connection (e.g., with SET NAMES), so if the server defaults to a client charset different from hubzilla's actual charset, the data gets stored corrupted (doubly-encoded) - but as long as you don't change the corrupted setup (utf8 client + latin1 set names + utf8 column, see here) it will work fine.
Now, on the new server, the connection is properly established as utf8mb4, because that's the current default for mariadb on debian (but wasn't the case for the old nondebian server nor when I first created the database). As a result, non-ASCII text gets garbled.
Here's an example of errors when posting a message.
2021-09-13T03:05:18Z:LOG_INFO:477bad87a7:Item.php:624:post: mod_item: post accepted from Alexandre Hannud Abdo for Alexandre Hannud Abdo
2021-09-13T03:05:18Z:LOG_ERR:477bad87a7:dba_driver.php:463:db_logger: dba_pdo: ERROR: select created from item where uid = 19 and created > UTC_TIMESTAMP() - INTERVAL 2 MINUTE and body = 'Test\r\n....\r\n....
' limit 1
SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='
2021-09-13T03:05:18Z:LOG_INFO:477bad87a7:planets.php:41:planets_post_hook: planets invoked
2021-09-13T03:05:18Z:LOG_ERR:477bad87a7:dba_driver.php:463:db_logger: dba_pdo: ERROR: INSERT INTO `item` (`aid`, `uid`, `uuid`, `owner_xchan`, `author_xchan`, `created`, `edited`, `expires`, `commented`, `received`, `changed`, `mid`, `parent_mid`, `mimetype`, `title`, `summary`, `body`, `app`, `location`, `coord`, `verb`, `obj_type`, `allow_cid`, `allow_gid`, `deny_cid`, `deny_gid`, `attach`, `thr_parent`, `postopts`, `item_unseen`, `item_wall`, `item_origin`, `item_type`, `item_private`, `item_thread_top`, `item_starred`, `item_uplink`, `item_consensus`, `item_notshown`, `item_nsfw`, `item_relay`, `item_mentionsme`, `item_nocomment`, `item_obscured`, `item_verified`, `item_retained`, `item_rss`, `item_deleted`, `item_hidden`, `item_unpublished`, `item_delayed`, `item_pending_remove`, `item_blocked`, `layout_mid`, `public_policy`, `comment_policy`, `plink`, `route`, `item_flags`, `lang`, `sig`, `revision`, `comments_closed`, `html`, `obj`, `tgt_type`, `target`, `llink`, `parent`) VALUES ('2', '19', '6d531243-0537-4c65-a104-b779a54de354', '3yovvMYJ4NQpykaMfTMC-cVHKJHu9uqUJCrb2flZiEVQiI2Swd9LAXwepN0NLmJpXTwH3va4wN4rvSwDg7xaVg', '3yovvMYJ4NQpykaMfTMC-cVHKJHu9uqUJCrb2flZiEVQiI2Swd9LAXwepN0NLmJpXTwH3va4wN4rvSwDg7xaVg', '2021-09-13 03:05:17', '2021-09-13 03:05:17', '0001-01-01 00:00:00', '2021-09-13 03:05:17', '2021-09-13 03:05:18', '2021-09-13 03:05:18', 'https://hubzilla.com.br/item/6d531243-0537-4c65-a104-b779a54de354', 'https://hubzilla.com.br/item/6d531243-0537-4c65-a104-b779a54de354', 'text/bbcode', 'L\'Id..e (t..st)', '', 'Test\r\n....\r\n....', '', 'Paris, France', '', 'http://activitystrea.ms/schema/1.0/post', 'http://activitystrea.ms/schema/1.0/note', '', '', '', '', '', 'https://hubzilla.com.br/item/6d531243-0537-4c65-a104-b779a54de354', '', '0', '1', '1', '0', '0', '1', '0', '0', '0', '0', '0', '0', '0', '0', '0', '1', '0', '0', '0', '0', '0', '0', '0', '0', '', '', 'contacts', 'https://hubzilla.com.br/item/6d531243-0537-4c65-a104-b779a54de354', '', '0', '', 'sha256.pURpsXtZvrRa_QMLJM4s3rPsJxHMuxjxNITJbUaRIrVDqRQtCTYTb4sj80hzgT-_nVjDNigbaM-j2dxJI27r7JExkyVNSpT2kJL2d8VjXuKhzd_eyqx72XYyfX5awBQE5euvX4Vcybq3eqvqX-d1bh0wSoC2QDytzC9E4_a_RJz_pFN27X4JetFtM3Lza0I7Ckt_K2PK6ViVCAZBqcjXMqhnKnOnN5q5HK08QFl05arOWxtLX5DQgh2P1N1UU3zWjKOOwHO3UWn-5aKL-ipU6NdMJ4ATnD8VlAZ1QmIGh06m4vofgmUnKbsfmNmIuNNuRrV3p6qWTpOmtLpXnBj0gZCLMLjIFq9ELOuirGaGQYJD2MSgljj3Ac6Vbqad2Wo7ScuCasNYqWjUB03atuI7uhdI4OwSWR2JC_AcsGtkmP7X2p0-KFC4ipkEXVeaQ-RMJf61PziYXnkWVQ4SXms3zn5CpYapdjCpueLOz-FlNkaspAQgczYPo9_PpFeXuVjOg5ZTbOtyNsTg6mkgmZSsPtcmNwzQ5R1T3S7ZxBmZnEv28e0HuG-ir3WqPplckS0fCb1jyk5h3-UjoMyyBCSeeVTZyYvv9rzUR_PJaXqVtjeld2uHHgzUPyzp6Re7MjL92W5voV6y4E-94s3AtLqGP-pPwgFll9RQuzgwhZFPp08', '0', '0001-01-01 00:00:00', '', '', '', '', 'https://hubzilla.com.br/display/b64.aHR0cHM6Ly9odWJ6aWxsYS5jb20uYnIvaXRlbS82ZDUzMTI0My0wNTM3LTRjNjUtYTEwNC1iNzc5YTU0ZGUzNTQ', '0')
SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF0\x9F\x92\xA9\x0D\x0A...' for column `hubzilla`.`item`.`body` at row 1
2021-09-13T03:05:18Z:LOG_INFO:477bad87a7:items.php:1960:item_store: item_store: could not locate stored item
2021-09-13T03:05:18Z:LOG_INFO:477bad87a7:Item.php:1336:post: mod_item: unable to retrieve post that was just stored.
Long story short, I had to manually convert every char, varchar, text and mediumtext column on every table to the correct charset+collation, then update their contents using the aforementioned command to undo double-encoding. I tried to automate as much as possible, but there were still some bumps that required manual intervention, like data being too long for the new type (and, to make things worse, I had left the hub on for a short period before I realized things were broken, so some recent insertions were already properly encoded and trying to undo the double-encoding threw an error).
Everything seems to be working fine now, there were some other details like columns that lacked defaults after the migration, but I can only assume that was due to some command I run while fixing the encoding issue.
Now, what I take from this for now is that Hubzilla should set names at connection time: SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;
, but I'm no SQL expert.
I'll share here some of the statements tha helped me with the task:
SET NAMES utf8mb4 COLLATE utf8mb4_general_ci; # because oddly the command line client connects as utf8 not utf8mb4.
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; # to check the collation
# the essential trick, first change collation then undo double-encoding:
alter table tab modify column col char(191) character set utf8mb4;
UPDATE tab SET col = CONVERT(BINARY(CONVERT(col USING latin1)) USING utf8mb4);
# attention: the statement generators below assume every column is doubly encoded
# tempalte for "255" columns (do this for char and varchar)
SET @old_column_type = "char(255)"
SET @new_column_type = "char(191)"
(SELECT
concat(
'alter table ', table_name, ' modify column ', column_name, ' ', @new_column_type, ' character set utf8mb4;',
'update ', table_name, ' set ', column_name, ' = CONVERT(BINARY(CONVERT(', column_name, ' USING latin1)) USING utf8mb4);'
)
FROM information_schema.columns
WHERE table_schema = DATABASE() and column_type = @old_column_type
order by table_name, column_name)
# for non-"255" columns, preserving column_type
(SELECT
concat(
'alter table ', table_name, ' modify column ', column_name, ' ', column_type, ' character set utf8mb4;',
'update ', table_name, ' set ', column_name, ' = CONVERT(BINARY(CONVERT(', column_name, ' USING latin1)) USING utf8mb4);'
)
FROM information_schema.columns
WHERE table_schema = DATABASE() and column_type not like "%char(191%"
order by table_name, column_name)
Pay uttermost attention not to undo double-encoding on the same column twice!
Finally, I know some types are not exact in my database. For one, in addressbooks displayname
is char(191) (converted from char(255) during migration), while in the schema it is varchar(255) - no idea why it wasn't changed to 191. There are a few other cases. I wish there was MariaDB command to apply all column definitions from a schema dump to a database.
.~´