ERROR 1062 (23000) at line 26903: Duplicate entry 'onli????' for key 'xxxx_script_name'
来源:互联网 发布:淘宝解id锁会退钱吗 编辑:程序博客网 时间:2024/06/07 05:48
1.版本
1)操作系统
cat /etc/issue
Red Hat Enterprise Linux Server release 5.5 (Tikanga)
Kernel \r on an \m
cat /proc/version
Linux version 2.6.32-504.el6.x86_64 (mockbuild@c6b9.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-11) (GCC) ) #1 SMP Wed
2)mysql数据库版本
mysql --version
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
2.错误描述
2.1 今天在导入一个库的dump文件到另一个库的时候报了如下错误:
<span style="color:#333333;">mysql -uroot -p -S /var/lib/mysql/3306.sock --default-character-set=latin1<database_latin1.sqlEnter password: </span><span style="color:#ff0000;">ERROR 1062 (23000) at line 26903: Duplicate entry 'onli????' for key 'xxxx_script_name'</span>##报错是说键xxxx_script_name导入的时候又重复值(报错中我们看到冲入的entry中有乱码)
2.2 查看xxxx_script_name及所属的表
mysql> desc TABLE_CONSTRAINTS;+--------------------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------------------+--------------+------+-----+---------+-------+| CONSTRAINT_CATALOG | varchar(512) | YES | | NULL | || CONSTRAINT_SCHEMA | varchar(64) | NO | | | || CONSTRAINT_NAME | varchar(64) | NO | | | || TABLE_SCHEMA | varchar(64) | NO | | | || TABLE_NAME | varchar(64) | NO | | | || CONSTRAINT_TYPE | varchar(64) | NO | | | |+--------------------+--------------+------+-----+---------+-------+6 rows in set (0.00 sec)mysql> select CONSTRAINT_SCHEMA,CONSTRAINT_NAME,TABLE_SCHEMA,TABLE_NAME,CONSTRAINT_TYPE from table_constraints where CONSTRAINT_NAME='xxx_script_name';+-------------------+------------------+--------------+-------------+-----------------+| CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |+-------------------+------------------+--------------+-------------+-----------------+| xxtask | xxxx_script_name | xxtask | xxxx_script | UNIQUE |+-------------------+------------------+--------------+-------------+-----------------+##从上面的查询我们看到 xxxx_script_name是一个唯一键(但是我们从主库中导出的数据不应该存在唯一键冲突啊)
2.3 检查主库相关表的唯一键列是否有重复值
mysql> select count(1) from xxxx_script;+----------+| count(1) |+----------+| 974 |+----------+1 row in set (0.00 sec)mysql> select count(distinct xxxx_script_name) from xxxx_script;+----------------------------------+| count(distinct task_script_name) |+----------------------------------+| 974 |+----------------------------------+1 row in set (0.02 sec)##通过上面查询我们发现,xxxx_script_name确实没有重复,那么为什么我们导入的时候会报唯一键重复呢?
2.4 导出时字符集指定错误,导致中文乱码,从而导致导入时报唯一键冲突
我们的导出脚本如下:
mysqldump -uroot -p -S /var/lib/mysql/mysql.sock --all-databases --events --routines --triggers --single-transaction --default-character-set=latin1 --flush-privileges --master-data=2 --hex-blob>/root/database228_latin1.sql
##字符集指定为latin1,而xxxx_script表的字符集为utf8,并且xxxx_script_name列中保存了中文,所以我们这样导出的时候就导致了乱码,在另一个库导入的时候就出现问题
3.解决方案
##指定正确的字符集进行导出,然后再进行导入,这样就正常了。(当然并不是说乱码就一定会导致唯一键重复错误,只是有可能)
mysqldump -uroot -p -S /var/lib/mysql/mysql.sock --all-databases --events --routines --triggers --single-transaction --default-character-set=utf8 --flush-privileges --master-data=2 --hex-blob>/root/database228_utf8.sql
- ERROR 1062 (23000) at line 26903: Duplicate entry 'onli????' for key 'xxxx_script_name'
- ERROR 1062 (23000) at line 1: Duplicate entry '1332883220' for key 'group_key'
- ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY'
- ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
- MySQL ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY'
- ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'
- MySQL --- SQL Error: 1062: Duplicate entry '2147483647' for key 'PRIMARY'
- txt导入Mysql:ERROR 1062(23000):Duplicate entry '0' for key 'PRIMARY'
- MySql出现ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY'
- mysql数据库:ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
- Duplicate entry for key
- Duplicate entry '**' for key '***'
- 1062 - Duplicate entry for key 1
- 1062 - Duplicate entry '20150820048' for key 'goods_serial'
- Mysql错误提示:Error: Duplicate entry '0' for key 'PRIMARY'
- Duplicate entry '##' for key 'PRIMARY'
- Duplicate entry '' for key 2
- Duplicate entry '**' for key 'all_inde
- ccf认证--201509-3
- php 错误级别
- selector.xml中状态无效
- 设计模式——浅复制VS深复制
- homerHEVC代码阅读(26)——编码器工作线程/wfpp线程wfpp_encoder_thread
- ERROR 1062 (23000) at line 26903: Duplicate entry 'onli????' for key 'xxxx_script_name'
- Swift!代码实现UI基础控件
- Git-0.99 README 第二部分 工作流
- 关于DNS故障的一些问题
- 位域字节对齐
- 编程高手修炼之道
- android 触摸事件、点击事件的区别,执行流程
- EzLaze III
- RabbitMQ配置(V5.30)