Bug #88772 mysqldbcopy copying constraint fails with hyphenated database name

• Submitted: 6 Dec 2017 4:59
• Modified: 6 Dec 2017 6:33
• Reporter: William Mowrey
• Status: Verified
• Category: MySQL Utilities
• Severity: S3 (Non-critical)
• Version: 1.6.5
• OS: Any
• CPU Architecture: Any

[6 Dec 2017 4:59] William Mowrey

Description:
I receive a syntax error when attempting to copy a database with foreign key constraints when the database name includes hyphens.

COMMAND:
mysqldbcopy --source=client:3306 --destination=client:3306 '`gseq`':'`gseq-db-10`'

RESPONSE:
# Source on gseq-mysql-encrypted.cf1wfsiunaox.us-east-2.rds.amazonaws.com: ... connected.
# Destination on gseq-mysql-encrypted.cf1wfsiunaox.us-east-2.rds.amazonaws.com: ... connected.
# Copying database gseq renamed as gseq-db-10
# Copying TABLE gseq.Chromosomes
# Copying TABLE gseq.Machines
# Copying TABLE gseq.Runs
# Copying TABLE gseq.Samples
# Copying TABLE gseq.Segments
# Copying TABLE gseq.Targets
# Copying TABLE gseq.Variants
# Copying GRANTS from gseq
ERROR: Unable to execute constraint query 
ALTER TABLE gseq-db-10.Variants add CONSTRAINT `Variants_ibfk_1`
FOREIGN KEY (`full_id`)
REFERENCES `gseq-db-10`.`Samples`
(`full_id`)
ON UPDATE CASCADE
ON DELETE CASCADE
. 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 '-db-10.Variants add CONSTRAINT `Variants_ibfk_1`
FOREIGN KEY (`full_id`)
REFEREN' at line 1

How to repeat:
Attempt to copy any database with a foreign key constraint to a new database with a hyphenated name.

Suggested fix:
Placing the database names within backticks in the automatically generated constraint copy command would likely resolve this issue.

[6 Dec 2017 6:33] Umesh Shastry

Hello William Mowrey,

Thank you for the report.

Thanks,
Umesh

[6 Dec 2017 6:34] Umesh Shastry

--

CREATE DATABASE gseq;
use gseq;

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

mysqldbcopy --source=[e-mail]:3308 --destination=[e-mail]:3308 '`gseq`':'`gseq-db-10`'

Launching console ...

Welcome to the MySQL Utilities Client (mysqluc) version 1.6.5
Copyright (c) 2010, 2017 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.

Type 'help' for a list of commands or press TAB twice for list of utilities.
mysqluc> mysqldbcopy --source=[e-mail]:3308 --destination=[e-mail]:3308 '`gseq`':'`gseq-db-10`'
WARNING: Using a password on the command line interface can be insecure.
# Source on hod03.no.oracle.com: ... connected.
# Destination on hod03.no.oracle.com: ... connected.
# Copying database gseq renamed as gseq-db-10
# Copying TABLE gseq.child
# Copying TABLE gseq.parent
ERROR: Unable to execute constraint query
ALTER TABLE gseq-db-10.child add CONSTRAINT `child_ibfk_1`
FOREIGN KEY (`parent_id`)
REFERENCES `gseq-db-10`.`parent`
(`id`)
ON UPDATE RESTRICT
ON DELETE CASCADE
. 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 righ
t syntax to use near '-db-10.child add CONSTRAINT `child_ibfk_1`
FOREIGN KEY (`parent_id`)
REFERENCES ' at line 1

Execution of utility: 'mysqldbcopy --source=[e-mail]:3308 --destination=[e-mail]:3308 `gseq`:`gseq-db-10`' end
ed with return code '1' but no error message was streamed to the standard error, please review the output from its execution.

mysqluc>