So my MySQL database is behaving a little bit wierd. This is my table:
Name shares id price indvprc
cat 2 4 81 0
goog 4 4 20 20
fb 4 9 20 20
I’m getting this #1062 error when I try to insert into the table. So I looked into it further and realized that when I try to insert values into the table, in which the name and shares values are the same, it will return the #1062 error. For example, If i inserted:
fb 4 6 20 20
It would return an error. But if i changed the shares number to 6, it would run fine. Is it because of one of my columns that could be unique, or is it just something with mysql?
asked Jul 24, 2012 at 20:05
5
You need to remove shares as your PRIMARY KEY OR UNIQUE_KEY
answered Jul 24, 2012 at 20:12
mlishnmlishn
1,67914 silver badges19 bronze badges
1
Use SHOW CREATE TABLE your-table-name to see what column is your primary key.
answered Jul 24, 2012 at 20:10
Majid FouladpourMajid Fouladpour
29.2k20 gold badges76 silver badges127 bronze badges
2
- Make sure
PRIMARY KEYwas selectedAUTO_INCREMENT. - Just enable Auto increment by :
ALTER TABLE [table name] AUTO_INCREMENT = 1 - When you execute the insert command you have to skip this key.
chw21
7,9151 gold badge16 silver badges31 bronze badges
answered Feb 4, 2016 at 2:34
I solved it by changing the «lock» property from «shared» to «exclusive»:
ALTER TABLE `table`
CHANGE COLUMN `ID` `ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '' , LOCK = EXCLUSIVE;
davejal
6,00910 gold badges39 silver badges82 bronze badges
answered Dec 9, 2015 at 2:19
What is the exact error message? #1062 means duplicate entry violating a primary key constraint for a column — which boils down to the point that you cannot have two of the same values in the column. The error message should tell you which of your columns is constrained, I’m guessing «shares».
answered Jul 24, 2012 at 20:11
ChrisChris
3221 silver badge3 bronze badges
Probably this is not the best of solution but doing the following will solve the problem
Step 1: Take a database dump using the following command
mysqldump -u root -p databaseName > databaseName.db
find the line
ENGINE=InnoDB AUTO_INCREMENT="*****" DEFAULT CHARSET=utf8;
Step 2: Change ******* to max id of your mysql table id. Save this value.
Step 3: again use
mysql -u root -p databaseName < databaseName.db
In my case i got this error when i added a manual entry to use to enter data into some other table. some how we have to set the value AUTO_INCREMENT to max id using mysql command
FastFarm
4091 gold badge6 silver badges21 bronze badges
answered Apr 15, 2013 at 10:15
KshitizKshitiz
2,6731 gold badge18 silver badges24 bronze badges
1
Repair the database by your domain provider cpanel.
Or see if you didnt merged something in the phpMyAdmin
answered Apr 14, 2017 at 20:03
The DB I was importing had a conflict during the import due to the presence of a column both autoincrement and primary key.
The problem was that in the .sql file the table was chopped into multiple «INSERT INTO» and during the import these queries were executed all together.
MY SOLUTION was to deselect the «Run multiple queries in each execution» on Navicat and it worked perfectly
answered Oct 30, 2020 at 9:01
ivorotoivoroto
90512 silver badges12 bronze badges
Do not mix SQLAlchemy commands with Python’s Try-Except. SQL will keep trying regardless, and the error will only show later in your program flow.
answered Mar 2 at 18:26
Al MartinsAl Martins
4135 silver badges13 bronze badges
I had this error from mySQL using DataNucleus and a database created with UTF8 — the error was being caused by this annotation for a primary key:
@PrimaryKey
@Unique
@Persistent(valueStrategy = IdGeneratorStrategy.UUIDSTRING)
protected String id;
dropping the table and regenerating it with this fixed it.
@PrimaryKey
@Unique
@Persistent(valueStrategy = IdGeneratorStrategy.UUIDHEX)
protected String id;
answered Jun 3, 2017 at 19:50
bsautnerbsautner
4,3991 gold badge35 silver badges49 bronze badges
2
- Подробности
- Категория: oscommerse
- Просмотров: 52456
Ошибка вида #1062 — Duplicate entry ‘1’ for key ‘PRIMARY’, может возникать при переносе на другой хостинг, у меня возникла, когда я переносил сайт на oscommerse…будем решать ее в этой статье…
Duplicate entry ‘1’ for key ‘PRIMARY’ — то есть вы пытаетесь создать то, что уже создано(в phpmyadmin). Обычно такая ошибка возникает, когда вы поверх уже установленной базы (БД) движка, пытаетесь сверху накинуть примерно такую же базу, но таблицы к примеру уже такие были созданы и поэтому вылазит такая ошибка.
Открываем файл БД в программе notepad++ и меняем INSERT INTO на REPLACE INTO. Теперь мы не будем создавать то, что уже есть, а будем перезаписывать. Теперь заливаем еще раз эту БД и ошибка должна исчезнуть.
Добавить комментарий
На днях я переносил один из своих сайтов на новый хостинг и столкнулся с проблемой. Подробно о том, как перенести сайт на другой хостинг я расскажу в одной из следующих статей, а пока расскажу о самой проблеме и ее решении.
Файлы сайта я скопировал быстро, сделал экспорт базы данных со старого хостинга, но при попытке импортировать таблицы в базу на новом хостинге возникла ошибка в 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.
Получил ошибку «Duplicate entry» или она еще называется «Error_code: 1062» в MySQL репликации (Master-Slave). И хочу рассказать, как ее можно исправить.
Чтобы проверить, работает ли SLAVE на сервере ( с репликацией Master-Slave), нужно выполнить команду, но для начала подключитесь к серверу MYSQL:
mysql> show slave statusG
Ошибка выглядит вот так:
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 66.66.66.66
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000210
Read_Master_Log_Pos: 115912924
Relay_Log_File: mysqld-relay-bin.000640
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000210
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Could not execute Write_rows event on table linux-notes.log_visitor; Duplicate entry '14158493' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000210, end_log_pos 333
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 115913373
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Could not execute Write_rows event on table linux-notes.log_visitor; Duplicate entry '14158493' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000210, end_log_pos 333
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3361
1 row in set (0.00 sec)
mysql>
Где:
- 66.66.66.66 — ИП адрес Master-а
- Duplicate entry ‘14158493’ — Это дублирующиеся запись, ее нужно убрать.
Исправление Duplicate entry/Error_code: 1062 в MySQL репликации (Master-Slave)
Обычно репликация MySQL остановится всякий раз, когда возникает ошибка выполнения запроса на slave. Это происходит для того, чтобы мы могли идентифицировать проблему и устранить ее, и сохранить данные в соответствии с master-ом, который послал запрос. Вы можете пропустить такие ошибки (даже если это не рекомендуется) до тех пор, пока не найдете причину.
Для начала, остановим SLAVE:
mysql> SLAVE STOP; Query OK, 0 rows affected (0.00 sec) mysql>
И для исправления данной проблемы, имеется вот эта команда:
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=14158493; START SLAVE; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql>
И собственно, проверяем и убеждаемся что все хорошо:
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 66.66.66.66
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000210
Read_Master_Log_Pos: 132954424
Relay_Log_File: mysqld-relay-bin.000641
Relay_Log_Pos: 1457939
Relay_Master_Log_File: mysql-bin.000210
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 12183443
Exec_Master_Log_Pos: 132954424
Relay_Log_Space: 132954873
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3361
1 row in set (0.00 sec)
mysql>
Видим что проблема решена!
Если вы уверены, что пропуск этой ошибки не приведет падению slave, то можно прописать в конфиг-файл mysql пропуск этих ошибок, а сделать это можно вот так:
# vim /etc/my.cnf
И вставляем (если нет, то прописываем) данную переменную:
[...] slave-skip-errors = 1062 [...]
Как было показано в примере выше, я пропускаю ошибку 1062: _ Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY) Message: Duplicate entry ‘%s’ for key %d_
Вы можете пропускать и другие типы ошибок:
slave-skip-errors=[err_code1,err_code2,...|all]
Или можно пропускать все ошибки, но это крайне не рекомендуется делать! БУДЬТЕ ВНИМАТЕЛЬНЫ! А на этом, у меня все, статья «Duplicate entry/Error_code: 1062 в MySQL репликации (Master-Slave)» завершена.
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.
MAKE YOUR SERVER ROCK SOLID!
Never again lose customers to poor page speed! Let us help you.
Sign up once. Enjoy peace of mind forever!
GET 24/7 EXPERT SERVER MANAGEMENT
var google_conversion_label = «owonCMyG5nEQ0aD71QM»;