php: mysql_insert_id() issues

Question

Can the php function mysql_insert_id() return no result after processing the INSERT query in mysql db?

Just to clarify. There was a script performing by cron on the production site. It contained a cycle for generating invoices for users. Each iteration consists of a INSERT db query and the mysql_insert_id() operation going right after the query - to fetch the generated invoice number. A set of iterations were performed without fetching the last inserted number.

Can it be caused by high db server load or by some other reasons that are not linked to the problem at the php code site?

Any help would be appreciated!

Answer 1

Offhand, I can think of a few cases where MySQL wouldn't return the ID:

• The table you're inserting into doesn't have an AUTO_INCREMENTed primary key.
• You're inserting multiple rows at once.
• You're calling mysql_insert_id() from a different connection than the INSERT query was executed.
• The INSERT query didn't succeed (for instance, it encountered a deadlock). Make sure you are checking the return value from mysql_query(), then use mysql_errno() and mysql_error().

MySQL docs have a full list of conditions and details on how this function works.

Of course, it's also possible there is a bug in MySQL, which would depend on which version of MySQL you are using.

Answer 2

If you're running the commands through a shell script, and run them both separately as in;

mysql -e "insert into table ( field1 ) values ( 'val1' );" "database"

lastId=`mysql -e "select last_insert_id();" "database"`

Then that won't work as the second call makes a new connection to the server. You need to do something like the following, as it is all done within a single database call / connection;

lastId=`mysql -e "
                  insert into table ( field1 ) values ( 'val1' );
                  select last_insert_id();
                 " "database"`

You'll need to look up the extra parameters required for the MySQL command to remove formatting and header row - I'm afraid I can't remember them off the top of my head!