Bug #93988 mysqldbcopy fails with different errors
Submitted: 20 Jan 2019 18:01 Modified: 25 Jan 2019 6:39
Reporter: Tene Thomas Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Utilities Severity:S2 (Serious)
Version:1.3.6, 1.6.5 OS:Oracle Linux
Assigned to: CPU Architecture:Any

[20 Jan 2019 18:01] Tene Thomas
Description:
mysqldbcopy --version
MySQL Utilities mysqldbcopy version 1.3.6 (part of MySQL Workbench Distribution 5.2.47)

mysql --version
mysql  Ver 14.14 Distrib 5.6.23, for Linux (x86_64) using  EditLine wrapper

When mysqldbcopy utility is used to copy database from one server to another, it is failing with multiple errors:

How to repeat:
mysqldbcopy --source root:root@xxx.xxx.com --destination root:root@xxxxx.xx.com --force test:test_master

The following errors are obtained on different conditions:

Problem inserting data. Error = Query failed. 1064 (42000): 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

Problem updating blob field. Error = Query failed. 1064 (42000): 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

I found couple of cases when it fails:

1. When there is single quotes in varchar or text fields
2. When there is blob field with not null constraint

Suggested fix:
Even after altering the above conditions, mysqldbcopy is failing with errors. Please suggest a solution to make this work
[21 Jan 2019 4:35] Umesh Shastry
Hello Tene Thomas,

Thank you for the report.
Imho this is duplicate of Bug #72951, please see Bug #72951
Per bug updates, this is already fixed in 1.6.1 release.

[9 Dec 2014 5:03] Philip Olson
Posted by developer:
 
Fixed as of the upcoming MySQL Utilities 1.6.1 release, and here's the changelog entry:

The "mysqldbcopy" utility was not able to copy table rows that contained
single quotes in text columns.

Thank you for the bug report.

thanks,
Umesh
[21 Jan 2019 11:27] Tene Thomas
I upgraded mysqldbcopy to 1.6.5 and it is better than before. But still it failed on a table with this error.

ERROR: Problem updating blob field. Error = Query failed. 1064 (42000): 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
[22 Jan 2019 4:58] Umesh Shastry
Thank you Tene for the feedback.
Could you please provide exact repeatable test case to confirm this issue at our end? Please provide logical dump of the table and subset of data which is triggering this issue.

regards,
Umesh
[22 Jan 2019 6:14] Tene Thomas
I am facing the issue when I copy DB from one server to another. But it works if I copy on the same server. 

Same Server
-----------
mysqldbcopy --drop-first --not-null-blobs --source root:root@denxxx.xxxx.com --destination root:root@denxxx.xxxx.com blob_issue:blob_issue_copy

WARNING: Using a password on the command line interface can be insecure.
WARNING: The following tables have blob fields set to NOT NULL.
    blob_issue.blob_table_new Column password_sha
    blob_issue.blob_table_new Column password

# Source on denxxx.xxxx.com: ... connected.
# Destination on denxxx.xxxx.com: ... connected.
# Copying database blob_issue renamed as blob_issue_copy
# Copying TABLE blob_issue.blob_table_new
# Copying data for TABLE blob_issue.blob_table_new
#...done.

Different Server
----------------
mysqldbcopy --drop-first --not-null-blobs --source root:root@denxxx.xxxx.com --destination root:root@slcxxx.xxxx.com blob_issue:blob_issue_copy

WARNING: Using a password on the command line interface can be insecure.
WARNING: The following tables have blob fields set to NOT NULL.
    blob_issue.blob_table_new Column password_sha
    blob_issue.blob_table_new Column password

# Source on denxxx.xxxx.com: ... connected.
# Destination on slcxxx.xxxx.com: ... connected.
# Copying database blob_issue renamed as blob_issue_copy
# Copying TABLE blob_issue.blob_table_new
# Copying data for TABLE blob_issue.blob_table_new
ERROR: Problem inserting data. Error = Query failed. 1048 (23000): Column 'password' cannot be null
[23 Jan 2019 10:50] Umesh Shastry
Thank you Tene for the details.
This doesn't look like mysqldbcopy's issue here but rather server's sql_mode which is preventing. It seems to me that you are trying to copy table/data to a server which has strict sql_mode enabled. Quoting from our manual - For transactional tables, an error occurs for invalid or missing values in a data-change statement when either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled and hence you are seeing the error - ERROR: Problem inserting data. Error = Query failed. 1048 (23000): Column 'password' cannot be null - https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

Imho original issue is fixed post Bug #72951 fix.
[23 Jan 2019 13:45] Tene Thomas
There is no sql_mode set

mysql> SELECT @@sql_mode;
+--------------+
| @@sql_mode |
+--------------+
|                        |
+--------------+
1 row in set (0.00 sec)
[24 Jan 2019 14:01] Tene Thomas
I think the issue is still present in version 1.6.5 as the comments indicate in https://bugs.mysql.com/bug.php?id=72951
[25 Jan 2019 5:12] Umesh Shastry
Thank you for the feedback. Are you using MySQL Server 5.6.23 for both source and target? Pls let me know.
[25 Jan 2019 5:16] Tene Thomas
Source 
mysql  Ver 14.14 Distrib 5.6.23

Target
mysql  Ver 14.14 Distrib 5.7.20
[25 Jan 2019 6:39] Umesh Shastry
Thank you for the feedback.
[25 Jan 2019 6:40] Umesh Shastry
test results

Attachment: 93988.results (application/octet-stream, text), 4.69 KiB.

[25 Jan 2019 6:41] Umesh Shastry
Per https://downloads.mysql.com/archives/utilities/

MySQL Utilities is now covered under Oracle Lifetime Sustaining Support 
Per Oracle's Lifetime Support policy, as of May 30, 2018, MySQL Utilities is covered under Oracle Sustaining Support. Some features of Utilities are on the roadmap for Shell, users are encouraged to migrate to MySQL Shell.