I'm trying to migrate from drupal_write_record() to db_insert() / db_update(), but db_insert() returns a weird key instead of the last inserted key.

On an empty table, I'm doing an insert, and instead of real row number (suppose to be 1, right?), I'm getting a wrong one.

$fields['created'] = REQUEST_TIME;
$fields['uid'] = 1;
$insert = db_insert('table')->fields($fields);
$last_insert_id = $insert->execute(); // suppose to be 1, but = 43587087

Any ideas? Thanx.


The autonumber isn't related to the number of records in the table, but rather the AUTO_INCREMENT value of the table's primary key column.

If you have a completely new table, and add 100 rows, the next ID will be 101. If you just remove all the rows in that table the next ID will still be 101, unless you TRUNCATE the table, or reset the AUTO_INCREMENT column.

For an easy way to test, fire up your preferred DB client, and insert a record into the database. If you get 1 as the next insert ID, there's a bug with your Drupal installation/DB engine. If not, it's just what's mentioned above.

| improve this answer | |
  • I'm referring to a new truncated table, and the ID I can see in the DB is 1. So what bug are you talking about? using drupal_write_record() works perfect, returning the correct ID on the same installation / db engine. – alechko May 23 '13 at 9:46
  • That doesn't really make any sense, drupal_write_record() uses the return value from db_insert() explicitly, so there wouldn't logically be any difference. I'm not talking about any bug in particular, I just know this isn't an issue on a standard Drupal/server install so there must be something non-standard about your setup – Clive May 23 '13 at 9:52
  • No it's not. drupal_write_record() uses db_insert() for the InsertQuery, but uses Database::RETURN_INSERT_ID to return last ID, while db_insert() uses InsertQuery::execute() function to return ID. – alechko May 23 '13 at 9:55
  • 2
    No it doesn't, it uses if ($query_return = $query->execute()) {. The return option is checked against Database::RETURN_INSERT_ID, but that constant is not used to determine the new ID in any way – Clive May 23 '13 at 9:56
  • Just wondering - is there only one AI column in this table? And key isn't composite? Drupal don't like composite PKs. – Mołot May 23 '13 at 10:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.