MySQL Bugs: #29788: MySQL 5.0.45 restore of dump fails

Bug #29788	MySQL 5.0.45 restore of dump fails
Submitted:	13 Jul 2007 14:35	Modified:	27 Jul 2007 5:25
Reporter:	Joshua Butcher	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S2 (Serious)
Version:	5.0.45, 5.0 BK, 6.0.1-Falcon	OS:	Any
Assigned to:	Gleb Shchepa	CPU Architecture:	Any
Tags:	MySQL MySQLDUMP restore DUMP FAILS

Description:
I was testing the migration path of a databsae from MySQL 5.0.41 community to MySQL 5.0.45 community, The dump is being pulled from a box running 5.0.41, and the restore is being run on a box that has 5.0.45 on it.

I am running CentOS 4.4 x86-64 with all the latest patches.  4G of RAM in the test box and dual core AMD X2.

I get duplicate key errors in the following piece of the dump:

DROP TABLE IF EXISTS `asset_ratings`;
CREATE TABLE `asset_ratings` (
  `asset_rating_id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  `description` varchar(255) default NULL,
  `minium_age` int(11) default NULL,
  `display_order` int(11) NOT NULL default '0',
  `asset_type_id` int(11) default NULL,
  PRIMARY KEY  (`asset_rating_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `asset_ratings`
--

LOCK TABLES `asset_ratings` WRITE;
/*!40000 ALTER TABLE `asset_ratings` DISABLE KEYS */;
INSERT INTO `asset_ratings` VALUES (0,'Unknown','Unknown',0,0,0),(1,'General','General (Everyone)',0,1,1),(2,'Teen','Teen (13 and up)',13,2,1),(3,'Mature','Mature (18 and up)',18,3,1),(4,'General','General (Everyone)',0,1,2),(5,'Teen','Teen (13 and up)',13,2,2),(6,'Mature','Mature (18 and up)',18,3,2),(7,'General','General (Everyone)',0,1,3),(8,'Teen','Teen (13 and up)',13,2,3),(9,'Mature','Mature (18 and up)',18,3,3);
/*!40000 ALTER TABLE `asset_ratings` ENABLE KEYS */;
UNLOCK TABLES;

This is a huge problem for us, because sometimes we insert an id 0 for unknown or unspecified, and the dump is being output correctly, but not being importted correctly by 5.0.45.  There are several bugs fixed in 5.0.45, and we really need to use it, but if I can't get a dump/restore to work, I can't use it at all...

How to repeat:
Execute the code given above.

Suggested fix:
Allow inserts into the auto-inc field.

How exactly do you call mysqldump? And how does the header of the dump file look like?

It *should* have set sql_mode to include NO_AUTO_VALUE_ON_ZERO which prevents
a new auto_increment value being created on inserting a zero (only a NULL value will be replaced by a new auto_increment value then).

This is how it looks in my mysqldump files:

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

Without NO_AUTO_VALUE_ON_ZERO the 0 in the firt data row will be replaced by auto_increment value 1, and then the next row will cause the duplicate error due to it setting the auto_increment field to 1 which is already taken now.

I dump with  mysqldump -u myun -p -h 1.1.1.1 -C --routines --databases db1 db2 db3 db4 db5 > dev_snapshot.sql

-- MySQL dump 10.11
--
-- Host: 10.11.11.6    Database: kaneva
-- ------------------------------------------------------
-- Server version       5.0.41-community-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!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' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `db1`
--

Thank you for the feedback.

Output of the mysqldump command which you provided contains "SQL_MODE='NO_AUTO_VALUE_ON_ZERO'" which should prevent generating of next sequence value if 0 for auto_increment column is specified.

Do you load full dump generated by mysqldump? If yes, please provide whole file which generated mysqldump to we can find what is wrong.

Just close the ticket then.  My company would not let me give out our entire schema to you.  That would be corporate suicide.  Another reason to switch to Microsoft SQL Server I guess...

If I get them to agree to send the entire dump file, it is 170Meg in size, and you only allow 500k uploads.  Where would I send the file?

You can upload the file at: ftp://ftp.mysql.com/pub/mysql/upload
using a name which identifies this bug report i.e: bug29788.zip.
Only MySQL developers will have access to that file. Thanks in
advance.

test case

Attachment: bug29788.test (application/octet-stream, text), 468 bytes.

Thank you for the feedback.

No dump needed: I found cause of not expected behaviour.

Verified using attached test case.

Related with bug #28223.

Bug also exists in 5.0 development tree and Falcon tree, but does not exists in 5.1

I am not sure what you found in the code, but my dump of production (.41) actually restores correctly on .45, which I found "interesting".  The dev copy still does not work :)

Joshua

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31298

ChangeSet@1.2475, 2007-07-21 04:50:11+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #29788.
  After dumping triggers mysqldump copied 
  the value of the OLD_SQL_MODE variable to the SQL_MODE
  variable. If the --compact option of the mysqldump was
  not set the OLD_SQL_MODE variable had the value
  of the uninitialized SQL_MODE variable. So
  usually the NO_AUTO_VALUE_ON_ZERO option of the
  SQL_MODE variable was discarded.
  
  This fix is for non-"--compact" mode of the mysqldump,
  because mysqldump --compact never set SQL_MODE to the
  value of NO_AUTO_VALUE_ON_ZERO.
  
  The dump_triggers_for_table function has been modified
  to restore previous value of the SQL_MODE variable after
  dumping triggers using the SAVE_SQL_MODE temporary
  variable.

Pushed into 5.1.21-beta

Pushed into 5.0.48

Noted in 5.0.48, 5.1.21 changelogs.

mysqldump produced output that incorrectly discarded the
NO_AUTO_VALUE_ON_ZERO value of the SQL_MODE variable after dumping
triggers.