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.