MySQL dump to MariaDB issue with collation

All we need is an easy explanation of the problem, so here it is.

I have a django app running on a staging server (windows server 2012) with a MariaDB database. I have a git hook running mysqldump locally when I push to the staging branch. On the server I have a batch script pulling from this branch, migrating django migrations, import the dump and finally restarting the site. Looks a bit like this:

@echo off
D:
cd <repo>
cmd /k "git stuff && python manage.py migrate && mysql -uroot db < D:/repo/db.sql && stop/start site... && exit
PAUSE

Sadly though when the script reaches the dump import it spits out an error saying it doesn’t know the collation (ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_0900_ai_ci'
).
After some googling I found out I just have to open the sql in an editor and replace all ‘utf8mb4_0900_ai_ci’ with ‘utf8mb4_general_ci’. Sure that works for this one time, that’s not entirely useful for me as this dump is part of an automated process.

Does someone know a simple solution to automate the collation replacement?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

{ Related to @nbk’s suggestion (in a comment on the question) to use:
sed -e 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' -i myfilename.sql
}

I think there are two main options for running Unix commands in Windows:

  1. "Windows Subsystem for Linux Documentation (WSL)" — this is provided by Microsoft. According to the "Windows interoperability with Linux" page, you should be able to run sed via the wsl.exe command, as in:

    C:\>wsl sed ...
    

    Please be aware that WSL2 requires Hyper-V and that might prevent VMWare and/or VirtualBox from working (if you use either or both of those). At first they didn’t work with Hyper-V, then they did, then something in Windows changed and they stopped working with Hyper-V, and the current status is a little unclear (please see: WSL2 breaks VirtualBox 6.1 #798 )

  2. Many Unix/Linux commands have been ported to Windows via various 3rd party packages. The two that I am aware of are:

    • Cygwin — this project is actively maintained (most recent build is a few months ago). This is a fairly large project (currently 9907 "packages" – commands, libraries, etc), but I think you have the option to only install the components that you want. You can see the full list of packages here.
    • UnxUtils — smaller project than Cygwin, and not currently maintained (most recent update is from 2014, and on a diff site; will give link in a moment), but I believe this one is lighter weight / easier to take one or two commands as there are no dependencies. There are two sites with files to download, and it’s unclear which is newer, but I think I would go to this site: http://unxutils.sourceforge.net/ and get both the UnxUtils.zip and UnxUpdates.zip files.

In both cases you can get favorites such as: sed, gawk, etc.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply