My database provider, bless their hearts, migrated our MySQL databases to another server recently, and introduced double-encoding of UTF-8 data via Latin1 into our text data. Strings like 'emdash—here' became 'emdashâ€”here'. 'ellipsis…' became 'ellipsisâ€¦'. 'her’s' (with curly right single quote) became 'herâ€™s'. 'Euro €' became 'Euro â‚¬'. Affected columns had data types like
VARCHAR, in tables with
DEFAULT CHARSET=utf8, before and after the migration.
Live websites use some of the databases, which may therefore have user data submitted since the migration. That means some columns will have some pre-migration rows which have double-encoded text, and also some post-migration rows which will have correctly encoded UTF-8 text. Fortunately, all of the websites are lightly used, and so there won't be much correctly-encoded recent text. But there probably is some.
What I would like is:
- A MySQL query which tells me which columns of which tables of which databases on this server are of text types which are vulnerable to double-encoding. Columns of type
VARCHARare vulnerable. Columns of type
- A MySQL query which tells me which of the vulnerable text-type columns actually have content which needs repair. Columns with zero rows do not need repair. Rows with
NULLvalues or zero-length strings do not need repair. Nor do rows which have only character values from 0 to 127, which are the same in UTF-8 and Latin1 encodings.
- A MySQL query which tells me which of the vulnerable text-type columns are safe to repair by
ALTER TABLE MODIFYqueries which affect all rows, and which have some rows which are double-encoded and others which are not — and so must be repaired selectively.
- A MySQL query which validates that the repairs were successful, that all rows of all vulnerable columns show as having been repaired successfully. I want to use this query before pushing the repaired databases live. One kind of failure I worry about is that the repair operation, on uncorrupted UTF-8 text, might turn some characters into '?' or some rows into
NULLvalues. That would lose data. I want to be sure that has not happened.
- MySQL queries which automate the process of copying a live database to a backup, doing the changes, validating the result; and other queries which make the repaired database the live one. I imagine doing the repairs by taking the websites they support offline for the duration of the repair. I want the tasks automated so that the offline interval is shorter, and the chance of stupid mistakes lower.
I figure I am not the first person to have double-encoded data. Others have probably written these queries. I have not seem them on this website, however. If I end up writing them myself, I will post them as an answer here.
P.S. Useful reading about the double-encoding problem and fixes in MySQL:
- Debugging Character Set Issues by Example, Posted on Jun 13, 2017 by Bernt Marius Johnsen, MySQL Blog Archive.
- Reply to Trouble with UTF-8 characters; what I see is not what I stored, July 14, 2016 by Rick James.
- MySQL 8.0 Reference Manual, 12.11 Cast Functions and Operators, Character Set Conversions
- MySQL 8.0 Reference Manual, 13.1.9 ALTER TABLE Statement
- MySQL Charset/Collate, Diagnosing CHARSET issues by Rick James
For reference, I can repair a selection of rows in a column with a query like,
UPDATE `wp_posts` SET post_title=CONVERT(CAST(CONVERT(post_title USING latin1) AS binary) USING utf8), post_content=CONVERT(CAST(CONVERT(post_content USING latin1) AS binary) USING utf8) WHERE ...[selection expression here]...
and I can update all rows in a column with a query like,
ALTER TABLE `wp_posts` MODIFY COLUMN `post_title` text NOT NULL CHARSET latin1, MODIFY COLUMN `post_content` longtext NOT NULL CHARSET latin1 ; ALTER TABLE `wp_posts` MODIFY COLUMN `post_title` text NOT NULL CHARSET binary, MODIFY COLUMN `post_content` longtext NOT NULL CHARSET binary ; ALTER TABLE `wp_posts` MODIFY COLUMN `post_title` text NOT NULL CHARSET utf8mb4, MODIFY COLUMN `post_content` longtext NOT NULL CHARSET utf8mb4 ;
But to use these queries, I need to know which columns of which databases on my server to modify, and which of the rows need repair, and which are not double-encoded so "repair" would corrupt them.