Re: [CentOS] mysql 4 5 export import issue
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 ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
[CentOS] mysql 4 5 export import issue
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 ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
Re: [CentOS] mysql 4 5 export import issue
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 -- Karanbir Singh : http://www.karan.org/ : [EMAIL PROTECTED] ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
Re: [CentOS] mysql 4 5 export import issue
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: localhostDatabase: spamassassin -- -- -- Server version4.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 [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40111 SET [EMAIL PROTECTED] */; ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
Re: [CentOS] mysql 4 5 export import issue
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. -- Karanbir Singh : http://www.karan.org/ : [EMAIL PROTECTED] ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
Re: [CentOS] mysql 4 5 export import issue
On Thu, Oct 30, 2008 at 9:52 AM, Karanbir Singh [EMAIL PROTECTED]wrote: 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 -- -matt ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
Re: [CentOS] mysql 4 5 export import issue
On Thu, Oct 30, 2008 at 6:22 AM, Karanbir Singh [EMAIL PROTECTED] 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. ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos
Re: [CentOS] mysql 4 5 export import issue
Hi, On Thu, Oct 30, 2008 at 09:14, Tom Brown [EMAIL PROTECTED] 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 ___ CentOS mailing list CentOS@centos.org http://lists.centos.org/mailman/listinfo/centos