Bug #81866 tables with primary key of 0 with auto_increment fail to copy.
Submitted: 15 Jun 2016 15:38 Modified: 28 Oct 2016 19:06
Reporter: Hans Bogert Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Utilities Severity:S3 (Non-critical)
Version:1.5.6, 1.6.3 OS:Any
Assigned to: CPU Architecture:Any

[15 Jun 2016 15:38] Hans Bogert
Description:
When having a table with an auto_increment, thus primary key which holds a row with value 0, copying fails.

You could argue that ideally 0 shouldn't have been a value in the database in the first place, but alas, it happens, and I think we should deal with it.

How to repeat:
Mysql> CREATE TABLE `csv_status_names` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

SET SESSION SQL_MODE='NO_AUTO_VALUE_ON_ZERO';

insert into csv_status_names (id, name) values (0, "bar"),(1, "foo");

shell> mysqldbcopy -v --drop-first --source=root@localhost --destination=root@localhost foo:foo2 # Assume csv_status_names was created in foo

Suggested fix:
mysqldump changes the SQL_MODE to 'NO_AUTO_VALUE_ON_ZERO' at the start of a dump file.

I have no direct solution for mysqldbcopy.
[15 Jun 2016 18:16] Hans Bogert
Forgot to add actual error output:

# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database foo renamed as foo2
# Dropping new object TABLE foo2.`csv_status_names`
# WARNING: Unable to drop `csv_status_names` from source database (object may not exist): DROP TABLE `foo2`.`csv_status_names`
# Copying TABLE foo.csv_status_names
CREATE TABLE `foo2`.`csv_status_names` LIKE `foo`.`csv_status_names`
# Copying data for TABLE foo.csv_status_names
INSERT INTO `foo2`.`csv_status_names` SELECT * FROM `foo`.`csv_status_names`
ERROR: Query failed. 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
[16 Jun 2016 9:06] Hans Bogert
added version
[16 Jun 2016 12:30] MySQL Verification Team
Hello Hans Bogert,

Thank you for the report and test case.

Thanks,
Umesh
[19 Oct 2016 18:12] Chuck Bell
Posted by developer:
 
pushed to release-1.6.5
[19 Oct 2016 18:31] Chuck Bell
Posted by developer:
 
Pushed to release-1.6.5
[28 Oct 2016 19:06] Christine Cole
Fixed as of the upcoming MySQL Utilities 1.6.5 release, and here's the
changelog entry:

The mysqldbcopy utility failed to copy tables with a primary key
set to zero and with AUTO_INCREMENT applied.

Thank you for the bug report.