Bug #10739 | mysqldump skips NULL values in --extended-insert=false mode | ||
---|---|---|---|
Submitted: | 19 May 2005 12:07 | Modified: | 19 May 2005 13:42 |
Reporter: | Abraham Guyt | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server: mysqldump Command-line Client | Severity: | S2 (Serious) |
Version: | 4.1.2 | OS: | Windows (windows 2003/xp) |
Assigned to: | CPU Architecture: | Any |
[19 May 2005 12:07]
Abraham Guyt
[19 May 2005 12:40]
MySQL Verification Team
I was unable to repeat: DROP TABLE IF EXISTS `person`; CREATE TABLE `person` ( `id` int(11) default NULL, `name` varchar(30) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `person` -- /*!40000 ALTER TABLE `person` DISABLE KEYS */; LOCK TABLES `person` WRITE; INSERT INTO `person` VALUES (1,'nana'); INSERT INTO `person` VALUES (2,NULL); INSERT INTO `person` VALUES (3,NULL); UNLOCK TABLES; /*!40000 ALTER TABLE `person` ENABLE KEYS */;
[19 May 2005 13:10]
Abraham Guyt
Hi, Apparantly it depends on the column type, this example works: CREATE TABLE kind ( id int(11) NOT NULL auto_increment, label varchar(40) default '', ref int(11) default NULL, PRIMARY KEY (id) ) TYPE=InnoDB CHARSET=latin1; Then insert some values: INSERT INTO kind (LABEL) VALUES ('type 1'),('type 2'); Then trying to dump this table with the 4.1.2 version of mysqldump with -extended-insert=false results in: INSERT INTO `kind` VALUES (1,'type 1',); INSERT INTO `kind` VALUES (2,'type 2',); while the 4.1.0 version of mysqldump does it right with the same option producing: INSERT INTO kind VALUES (1,'type 1',NULL); INSERT INTO kind VALUES (2,'type 2',NULL); Cheers, Abraham.
[19 May 2005 13:42]
MySQL Verification Team
With your test case still I can't repeat: DROP TABLE IF EXISTS `kind`; CREATE TABLE `kind` ( `id` int(11) NOT NULL auto_increment, `label` varchar(40) default '', `ref` int(11) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `kind` -- /*!40000 ALTER TABLE `kind` DISABLE KEYS */; LOCK TABLES `kind` WRITE; INSERT INTO `kind` VALUES (1,'type 1',NULL); INSERT INTO `kind` VALUES (2,'type 2',NULL); UNLOCK TABLES; /*!40000 ALTER TABLE `kind` ENABLE KEYS */;
[19 May 2005 15:01]
Abraham Guyt
Now that's strange, I'm running both versions of mysqldump against a 4.1.0 server, but the behaviour is the same against a 4.1.2 server. Just for the sake of completeness I include once again the _exact_ same mysqldump command I used, and both _exact_ dump outputs from both mysqldump clients. The last dump is the correct one of course. Hope this helps, cause I'm quite puzzled :-) Beware that both dumps were run against the same 4.1.0 server just seconds after one another. The dump command: mysqldump --extended-insert=false -u ecosys_user ecosys kind ***** dump for the 4.1.2 mysqldump (version 10.7) client: -- MySQL dump 10.7 -- -- Host: localhost Database: ecosys -- ------------------------------------------------------ -- Server version 4.1.0-alpha-max-nt-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT, CHARACTER_SET_CLIENT=utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE="NO_AUTO_VALUE_ON_ZERO" */; -- -- Table structure for table `kind` -- DROP TABLE IF EXISTS `kind`; CREATE TABLE `kind` ( `id` int(11) NOT NULL auto_increment, `label` varchar(40) default '', `ref` int(11) default NULL, PRIMARY KEY (`id`) ) TYPE=InnoDB CHARSET=latin1; -- -- Dumping data for table `kind` -- /*!40000 ALTER TABLE `kind` DISABLE KEYS */; LOCK TABLES `kind` WRITE; INSERT INTO `kind` VALUES (1,'type 1',); INSERT INTO `kind` VALUES (2,'type 2',); UNLOCK TABLES; /*!40000 ALTER TABLE `kind` ENABLE KEYS */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; ***** dump for the 4.1.0 mysqldump (version 10.2) client: -- MySQL dump 10.2 -- -- Host: localhost Database: ecosys --------------------------------------------------------- -- Server version 4.1.0-alpha-max-nt-log -- -- Table structure for table 'kind' -- DROP TABLE IF EXISTS kind; CREATE TABLE kind ( id int(11) NOT NULL auto_increment, label varchar(40) default '', ref int(11) default NULL, PRIMARY KEY (id) ) TYPE=InnoDB CHARSET=latin1; -- -- Dumping data for table 'kind' -- /*!40000 ALTER TABLE kind DISABLE KEYS */; LOCK TABLES kind WRITE; INSERT INTO kind VALUES (1,'type 1',NULL); INSERT INTO kind VALUES (2,'type 2',NULL); UNLOCK TABLES; /*!40000 ALTER TABLE kind ENABLE KEYS */;