Dump and load of MySQL with correct charset/collation

Question

I've a Bamboo build script that shells out to MySQL Admin and does the following:

    "C:\Program Files\MySQL\MySQL Utilities 1.6\mysqladmin" -f -h server -u user -p pwd drop db1
    "C:\Program Files\MySQL\MySQL Utilities 1.6\mysqladmin" -f -h server -u user -p pwd create db1
    "C:\Program Files\MySQL\MySQL Utilities 1.6\mysqldump" db2 -h server -r c:\temp\db2.sql -n --no-data -u user -p pwd
    "C:\Program Files\MySQL\MySQL Utilities 1.6\mysql" -h server -D db1 -u user -p pw < c:\temp\db2.sql

The code essentially drops the db, recreates it and loads from a default DB (db2)

The problem is that db2 is the following charset/collation

Whereas the copied DB is;

This is creating a problem when our MySQL bulk loader runs and encounters certain characters in the data, such as:

    MySql.Data.MySqlClient.MySqlException: Invalid utf8mb4 character string: 'TO CHARGE VOLAC '

I've spent some time on this site and Google but nothing I've tried has worked.

Could someone point me in the right direction please?

It looks like you are dumping db1 right after you drop and recreate it. Is that what you expect? — robsiemb, Oct 10 '19 at 14:45
Sorry, probably a mistake in my code as I was removing sensitive data. — Mark Beynon, Oct 14 '19 at 09:59

robsiemb · Answer 1 · 2019-10-10T15:03:00.733

To answer this, I'm interpreting "default db" as the db you are copying from and "copied db" as the new DB you are trying to copy to.

If you create the new database with a SQL command instead of mysqladmin, you can set the character set of the new database:

CREATE DATABASE db1 CHARACTER SET latin1 COLLATE latin1_bin;

To do from command line:

echo 'CREATE DATABASE db1 CHARACTER SET latin1 COLLATE latin1_bin;' | mysql -h server -u user -ppw

(Also, -p with a space after it as in your example won't interpret the password properly, you need to attach it to the -p).

Dump and load of MySQL with correct charset/collation

1 Answers1