Description:
MySQL dump 10.7
4.1.2-alpha-standard
OS: Linux enterprise 2.6.4-54.5-smp #2 SMP Wed May 26 14:17:48 EDT 2004 i686 i686 i386 GNU/Linux; Suse 9.0
We switched from 4.1.1 to 4.1.2 yesterday and noticed that dump files that we create to port back to older versions of mysql (4.0.) are missing NULL in all insert statements (just a blank instead) when using the option --skip-opt or just --skip-extended-insert. Example: INSERT INTO `configuration` (`configuration_id`, `test1`, `test2`) VALUES (1,,);
How to repeat:
For example:
We create a database with
-------------------------------------------------------------------
create database bugtest;
use bugtest;
CREATE TABLE `configuration` (
`configuration_id` int(5) NOT NULL auto_increment,
`test1` varchar(32) default NULL,
`test2` varchar(255) default NULL,
PRIMARY KEY (`configuration_id`)
) ENGINE=MyISAM;
insert into configuration (configuration_id, test1, test2) values (1,NULL,NULL);
-------------------------------------------------------------------
When we try to dump this using mysqldump in the following way:
-------------------------------------------------------------------
mysqldump -uccf --compatible=mysql40 --skip-opt -c bugtest > bugtestdump.sql
-------------------------------------------------------------------
The output reads:
-------------------------------------------------------------------
-- MySQL dump 10.7
--
-- Host: localhost Database: bugtest
-- ------------------------------------------------------
-- Server version 4.1.2-alpha-standard
/*!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,MYSQL40" */;
--
-- Table structure for table `configuration`
--
CREATE TABLE `configuration` (
`configuration_id` int(5) NOT NULL auto_increment,
`test1` varchar(32) default NULL,
`test2` varchar(255) default NULL,
PRIMARY KEY (`configuration_id`)
);
--
-- Dumping data for table `configuration`
--
INSERT INTO `configuration` (`configuration_id`, `test1`, `test2`) VALUES (1,,);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
-------------------------------------------------------------------
The line INSERT INTO `configuration` (`configuration_id`, `test1`, `test2`) VALUES (1,,); produces a syntax error ([Noguska] ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1) when trying to reinsert the dump in a database because instead of NULL it produces nothing (just have commas in this case). Before switching to 4.1.1 the mysqldump command executed above would have produced the line INSERT INTO `configuration` (`configuration_id`, `test1`, `test2`) VALUES (1,NULL,NULL); instead.
We found that if we remove the option --skip-opt the NULLs are produced correctly. We also found that if we skip all options individually (--skip-add-drop-table --skip-add-locks --skip-create-options --skip-disable-keys --skip-extended-insert) except for --skip-extended-insert the nulls are produced correctly.
It appears the problem has something to do with the --skip-extended-insert option. The problem still occurs when the mysqldump command is simplified to:
mysqldump -uccf --skip-extended-insert bugtest > bugtestdump.sql
