I am at a complete loss here. I have two databases. One on my localhost site that I use for development and one on my remote site that I use for my live (production) site. I manage both of them through phpMyadmin. As I have been doing for months now, when I need to update the live site, I dump the related database and import the database from my localhost site.
我在这里完全失败了。我有两个数据库。一个在我的localhost站点上,我用于开发,一个在我的远程站点上,我用于我的实时(生产)站点。我通过phpMyadmin管理它们。正如我几个月来一直在做的那样,当我需要更新实时站点时,我会转储相关数据库并从我的localhost站点导入数据库。
Now, no matter what I try, I keep getting this error:
现在,无论我尝试什么,我都会收到这个错误:
Error SQL query:
错误SQL查询:
--
-- Dumping data for table `oc_address_type`
--
INSERT INTO `oc_address_type` ( `address_type_id` , `address_type_name` )
VALUES ( 1, 'Billing' ) , ( 2, 'Shipping' ) ;
MySQL said: Documentation
MySQL说:文档
#1062 - Duplicate entry '1' for key 'PRIMARY'
#1062 - 密钥'PRIMARY'重复输入'1'
I tried creating a new blank database on my localhost and importing into that but same results. I have validated all of the tables and indexes and cannot find anything wrong there.
我尝试在我的localhost上创建一个新的空白数据库并导入到相同的结果中。我已经验证了所有表和索引,但在那里找不到任何错误。
Any suggestions please as I am completely down until this gets resolved.
任何建议,因为我完全失望,直到这个问题得到解决。
By the way, I am completely dropping all tables and importing structure and data. This has always worked until today.
顺便说一句,我正在完全删除所有表并导入结构和数据。这一直持续到今天。
7 个解决方案
#1
7
you need to dump with the drop statements. The table exists and has data already and your trying to insert more which is identical. Im not 100% sure on phpmyadmin but the dumps will have an option for "add drop table" statements
你需要使用drop语句进行转储。该表存在并且已经有数据,并且您尝试插入更多相同的数据。我不是100%肯定phpmyadmin但转储将有一个“添加删除表”语句的选项
#2
5
Dump your database on localhost with "mysqldump --insert-ignore ..." then try to import with phpmyadmin on your live machine.
使用“mysqldump --insert-ignore ...”将您的数据库转储到localhost,然后尝试在您的实时计算机上使用phpmyadmin导入。
Or try to connect to your live database with command line tools (configure your database to be able to connect from other hosts than "localhost" first!)
或者尝试使用命令行工具连接到您的实时数据库(将数据库配置为能够首先从“localhost”以外的其他主机进行连接!)
Then you can try following:
然后你可以试试以下:
$ mysql -f -p < yourdump.sql
$ mysql -f -p
with -f "force" you can ignore errors during importing. It's the same as adding "--force" parameter to "mysqlimport".
使用-f“force”可以在导入期间忽略错误。这与将“--force”参数添加到“mysqlimport”相同。
#3
3
The problem is related with your file - you are trying to create a DB using a copy - at the top of your file you will find something like this:
问题与您的文件有关 - 您正在尝试使用副本创建数据库 - 在文件的顶部,您会发现类似这样的内容:
CREATE DATABASE IF NOT EXISTS *THE_NAME_OF_YOUR_DB* DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci; USE *THE_NAME_OF_YOUR_DB*;
创建数据库,如果不是EXISTS * THE_NAME_OF_YOUR_DB *默认字符集latin1 COLLATE latin1_general_ci;使用* THE_NAME_OF_YOUR_DB *;
and I'm sure that you already have a DB with this name - IN THE SAME SERVER - please check, because you are trying to overwrite!! Just change the name OR (better) ERASE THIS LINE!
而且我确定你已经有一个这个名字的数据库 - 在同一台服务器 - 请检查,因为你试图覆盖!!只需更改名称或(更好)删除此行!
#4
0
I had this same issue, my problem was I had a primary key column called unique_id and when you try to add two of the same value in that primary keyed column, it comes back with the error below.
我有同样的问题,我的问题是我有一个名为unique_id的主键列,当你尝试在该主键列中添加两个相同的值时,它会返回下面的错误。
A primary key column's data is all suppose to be different, so that bottom 1 I changed to 3 and the error went away.
主键列的数据都假设不同,因此底部1我改为3并且错误消失了。
Your MySql is not corrupt, like previous answers and comments.
您的MySql没有损坏,就像以前的答案和评论一样。
#5
0
you need to delete any previous tables that you are over-writing. if you are doing a complete restore of all tables, delete all existing tables.
您需要删除任何以前覆盖的表。如果要对所有表执行完全还原,请删除所有现有表。
#6
0
For me the foreign_key_checks and truncate table options was useful.
对我来说,foreign_key_checks和truncate table选项很有用。
SET foreign_key_checks = 0;
TRUNCATE `oc_address_type`;
SET foreign_key_checks = 1;
Run the above sql script, and after the import.
运行上面的sql脚本,然后导入。
#7
-1
I have met the same problem, I drop the table and rebuilt the database, then the problem solved.
我遇到了同样的问题,我删除了表并重建了数据库,然后问题解决了。
#1
7
you need to dump with the drop statements. The table exists and has data already and your trying to insert more which is identical. Im not 100% sure on phpmyadmin but the dumps will have an option for "add drop table" statements
你需要使用drop语句进行转储。该表存在并且已经有数据,并且您尝试插入更多相同的数据。我不是100%肯定phpmyadmin但转储将有一个“添加删除表”语句的选项
#2
5
Dump your database on localhost with "mysqldump --insert-ignore ..." then try to import with phpmyadmin on your live machine.
使用“mysqldump --insert-ignore ...”将您的数据库转储到localhost,然后尝试在您的实时计算机上使用phpmyadmin导入。
Or try to connect to your live database with command line tools (configure your database to be able to connect from other hosts than "localhost" first!)
或者尝试使用命令行工具连接到您的实时数据库(将数据库配置为能够首先从“localhost”以外的其他主机进行连接!)
Then you can try following:
然后你可以试试以下:
$ mysql -f -p < yourdump.sql
$ mysql -f -p
with -f "force" you can ignore errors during importing. It's the same as adding "--force" parameter to "mysqlimport".
使用-f“force”可以在导入期间忽略错误。这与将“--force”参数添加到“mysqlimport”相同。
#3
3
The problem is related with your file - you are trying to create a DB using a copy - at the top of your file you will find something like this:
问题与您的文件有关 - 您正在尝试使用副本创建数据库 - 在文件的顶部,您会发现类似这样的内容:
CREATE DATABASE IF NOT EXISTS *THE_NAME_OF_YOUR_DB* DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci; USE *THE_NAME_OF_YOUR_DB*;
创建数据库,如果不是EXISTS * THE_NAME_OF_YOUR_DB *默认字符集latin1 COLLATE latin1_general_ci;使用* THE_NAME_OF_YOUR_DB *;
and I'm sure that you already have a DB with this name - IN THE SAME SERVER - please check, because you are trying to overwrite!! Just change the name OR (better) ERASE THIS LINE!
而且我确定你已经有一个这个名字的数据库 - 在同一台服务器 - 请检查,因为你试图覆盖!!只需更改名称或(更好)删除此行!
#4
0
I had this same issue, my problem was I had a primary key column called unique_id and when you try to add two of the same value in that primary keyed column, it comes back with the error below.
我有同样的问题,我的问题是我有一个名为unique_id的主键列,当你尝试在该主键列中添加两个相同的值时,它会返回下面的错误。
A primary key column's data is all suppose to be different, so that bottom 1 I changed to 3 and the error went away.
主键列的数据都假设不同,因此底部1我改为3并且错误消失了。
Your MySql is not corrupt, like previous answers and comments.
您的MySql没有损坏,就像以前的答案和评论一样。
#5
0
you need to delete any previous tables that you are over-writing. if you are doing a complete restore of all tables, delete all existing tables.
您需要删除任何以前覆盖的表。如果要对所有表执行完全还原,请删除所有现有表。
#6
0
For me the foreign_key_checks and truncate table options was useful.
对我来说,foreign_key_checks和truncate table选项很有用。
SET foreign_key_checks = 0;
TRUNCATE `oc_address_type`;
SET foreign_key_checks = 1;
Run the above sql script, and after the import.
运行上面的sql脚本,然后导入。
#7
-1
I have met the same problem, I drop the table and rebuilt the database, then the problem solved.
我遇到了同样的问题,我删除了表并重建了数据库,然后问题解决了。