1

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!

2

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.

  • I know the query is not the issue, otherwise it wouldn't work at any point in time, the query does depend on a variable being passed to the script, however, other queries that use this same variable are executed in the script successfully before the query that fails, well, fails. Still have no idea what is causing it, waiting for it to happen again now that I have the general log enabled – patrickdamery May 12 '16 at 4:35
  • I still advocate adding some logic to detect the null case and logging lots of information about the variables, query, values, etc. The query log may not show anything more than that something is null. And if you are waiting a long time for the next error, you'll be waiting twice as long for the second one. – mbrandeis May 12 '16 at 18:14
1

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 my.cnf.

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.

  • I've enabled general logs already. I'm waiting for the error to happen again so I can see if there is anything useful there, the thing is this error happens very rarely, so I'll be waiting a while. I checked dmesg and can't find anything interesting there either, the only line I could find relating to mysql was this (although I'm pretty sure that's normal apparmor behaviour): type=1400 audit(1462745659.434:82): apparmor="STATUS" operation="profile_replace" profile="unconfined" name="/usr/sbin/mysqld" pid=8917 comm="apparmor_parser" – patrickdamery May 8 '16 at 22:53
0

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!

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.