How can I know if there is data loss when converting mysql character set(s)?

Question

I'm converting a large (70G) legacy mysql database that is mostly utf8 (but with a sprinkling of other encodings in fields) to one that is (as uniformly as possible) utf8mb4 (utf8mb4_unicode_ci).

The db encoding was utf8, and most tables were utf8. Some fields were a mish-mash of different encodings, which I think is a result of the db being decades old, and are seemingly random/unintentional. There are 350 tables in the db, and all text (or %99+) is in English.

This article gave me a good start on how to go about changing the encodings. The main difficulty was in changing the field sizes for VARCHAR fields that also had an index, so that the sizes worked. Basically this involved changing text fields with an index from VARCHAR(255) to VARCHAR(191).

I made a somewhat crude check for those fields to see what the longest value was and to prevent data loss. It only found one field among hundreds that might have been truncated going from VARCHAR(255) to VARCHAR(191).

  def check_for_safe_length(column, table)
    return is_length_exception?(column, table)
    ap "Checking safety for #{table}##{column}"
    connection = Blurb.connection
    length_info = connection.execute("SELECT LENGTH(#{column}), #{column} from #{table} ORDER BY LENGTH(#{column}) DESC LIMIT 1").first
    ap length_info
    return if length_info.blank? || length_info[0].blank?
    is_safe     = length_info[0] < 191

    raise "It might not be safe to modify #{table}##{column}" if !is_safe

  end

There were also some MEDIUMTEXT fields I needed to change to TEXT.

So that is the background. My real question is how can I be confident with so much data that there is no loss of data or undesirable character conversions?

If/when I run this conversion script, do user testing, then go live, the backup db will start to diverge from the converted db. At that point, going "back" to the backup will lose whatever data went into the converted version between the time it deployed and the time we realized there was an issue. And that could be days or weeks.

So I'm feeling concerned about my ability to detect data issues. Do I have anything to worry about?

Any suggestions?

Thanks

Answer 1

converting ... utf8 ... to utf8mb4

Please provide the exact way you plan to do the conversion. There are ways that can mess things up. (mysqldump? ALTER .. CONVERT TO? ALTER some other way? etc?)

With the proper conversion, there is no data loss or mangling when going from utf8 to utf8mb4.

All ROW_FORMATs are fully interchangeable without any data loss or mangling.

VARCHAR(255) ... VARCHAR(191)

For some versions and some settings, you will need to change from 255 to 191. Please provide the exact version you are moving from and to. Please provide the GLOBAL values (before and after) of innodb_file_format, innodb_file_per_table, innodb_large_prefix. Also, the table's ROW_FORMAT

some MEDIUMTEXT fields I needed to change to TEXT.

That will save one byte; I can't think of any reason for making the change. Do you have some reason for making the change? There is the issue that certain conversions will see the bigger charset and automatically change TEXT to MEDIUMTEXT.

definitely hitting the 767 limit all day ... cols title code, and author

More details, please. Please include the next two items.

check for those fields to see what the longest value

SELECT MAX(CHAR_LENGTH(col)) ... is useful for checking for 191 characters, etc.
SELECT MAX(LENGTH(col)) ... is useful for measuring bytes in anticipation of changing TEXT to/from MEDIUMTEXT.

a mish-mash of different encodings

SELECT ... WHERE HEX(col) REGEXP '^(..)*[89A-F]' LIMIT 1; will discover whether col has any non-English letters (etc); it works for any charset (latin1, utf8, etc). It stops at the first such character; it takes a long time to scan. (Adjust as needed.)

It is probably worth noting some of the tables with non-English characters so you can especially watch those tables.

detect data issues

It is hard to check that data in a table is identical. Percona has a "checksum" tool in its "toolkit" that might achieve this goal.