Hi
I am migrating a mailserver from CentOS 4 to 5 and i am migrating the db's over
from mysql-max-4.1.13 to mysql-server-5.0.45-7.el5
during the mysql import it fails with this...
ERROR 1062 (23000) at line 129: Duplicate entry '3-r��?�' for key 1
anyone got any clues to this one as so far i have drawn a blank
thanks
Tom Brown wrote:
ERROR 1062 (23000) at line 129: Duplicate entry '3-r��?�' for key 1
anyone got any clues to this one as so far i have drawn a blank
you are inserting multiple duplicate values somewhere where its not allowed. without looking at your schema it would be hard to work out what the issue is.
in other news, you dont need to dump + reload when you move from mysql-4 to mysql-5, just service mysqld stop; yum update mysql*; /usr/bin/mysql_upgrade ; service mysqld start
you are inserting multiple duplicate values somewhere where its not allowed. without looking at your schema it would be hard to work out what the issue is.
its a bayes db for spamassassin
in other news, you dont need to dump + reload when you move from mysql-4 to mysql-5, just service mysqld stop; yum update mysql*; /usr/bin/mysql_upgrade ; service mysqld start
i am changing the actual box so i have to dump and then import as i am also moving from centos4 to 5 -
schema is below.... thanks
-- MySQL dump 10.9 -- -- Host: localhost Database: spamassassin -- ------------------------------------------------------ -- Server version 4.1.13-max
/*!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 */; /*!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 */;
-- -- Table structure for table `bayes_expire` --
DROP TABLE IF EXISTS `bayes_expire`; CREATE TABLE `bayes_expire` ( `id` int(11) NOT NULL default '0', `runtime` int(11) NOT NULL default '0', KEY `bayes_expire_idx1` (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- -- Table structure for table `bayes_global_vars` --
DROP TABLE IF EXISTS `bayes_global_vars`; CREATE TABLE `bayes_global_vars` ( `variable` varchar(30) NOT NULL default '', `value` varchar(200) NOT NULL default '', PRIMARY KEY (`variable`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- -- Table structure for table `bayes_seen` --
DROP TABLE IF EXISTS `bayes_seen`; CREATE TABLE `bayes_seen` ( `id` int(11) NOT NULL default '0', `msgid` varchar(200) character set latin1 collate latin1_bin NOT NULL default '', `flag` char(1) NOT NULL default '', PRIMARY KEY (`id`,`msgid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- -- Table structure for table `bayes_token` --
DROP TABLE IF EXISTS `bayes_token`; CREATE TABLE `bayes_token` ( `id` int(11) NOT NULL default '0', `token` char(5) NOT NULL default '', `spam_count` int(11) NOT NULL default '0', `ham_count` int(11) NOT NULL default '0', `atime` int(11) NOT NULL default '0', PRIMARY KEY (`id`,`token`), KEY `bayes_token_idx1` (`token`), KEY `bayes_token_idx2` (`id`,`atime`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- -- Table structure for table `bayes_vars` --
DROP TABLE IF EXISTS `bayes_vars`; CREATE TABLE `bayes_vars` ( `id` int(11) NOT NULL auto_increment, `username` varchar(200) NOT NULL default '', `spam_count` int(11) NOT NULL default '0', `ham_count` int(11) NOT NULL default '0', `token_count` int(11) NOT NULL default '0', `last_expire` int(11) NOT NULL default '0', `last_atime_delta` int(11) NOT NULL default '0', `last_expire_reduce` int(11) NOT NULL default '0', `oldest_token_age` int(11) NOT NULL default '2147483647', `newest_token_age` int(11) NOT NULL default '0', PRIMARY KEY (`id`), UNIQUE KEY `bayes_vars_idx1` (`username`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
Tom Brown wrote:
in other news, you dont need to dump + reload when you move from mysql-4 to mysql-5, just service mysqld stop; yum update mysql*; /usr/bin/mysql_upgrade ; service mysqld start
i am changing the actual box so i have to dump and then import as i am also moving from centos4 to 5 -
rsync or tar the /var/lib/mysql dir up and move that. I am guessing in this case that some delimiter is breaking or the dump you did wasent clean.
Alternatively if you did use complete-inserts in your dump, you can run the load with : mysql -f db_name < blah.sql ; then look at what lines the load breaks on and make sure the data looks sane in those lines.
On Thu, Oct 30, 2008 at 9:52 AM, Karanbir Singh mail-lists@karan.orgwrote:
Tom Brown wrote:
in other news, you dont need to dump + reload when you move from mysql-4
to mysql-5, just service mysqld stop; yum update mysql*; /usr/bin/mysql_upgrade ; service mysqld start
i am changing the actual box so i have to dump and then import as i am also moving from centos4 to 5 -
rsync or tar the /var/lib/mysql dir up and move that. I am guessing in this case that some delimiter is breaking or the dump you did wasent clean.
But make sure mysqld is not running on either box when doing it this way. Also, if you have any innodb tables you must have the exact same innodb settings in your /etc/my.cnf
On Thu, Oct 30, 2008 at 6:22 AM, Karanbir Singh mail-lists@karan.org wrote:
in other news, you dont need to dump + reload when you move from mysql-4 to mysql-5, just service mysqld stop; yum update mysql*; /usr/bin/mysql_upgrade ; service mysqld start
Theoretically true, but if you ask MySQL.com support they'll tell you that a dump and restore is advisable. It all depends on which features you were using. As just one example, if you were relying on some of the previous auto-update semantics of timestamp columns, you'll need to alter all the corresponding table definitions manually; the upgrade process won't do it.
Hi,
On Thu, Oct 30, 2008 at 09:14, Tom Brown tom@ng23.net wrote:
I am migrating a mailserver from CentOS 4 to 5 and i am migrating the db's over from mysql-max-4.1.13 to mysql-server-5.0.45-7.el5 during the mysql import it fails with this... ERROR 1062 (23000) at line 129: Duplicate entry '3-r��?�' for key 1
The characters look binary, so this might be a problem of conversion latin1 -> utf8 or vice versa.
Try dumping with:
mysqldump --default-character-set=binary ... >/path/to/dumpfile
And loading the dump with:
mysql --default-character-set=binary ... >/path/to/dumpfile
Let us know if that works.
HTH, Filipe
The characters look binary, so this might be a problem of conversion latin1 -> utf8 or vice versa.
Try dumping with:
mysqldump --default-character-set=binary ... >/path/to/dumpfile
And loading the dump with:
mysql --default-character-set=binary ... >/path/to/dumpfile
Let us know if that works.
HTH,
thanks for all the suggestions - in the end i opted for rsyncing the datafiles for this db and for me this worked fine. Not the 'correct' way but one that worked OK at the time.
thanks
-
Bart Schaefer -
Filipe Brandenburger -
Karanbir Singh -
Matt Shields -
Tom Brown