CODEWITHSUNDEEP
mysqliso-8859-1character-set
Mark Beynon
Mark Beynon

Reputation: 265

Dump and load of MySQL with correct charset/collation

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

Charset: latin1, Collation: latin1_bin

Whereas the copied DB is;

Charset: utf8mb4, Collation: utf8mb4_0900_...

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?

Upvotes: 0

Views: 319

Answers (1)

robsiemb
robsiemb

Reputation: 6354

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).

Upvotes: 1

Related Questions