0

I don't know why, my query is doing his job, but sometimes ID auto increment don't works like it should.

$stmt = $this->mysqli->prepare("INSERT INTO `user` (`name`, `date`) VALUES ('$name','$timestamp') ON DUPLICATE KEY UPDATE `name` = VALUES(name), `update` = '$timestamp'");
$stmt->execute();

// My MySql table
---------------------
id | name | date                |   update                |
1  | John | 2014-10-07 15:42:14 |   0000-00-00 00:00:00   |
---------------------

When I insert John again

// he will do update good
---------------------
id | name | date                |   update                |
1  | John | 2014-10-07 15:42:14 |   2014-10-07 15:43:20   |
---------------------

Now, comes bug, when I insert Anie for example I will get:

// But now he will skip ID number 2 - Why ? and how to fix this ?
---------------------
id | name  | date                |   update                |
3  | Annie | 2014-10-07 15:44:14 |   0000-00-00 00:00:00   |
---------------------

ID is set like primary key, unique, auto_increment

and

I am using MySqli INNODB engine, and PHP 5.5

Ivan Vulović
  • 2,147
  • 6
  • 27
  • 43
  • 7
    It works exactly as it should. Your `INSERT` fails, at which point `UPDATE` occurs - at this point, an `auto_increment` is "spent" (had the insert succeeded, the record would store the calculated auto_increment number as PK). `auto_increment` values are never reused, and MySQL should never be forced to reuse them. Bottom line, everything works fine and as intended. Now, you probably think that it's "ugly" to have gaps - that's not what auto_increment should be used for. It exists only to act as a primary key, not to display sequential numbers. – N.B. Oct 07 '14 at 14:37
  • @N.B. Thanks, that was my question, I was afraid that is something wrong with my query or something else. I don't have problem to look ugly. – Ivan Vulović Oct 07 '14 at 14:42

0 Answers0