Error: Duplicate entry '0' for key 'PRIMARY'

Question

I can't resolve my problem, this is the error from mysql that I'm getting:

I can edit and update my data when I've got one record in the database but when I add two rows, I get the error.

Some pictures from database

And when I change the row, row ID goes down to 0 and that's is a problem as I can't edit other rows.

When i try to change row, first row ID goes down to 0 Database

enter image description here

CREATE TABLE `dati` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) NOT NULL,
 `value1` varchar(255) NOT NULL,
 `value2` varchar(255) NOT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 PACK_KEYS=1

Update Code:

<?php // Izlabot datus datubāzē!
$titletxt = $_POST['title_edit'];
$value1 = $_POST['value1_edit'];
$value2 = $_POST['value2_edit'];

if(isset($_POST['edit'])){
$con=mysqli_connect("localhost","root","","dbname");
if (mysqli_connect_errno())
  {
  echo "Neizdevās savienoties ar MySQL: " . mysqli_connect_error();
  }
$sql="UPDATE dati SET ID='$ID',title= '$titletxt',value1='$value1',value2='$value2' WHERE 1";
if (!mysqli_query($con,$sql))
  {
  die('Error: ' . mysqli_error($con));
  }
echo '<script>
        alert(" Ieraksts ir veiksmīgi labots! ");

        window.location.href = "index.php";
    </script>';
mysqli_close($con);
}
?>

From form:

<?php
            $con=mysqli_connect("localhost","root","","dbname");
            if (mysqli_connect_errno())
              {
              echo "Neizdevās savienoties ar MySQL: " . mysqli_connect_error();
              }
            $result = mysqli_query($con,"SELECT * FROM dati");
            while($row = mysqli_fetch_array($result))
              {
              echo "<tr>";
                  echo "<td><input id='titled' type='text' name='title_edit' value='" . $row['title'] . "'></td>";
                  echo "<td><input id='value1d' type='text' name='value1_edit' value='" . $row['value1'] . "'></td>";
                  echo "<td><input id='value2d' type='text' name='value2_edit' value='" . $row['value2'] . "'></td>";
                  echo "<input type='hidden' name='id' value='" . $row['ID'] . "'>";
                  echo "<td><button name='edit' id='edit_btn' class='frm_btns' value='" . $row['ID'] . "'>Edit</button></td>";
              echo "</tr>";
              }
            mysqli_close($con);
        ?>

It couldn't read the value of ID, as 0 was returned.

http://www.bildites.lv/images/jvacde0uqy7dahton1w2.jpg Photo of schema — Arvis, Sep 08 '13 at 21:25
If you edit in phpMyAdmin it could be a bug there, which version do you use? — t.niese, Sep 08 '13 at 21:28
SHOW CREATE TABLE dati; run this query post the results in the main post.That is the schema. — Mihai, Sep 08 '13 at 21:32
you have `ID='$ID'`, but `$ID` is not set to anything, so this becomes `ID=''` which would result in `ID=0`. — t.niese, Sep 08 '13 at 21:41
Also,is there a need for auto increment to be 3?If not use ALTER TABLE doti AUTO_INCREMENT = 1; to increment with 1. — Mihai, Sep 08 '13 at 21:45
@Mihai well that's the next value to insert. Because the largest `ID` is `2` (from the screenshots) the `AUTO_INCREMENT` value is currently `3`. — t.niese, Sep 08 '13 at 21:48
@t.niese No it means it will autoincrement from 3 to 3, so its 2 the next will be 5 and so on. — Mihai, Sep 08 '13 at 21:50
@Mihai No, it doesn't. It means the next row inserted will get `ID=3`. — ypercubeᵀᴹ, Sep 08 '13 at 21:52
@ypercube Can you set it at table creation,in the sense I mean?I thought that was it. — Mihai, Sep 08 '13 at 21:54
That is controlled by [`auto_increment_increment`](http://dev.mysql.com/doc/refman/5.5/en/replication-options-master.html#sysvar_auto_increment_increment) which is a server wide variable (it affects all tables in all databases in the server) — ypercubeᵀᴹ, Sep 08 '13 at 21:57

score 48 · Answer 1 · edited May 23 '17 at 12:17

48

For those arriving at this question because of the question title (as I did), this solved my problem:

This error can indicate that the table's PRIMARY KEY is not set to AUTO-INCREMENT, (and your insert query did not specify an ID value).

To resolve:

Check that there is a PRIMARY KEY set on your table, and that the PRIMARY KEY is set to AUTO-INCREMENT.

How to add auto-increment to column in mysql database using phpmyadmin?

edited May 23 '17 at 12:17

Community

1
1

answered Nov 13 '15 at 05:53

cssyphus

37,875
18
96
111

2

Also make sure you're on the `right` database. I need coffee. #lame – Alwin Kesler May 04 '18 at 14:48
2

And you need to remove any row that might have gotten added with an ID of 0 because auto incrementing starts at 1 and it'll try to fix that 0 and make it a 1, but chances are there already is an ID of 1 so you'll see an error about duplicate reindexing. – pinksharpii Sep 28 '20 at 15:06

score 5 · Answer 2 · answered Mar 24 '19 at 01:49

The error log like (In my case), I'm using Aurora DB:

PHP message: WordPress database error Duplicate entry '0' for key 'PRIMARY' for query INSERT INTO `date173_postmeta

How to fix it using MySQL Workbench:

1- Connect at your DB, and go to the table with the issue, in my case date173_postmeta

2- Select the tools icon:

3- In the windows/tab at right, select the AI checkbox and click on Apply button:

Following the last steps my issues gone.

score 4 · Answer 3 · answered Sep 08 '13 at 22:01

The problem is that your code attempts to change every row in the data changing the primary key to the value in $ID. This is not set anywhere in your code, and presumably is being cast as 0

$sql="UPDATE `dati` SET `ID`='$ID',`title`= 
'$titletxt',`value1`='$value1',`value2`='$value2' WHERE 1";

The primary key value should be sent to the form and returned so it can be processed by your code, but the value should be retained, hence....

$sql="UPDATE `dati` SET `title`= 
'$titletxt',`value1`='$value1',`value2`='$value2' WHERE `ID`=$ID";

You should also read up on MySQL injection - even after you've fixed the errors here, anyone can do just about anything they want with your database.

score 3 · Answer 4 · answered Sep 08 '13 at 21:43

3

Try this:

ID int(11) PRIMARY KEY AUTOINCREMENT(1,3)

answered Sep 08 '13 at 21:43

Stefano Munarini

2,711
2
22
26

mh, correct, problem is for $ID, they give the same numbers :/ – Arvis Sep 08 '13 at 22:13
$sql="UPDATE `dati` SET `title`= '$titletxt',`value1`='$value1',`value2`='$value2' WHERE `ID`='$ID'"; With this he do nothing, all compile but information wont change. – Arvis Sep 08 '13 at 22:23
Please add some explanation to your answer - what **exactly** should be tried, and why should that solve the problem? Keep in mind that such an explanation helps others to understand your answer – Nico Haase Jan 18 '20 at 21:17

a14m · Answer 5 · 2013-09-08T21:46:43.310

2

The problem in set ID = $ID

Try removing it so the code should be

$sql="UPDATE `dati` `title`=        '$titletxt',`value1`='$value1',`value2`='$value2' WHERE 1";

Be sure to change this where cause it'll update ever row with these values

edited Sep 08 '13 at 21:46

answered Sep 08 '13 at 21:41

a14m

7,808
8
50
67

when i add this $sql="UPDATE `dati` `title`= '$titletxt',`value1`='$value1',`value2`='$value2' WHERE 1"; they change all rows not single, like a copy. – Arvis Sep 08 '13 at 21:47
@ArvisAvens that what your query in the code currently does. You just have a `WHERE 1` so you don't limit the update to a row. – t.niese Sep 08 '13 at 21:50
@t.niese i need to add LIMIT 1? – Arvis Sep 08 '13 at 21:53
@ArvisAvens make it `where ID=$ID` but make sure the id was sent correctly in the post aka `$ID=$_POST['ID']` – a14m Sep 08 '13 at 21:56

score 2 · Answer 6 · answered Feb 04 '16 at 06:31

2

Just make sure that your primery keys are also A-I.

answered Feb 04 '16 at 06:31

Eyal Sooliman

1,876
23
29

score 1 · Answer 7 · answered Jul 29 '21 at 18:05

I'd been struggling to fix this. My tables had auto increment (AI) switched on Before I started tinkering with records I tried a simple repair in phpMyAdmin. Go to the SQL tab and run each command in turn.

REPAIR TABLE wp_options REPAIR TABLE wp_users REPAIR TABLE wp_usermeta

This did the trick for me and allowed me to login.

score 0 · Answer 8 · edited Sep 07 '21 at 05:13

0

i am using phpmyadmin, so go to db , search for wp_postmeta tabel add AI(auto-increment) to meta_id save the changes

edited Sep 07 '21 at 05:13

NcXNaV

1,657
4
14
23

answered Sep 06 '21 at 11:35

Rupesh Jha

1

Error: Duplicate entry '0' for key 'PRIMARY'

8 Answers8

Linked