Bug #81553 mysqldbcopy Issue with tinytext
Submitted: 23 May 2016 19:21 Modified: 15 Dec 2016 20:07
Reporter: Julien JOLY Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Utilities Severity:S1 (Critical)
Version:1.5.6 OS:CentOS (7.2 x86_64)
Assigned to: CPU Architecture:Any

[23 May 2016 19:21] Julien JOLY
Description:
Base on Bugs N°71890, I have block by a fatal error when I use "mysqldbcopy" from a remote copy a database to a local server.

[root@localhost:/]#mysqldbcopy --skip-gtid --drop-first --locking=snapshot --source=root@remotehost --destination=root@localhost foo:foo
WARNING: Using a password on the command line interface can be insecure.
# Source on remoteserver: ... connected.
# Destination on localserver: ... connected.
# WARNING: The server supports GTIDs but you have elected to skip exexcuting the GTID_EXECUTED statement. Please refer to the MySQL online reference manual for more information about how to handle GTID enabled servers with backup and restore operations.
# Copying database foo renamed as foo
# Copying TABLE foo.bar
# Copying data for TABLE foo.bar
ERROR: Problem inserting data. Error = Query failed. 1048 (23000): Column 'value' cannot be null

How to repeat:
To describe this issue, I have prepare two test. First is fonctionnal, and the second not.
First I used the following command to remote copy a database on my server:

mysqldbcopy --skip-gtid --drop-first --locking=snapshot --source=root@remotehost --destination=root@localhost foo:foo

My database foo is, on the first functionnal test:

        DROP DATABASE IF EXISTS `foo`;
        CREATE DATABASE `foo`;
        USE `foo`;
        DROP TABLE IF EXISTS `bar`;
        CREATE TABLE `bar` (
           `id` INT NOT NULL AUTO_INCREMENT,
           `value` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
           PRIMARY KEY (`value`),
           UNIQUE KEY `id` (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

        LOCK TABLES `bar` WRITE;
        INSERT INTO `bar` VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
        UNLOCK TABLES;

The copy give:

        [root@localhost:/]#mysqldbcopy --skip-gtid --drop-first --locking=snapshot --source=root@remotehost --destination=root@localhost foo:foo
        WARNING: Using a password on the command line interface can be insecure.
        # Source on cloudsso-db-master: ... connected.
        # Destination on cloudsso-db-rescue: ... connected.
        # WARNING: The server supports GTIDs but you have elected to skip exexcuting the GTID_EXECUTED statement. Please refer to the MySQL online reference manual for more information about how to handle GTID enabled servers with backup and restore operations.
        # Copying database foo renamed as foo
        # Copying TABLE foo.bar
        # Copying data for TABLE foo.bar
        #...done.

On the over hand, the "bug":

        DROP DATABASE IF EXISTS `foo`;
        CREATE DATABASE `foo`;
        USE `foo`;
        DROP TABLE IF EXISTS `bar`;
        CREATE TABLE `bar` (
           `id` INT NOT NULL AUTO_INCREMENT,
           `value` TINYTEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
           PRIMARY KEY (`id`),
           UNIQUE KEY (`value`(255))
        ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

        LOCK TABLES `bar` WRITE;
        INSERT INTO `bar` VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
        UNLOCK TABLES;

The copy give:

        [root@localhost:/]#mysqldbcopy --skip-gtid --drop-first --locking=snapshot --source=root@remotehost --destination=root@localhost foo:foo
        WARNING: Using a password on the command line interface can be insecure.
        # Source on cloudsso-db-master: ... connected.
        # Destination on cloudsso-db-rescue: ... connected.
        # WARNING: The server supports GTIDs but you have elected to skip exexcuting the GTID_EXECUTED statement. Please refer to the MySQL online reference manual for more information about how to handle GTID enabled servers with backup and restore operations.
        # Copying database foo renamed as foo
        # Copying TABLE foo.bar
        # Copying data for TABLE foo.bar
        ERROR: Problem inserting data. Error = Query failed. 1048 (23000): Column 'value' cannot be null

REMARK, if you suppress the primary key and his associate field, issue does not appears.
[23 May 2016 20:31] Julien JOLY
[root@localhost:/]# mysql --version
        mysql  Ver 14.14 Distrib 5.7.12, for Linux (x86_64) using  EditLine wrapper

[root@localhost:/]# mysqldbcopy --version
        MySQL Utilities mysqldbcopy version 1.5.6 
        License type: GPLv2
[24 May 2016 7:55] MySQL Verification Team
Hello Julien JOLY,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh
[24 May 2016 7:59] MySQL Verification Team
Bug #73333 marked duplicate of this
[19 Oct 2016 18:14] Chuck Bell
Posted by developer:
 
pushed to release-1.6.5
[13 Dec 2016 17:47] Chuck Bell
Posted by developer:
 
Pushed to release-1.6.5
[15 Dec 2016 20:07] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL Utilities 1.6.5 release, and here's the changelog entry:

Tables with BLOB data in fields marked NOT NULL restricted the operation
of the mysqldbcopy utility. This fix enables the utility to copy these
tables by temporarily altering the columns on the destination to remove
the NOT NULL option and then to restore it after the copy.

Thank you for the bug report.