MySQL Bugs: #93988: mysqldbcopy fails with different errors

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

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

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

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

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

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

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.

There is no sql_mode set

mysql> SELECT @@sql_mode;
+--------------+
| @@sql_mode |
+--------------+
|                        |
+--------------+
1 row in set (0.00 sec)

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

Thank you for the feedback. Are you using MySQL Server 5.6.23 for both source and target? Pls let me know.

Source 
mysql  Ver 14.14 Distrib 5.6.23

Target
mysql  Ver 14.14 Distrib 5.7.20

Thank you for the feedback.

test results

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

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.