MySQL – Round Trip Import and Export Data Discrepancies

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 |
+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+