What could cause MySQL to intermittently fail to return a row?

I have a MySQL database that I am running very simple queries against as part of a webapp. I have received reports from users starting today that they got an error saying that their account doesn’t exist, and when they log in again, it does (this happened to only a few people, and only once to each, so clearly it is rare). Based on my backend code, this error can only occur if the same query returns 0 row the first time, and 1 row the second. My query is basically SELECT * FROM users WHERE username="...". How is this possible? My suspicion is that the hard disk is having I/O failures, but I am unsure because I would not expect MySQL to fail silently in this case. That said, I don’t know what else it could be.

Answer

This could be a bug with your mysql client (Though I’m unsure as to how the structure of your code is, it could just be bad query). However let’s assume that your query has been working fine up until now with no prior issues, so we’ll rule out bad code.

With that in mind, I’m assuming it’s either a bug in your mysql client or your max connection count is reached (Had this issue with my previous host – Hostinger).

Let’s say your issue is a bug in your mysql client, set your sessions to per session basis by running this

SET SESSION optimizer_switch="index_merge_intersection=off";

or in your my.cnf you can set it globally

[mysqld] optimizer_switch=index_merge_intersection=off

As for max connection you can either increase your max_connection value (depending if your host allows it), or you’ll have to make a logic to close the mysql connection after a query execution.

$mysqli->close();