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.
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.