One of my teammates reached out for help. There was a bug in some code they were modifying, and they had no idea what was causing it.

Here’s what happened.

There was a query that ran periodically to fetch data, and the logs recorded how many rows it returned. The problem was that it kept coming back with zero rows.

Strangely, when I ran the exact same query directly in a database tool, I got two rows back.

In other words, the exact same query returned zero rows when run at runtime on the server, but two rows when run directly in the database tool.

My first suspicion was that the query was misconfigured, which caused the object binding through the ORM to fail. However, an error should have been returned if the object binding failed. There were no errors in the code in question.

My next suspicion was that the running process did not match the version of the code we thought was deployed. I checked with the team, but that was not the case.

I needed more clues.

I first SSH’d directly into the pod where the code was running, wondering whether this might be an environment issue. I installed the MySQL CLI there and ran the query directly inside the pod, and it correctly returned two rows. In other words, it wasn’t an environment issue.

I tried this and that for a while, but didn’t see anything unusual. I pulled up the query in question in the IDE and started to take a closer look.

The table referenced by the query had the following columns: start_time, end_time, and timezone. The columns start_time and end_time stored time data in the format hhMM. The query was to retrieve rows whose current time falls between start_time and end_time based on the corresponding timezone.

The part that changes the current time to timezone is shown below.

... CONVERT_TZ(NOW(), 'GMT', timezone), ...

Since I was unfamiliar with the CONVERT_TZ function itself, I checked the MySQL official documentation for a definition.

CONVERT_TZ(*dt*,*from_tz*,*to_tz*)

CONVERT_TZ() converts a datetime value dt from the time zone given by from_tz to the time zone given by to_tz and returns the resulting value.

In other words, the function treats the datetime dt as being in from_tz and converts it to to_tz. There didn’t seem to be anything inherently wrong with using the function. But it suddenly occurred to me that if the value returned by NOW() was not actually in GMT, the query might fail to return rows it should have returned.

I went back to the MySQL documentation and found that the NOW() function is affected by the session’s timezone setting. On the backend, we don’t suddenly change the session timezone in the middle of a query. But the session timezone can be set in the connection string when the application first connects to the database. I checked the connection string, and sure enough, the timezone was set to Asia/Seoul.

parseTime=true&charset=utf8&loc=Local&time_zone=%27Asia%2FSeoul%27

Gotcha.

I removed the timezone setting from the connection string and ran the code again. Sure enough, it correctly returned the two rows.