MySQL Bugs: #6492: NO_AUTO_VALUE_ON_ZERO causes restore to fail

Bug #6492	NO_AUTO_VALUE_ON_ZERO causes restore to fail
Submitted:	8 Nov 2004 2:27	Modified:	27 Aug 2006 7:37
Reporter:	[ name withheld ]	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Administrator	Severity:	S1 (Critical)
Version:	1.0.14	OS:	Windows (windows xp)
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	Backup

Description:
it appears that NO_AUTO_VALUE_ON_ZERO is added to backup files by default. this is causing my restore to crash however. specifically, if i remove the offending lines, the first at the beginning before create table and the second after the inserts for the data, i get no errors. the error causes my restores to fail even when i check the ignore errors. 

the lines are:

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=NO_AUTO_VALUE_ON_ZERO */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

i don't ever have zeros in my primary keys, but as all of my tables have auto-increment integers for primary keys, i can see the need for it. it just seems to be causing error for me anyway. i checked the lists etc. but i didn't see anything that spoke to this directly.

How to repeat:
simply backup a table (myisam or innodb) and then restore it to a new schema. 

Suggested fix:
i have had success just deleting the lines, but i'm not sure what other consequences this may have.

Hi,

I can't repeat it. I was able to restore database from backup without any problem.
Could you create small test case to reproduce this problem?
What version of MySQL server do you use?

this is mysql 4.1.7-nt with mysql client version 5.0. the following is a simple backup of the test database to which i added one table with one key. it generated the same 1064 error with sql syntax problem i encountered with my production database.

note that i drop the test schema entirely before running the backup simulating a catastrophic crash in which i need to restore the db to a newly installed server.

...gg

SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES 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 */;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test`;
USE `test`;
CREATE TABLE "mytable" (
  "mykey" int(10) unsigned NOT NULL auto_increment,
  "mytext" varchar(45) NOT NULL default '',
  PRIMARY KEY  ("mykey")
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `mytable` (`mykey`,`mytext`) VALUES (1,'test'),(2,'test123'),(3,'this is a little clunky');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

I was able to repeat with a similar table.

The statements from above cannot execute successfully because you have removed the ANSI_QUOTE mode from SQL_MODE. Either add this or change the "" quotes to back ticks. This script works fine for me:

SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES 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 */;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test`;
USE `test`;

drop table if exists `mytable`;

CREATE TABLE `mytable` (
  `mykey` int(10) unsigned NOT NULL auto_increment,
  `mytext` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`mykey`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `mytable` (`mykey`,`mytext`) VALUES
(1,'test'),(2,'test123'),(3,'this is a little clunky');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

If this is not related to your problem then please add a script file to this bug report, which actually shows the problem.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".