0

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

14
  • "how can I be confident with so much data that there is no loss of data or undesirable character conversions?" Apart from comparing values of every column of every row of every table before and after conversion, you can't. And since you're modifying your database in place, you can't do such a comparison either. "Do I have anything to worry about?" You probably do, if your data are important to you.
    – mustaccio
    Nov 3, 2021 at 17:12
  • @mustaccio what do you mean in place? I referred to backups.
    – pixelearth
    Nov 3, 2021 at 17:15
  • I mean "in place" in the literal sense of these words: you ALTER your database and tables. An alternative would be to create a new instance of your database and copy data over.
    – mustaccio
    Nov 3, 2021 at 17:18
  • I have one instance running which is live, and I'm running this script on a copy of it. Both instances are up, running and available to compare. In fact, I'm pointing an instance of our app at the db with the new encodings, so I can compare "visually", although this is clearly not thorough. I think what you mention about comparing some values, maybe even all values might be useful as tedious as that is. It's 35 years worth of data. It's a rails app, so ActiveRecord will help with that.
    – pixelearth
    Nov 3, 2021 at 17:25
  • What version of MySQL? (the 191 issue went away in 5.7.)
    – Rick James
    Nov 3, 2021 at 22:40

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.