MySQL Bugs: #80706: mysqlpump does not handle re-import from upgrade

Bug #80706	mysqlpump does not handle re-import from upgrade
Submitted:	11 Mar 2016 15:03	Modified:	17 Aug 2016 17:59
Reporter:	Morgan Tocker	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqlpump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.7.11	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If I create a set of tables in MySQL 5.6 (or below) the default is to have innodb-strict-mode OFF and strict_trans_tables OFF.

If I then mysql_upgrade in place (as supported and recommended) it will work fine.  But if I use mysqlpump as my backup method I can now no longer restore.

How to repeat:
- Using wordpress as an example
- Dumped using:
mysqlpump > mysqld4.sql

- Manually edited mysqlpump file because of bug #80705.  Removed every table and insert under mysql schema.

morgo@Rbook:~$ msb 580 < mysqld4.sql
ERROR 1067 (42000) at line 29: Invalid default value for 'comment_date'

This is because:

CREATE TABLE `wordpress_tockerca`.`wp_comments` (
`comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`comment_post_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
`comment_author` tinytext NOT NULL,
`comment_author_email` varchar(100) NOT NULL DEFAULT '',
`comment_author_url` varchar(200) NOT NULL DEFAULT '',
`comment_author_IP` varchar(100) NOT NULL DEFAULT '',
`comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', <--- Has the zero date
`comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`comment_content` text NOT NULL,
`comment_karma` int(11) NOT NULL DEFAULT '0',
`comment_approved` varchar(20) NOT NULL DEFAULT '1',
`comment_agent` varchar(255) NOT NULL DEFAULT '',
`comment_type` varchar(20) NOT NULL DEFAULT '',
`comment_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
`user_id` bigint(20) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`comment_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=983 DEFAULT CHARSET=utf8
;

Suggested fix:
The priority should be to make backups restorable.  The error message is not even clear that this is influenced by a mode.

mysqlpump should potentially disable strict mode for its session, but offer an option (disabled by default) to be --strict.

Hello Morgan,

Thank you for the report.
Observed this issue with 5.7.11 build.

Thanks,
Umesh

Posted by developer:
 
Noted in 5.7.16, 8.0.1 changelogs.

A binary (in-place) upgrade from MySQL 5.6 to 5.7 followed by a data
export performed using mysqlpump resulted in an "Invalid default value
for date_column" error for attempts to reload the dump file.

Posted by developer:
 
Noted in 5.7.17 (not 5.7.16) changelog.