The database skipped an insert

MySQL & MariaDB Other MySQL® Questions
1

Hello.everyone!

The PHP program inserts a piece of data into a table, but the data is not written, and the table’s auto-increment ID is skipped on number, and the binlog does not record this data with that auto-increment ID.PHP program returned a null value.We are not using MySQL transactions.Write once, commit once.No one deleted the data.The database log does not report any errors.

Server version is 5.7.20-18-log.

Part of the PHP code is as follows:

Founction insertId(){
if( $result = mysql_insert_id($this->LinkID) ) {
Return $result;
} else {
Return False;
}
}

Can anyone tell me me what happens under such circumstances and how to find out the cause of this matter? Or what other information do I need to provide?
Thank you very much for any help.
Sorry for my poor english.

2

Hi Keith. If there is nothing in the binary log then you probably not committing the transaction. I know you said you are not using transactions, but does that mean you’ve set autocommit=0? If so, then you must use transactions or set autocommit=1. For more diagnosis, I would enable the general query log (SET GLOBAL general_log=ON) and then run your PHP code. Use “tail -f” on the general log and watch for your query. If you do not see it at all in the general log, then you have other issues. If you do see it in the general log, but do not see it in the table or in the binary log, then you have a transaction issue.

3

Hi matthewb. The autocommit is already on.This problem only happens once, our database and program have been running for a long time,I will enable the general query log ,but not sure if we can reproduce the problem.Can you tell me under what circumstances the database may cause such a problem?

Thank you very much!

4

Hi Keith,
Not committing the transaction is the only way I can think of how data would not be present in the table and also not be in the binary log. Do not have the general query log enabled for long term. That will cause excessive IO on the disk. The GQL should only be enabled for short term durations to diagnose issues like this.