I’ve been running into several problems with restoring MySQL backups. Namely, the backups come from an environment other than the one I’m working in and I’m forced to remove superuser commands contained in the backups.

The problem is when trying to remove those commands I’m constantly getting UTF-8 encoding errors because there are loads of invalid character sequences.

Why would MySQL encode a backup as UTF-8 if the data isn’t actually UTF-8? This feels like bad design to me.

  • folekaule@lemmy.world
    link
    fedilink
    arrow-up
    32
    ·
    7 days ago

    Not sure if this helps you, but for anyone working with utf8 and MySQL, it’s worth reading up on the details of their Unicode support. Especially the part where it says that ‘utf8’ is an alias for ‘utf8mb3’, which may not be compatible with what other systems consider to be ‘utf8’. If you aren’t careful with this you will have problems, especially with high code points, like emoji.

    • modeler@lemmy.world
      link
      fedilink
      arrow-up
      4
      ·
      7 days ago

      This is the right answer. I had the job of planning a schema update to fix this shitty design.

      Saying that, unicode and character formats are incredibly complex things that are not easily implemented. For example two strings in utf-8 can contain the same number of characters but be hugely different in size (up to 3-4x different!). It’s well worth reading through some articles to get a feel of the important points.

    • limer@lemmy.dbzer0.com
      link
      fedilink
      English
      arrow-up
      8
      ·
      7 days ago

      Not only are there different character sets that seem like it’s Unicode, but the set in MySQL can change based on the session, the client, the server, the db , the table and the column. All six of them can have different encodings.

      Just make sure all are using the same 4 byte Unicode. Different collation is ok when backing up because only important when comparing strings.