Public service announcement: Check your MySQL backup!

[Sim]

Not a charset issue but sorta related -- I was testing some recent backups done with mysqldump and found that I could not restore them properly due to issues with the VARBINARY fields that XF uses.

From what I've gathered, it seems to be recommended to use the --hex-blob option if you have binary data in your database. I tried this and I can now import my backups without any issues.

When dumping tables through some other tools like phpMyAdmin, it seems to use the hex format by default, so not an issue when exporting there. At any rate I'm glad I caught this before I actually had to use one of the backups. The only downside of using hex-blob is that it seems to increase the size of the backups, but since it's only used for smaller fields in XF not an issue really (correct me if I'm wrong on this).

Anyway, just posting this in case anyone else runs into the same issue.

Just a follow up to this.

I was looking to take a snapshot of my XF 1.5 production site that runs on Linux - and install it on my dev machine in Laravel Herd which is Windows based.

I was receiving duplicate key errors importing a table with varbinary IP addresses in it (custom table from an addon).

Interestingly, importing the same database dump into one of my dev VMs running Linux had no issue.

After much trial and error with character sets and line encodings and a lot of Googling, I've come to the following conclusion:

If importing a mysql dump on Windows that contains binary data, you will likely need to use --hex-blob when generating the database dump, but this does not seem to be required on Linux servers for some reason.