How can I detect double-encoded MySQL columns and rows, and validate the repair?

Question

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 TEXT and 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.

Now the problem of double-encoding, and the SQL queries to repair it, have been covered many times on this site and on StackOverflow. See below for a summary. That is not my question.

What I would like is:

1. 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 VARCHAR are vulnerable. Columns of type INT are not.
2. 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 NULL values 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.
3. A MySQL query which tells me which of the vulnerable text-type columns are safe to repair by ALTER TABLE MODIFY queries which affect all rows, and which have some rows which are double-encoded and others which are not — and so must be repaired selectively.
4. 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 NULL values. That would lose data. I want to be sure that has not happened.
5. 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.

Answer 1

I think you need to look at the bytes in each column of each table. Do not trust displaying the output; browsers, especially, will "fix" the rendering of the output.

Follow the guidelines here: http://mysql.rjweb.org/doc.php/charcoll#fixes_for_various_cases You seem to be suggesting the 2-step ALTER. It assumes the column has "CHARACTER SET latin1, but has utf8 bytes in it"

To discuss specific cases, please provide SELECT col, HEX(col) FROM ... so we can discuss whether it is simply Mojobake, double-encoding, or (rarely) triple-encoding, or even some other gibberish.

You can use the following to check a few cases on a column via

SELECT col, HEX(col)
    FROM tbl
    WHERE HEX(col) REGEXP '(..)*[89a-fA-F]'
    LIMIT 5;

You can use a SELECT against information_schema.COLUMNS to generate such Selects for each text column in your system.