0

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?

1 Answer 1

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>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.