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:
None 
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
Description:
When dumping db contents with mysqldump with the --extended-insert option set to false to produce single line inserts for each record, the program skips any null values when writing the insert statement.

For example, a record with a null value for the name field would result in:

INSERT INTO PERSON (ID,NAME) VALUES (1,);

instead of the correct

INSERT INTO PERSON (ID,NAME) VALUES (1,NULL);

Just switching the extended-insert option op fixes the problem, but this is weird behaviour, as this option nicely produces clean single line inserts that are readably by human beings.

How to repeat:
Just dump a table with the --extended-insert option set to false and any null values in it. Watch the null values being skipped.
[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 */;