4

EDIT This question is almost a duplicate of this question from 2010, except that I'm not using transactions, so none of the answers make sense.

EDIT I am not asking how to reset the primary key. I am asking why the primary key is not auto-incrementing by consecutive numbers.

I'm using xmlhttprequest to capture as-you-type information and store it in my Mysql database.

HTML

<input type="text" name="example" onKeyUp="saveData(this)">

JAVASCRIPT

function saveData(a){
    var z;
    var d=new FormData();
    d.append('data',a.value);
    if(window.XMLHttpRequest){z=new XMLHttpRequest();}else{z=new ActiveXObject("Microsoft.XMLHTTP");}
    z.onreadystatechange=function(){if(z.readyState==4&&z.status==200){if(z.responseText=='false'){alert(z.responseText);}}}
    z.open("POST", '/scripts/save_data.php');
    z.send(d);
}

PHP For testing purposes I'm hard-coding the uid user id.

<?php
$stmt=$pdo->prepare("INSERT INTO `user_data` (`uid`, `data`) VALUES (:myuid,:mydata) ON DUPLICATE KEY UPDATE `data`=values(`data`)");
$stmt->bindValue('myuid',1,PDO::PARAM_INT);
$stmt->bindParam('mydata',$_POST['data'],PDO::PARAM_STR);
$stmt->execute();

My MySQL table has...

id, primary INT(10) unsigned auto-increment
uid, INT(10) unsigned
data, VARCHAR(24)

Everything works as expected, but I'm not seeing the values of id increment sequentially as I would expect (1, 2, 3, etc.). It's jumping (1, 20, etc.) as if lines have been created, then dropped, to form the final line. Can anyone explain this behavior? Need I worry about it?

JBH
  • 1,823
  • 1
  • 19
  • 30
  • I can't see where you echo anything in PHP script. What do you get by POSTing to `/scripts/save_data.php`? – lilezek Jul 15 '17 at 19:46
  • Probably comes from the `INSERT INTO ... ON DUPLICATE KEY UPDATE ...` It seems like a key is reserved for the insert and then discarded on the update. Btw. I wouldn't write into the DB on each key up event. – maraca Jul 15 '17 at 19:53
  • Possible duplicate of [Resetting the primary key to 1 after deleting all the data](https://stackoverflow.com/questions/6972275/resetting-the-primary-key-to-1-after-deleting-all-the-data) – James Jul 15 '17 at 20:00
  • 2
    You don't have a uniqe key in your query. The update part will never happen. The ID will just auto-increment each time. – colburton Jul 15 '17 at 20:19
  • 1
    @colburton Unless perhaps `uid` is a UNIQUE KEY that wasn't mentioned in the question? –  Jul 15 '17 at 20:58
  • UID is a unique key. Bear in mind the code works just fine. It's just skipping large numbers when the auto-increment value is assigned. Litterally by counts of 20 or more. The PHP script echos nothing, it only posts the query. @maraca, that's an interesting idea, but why would it consume more than one value? (BTW, it's worth noting that my test, above, is a simplification of my actual code, which uses three UNIQUE keys alongside the PRIMARY key.) – JBH Jul 15 '17 at 21:58
  • @James, this is not a duplicate. I am not trying to reset the value of the primary index. I'm just trying to figure out why it's not auto-incrementing consecutively. – JBH Jul 15 '17 at 22:00

1 Answers1

1

Since you clarified, that your INSERT is creating duplicate entries with the uid, this is the issue:

MySQL reserves the next auto increment value and issues an end table lock before even trying to insert. The insert then fails and the UPDATE part happens. But since the auto increment already was moved up and another process could have picked the next number, it will not be decreased again. So you get a hole.

This of course, creates only one "hole" at a time, but since you are doing this on keyup it happens very often. And because of the hard coded uid it happens also for every concurrent request.

colburton
  • 4,685
  • 2
  • 26
  • 39
  • Secondary question: do I need the primary key since I have unique keys that explicitly define uniqueness? It would seem that I'd never actually fill up the INT(10) primary, but hey, the site may be up for 50 years... Or should I just ignore the whole thing? – JBH Jul 16 '17 at 04:37
  • You should not include keys (or columns for that matter) that are not needed (performance). If you can pick another column (can also be a combination of columns) you should do that. An `id` is usually used as foreign key, but you might also not need that. If you use `INNODB` however, it will always create an auto increment index and just not show it to you, if you don't add one yourself. – colburton Jul 16 '17 at 04:41