Bug #93566 mysqldump --compact omits NO_AUTO_VALUE_ON_ZERO option
Submitted: 11 Dec 2018 22:53 Modified: 12 Dec 2018 6:51
Reporter: Jinn Ko Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S2 (Serious)
Version:5.6.42, 5.7.24 OS:Debian (http://repo.mysql.com/apt/debian stretch/mysql-5.6)
Assigned to: CPU Architecture:x86
Tags: compact, mysqldump, NO_AUTO_VALUE_ON_ZERO

[11 Dec 2018 22:53] Jinn Ko
Description:
When setting the `--compact` option to mysqldump the comment setting NO_AUTO_VALUE_ON_ZERO, which affects functional behaviour, is ommited.  Nowhere in the documentation does it indicate the resulting dump will be functionally different.

Leaving out the NO_AUTO_VALUE_ON_ZERO results in the dumped data not being imported as expected. 

How to repeat:
-- Create the initial table and populate it

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE TABLE `test` (
  `test_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `test_string` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`test_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `test` (`test_id`, `test_string`) VALUES (0,'test0'),(1,'test1'),(2,'test2');

SELECT * FROM test;
+---------+-------------+
| test_id | test_string |
+---------+-------------+
|       0 | test0       |
|       1 | test1       |
|       2 | test2       |
+---------+-------------+

-- Dump the table

# mysqldump --compact --databases test

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `test_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `test_string` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`test_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test` VALUES (0,'test0'),(1,'test1'),(2,'test2');

-- Re-import the table

# mysql -e "DROP DATABASE test; CREATE DATABASE test;"
# mysqldump --compact --databases test | mysql test

SELECT * FROM test;
+---------+-------------+
| test_id | test_string |
+---------+-------------+
|       1 | test1       |
|       2 | test2       |
|       3 | test0       |
+---------+-------------+

Suggested fix:
`--compact` shouldn't omit functional behaviours, or it should be documented that it does this.
[12 Dec 2018 6:51] Umesh Shastry
Hello Jinn Ko,

Thank you for the report.

regards,
Umesh