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 attachment 100646 [details, diff] 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.