Bug 108502 - mysql restore problems, and solution for doc
Bug#: 108502 Product:  Docs-user Version: n/a Platform: x86
OS/Version: Linux Status: RESOLVED Severity: enhancement Priority: P2
Resolution: FIXED Assigned To: neysx@gentoo.org Reported By: kristian.karl@prolore.se
Component: Other
URL:  http://www.gentoo.org/doc/en/mysql-upgrading.xml
Summary: mysql restore problems, and solution for doc
Keywords:  
Status Whiteboard: 
Opened: 2005-10-08 07:47 0000
Description:   Opened: 2005-10-08 07:47 0000 
In the document http://www.gentoo.org/doc/en/mysql-upgrading.xml, section "Code
Listing 3.5: Importing the SQL backup", a comment should be added.

If the ERROR 1062 - Duplicate entry '1' for key 1, is encountered, it can be
solved by adding the row below into /etc/mysql/my.cnf (in the [mysqld] section):
sql-mod = NO_AUTO_VALUE_ON_ZERO

(Also see:
http://mysqldump.azundris.com/index.php?url=archives/7-Test-your-backups.html&serendipity%5Bcview%5D=linear)

Reproducible: Always
Steps to Reproduce:

------- Comment #1 From Jan Kundrát 2006-01-18 10:52:43 0000 ------- 
mysql, any suggestions on this? (removing vivo as he's on the mysql-bugs alias)

------- Comment #2 From Chris White (RETIRED) 2006-01-18 11:07:54 0000 ------- 
Yah, this does need to be noted.  MySQL is taking the 0 as a need to hit the
AUTO_INCREMENT property of the value and increase it.  This creates somewhat of
an annoying predicement as described in the mentioned link.

------- Comment #3 From Francesco R. (RETIRED) 2006-01-19 03:35:08 0000 ------- 
Sorry I've seen this bug only today ...

an alternative is to prepend the .sql dump with the string 

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

and add this to the end:

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

this is exactly what newer mysqldump do when it meet an autoincrement field
with value 0 in the dump.

Basically it can be read as:
if importing mysql version is greater than 4.1.1 then 
  save in OLD_SQL_MODE the current SQL_MODE, 
  then set SQL_MODE to NO_AUTO_VALUE_ON_ZERO but only on NULL
fi

...

if importing mysql version is greater than 4.1.1 then 
  restore the SQL_MODE to the on saved in OLD_SQL_MODE
fi

also a note should be added that it's not good practice to use values < 1 in
autoincrement fields, and < 0 are _EVIL_

thanks

------- Comment #4 From Francesco R. (RETIRED) 2006-06-24 09:12:23 0000 ------- 
bump,
An additional note, at the end of the section:
"4. Recover the old installation of MySQL 4.0" 

a note should be added that you need reemerge ALL the applications that have
been merged in the meantime.

------- Comment #5 From Francesco R. (RETIRED) 2006-10-28 04:32:33 0000 ------- 
Created an attachment (id=100646) [details]
patch to version "1.13" "2006-10-28"

Updated the doc to take kare of the problem prior to do the dump.
There is also a typo fix suggested by Evert Meulie, thanks

------- Comment #6 From Xavier Neys 2006-10-28 04:43:56 0000 ------- 
In CVS. Thanks for reporting, and thanks for the patch.