Mysql duplicate entry error code

Here at Bobcares, we provide Server Administration and Maintenance services to website owners and web solution providers.

An error we sometimes see in MySQL servers while updating, restoring or replicating databases is: “Error No: 1062” or “Error Code: 1062” or “ERROR 1062 (23000)“

A full error log that we recently saw in a MySQL cluster is:

could not execute Write_rows event on table mydatabasename.atable; Duplicate entry ’174465′ for key ‘PRIMARY’, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event’s master log mysql-bin.000004, end_log_pos 60121977

What is MySQL Error No: 1062?

Simply put, error 1062 is displayed when MySQL finds a DUPLICATE of a row you are trying to insert.

We’ve seen primarily 4 reasons for this error:

• The web application has a bug that adds primary key by large increments, and exhausts the field limit.
• MySQL cluster replication tries to re-insert a field.
• A database dump file contains duplicate rows because of coding error.
• MySQL index table has duplicate rows.

In rare cases, this error is shown when the table becomes too big, but let’s not worry about that for now.

How to fix Error No 1062 when your web appilcation is broken

Every database driven application like WordPress, Drupal or OpenCart distinguishes one user or data set from another using something called a “primary field”.

This primary field should be unique for each user, post, etc.

Web apps use a code like this to insert data:

INSERT INTO table ('id','field1','field2','field3') VALUES ('NULL','data1','data2','data3');

Where “id” is the unique primar key, and is set to auto-increment (that is a number inserted will always be greater than the previous one so as to avoid duplicates).

This will work right if the value inserted is “NULL” and database table is set to “auto-increment”.

Some web apps make the mistake of passing the value as

VALUES ('','data1','data2','data3');

where the first field is omitted. This will insert random numbers into the primary field, rapidly increasing the number to the maximum field limit (usually 2147483647 for numbers).

All subsequent queries will again try to over-write the field with “2147483647”, which MySQL interprets as a Duplicate.

Web app error solution

When we see a possible web application code error, the developers at our Website Support Services create a patch to the app file that fixes the database query.

Now, we have the non-sequential primary key table to be fixed.

For that, we create a new column (aka field), set it as auto-increment, and then make it the primary key.

The code looks approximately like this:

alter table table1 drop primary key;
alter table table1 add field2 int not null auto_increment primary key;

Once the primary key fields are filled with sequential values, the name of the new field can be changed to the old one, so that all web app queries will remain the same.

Warning : These commands can get very complex, very fast. So, if you are not sure how these commads work, it’s best to get expert assistance.

Click here to talk to our MySQL administrators. We are online 24/7 and can help you within a few minutes.

How to fix MySQL replication Error Code : 1062

Due to quirks in network or synching MySQL is sometimes known to try and write a row when it is already present in the slave.

So, when we see this error in a slave, we try either one of the following depending on many factors such as DB write traffic, time of day etc.

• Delete the row – This is the faster and safer way to continue if you know that the row being written is exactly the same as what’s already present.
• Skip the row – If you are not sure there’d be a data loss, you can try skipping the row.

How to delete the row

First delete the row using the primary key.

delete from table1 where field1 is key1;

Then stop and start the slave:

stop slave;
start slave;
select sleep(5);

Once it is done, check the slave status to see if replication is continuing.

show slave status;

If all is well, you’ll see “Seconds_Behind_Master” as a number. If not, your replication is broken and it needs to be fixed.

How to skip the row

For this, you can set the Skip counter to 1.

Here’s how it could look like:

stop slave;
set global SQL_SLAVE_SKIP_COUNTER = 1;
start slave;
select sleep(5);

Then check the slave status to see if replication is continuing.

show slave status;

Again, if all is well, you’ll see “Seconds_Behind_Master” as a number. If not, your replication is broken and it needs to be fixed.

Proceed with caution

Stopping and starting the slave cannot cause any issue unless you havea  very busy database. But, the delete statement, skipping and following up with a broken replication requires expert knowledge about MySQL organization and functioning.

If you are not sure how these commands will affect your database, we recommend you talk to a DB administrator.

Click here to consult our MySQL admins. We are online 24/7 and can attend your request within minutes.

How to fix MySQL restore errors

Restore errors usually take the form of:

ERROR 1062 (23000) at line XXXX: Duplicate entry ‘XXXXXX’ for key X”

When restoring database dumps, this error can happen due to 2 reasons:

• The SQL dump file has dulpicate entries.
• The index file is duplicate rows.

To find out what is exactly going wrong, we look at the conflicting rows and see if they have the same or different data.

If it’s the same data, then the issue could be due to duplicate index rows. If it is different data, the SQL dump file needs to be fixed.

How to fix duplicate entries in database dumps

This situation can happen when two or more tables are dumped into a single file without checking for duplicates.

To resolve this, one way we’ve used is to create a new primary key field with auto-increment and then change the queries to insert NULL value into it.

Then go ahead with the dump.

Once the new primary field table is fully populated, the name of the field is changed to the old primary table name to preserve the old queries.

The alter table command will look like this:

alter table table1 change column 'newprimary' 'oldprimary' varchar(255) not null;

If your index file is corrupted

There’s no easy way to fix an index file if there are duplicate entries in it.

You’ll have to delete the index file, and restore that file either from backups or from another server where your database dump is restored to a fresh DB server.

The steps involved are quite complex to list out here. We recommend that you consult a DB expert if you suspect the index file is corrupted.

Click here to talk to our MySQL administrators. We are online 24/7 and can help you within a few minutes.

Summary

MySQL error no 1062 can occur due to buggy web applications, corrupted dump files or replication issues. Today we’ve seen the various ways in which the cause of this error can be detected, and how it can be resolved.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

How to fix “error no 1062” in MySQL servers and clusters

by Visakh S | May 25, 2018

Here at Bobcares, we provide Server Administration and Maintenance services to website owners and web solution providers.

An error we sometimes see in MySQL servers while updating, restoring or replicating databases is: “Error No: 1062” or “Error Code: 1062” or “ERROR 1062 (23000)“

A full error log that we recently saw in a MySQL cluster is:

could not execute Write_rows event on table mydatabasename.atable; Duplicate entry ’174465′ for key ‘PRIMARY’, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event’s master log mysql-bin.000004, end_log_pos 60121977

What is MySQL Error No: 1062?

Simply put, error 1062 is displayed when MySQL finds a DUPLICATE of a row you are trying to insert.

We’ve seen primarily 4 reasons for this error:

• The web application has a bug that adds primary key by large increments, and exhausts the field limit.
• MySQL cluster replication tries to re-insert a field.
• A database dump file contains duplicate rows because of coding error.
• MySQL index table has duplicate rows.

In rare cases, this error is shown when the table becomes too big, but let’s not worry about that for now.

How to fix Error No 1062 when your web appilcation is broken

Every database driven application like WordPress, Drupal or OpenCart distinguishes one user or data set from another using something called a “primary field”.

This primary field should be unique for each user, post, etc.

Web apps use a code like this to insert data:

INSERT INTO table (‘id‘,’field1′,’field2′,’field3’) VALUES (‘NULL‘,’data1′,’data2′,’data3’);

Where “id” is the unique primar key, and is set to auto-increment (that is a number inserted will always be greater than the previous one so as to avoid duplicates).

This will work right if the value inserted is “NULL” and database table is set to “auto-increment”.

Some web apps make the mistake of passing the value as

where the first field is omitted. This will insert random numbers into the primary field, rapidly increasing the number to the maximum field limit (usually 2147483647 for numbers).

All subsequent queries will again try to over-write the field with “2147483647”, which MySQL interprets as a Duplicate.

Web app error solution

When we see a possible web application code error, the developers at our Website Support Services create a patch to the app file that fixes the database query.

Now, we have the non-sequential primary key table to be fixed.

For that, we create a new column (aka field), set it as auto-increment, and then make it the primary key.

The code looks approximately like this:

alter table table1 drop primary key;
alter table table1 add field2 int not null auto_increment primary key;

Once the primary key fields are filled with sequential values, the name of the new field can be changed to the old one, so that all web app queries will remain the same.

Warning : These commands can get very complex, very fast. So, if you are not sure how these commads work, it’s best to get expert assistance.

How to fix MySQL replication Error Code : 1062

Due to quirks in network or synching MySQL is sometimes known to try and write a row when it is already present in the slave.

So, when we see this error in a slave, we try either one of the following depending on many factors such as DB write traffic, time of day etc.

• Delete the row – This is the faster and safer way to continue if you know that the row being written is exactly the same as what’s already present.
• Skip the row – If you are not sure there’d be a data loss, you can try skipping the row.

How to delete the row

First delete the row using the primary key.

delete from table1 where field1 is key1;

Then stop and start the slave:

stop slave;
start slave;
select sleep(5);

Once it is done, check the slave status to see if replication is continuing.

show slave status;

If all is well, you’ll see “Seconds_Behind_Master” as a number. If not, your replication is broken and it needs to be fixed.

How to skip the row

For this, you can set the Skip counter to 1.

Here’s how it could look like:

stop slave;
set global SQL_SLAVE_SKIP_COUNTER = 1;
start slave;
select sleep(5);

Then check the slave status to see if replication is continuing.

show slave status;

Again, if all is well, you’ll see “Seconds_Behind_Master” as a number. If not, your replication is broken and it needs to be fixed.

Proceed with caution

Stopping and starting the slave cannot cause any issue unless you havea very busy database. But, the delete statement, skipping and following up with a broken replication requires expert knowledge about MySQL organization and functioning.

If you are not sure how these commands will affect your database, we recommend you talk to a DB administrator.

How to fix MySQL restore errors

Restore errors usually take the form of:

ERROR 1062 (23000) at line XXXX: Duplicate entry ‘XXXXXX’ for key X”

When restoring database dumps, this error can happen due to 2 reasons:

• The SQL dump file has dulpicate entries.
• The index file is duplicate rows.

To find out what is exactly going wrong, we look at the conflicting rows and see if they have the same or different data.

If it’s the same data, then the issue could be due to duplicate index rows. If it is different data, the SQL dump file needs to be fixed.

How to fix duplicate entries in database dumps

This situation can happen when two or more tables are dumped into a single file without checking for duplicates.

To resolve this, one way we’ve used is to create a new primary key field with auto-increment and then change the queries to insert NULL value into it.

Then go ahead with the dump.

Once the new primary field table is fully populated, the name of the field is changed to the old primary table name to preserve the old queries.

The alter table command will look like this:

alter table table1 change column ‘newprimary’ ‘oldprimary’ varchar(255) not null;

If your index file is corrupted

There’s no easy way to fix an index file if there are duplicate entries in it.

You’ll have to delete the index file, and restore that file either from backups or from another server where your database dump is restored to a fresh DB server.

The steps involved are quite complex to list out here. We recommend that you consult a DB expert if you suspect the index file is corrupted.

Summary

MySQL error no 1062 can occur due to buggy web applications, corrupted dump files or replication issues. Today we’ve seen the various ways in which the cause of this error can be detected, and how it can be resolved.

MAKE YOUR SERVER ROCK SOLID!

Never again lose customers to poor page speed! Let us help you.

Источник

linux-notes.org

Получил ошибку «Duplicate entry» или она еще называется «Error_code: 1062» в MySQL репликации (Master-Slave). И хочу рассказать, как ее можно исправить.

Чтобы проверить, работает ли SLAVE на сервере ( с репликацией Master-Slave), нужно выполнить команду, но для начала подключитесь к серверу MYSQL:

Ошибка выглядит вот так:

• 66.66.66.66 — ИП адрес Master-а
• Duplicate entry ‘14158493’ — Это дублирующиеся запись, ее нужно убрать.

Исправление Duplicate entry/Error_code: 1062 в MySQL репликации (Master-Slave)

Обычно репликация MySQL остановится всякий раз, когда возникает ошибка выполнения запроса на slave. Это происходит для того, чтобы мы могли идентифицировать проблему и устранить ее, и сохранить данные в соответствии с master-ом, который послал запрос. Вы можете пропустить такие ошибки (даже если это не рекомендуется) до тех пор, пока не найдете причину.

Для начала, остановим SLAVE:

И для исправления данной проблемы, имеется вот эта команда:

И собственно, проверяем и убеждаемся что все хорошо:

Видим что проблема решена!

Если вы уверены, что пропуск этой ошибки не приведет падению slave, то можно прописать в конфиг-файл mysql пропуск этих ошибок, а сделать это можно вот так:

И вставляем (если нет, то прописываем) данную переменную:

Как было показано в примере выше, я пропускаю ошибку 1062: _ Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY) Message: Duplicate entry ‘%s’ for key %d_

Вы можете пропускать и другие типы ошибок:

Или можно пропускать все ошибки, но это крайне не рекомендуется делать! БУДЬТЕ ВНИМАТЕЛЬНЫ! А на этом, у меня все, статья «Duplicate entry/Error_code: 1062 в MySQL репликации (Master-Slave)» завершена.

One thought on “ Duplicate entry/Error_code: 1062 в MySQL репликации (Master-Slave) ”

SQL_SLAVE_SKIP_COUNTER=14158493;
это значит пропустит 14158493 запросов!
Так себе идея, обычно хватает SQL_SLAVE_SKIP_COUNTER=1;

Добавить комментарий Отменить ответ

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.

Источник

Technology blog by Rathish kumar

[Solved] How to solve MySQL error code: 1062 duplicate entry?

Error Message:

Example:

Possible Reason:

Case 1: Duplicate value.

Case 2: Unique data field.

Case 3: Data type –upper limit.

MySQL NUMERICAL DATA TYPE — STORAGE & RANGE

Solution:

Case 1: Duplicate value.

Case 2: Unique data field.

Case 3: Data type-upper limit.

Final workaround:

Step 1: Backup database:

Step 2: Drop and recreate database:

Step 3: Import database:

The article has genuinely peaks my interest. I’m planning to marker your website and maintain checking for brand new details. I actually idolized reading your journal, valuable data.

Mara bhi dublicate error araha ha ma kya Karun.

thanks bro , It helped me

we have sell some merchandise of rotate custom boxes.it’s far absolutely useful and completely low fee allure visits this web page thanks and appeal component this say thinking of your links. Facial Treatment

I never comment on blogs but your article is so best that I never stop myself to say something about it. You’re amazing Man, I like it Database Errors. Keep it up

Awesome and interesting article. Great things you’ve always shared with us. Thanks. Just continue composing this kind of post. entreprise informatique

That is the place IT specialists come in and it is actually why it tends to be so natural to discover lucrative chances in the event that you have the best possible measure of innovation based information.
information technology

Great job for publishing such a beneficial web site. Your web log isn’t only useful but it is additionally really creative too. skytechosting.com

Thanks for your insight for your fantastic posting. I’m glad I have taken the time to see this. ternopil national medical university

I recommend that you ask for a free trial session. cursos de ti

This is awesome post. I really like it. I will also want to know APK Game Killer is good or not?

We are really grateful for your blog post. You will find a lot of approaches after visiting your post. I was exactly searching for. Thanks for such post and please keep it up. Great work water softeners

Your writers are your resources in providing quality articles to your clients. Obviously, a badly written article reflects the type of service that you offer. Helphub

I have read a few of the articles on your website now, and I really like your style of blogging. I added it to my favorites blog site list and will be checking back soon. Please check out my site as well and let me know what you think. fue hair transplant in lucknow

I was reading your article and wondered if you had considered creating an ebook on this subject. Your writing would sell it fast. You have a lot of writing talent. techno tech blog

Now, the question would be how someone will analyze data in the aforementioned manner and give some useful results. 360DigiTMG data science training in hyderabad

This Was An Amazing ! I Haven’t Seen This Type of Blog Ever ! Thankyou For Sharing, data sciecne course in hyderabad

This Was An Amazing ! I Haven’t Seen This Type of Blog Ever ! Thank you For Sharing
https://socialprachar.com/data-science/

Very nice blogs. i have to learning for lot of information for this sites. Sharing for wonderful information.Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing, best data science courses in Hyderabad

It is a great website.. The Design looks very good.. Keep working like that!. Seo house.

Very nice blogs. i have to learning for lot of information for this sites. Sharing for wonderful information.Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing, data science course in Hyderabad

For full coverage, use a can of spray adhesive or a Xyron machine. To use your Xyron machine, just roll the die cut or item you want to completely cover through the machine with the piece face down (just opposite of how you normally do it), then apply the glitter. Craft glitter bulk

I was looking at some of your posts on this website and I conceive this web site is really instructive! Keep putting up.. erek erek 2d

Really interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.
Data Science Course Training in Hyderabad

It’s very informative and you are obviously very knowledgeable in this area. You have opened my eyes to varying views on this topic with interesting and solid content.
data science courses

Although this is not a magic tool which will write on your behalf but it can enhance and improve your writing style to a professional level. Essay Writing Service

Data Science helps in combining the disruption into categories and communicating their potential, which allows data and analytics leaders to drive better results.
Data Science Course in Hyderabad

You completed certain reliable points there. I did a research on the subject and found nearly all persons will agree with your blog.
data scientist courses

I am a new user of this site so here i saw multiple articles and posts posted by this site,I curious more interest in some of them hope you will give more information on this topics in your next articles.
ddata science course in Hyderabad

Can you make tiktok video how you do it? On this site https://soclikes.com/buy-tiktok-likes you can buy tiktok likes for this video

Where telethons of the 1980s such as Live Aid attempted to create a buzz and sense of public involvement through telephone technology, this can be done faster today using the internet. Audiences can watch events in real time and engage in online ‘chat’ behaviors as events unfold. data science course syllabus

If you want to start a blog about code you can buy instagram followers from this site https://viplikes.in

Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.
data science course in India

This post is great. I really admire your post. Your post was awesome. data science course in Hyderabad

This is a wonderful article, Given so much info in it, Thanks for sharing. CodeGnan offers courses in new technologies and makes sure students understand the flow of work from each and every perspective in a Real-Time environment Augmented Reality Training. ,

You might comment on the order system of the blog. You should chat it’s splendid. Your blog audit would swell up your visitors. I was very pleased to find this site.I wanted to thank you for this great read!!
Artificial Intelligence Course

Thank you so much for shearing this type of post.
This is very much helpful for me. Keep up for this type of good post.
please visit us below
data science training

I favor the entire group of products, Considered truly loved, I want more information. about this, thinking about that it’s fairly good., Many thanks a great deal with regards to displaying. Big pharma Companies

I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.Make Money Online data science training in Hyderabad

Python is also quite useful as a modern scripting language similar to Perl, which can used to tie disparate systems together. data science course in india

I see the greatest contents on your blog and I extremely love reading them. ExcelR Data Analyst Course

Thanks for the information about Blogspot very informative for everyone
data scientist malaysia

This post is very simple to read and appreciate without leaving any details out. Great work!
data scientist course noida

You totally coordinate our desire and the assortment of our data.
data scientist malaysia

Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
Data Science Course in Bangalore

This is my first time I visit here. I found so many engaging stuff in your blog, particularly its conversation. From the huge loads of remarks on your articles, I surmise I am by all account not the only one having all the recreation here! Keep doing awesome. I have been importance to compose something like this on my site and you have given me a thought.
data scientist training and placement

Источник

Duplicate Entry error is a very common error that has been experienced by users working with databases. Users have reported that the error has commonly occurred when using the SQL. The error appears when updating the tables. Furthermore, the error has occurred in several scenarios which include using Laravel, PHP, Atlassian, Joomla, and similar web development and databases. Now in this article, the objective is to give you important information regarding the error and to give you some methods by which you can fix the issue by yourself in no time. But before let’s go through its causes.

Causes of Duplicate Entry Error Problem Issue

While researching about the error and its possible solution we come up with some very common users that have been reported by the users. The Duplicate Entry error appears because of multiple reasons depending like if you are working with SQL possible the error comes because of the duplicate key, unique data field, or data type -upper limit. Also if the table indexes are corrupted then also the error seems to appear. However, the error also occurs due to mistakes in the codes or the way the user is updating or modifying the table.

• Duplicate key or entries
• Unique data field
• Data type -upper limit
• Table indexes are corrupted
• Mistakes in the codes

Similar Types of Duplicate Entry Error Problem Issue

• MySQL error 1062 duplicate entry ‘0’ for key ‘primary’
• MySQL for key ‘primary auto_increment
• #1062 – duplicate entry ‘1’ for key ‘primary’ PHPMyAdmin
• ‘0’ for key ‘primary Codeigniter
• Duplicate entry 255 for key ‘primary
• 82 for key primary
• Duplicate entry ‘4294967295’ for key ‘primary
• #1062 40 for key primary

In this section, we will try to cover some methods that you can try to resolve the Duplicate Entry Error. The following are the methods we will go through. Since we do not know the actual cause of the issue we will be giving you solutions according to the scenarios.

1. The Value Already Exist (Duplicate Value)

Now the #1062 – duplicate entry ‘1’ for key ‘primary’ Error may occur when the data or value which you are trying to insert already exists in the Primary key. Furthermore, it is important to know that the Primary key does not accept duplicate entries. To resolve this you can do the below steps.

• STEP 1. Put the Primary Key Colum as to be auto increment
• STEP 2. Use the below syntax

ALTER TABLE ‘table_name’ ADD ‘column_name’ INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

**NOTE: You can ignore the Primary key column while inserting values.
Alternatively,
you can put NULL vale to Primary Key which in turn automatically generates sequence numbers.

2. Unique Data Filed

The error also appears when an existing table has been set to unique. So when you try to add any column the error appears. So the simple fix to this duplicate entry for key ‘primary’ issue is to create a new column but don’t set it to the Unique field. Insert whatever data you wish to insert and the set is as unique if you want to.

3. Data Limit Out Of Range

The error also appears when if you have been using the auto_increment function and the data exceeds the limit of auto_increment function the #1062 – duplicate entry ‘0’ for key ‘primary’ error appears. Suppose you have assigned the primary key column as something. And the limit of the auto_increment is set to a max of 127. Now when you enter a new record whose id is more than 127 the error will emerge. To fix this follow the steps.

• STEP 1. We can resolve the issue by modifying the index field. You may use any of the following signed/unsigned INT/ BIGINT
• STEP 2. Use the below command to increase the maximum range

ALTER TABLE ‘table_name’ MODIFY ‘column_name’ INT UNSIGNED NOT NULL AUTO_INCREMENT;

• STEP 3. Also if you want to retrieve the recently incremented value, the below command

mysql> SELECT LAST_INSERT_ID();

4. Creating a New Database & Importing

If you have tried the above methods and the duplicate entry 0 for key primary error still persists, try the below step to fix the issue.

• STEP 1. Firstly backup your database using the below command

mysqldump database_name > database_name.sql

• STEP 2. Once the backup is done, drop the database

DROP DATABASE database_name;

• STEP 3. Now recreate the database, by using the below command

CREATE DATABASE database_name;

• STEP 4. Now that you have created the database import using the below command

mysql database_name < database_name.sql;

• STEP 5. Now check if the duplicate entry ‘1’ for key ‘primary’ error still occurs

5. When Importing Table from Database in PHPmyadmin

The error has been seen when the user tries to import the exported tables in PHPMyAdmin the duplicate entry for key primary error seems to appear. Follow the steps to do it the right way.

• STEP 1. When you are exporting your SQL database in PHPMyAdmin, use the custom export method
• STEP 2. In the export, method choose Custom- display all possible options

• STEP 3. In the options instead of selecting any of the insert options, choose update

• STEP 4. Using this way will prevent any kind of duplicated inserts to get rid of remove duplicate entry in excel error.

6. Duplicate Username

One of the users has been facing the issue because the database does not allow duplicate usernames. So kindly make sure that the username is unique. To fix this how to remove the duplicate entry in excel issue follow the steps.

• STEP 1. In order to resolve the issue, you have to find find the duplicate usernames, which can  be done using the following command

SELECT username FROM #__users GROUP BY username HAVING COUNT(*) > 1

**NOTE: Instead of #_ use the prefix for your tables.

• STEP 2. Fix the 1062 duplicate entry 1 for key primary issue

7. Other Troubleshooting Points

• Error in WordPress Database: So one the cause why this duplicate entry 1 for key primary error occurs is because of adswsc_DbInstall. It is comparing the unequal table names.
• Update the Program: Make sure that if you are using any database application it is updated to the latest version.
• Crosscheck the code: Make sure that the code is accurate and without errors

Conclusion:

In this troubleshooting, we have seen various methods that are the solution to fix Duplicate Entry Error. The error may appear due to multiple reasons and we have tried to cover most of them. Furthermore, we have also talked about the possible causes that lead to this error.

We hope this Duplicate Entry troubleshooting guide fixed your issue. For articles on troubleshooting and tips follow us. Thank You!

На днях я переносил один из своих сайтов на новый хостинг и столкнулся с проблемой. Подробно о том, как перенести сайт на другой хостинг я расскажу в одной из следующих статей, а пока расскажу о самой проблеме и ее решении.

Файлы сайта я скопировал быстро, сделал экспорт базы данных со старого хостинга, но при попытке импортировать таблицы в базу на новом хостинге возникла ошибка в My SQL вот такого вида:

Ошибка

SQL-запрос:

— — Дамп данных таблицы `rich_blc_instances` — INSERT INTO `rich_blc_instances` (`instance_id`, `link_id`, `container_id`, `container_type`, `link_text`, `parser_type`, `container_field`, `link_context`, `raw_url`) VALUES (1, 1, 1, ‘blogroll’, ‘Документация’, ‘url_field’, ‘link_url’, », ‘http://codex.wordpress.org/Заглавная_страница’), (2, 2, 2, ‘blogroll’, ‘Блог WordPress’, ‘url_field’, ‘link_url’, », ‘http://wordpress.org/news/’), (3, 3, 3, ‘blogroll’, ‘Форумы поддержки’, ‘url_field’, ‘link_url’, », ‘http://ru.forums.wordpress.org/’), (4, 4, 4, ‘blogroll’, ‘Плагины’, ‘url_field’, ‘link_url’, », ‘http://wordpress.org/extend/plugins/’), (5, 5, 5, ‘blogroll’, ‘Темы’, ‘url_field’, ‘link_url’, », ‘http://wordpress.org/extend/themes/’), (6, 6, 6, ‘blogroll’, ‘Обратная связь’, ‘url_field’, ‘link_url’, », ‘http://ru.forums.wordpress.org/forum/20’), (7, 7, 7, ‘blogroll’, ‘Планета WordPr[…]

Ответ MySQL:

#1062 — Duplicate entry ‘1’ for key ‘PRIMARY’

Так как в базах данных я полный чайник, пришлось копаться в интернете в поисках решения. На форумах довольно много записей от людей с подобной проблемой, но нигде нет четкого ответа – сделай вот так и так.

В общих чертах я проблему понял, но вот как ее решить было неясно. На всех форумах, где встречалось описание подобной ошибки, люди писали, что она возникает при попытке добавить записи из одной базы данных в уже существующую другую.

Но я импортировал базу данных в пустые таблицы, более того, таблицы создавались в процессе импорта.

Решил проблему с ошибкой «#1062 — Duplicate entry ‘1’ for key ‘PRIMARY’» следующим образом:

Заменил в таблицах базы данных команду INSERT INTO на REPLACE INTO. В тексте ошибки, который я привел выше вы можете посмотреть в каком месте таблицы располагаются эти слова (выделил жирным).

По умолчанию, с помощью директивы insert база пыталась вставить значения в таблицу, но почему-то, находила дублированный key ‘PRIMARY’ и не могла вставить данные (как она их находила, я так и не разобрался). Директива replace заставила базу заменять данные при совпадении значений, не обращая внимания на прошлые записи.

Заменить эту директиву можно открыв сам файл базы данных с помощью текстового редактора – эта команда стоит перед блоком каждой таблицы. Открываете базу данных в текстовом редакторе, например, Akelpad и меняете все команды INSERT INTO на REPLACE INTO.

В моем же случае, получилось сделать проще – я по новой сделал экспорт таблицы на старом хостинге и в настройках экспорта установил этот самый REPLACE вместо INSERT.