MySQL - Query logged but not inserted in database

Question

I'm working on an automated system on LAMP stack and experiencing several query misses, i.e. the query gets executed by PHP but its not present in the database and there are no errors.

So I enabled logging all queries in mysql to see if the query reaches there at all and sure enough, the query is there. It happened right now so I'll explain with an example:

(BTW don't try to find errors in the queries. There aren't any. The same queries do execute most of the time)

PHP executes:

$saveOrderQ = "INSERT INTO orders (order_id, parent_order, date_created, status, type, phase, ticker, quantity, entry_price, tp1, tp2, strategy, relative_volume, distance_from_close) VALUES ({$order->order_id}, 0, '$now', 'OPEN', 'BUY', 0, '$ticker', $positionsSize, $price, $tp1, $tp2, {$tickerAttr['strategy']}, $relativeVol, $distanceFromClose)";
writeToLog($saveOrderQ); // I get the query in the log. This function's log is seperate from mysql log
$saveOrder = mysqli_query($connect, $saveOrderQ);
if(!$saveOrder){
    writeToLog("Failed to insert file 30daybreakout.php Line 59. Error: ".mysqli_error($connect)."\nQuery: $saveOrderQ"); // no error received
}

MySQL log around the same time:

2020-04-13T09:23:02.222315+05:30    127059 Query    SELECT order_id FROM orders WHERE ticker = 'NSE:BALMLAWRIE' AND DATE(date_created) = '2020-04-13' AND parent_order = 0 AND phase < 3
2020-04-13T09:23:02.356930+05:30    127059 Query    INSERT INTO orders (order_id, parent_order, date_created, status, type, phase, ticker, quantity, entry_price, tp1, tp2, strategy, relative_volume, distance_from_close) VALUES (200413000329794, 0, '2020-04-13 09:23:01', 'OPEN', 'BUY', 0, 'NSE:BALMLAWRIE', 54, 91.3, 94.95, 98.6, 1, 108.87022900763, 6.5966141272621)

This entry is simply not present in the orders table.

My best guess is that somehow the order table is locked but I don't know for sure. How do I troubleshoot it?

Answer 1

Also check for "Warnings". Do this after you have checked for errors, and before running any other SQL, by executing SHOW WARNINGS LIMIT 11 and print results (if any). Warnings are not as severe as errors, but sometimes they give a clue that things are not as expected, thereby leading to discovering the real error.

Only recently did PHP switch from 32-bit builds to 64-bit. That is, it has been unsafe to represent large numbers in PHP. What version of PHP are you using? Since the problem occurs "sometimes", it may be that you are getting rounding errors above 31, 32, (INT sizes) or 53 bits (DOUBLE size). To help with that, please provide some specific numbers and say which worked and which failed. (200413000329794 takes 48 bits, so it won't loose anything if it goes through DOUBLE in PHP.)

Putting quotes around numbers in MySQL won't matter: WHERE foo = 123 and WHERE foo = "123" work the same if foo in integral, not char.

What value of autocommit do you use? In my opinion, OFF is error-prone because you must remember to COMMIT eventually. And it is too easy to forget to do so, or take a branch sometimes that avoids the COMMIT. Then, when the connection is closed, that open transaction is automatically ROLLBACKd.

(Some more "unsolicited advice") A Comment suggested a change to make the date "sargable". It needs some expansion:

AND date_created >= '2020-04-13'
AND date_created  < '2020-04-13' + INTERVAL 1 DAY

(Some more "unsolicited advice") With that, this might be the optimal index:

INDEX(ticker, parent, date_created)

More (Mostly "fishing as straws")

When inserting a numeric constant into a numeric field, or comparing such in a WHERE clause, it does not matter whether the numeric constant is quoted.

Suggest turning on the "general log". Although your logging mechanism seems equivalent, maybe there is something subtle that the general log will point out.

With autocommit=ON and no explicit transactions, I am having trouble imagining how a deadlock could occur. Please capture SHOW ENGINE INNODB STATUS; now and any time you suspect that the problem has recurred.

Please provide SHOW CREATE TABLE and the query that declares that the row is missing. Misuse of FLOAT when searching for non-integral numbers can lead to rounding issues.

Answer 2

I think you must give your table structure or you can try to run this query log in your MySQL GUI,

INSERT INTO orders (order_id, parent_order, date_created, status, type, phase, ticker, quantity, entry_price, tp1, tp2, strategy, relative_volume, distance_from_close) VALUES (200413000329794, 0, '2020-04-13 09:23:01', 'OPEN', 'BUY', 0, 'NSE:BALMLAWRIE', 54, 91.3, 94.95, 98.6, 1, 108.87022900763, 6.5966141272621)

I think there is something error like your query doesn't fulfill the unique key table condition or something like that.

Answer 3

the problem in such cases can be one of the followings:

1. data type mismatch
2. primary key violation
3. foreign key valuation the most likely to happen in your case is the 2nd or the 3rd one.

Just for testing :try to use INSERT ON DUPLICATE KEY UPDATE .... : instead of insert only in your SQL. .. let us know.

Answer 4

This is not a definitive solution but for now I've used the following code to build some redundancy in the system and hopefully get around the problem.

$saveOrderCount = 0;
do {
    $saveOrder = mysqli_query($connect, $saveOrderQ);
    $saveOrderCount++;
} while(!mysqli_affected_rows($connect) and $saveOrderCount < 3); //Will retry upto a max of 3 times

if($saveOrderCount >= 3){
    writeToLog("Failed to insert in file test.php Line 59. Error: ".mysqli_error($connect)."\nQuery: $saveOrderQ");
}