Gentoo Websites Logo
Go to: Gentoo Home Documentation Forums Lists Bugs Planet Store Wiki Get Gentoo!
Bug 108502 - mysql restore problems, and solution for doc
Summary: mysql restore problems, and solution for doc
Status: RESOLVED FIXED
Alias: None
Product: [OLD] Docs-user
Classification: Unclassified
Component: Other (show other bugs)
Hardware: x86 Linux
: High enhancement (vote)
Assignee: Xavier Neys (RETIRED)
URL: http://www.gentoo.org/doc/en/mysql-up...
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2005-10-08 07:47 UTC by Kristian Karl
Modified: 2006-10-28 04:43 UTC (History)
4 users (show)

See Also:
Package list:
Runtime testing required: ---


Attachments
patch to version "1.13" "2006-10-28" (pippo.diff,1.48 KB, patch)
2006-10-28 04:32 UTC, Francesco R. (RETIRED)
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Kristian Karl 2005-10-08 07:47:49 UTC
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 Jan Kundrát (RETIRED) gentoo-dev 2006-01-18 10:52:43 UTC
mysql, any suggestions on this? (removing vivo as he's on the mysql-bugs alias)
Comment 2 Chris White (RETIRED) gentoo-dev 2006-01-18 11:07:54 UTC
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 Francesco R. (RETIRED) gentoo-dev 2006-01-19 03:35:08 UTC
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 Francesco R. (RETIRED) gentoo-dev 2006-06-24 09:12:23 UTC
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 Francesco R. (RETIRED) gentoo-dev 2006-10-28 04:32:33 UTC
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
Comment 6 Xavier Neys (RETIRED) gentoo-dev 2006-10-28 04:43:56 UTC
In CVS. Thanks for reporting, and thanks for the patch.