I'm not sure what exactly is causing this but on occasions a mysql query that I know has to return results will sometimes return null as a result. I've seen this happen twice before but as I couldn't find anything in the error log I just updated everything and just assumed it should be ok now, but I just recently saw the same error happen again. Is there any reason why Mysql would return null as a result even when the query should return an actual value? At the moment I'm kind of stumped and my best guess would be maybe data corruption? Can't find much on google about this issue so I decided to ask for advice before I go off buying a new hard disk for my computer. Any insight you can give me into this problem will be greatly appreciated, thanks!
Sorry for posting this is an answer, my reputation is not high enough to post a comment.
Without more details on your query it will be difficult to give an answer.
If the query is something as simple as "select * from table" then it would be strange to get random nulls.
If your query is using some kind of where clause it could be that your query is randomly using the wrong value. You should try catching the null responses and logging the query that you just tried to run.
Your best bet is to enable the general query log and then inspect both the query log and the error log when you receive an unexpected NULL result. To enable the general log, set this in the [mysqld] section of your
general_log_file = /path/to/query.log general_log = 1
Keep in mind that unlike the binlog, all queries are written to the general log, even SELECT queries, so the log can consume a lot of space. So if you do this, keep an eye on things.
Additionally, check the output of
dmesg for any filesystem-related errors.
Ok so I have figured this one out. Mysql was not the problem as I had initially suspected. The php script that is executed returns an xml file, and I was using firefox to run the web app that executes this script via an ajax request. Turns out firefox seems to have a bug that sends multiple ajax request when it is dealing with xml files, which was causing the database to update and then when the second request came in the result was null (the update would render the query's results as null). I have fixed this by making sure that each request is unique.
Thanks for the help everyone!