从5.0.27转换为5.1.41，得到重复的键错误(1062) [英] Converting from 5.0.27 to 5.1.41, getting duplicate key errors (1062)

问题描述

我有一个当前正在5.0.27服务器上运行的数据库.我想移至新的5.1.41服务器.

I have a database that's currently running on a 5.0.27 server. I want to move to a new 5.1.41 server.

我mysqldumpd所有文件.还原时出现错误

I mysqldump'd all the files. When restoring, I get an error

ERROR 1062 (23000) at line 21: Duplicate entry 'weiÃ' for key 'title'

我将失败范围缩小到了可以运行的脚本，但失败了:

I've narrowed the failure down to this script, which I can run and it fails:

--
-- Table structure for table `word`
--

set names utf8;

DROP TABLE IF EXISTS `word`;
CREATE TABLE `word`
(
  `wordid` int (10) unsigned NOT NULL auto_increment,
  `title` char (50) NOT NULL default '',
  PRIMARY KEY  (`wordid`),
  UNIQUE KEY `title` (`title`)
) ENGINE=MyISAM AUTO_INCREMENT=280707 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `word`
--

LOCK TABLES `word` WRITE;
INSERT INTO `word` VALUES
(198036,'weis'),
(241473, unhex('776569C39F'));
UNLOCK TABLES;

编辑-更改为UNHEX.

EDIT - changed to UNHEX.

我已经检查并重新检查了两个服务器之间的所有字符集和排序规则变量，它们看起来是相同的.即使不是，我也要自己指定排序规则.

I've checked and rechecked all the charset and collation variables between the two servers, and they look identical. Even if they weren't, I'm specifying the collation myself.

关于我在这里做错什么的任何线索吗?

Any clues as to what I'm doing wrong here?

这是我用来转储数据库的命令:

here's the command I'm using to dump the database:

mysqldump --add-drop-table --add-locks --disable-keys --lock-tables --quick  -uusername -ppassword database > filename

并加载

mysql -D$MYSQL_DB -u$MYSQL_USER -p$MYSQL_PASSWD < filename

如何检查客户端连接的排序规则?

How can I check the collations for the client connections?

推荐答案

从LiveJournal的一个伙伴中，我发现这是5.0到5.1之间的错误修复":他们更改了排序规则.如果您阅读了错误报告，他们实际上是在
broke 处(weis和weiss不应等效).但是他们不会破坏它.因此，我将不得不更改排序规则(如Dave Orr所建议的那样)，或者手动编辑我的数据.

From a buddy on LiveJournal, I found out it's a bug "fix" between 5.0 and 5.1: They changed the collation. If you read the bug report, they actually broke it (weis and weiss should not be equivalent). But they're not going to unbreak it. So I will either have to change the collation (as Dave Orr suggests), or manually edit my data.

http://bugs.mysql.com/bug.php?id=27877

这篇关于从5.0.27转换为5.1.41，得到重复的键错误(1062)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！