Mysql insert duplicate entry error

Error Code: 1062. Duplicate entry ‘1’ for key ‘PRIMARY’

I have a problem on this error message, when i try this:

Error Code: 1062. Duplicate entry ‘1’ for key ‘PRIMARY’

I haven’t auto_increment data, PLEASE HELP me!

This is the table related, UFFICIO-INFORMAZIONI

INSERT INTO

9 Answers 9

The main reason why the error has been generated is because there is already an existing value of 1 for the column ID in which you define it as PRIMARY KEY (values are unique) in the table you are inserting.

Why not set the column ID as AUTO_INCREMENT ?

and when you are inserting record, you can now skip the column ID

If you are using PHPMyAdmin You can be solved this issue by doing this:

CAUTION: Don’t use this solution if you want to maintain existing records in your table.

Step 1: Select database export method to custom:

Step 2: Please make sure to check truncate table before insert in data creation options:

Now you are able to import this database successfully.

If you have a new database and you make a fresh clean import, the problem may come from inserting data that contains a ‘0’ incrementation and this would transform to ‘1’ with AUTO_INCREMENT and cause this error.

My solution was to use in the sql import file.

If you are trying to populate a table from a SQL dump, make sure that the table listed in the «INSERT INTO» statements of the dump is the same one you are trying to populate. Opening «MyTable» and importing with a SQL dump will throw exactly that kind of error if the dump is trying to put entries into «MyOtherTable», which may already have entries.

Also check your triggers.

Encountered this with a history table trigger which tried to insert the main table id into the history table id instead of the correct hist-table . source_id column.

The update statement did not touch the id column at all so took some time to find:

The trigger tried to do something similar to this:

Was corrected to something like this:

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* ;

and I’m sure that you already have a DB with this name — IN THE SAME SERVER — please check. Just change the name OR ERASE THIS LINE!

When I get this kind of error I had to update the data type by a notch. For Example, if I have it as «tiny int» change it to «small int»

I just encountered the same issue but here it seemed to come from the fact that I declared the ID-column to be UNsigned and that in combination with an ID-value of ‘0’ (zero) caused the import to fail.

So by changing the value of every ID (PK-column) that I’d declared ‘0’ and every corresponding FK to the new value, my issue was solved.

You might try to truncate table entries before loading your data into the table:

Linked

Hot Network Questions

Subscribe to RSS

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

Site design / logo © 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA . rev 2023.1.14.43159

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

Источник

Mysql insert query returns ERROR 1062 (23000): Duplicate entry ‘2147483647’ for key ‘PRIMARY’

I’ve noticed an error during an insert query in my database.

ERROR 1062 (23000): Duplicate entry ‘2147483647’ for key ‘PRIMARY’

It’s the first time I get this error maybe this suggests that some kind of limit has been reached. Anyway I’ve checked in other posts complaining for the same error and found out that triggers may be the problem. Unfortunately it’s not the case:

EDIT The structure of my users table is shown below:

How can I solve this problem?

5 Answers 5

Thank you guys for your help! There was a bad configuration of the table. The uid column had the primary key and the auto_increment attribute but in the project I’m working on users were created with a query like this:

The uid was generated by the PHP function uniqid(«»,true) and this caused the problem

As you can see a new uid, created by a query like the one above, was always greater than the previous one. Probably the auto_increment accepted only uid value greater than the last value inserted. I have been lucky for 14 registrations and then the uid value exceeded the maximum allowed by the definition of the column and caused the error.

I’ve solved the problem by removing the Primary_Key from the uid columm:

modified it again to remove the auto_increment attribute:

and finally added a new column called id in order to track and count users registrations:

In the end the error was caused by a bad organization of the database and the functions acting on it. My fault!

Источник

Methods & Solutions to Resolve Duplicate Entry Error Problem Issue

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

How to Fix Duplicate Entry Error Problem Issue

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

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
  • STEP 3. Also if you want to retrieve the recently incremented value, the below command

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
  • STEP 2. Once the backup is done, drop the database
  • STEP 3. Now recreate the database, by using the below command
  • STEP 4. Now that you have created the database import using the below command
  • 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
  • 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!

Источник

How to handle mysql #1062 — Duplicate entry error when creating a large table

I am working on a table having around 5 million records. I’m loading records from a csv file.

There is a unique column, url .

While inserting, if the url is already in the table, I want to make a change in the new url value and then do the insertion.

Example:

try inserting a record with a url of «book» . If «book» already exists, the new record should have a url of «book-1» (then «book-2» and so on)

result: the url values «book-1″,»book-2». are in the table in addition to the initial value book

I have figured out that there are 2 ways to do so.

before inserting each record: check whether the url value already exists; if it does then make the required changes in the new url value and insert. I am afraid that this will result in a poor performance.

insert records without checking if the url value already exists. If url value already exists handle the «mysql #1062 — Duplicate entry error» and make the required changes in the url value; retry the insertion.

Is this possible? If so, how?

2 Answers 2

If this is an one-off problem, I’d like to recommend an ad-hoc MySQL solution:

  1. If your table isn’t MyISAM, convert to MyISAM.
  2. Temporarily create an auto_increment integer column named url_suffix .
  3. Temporarily delete the unique constraint on the url column.
  4. Create the multiple-column index ( url , url_suffix ) and ensure that there are no other indexes that use url_suffix .
  5. Insert all of your rows, allowing duplicate URLs. You’ll notice that the auto_increment url_suffix column is keyed on the url now. So, the first particular url will have url_suffix of 1 and the next 2 , and so on.
  6. Do an update like the following, then delete your temporary url_suffix column and put your unique constraint back.

Query to update all the rows:

In fact, you could skip step 6, keep the auto_increment field so you could easily add duplicate URLs in the future, and simply fetch your URLs like this:

Источник

Читайте также:  Tea error jack stauber перевод
Smartadm.ru
Adblock
detector