MySQL import and export problem

Question

I am trying to export a table from one database and import it into another. The table in question has a definition like this:

CREATE TABLE `mydb`.`people` (
 `id` BINARY(16) NOT NULL,
 `name` VARCHAR(50) NOT NULL,
 PRIMARY KEY (`id`)

And I'm exporting it like this:

mysqldump -u root -pmypass -h x.x.x.x mydb people > /tmp/export.sql

And trying to import it like this:

mysql -u root mydb < /tmp/export.sql

To which I get the error:

ERROR 1406 (22001) at line 1: Data too long for column 'id' at row 1

Looking at the file, It has exported the id as values like this:

'0円0円�\�\��DM�� �Ϥ6�'

Which seems to be interpreted as a string. Now, since I'm dealing with tremendous amounts of data and cannot go through it manually, is there a way I can fix the import/export to interpret this value as binary?

Answer 1

Use --hex-blob:

mysql> create database dbase;
Query OK, 1 row affected (0.00 sec)
mysql> use dbase;
Database changed
mysql> create table hexhex ( id binary(16) );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into hexhex values (UNHEX('DEADBEEF'));
Query OK, 1 row affected (0.01 sec)
mysql> select * from hexhex;
+------------------+
| id |
+------------------+
| ޭ▒ |
+------------------+
1 row in set (0.00 sec)
mysql> quit
Bye
root@ironforge:~# mysqldump --hex-blob -p dbase > dbase.sql
Enter password:
root@ironforge:~#

This produces INSERT statements in the format:

INSERT INTO `hexhex` VALUES (0xDEADBEEF000000000000000000000000);

.. which are import-safe.

Re-import it, and check it's right:

root@ironforge:~# mysql -p dbase < dbase.sql
Enter password:
root@ironforge:~# mysql -p dbase
mysql> use dbase;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from hexhex;
+------------------+
| id |
+------------------+
| ޭ▒ |
+------------------+
1 row in set (0.01 sec)
mysql> select hex(id) from hexhex;
+----------------------------------+
| hex(id) |
+----------------------------------+
| DEADBEEF000000000000000000000000 |
+----------------------------------+
1 row in set (0.00 sec)
mysql>