MySQL Round Trip import and export not the same

Question

I'm setting up a development environment for a content management system with a database backend.

The source for the dev environment is a virtual machine snapshot from production the target environment is a docker environment.

We have the application code under git source control, but also want to source control the database content. This will enable us to migrate both code and content all at once and know what we are changing.

I've written an import script and an export script.

Everything is working except for about 7 tables that won't roundtrip data. Each export and import loop the data is growing for some odd characters.

I have this data in an import statement

"Sparty’s\"

After importing it and re-exporting it it becomes this:

"Sparty’s\"

Here are my character sets and collations:

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'dbname';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8                       | utf8_unicode_ci        |
+----------------------------+------------------------+
1 row in set (0.00 sec)

Here is my export bash script

for x in `mysql --skip-column-names -u root -ppassword dbname -e 'show tables;'`; 
do
     echo exporting $x
     mysqldump -u root -ppassword --skip-add-drop-table --skip-add-locks --skip-disable-keys --skip-set-charset --default-character-set=utf8 --extended-insert=FALSE --replace --skip-dump-date dbname $x > "/var/db/$x.sql"
done

Here is my import statement

service mysql start 
for x in `ls /var/db/*.sql`; do
     echo importing $x
     mysql -ppassword dbname --default-character-set=utf8 --force < $x
done

I feel the problem is in the import because the data looks correct when I'm importing it, but I'm not sure what is up...

SHOW VARIABLES LIKE 'char%';                   
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.02 sec)

The column meta on this table is one of the seven tables giving me trouble.

show create table cms_form_field;
+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                                                                                                                                             |
+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| cms_form_field | CREATE TABLE `cms_form_field` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `form_id` int(10) unsigned NOT NULL,
  `label` text NOT NULL,
  `type` varchar(25) NOT NULL,
  `order_number` int(11) NOT NULL,
  `meta` text NOT NULL,
  `update_user_id` int(10) unsigned NOT NULL,
  `update_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=314 DEFAULT CHARSET=utf8 |
+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Answer 1

You had latin1 somewhere. See https://stackoverflow.com/a/38363567/1766831 . Do...

• the SELECT HEX ... to see whether you have the correct utf8 encoding: E28099.
• SHOW CREATE TABLE to see what CHARACTER SET that column has.
• SHOW VARIABLES LIKE 'char%'; to see what the connection is like.

Read about Mojibake.

Answer 2

After hours of trying to figure out what was going on I finally solved the issue.

The issue in my particular case was that the import statement I was using for test:

mysql -ppassword dbname --default-character-set=utf8 --force < ./db/tablename.sql

Was different from my scripted import.

mysql -ppassword dbname --force < ./db/tablename.sql

How I figured it out was by viewing the binary at each stage in the process to ensure it was what I expected.

First, the way I discovered there were discrepancies in the first place was by using git.

By adding my sql file to git git add utf8.sql then when I do an export I can do git status and git diff --word-diff=color to see if the data changed.

Using this method I isolated one table, then one row, and then one character that was failing to use as my test case.

The rest of these steps focus on ensuring that character is right.

How to find the character. Look in your data. In my case I had "Sparty’s\" that would look incorrect after export and import. By using a hex viewer (discussed below) I used the y and s characters as the endpoints and looked at what was in the middle to determine the character.

Starting with the database. I would select the one column from the one row with the issue as hex like this:

hex(meta) from cms_form_field where id = 187;

I would paste the hex result from the buffer into a text tool (Sublime in my case)

613A323A7B733A31323A226669656C645F76616C756573223B613A31333A7B733A363A2244696E696E67223B733A363A2244696E696E67223B733A31303A22537061727479C3A2E282ACE284A273

That string above is this text:

a:2:{s:12:"field_values";a:13:{s:6:"Dining";s:6:"Dining";s:10:"Sparty’s

After pasting the hex text into Sublime I would search for 79 and see what characters were next. If it was E28099 I was in good shape, time to move on to the next step of the process.

In the above example it is not the character i'm expecting (E28099) instead it is C3A2E282ACE284A2. This means the data is bad in the database.

After ensuring the data is correct in the database the next place it goes is the filesystem.

I used vim to follow a similar process to verify the character is what I need: E28099

First I export the data to a file on disk:

mysqldump -u root -ppassword --skip-add-drop-table --skip-add-locks --skip-disable-keys --skip-set-charset --default-character-set=utf8 --replace --skip-dump-date dbname  > "./utf8.sql"

Then I needed a hex viewer. Sublime has one, but it didn't work well (search was input in hex and didn't seem to search the whole buffer for me, and I had to have another window open to help me compare the expected text to) so I used vim. To put vim in hex mode I used the command :%!xxd after opening utf8.sql.

Vim is nice because it shows the hex on the left and unicode on the right. Additionally allows searching by unicode! So I can input my search term Sparty and quickly see the next character, looking for E28099. If I didn't see E28099 and in the previous step it was E28099 then that means the problem was with the export.

This is what the line looks like in vim

000a650: 3a31 303a 5c22 5370 6172 7479 e280 9973  :10:\"Sparty...s

I copy paste that line into Sublime to do my manual character comparison. This isn't necessary, but I'm not good at reading hex so it helps.

3a31 303a 5c22 5370 6172 7479 e280 9973  :10:\"Sparty...s
: 1  0 :  \ "  S p  a r  t y  ’      s 
these markers delinate the    |     |
character ’ character I'm     |     |
looking for (3 bytes)         |     |

Other things that helped: In mysql doing select hex('y'); shows me the

+----------+
| hex('y') |
+----------+
| 79       |
+----------+

To get the reverse I can do select X'79';

+-------+
| X'79' |
+-------+
| y     |
+-------+

In my case this was useful for dealing with select X'E28099'; although I couldn't paste select hex('’'); into my sql terminal (however it worked from save command files)