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.