Bugzilla Bug 108502

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:

mysql, any suggestions on this? (removing vivo as he's on the mysql-bugs alias)

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.

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

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.

Created an attachment (id=100646) [edit]
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

In CVS. Thanks for reporting, and thanks for the patch.