Mysql Error Duplicate Entry
Contents |
here for a quick overview of the site Help Center Detailed mysql error 1062 duplicate entry for key primary answers to any questions you might have Meta Discuss
Error Code 1062 Duplicate Entry For Key Primary
the workings and policies of this site About Us Learn more about Stack Overflow the
Mysql Error 1062 Duplicate Entry '0' For Key 'primary'
company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss
Duplicate Entry For Key 'primary' Mysql
Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Error Code: 1062. Duplicate entry 'PRIMARY' up vote 1 down vote favorite So, my professor gave me tables to insert duplicate entry '1' for key 'primary' mysql it in a database but when I execute his code, MySQL is constantly giving the Error Code: 1062. Here is the conflict tables and the inserts: TABLES CREATE TABLE FABRICANTES( COD_FABRICANTE integer NOT NULL, NOMBRE VARCHAR(15), PAIS VARCHAR(15), primary key (cod_fabricante) ); CREATE TABLE ARTICULOS( ARTICULO VARCHAR(20)NOT NULL, COD_FABRICANTE integer NOT NULL, PESO integer NOT NULL , CATEGORIA VARCHAR(10) NOT NULL, PRECIO_VENTA integer, PRECIO_COSTO integer, EXISTENCIAS integer, primary key (articulo,cod_fabricante), foreign key (cod_fabricante) references Fabricantes(cod_fabricante) ); INSERT INTO: INSERT INTO FABRICANTES VALUES(10,'CALVO', 'ESPAÑA'); INSERT INTO FABRICANTES VALUES(15,'LU', 'BELGICA'); INSERT INTO FABRICANTES VALUES(20,'BARILLA', 'ITALIA'); INSERT INTO FABRICANTES VALUES(25,'GALLO', 'ESPAÑA'); INSERT INTO FABRICANTES VALUES(30,'PRESIDENT', 'FRANCIA'); INSERT INTO ARTICULOS VALUES ('Macarrones',20, 1, 'Primera',100,98,120); INSERT INTO ARTICULOS VALUES ('Tallarines',20, 2, 'Primera',120,100,100); INSERT INTO ARTICULOS VALUES ('Tallarines',20, 1, 'Segunda',99,50,100); INSERT INTO ARTICULOS VALUES ('Macarrones',20, 1, 'Tercera',80,50,100); INSERT INTO ARTICULOS VALUES ('Atún',10, 3, 'Primera',200,150,220); INSERT INTO ARTICULOS VALUES ('Atún',10, 3, 'Seg
This happens in order for us to be able to identify the problem and fix it, and keep the data consistent with the mater that has sent the #1062 - duplicate entry '1' for key 'primary' phpmyadmin import query. You can skip such errors, even if this is not recommended, as duplicate entry for key 1 long as you know really well what are those queries and why they are failing, etc. For example you duplicate entry mysql can skip just one query that is hanging the slave using: 1 mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; There might be cases where you will want to skip more queries. For example you http://stackoverflow.com/questions/28702069/error-code-1062-duplicate-entry-primary might want to skip all duplicate errors you might be getting (output from show slave status;): 1 "1062 | Error 'Duplicate entry 'xyz' for key 1' on query. Default database: 'db'. Query: 'INSERT INTO ..." If you are sure that skipping those errors will not bring your slave inconsistent and you want to skip them ALL, you would add to your my.cnf: 1 http://www.ducea.com/2008/02/13/mysql-skip-duplicate-replication-errors/ class='line'>slave-skip-errors = 1062 As shown above in my example 1062 is the error you would want to skip, and from here we have: _ Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY) Message: Duplicate entry ‘%s’ for key %d_ You can skip also other type of errors, but again don’t do this unless you understand very well what those queries are and what impact they have on your data: 1 slave-skip-errors=[err_code1,err_code2,...|all] and for the error codes you can see them all here … (you will see them in your _show slave statu_s; also). Posted by Marius Ducea Feb 13th, 2008 posted in: tips & tricks tagged with: mysql, tips Tweet « Linux Tips: find all files of a particular size Increase PHP memory limit » Comments Please enable JavaScript to view the comments powered by Disqus. About Me My name is Marius Ducea. I am an experienced Infrastructure Developer based in the SF Bay Area. You can find out more about me here. I blog here mostly about things I don't want to forget ;) Do you have an interesting project idea? Or you just want to chat? Get in touch! Connect @mariusducea (Twitter) mariu
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies http://dba.stackexchange.com/questions/106294/add-unique-index-fails-with-duplicate-entry-error-but-no-duplicates-found of this site About Us Learn more about Stack Overflow the company Business https://gielberkers.com/fix-duplicate-entry-1-key-primary-data-migration-magento/ Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only duplicate entry takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Add UNIQUE index fails with duplicate entry error, but no duplicates found up vote 1 down vote favorite Given table: CREATE TABLE mytable ( field_a CHAR(15) NOT NULL DEFAULT '', field_b MEDIUMINT UNSIGNED NOT NULL duplicate entry for DEFAULT 0, field_c SMALLINT UNSIGNED NOT NULL DEFAULT 0, field_d SMALLINT UNSIGNED NOT NULL DEFAULT 0, field_e CHAR(1) NULL DEFAULT '', field_f SMALLINT UNSIGNED NOT NULL DEFAULT 0 ) ENGINE=MyISAM DEFAULT CHARACTER SET=UTF8 COLLATE utf8_general_ci; Required index: ALTER TABLE mytable ADD UNIQUE INDEX idx_key (field_a, field_b); Data size is about 51 Mio. rows. Following problem: Attempt 1: If I try to create the index after the data is in the table it fails with duplicate key error. A select on the failed key returns only one(!) row. [23000][1062] Duplicate entry 'aaaaaaaaaaaaaaa-11111' for key 'idx_key' SELECT COUNT(*) FROM mytable WHERE field_a='aaaaaaaaaaaaaaa' AND field_b='11111' returns 1 (!) Attempt 2: If I create the index on an empty table or make a combined private key and then put the data into the table, the table contains only 27 of 51 Mio. rows (!). Is there some kind of a limit on the unique index or a bug? I use MariaDB 10.0.20. Please help. Update 1 A count of unique rows SELECT COUNT(DISTINCT field_a,field_b) from mytable; returns 50 Mio rows. So there are about 1 Mio duplicates. This however does not explain the
entry ‘1' for key ‘PRIMARY'" in data migration in Magento Tags: fix, Magento, mysql So this morning I was boggling on a data migration with Magento that kept throwing the following MySQL error: [ERROR in query 5] Duplicate entry '1' for key 'PRIMARY' 1 [ERROR in query 5] Duplicate entry '1' for key 'PRIMARY' This was really puzzling me, since all I did was I created a database dump and tried to import it on another server. So what was going on? Well… It turns out that Magento uses a primary key with value "0" in some cases (which is bad practice). MySQL's default behaviour when inserting data in auto incremental fields with value "0" is to set it to the next possible auto increment value. Which - for an empty table - is "1". The query So… if you have the following query: MySQL INSERT INTO `core_store` (`store_id`, `code`, `website_id`, `group_id`, `name`, `sort_order`, `is_active`) VALUES (0,'admin',0,0,'Admin',0,1), (1,'store1',1,1,'Store 1',0,1), (2,'store2',2,2,'Store 2',10,1), (3,'store3',3,3,'Store 3',0,1), (4,'store4',3,4,'Store 4',0,1); 1234567 INSERT INTO `core_store` (`store_id`, `code`, `website_id`, `group_id`, `name`, `sort_order`, `is_active`)VALUES(0,'admin',0,0,'Admin',0,1),(1,'store1',1,1,'Store 1',0,1),(2,'store2',2,2,'Store 2',10,1),(3,'store3',3,3,'Store 3',0,1),(4,'store4',3,4,'Store 4',0,1); MySQL will set the store_id of line 1 to "1" instead of "0", causing the importing of line 2 to fail, because there already exists a row where store_id = 1. Oh you Magento… The fix The fix is utterly simple. It's a typical example where hours of work, stress and debugging result in a one-liner that fixes it all. Make sure in your SQL-dump file to add the following line at the top: MySQL SET sql_mode='NO_AUTO_VALUE_ON_ZERO'; 1 SET sql_mode='NO_AUTO_VALUE_ON_ZERO'; The key here is NO_AUTO_VALUE_ON_ZERO : this tells MySQL to not set the value to the value that the auto incrementer proposes, but just set the value to "0". And that's it! It really is that simp