1

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.

1 Answer 1

2

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.

11
  • 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
    Commented May 23, 2013 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
    Commented May 23, 2013 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
    Commented May 23, 2013 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
    Commented May 23, 2013 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
    Commented May 23, 2013 at 10:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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