Bug #69541 mysqldbcopy failed to copy table
Submitted: 21 Jun 2013 14:44 Modified: 24 Jul 2013 6:05
Reporter: Wendy Tao Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Utilities Severity:S2 (Serious)
Version: mysqldbcopy version 1.2.0 - MySQL Workb OS:Linux (Linux 2.6.32-358.6.2.el6.x86_64 #1 SMP)
Assigned to: CPU Architecture:Any
Tags: mysqldbcopy of mysql workbench failed to copy table

[21 Jun 2013 14:44] Wendy Tao
Description:
When I tried to copy a database from linux server k13 to linux server k14, I received the following error:

ERROR: Problem inserting data. Error = Query failed. 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'hybrid, A,B, NULL),  ('101D', '1', polydeoxyribonucleotide, A,B, NULL),  ('101M'' at line 1

FYI, I don't have any problem when loading the original data file directly to a table on server k13 or server k14. See attached data file. 

How to repeat:
1. create the table

create table entity_poly
(
 id        		char(5)    not null,
 entity_id 		char(10)   not null,
 entity_type 		varchar(80)   not null,
 identical_chain_id  	varchar(5000)   not null, 
 sequence  	     	text
);

create unique index Ientity_poly on entity_poly
(
  id,
  entity_id
);

2. Load data file to table entity_poly:
load data infile "/entity-poly-data.tdd"     IGNORE into table entity_poly FIELDS TERMINATED BY '\t'  LINES TERMINATED BY '\n' IGNORE 1 LINES;

3. Use mysqldbcopy to copy the table to a different server
[21 Jun 2013 16:19] MySQL Verification Team
Hello Wendy,

Thank you for the report.
I can not repeat described behavior on 1.3.1/1.2.1.. Could you please try on latest version? 

http://dev.mysql.com/doc/relnotes/mysql-utilities/en/wb-utils-news-1-2-1.html

// Tried on localhost ( 2 diff DBs ) and remote host

// Schema creation and data loading

[ushastry@cluster-repo mysql-5.6.12]$ bin/mysql -u root -p --port=3306  --socket=/tmp/5612_69236/mysql.sock test

mysql> show create table entity_poly\G
*************************** 1. row ***************************
       Table: entity_poly
Create Table: CREATE TABLE `entity_poly` (
  `id` char(5) NOT NULL,
  `entity_id` char(10) NOT NULL,
  `entity_type` varchar(80) NOT NULL,
  `identical_chain_id` varchar(5000) NOT NULL,
  `sequence` text,
  UNIQUE KEY `Ientity_poly` (`id`,`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>
mysql>
mysql> load data infile "/tmp/entity-poly-data.tdd"     IGNORE into table entity_poly FIELDS TERMINATED BY '\t'  LINES TERMINATED BY '\n' IGNORE 1 LINES;
Query OK, 191 rows affected (0.01 sec)
Records: 191  Deleted: 0  Skipped: 0  Warnings: 0

mysql> create database test1;
Query OK, 1 row affected (0.01 sec)

[root@cluster-repo scripts]# ./mysqldbcopy.py --version
MySQL Utilities mysqldbcopy.py version 1.3.1 (part of MySQL Workbench Distribution 5.2.47)
Copyright (c) 2010, 2013 Oracle and/or its affiliates. All rights reserved.
This is a release of dual licensed MySQL Utilities. For the avoidance of
doubt, this particular copy of the software is released
under the version 2 of the GNU General Public License.
MySQL Utilities is brought to you by Oracle.

[root@cluster-repo scripts]# ./mysqldbcopy.py --source=root@locahost:3306:/tmp/5612_69236/mysql.sock  --destination=root@locahost:3306:/tmp/5612_69236/mysql.sock test:test1
# Source on locahost: ... connected.
# Destination on locahost: ... connected.
# Copying database test renamed as test1
ERROR: destination database exists. Use --force to overwrite existing database.
[root@cluster-repo scripts]# ./mysqldbcopy.py --force --source=root@locahost:3306:/tmp/5612_69236/mysql.sock  --destination=root@locahost:3306:/tmp/5612_69236/mysql.sock test:test1
# Source on locahost: ... connected.
# Destination on locahost: ... connected.
# Copying database test renamed as test1
# Copying TABLE test.bar
# Copying TABLE test.entity_poly
# Copying TABLE test.foo
# Copying GRANTS from test
# Copying data for TABLE test.bar
# Copying data for TABLE test.entity_poly
# Copying data for TABLE test.foo
#...done.
[root@cluster-repo scripts]#
[21 Jun 2013 17:22] Wendy Tao
Thanks for your feedback. I have to ask our system admin to upgrade to the latest version of 1.3.1 and test again. If you know a quick way to upgrade, let me know.

Thanks,
[24 Jun 2013 6:05] MySQL Verification Team
You can just download and untar somewhere(no need to overwrite etc) and use - You can download from  http://downloads.mysql.com/
[25 Jul 2013 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".