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.
