I am working on a project, in which we use AWS Aurora MySQL version 8 for DB, and AWS Lambda Function for computing.
The issue I face is when executing the following query:
SELECT number, title, status, s.service_name AS client_name, s.service_id AS client_id, JSON_UNQUOTE(JSON_EXTRACT(assigned_to, '$[0]')) AS assigned_to FROM incidents i LEFT JOIN services s ON i.client_id = s.service_id WHERE alert_source_id = 'some_value' AND integration_id = 'some_value'
The database intermittently returns empty data for the same record. what I mean by 'intermittently' is it is not regular, and it happens a few times, if I have executed the query for 100 times, I might face this case one time.
I will give an example, but the issue can take many shapes, suppose we have a timeline A, B, and C.
- At the 'A' point in time, the query is executed and returns the record data as expected.
- At the 'B' point in time, the same query is executed, but it returns an empty result rather than expected data.
- At the 'C' point in time, the same query is executed and returns the expected data.
My question is what does make MySQL can't find an existing record in the 'B' point in time, however, it can find it at earlier and later times (the 'A' and the 'C' point in time)?
The table is using InnoDB.