php - MySQL - Query logged but not inserted in database
Get the solution ↓↓↓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
Solution:
Also check for "Warnings". Do this after you have checked for errors, and before running any other SQL, by executingSHOW 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
andWHERE foo = "123"
work the same iffoo
in integral, not char.
What value ofautocommit
do you use? In my opinion,OFF
is error-prone because you must remember toCOMMIT
eventually. And it is too easy to forget to do so, or take a branch sometimes that avoids theCOMMIT
. Then, when the connection is closed, that open transaction is automaticallyROLLBACKd
.
(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 aWHERE
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.
Withautocommit=ON
and no explicit transactions, I am having trouble imagining how a deadlock could occur. Please captureSHOW ENGINE INNODB STATUS;
now and any time you suspect that the problem has recurred.
Please provideSHOW CREATE TABLE
and the query that declares that the row is missing. Misuse ofFLOAT
when searching for non-integral numbers can lead to rounding issues.
Answer
Solution:
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
Solution:
the problem in such cases can be one of the followings:
- data type mismatch
- primary key violation
- 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
Solution:
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");
}
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: xml parsing error: no root element found
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.