Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.13
-
None
-
None
-
Windows Server
Description
We are observing queries that return inconsistent query results. The query in question selects records within a period of time (e.g., month) based on a datetime column.
The result set is missing random records within the queried time period. It appears that the inconsistent result is produced when the query uses an index containing the datetime column. When the index is not used, the result seems to contain all records.
Dropping and recreating the index caused the result to be complete again.
However, this has NOT solved the problem permanently as the wrong behaviour has reappeared afterwards. On second appearance, we have used OPTIMIZE table on the table in question which has again resolved the problem but it manifested itself again.
CHECK TABLE returned no errors for the affected table.
Environment:
- MariaDB Version: MariaDB 10.5.13
- MariaDB Storage engine: InnoDB
- MariaDB Collation: utf8mb4_unicode_ci
- OS: Windows Server
The table in question is defined as follows (left out some columns for better readability):
CREATE TABLE `t_taskperformed` ( |
`ID` varchar(41) COLLATE utf8mb4_unicode_ci NOT NULL, |
`CREATED_TIMESTAMP` datetime NOT NULL, |
`CREATED_USER` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, |
`DELETED` bit(1) NOT NULL, |
`MODIFIED_TIMESTAMP` datetime NOT NULL, |
`MODIFIED_USER` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, |
`TASK_PERFORMED_ENDED` datetime NOT NULL, |
`TASK_PERFORMED_STARTED` datetime NOT NULL, |
`FK_TASKPLANNEDBASE_ID` varchar(41) COLLATE utf8mb4_unicode_ci NOT NULL, |
PRIMARY KEY (`ID`), |
KEY `I_TASKPERFORMED_BASE_AND_CREATED_TIMESTAMP` (`FK_TASKPLANNEDBASE_ID`,`CREATED_TIMESTAMP`), |
KEY `I_TASKPERFORMED_TASK_PERFORMED_ENDED` (`TASK_PERFORMED_ENDED`), |
KEY `I_TASKPERFORMED_TASK_PERFORMED_STARTED` (`TASK_PERFORMED_STARTED`) USING BTREE, |
CONSTRAINT `FK_TASKPERFORMED_BASE_AND_SYNC_ORDER` FOREIGN KEY (`FK_TASKPLANNEDNBASE_ID`) REFERENCES `t_taskplannedbase` (`ID`), |
CONSTRAINT `FK_TASKPERFORMED_FK_TASKPERFORMED_GROUP_ID` FOREIGN KEY (`FK_TASKPERFORMED_GROUP_ID`) REFERENCES `t_taskperformed_group` (`ID`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
The following query returns 191 results which is not correct. As far as I can see, the result is missing records for random dates during the month (currently, some records for March, 8th).
Please note that this query uses joins to other tables which I have omitted from the description above.
SELECT * |
from t_taskperformed taskperfor0_ |
inner join t_taskplannedbase taskplanne1_ on taskperfor0_.FK_TASKPLANNEDBASE_ID=taskplanne1_.ID |
inner join t_module ccmodule2_ on taskplanne1_.FK_MODULE_ID=ccmodule2_.ID |
where ccmodule2_.MODULE_KEY='MK1' |
and (taskplanne1_.FK_PERSON_ID in ( /* List of 1900 Person IDs */)) |
and taskperfor0_.TASK_PERFORMED_STARTED>='2024-03-01 00:00:00' |
and taskperfor0_.TASK_PERFORMED_STARTED<'2024-04-01 :00:00:00' |
and taskperfor0_.DELETED=0 |
order by taskperfor0_.TASK_PERFORMED_STARTED desc |
Explain Plan:
id;select_type;table;type;possible_keys;key;key_len;ref;rows;Extra
|
1;SIMPLE;taskperfor0_;range;FK_TASKPERFORMED_BASE_AND_SYNC_ORDER,I_TASKPERFORMED_BASE_AND_CREATED_TIMESTAMP,I_TASKPERFORMED_TASK_PERFORMED_STARTED;I_TASKPERFORMED_TASK_PERFORMED_STARTED;5;\N;45398;Using where
|
1;SIMPLE;taskplanne1_;eq_ref;PRIMARY,FK_TASKPLANNEDBASE_FK_PERSON_ID,FK_TASKPLANNEDBASE_FK_MODULE_ID;PRIMARY;166;taskperfor0_.FK_TASKPLANNEDBASE_ID;1;Using where
|
1;SIMPLE;ccmodule2_;eq_ref;PRIMARY;PRIMARY;166;taskplanne1_.FK_MODULE_ID;1;Using where
|
The following query, the only difference being the IGNORE INDEX statement, returns 210 results:
SELECT * |
from t_taskperformed taskperfor0_ IGNORE INDEX (I_TASKPERFORMED_TASK_PERFORMED_STARTED) |
inner join t_taskplannedbase taskplanne1_ on taskperfor0_.FK_TASKPLANNEDBASE_ID=taskplanne1_.ID |
inner join t_module ccmodule2_ on taskplanne1_.FK_MODULE_ID=ccmodule2_.ID |
where ccmodule2_.MODULE_KEY='MK1' |
and (taskplanne1_.FK_PERSON_ID in ( /* List of 1900 Person IDs */)) |
and taskperfor0_.TASK_PERFORMED_STARTED>='2024-03-01 00:00:00' |
and taskperfor0_.TASK_PERFORMED_STARTED<'2024-04-01 :00:00:00' |
and taskperfor0_.DELETED=0 |
order by taskperfor0_.TASK_PERFORMED_STARTED desc |
As soon as I select for one specific person, this does not produce different results whether I ignore indexes or not. Explain plan does indicate that the index is not being used.
SELECT * |
from t_taskperformed taskperfor0_ |
inner join t_taskplannedbase taskplanne1_ on taskperfor0_.FK_TASKPLANNEDBASE_ID=taskplanne1_.ID |
inner join t_module ccmodule2_ on taskplanne1_.FK_MODULE_ID=ccmodule2_.ID |
where ccmodule2_.MODULE_KEY='MK1' |
and (taskplanne1_.FK_PERSON_ID in (/* 1 specific person id */)) |
and taskperfor0_.TASK_PERFORMED_STARTED>='2024-03-01 00:00:00' |
and taskperfor0_.TASK_PERFORMED_STARTED<'2024-04-01 :00:00:00' |
and taskperfor0_.DELETED=0 |
order by taskperfor0_.TASK_PERFORMED_STARTED desc |
Explain plan:
id;select_type;table;type;possible_keys;key;key_len;ref;rows;Extra
|
1;SIMPLE;taskplanne1_;ref;PRIMARY,FK_TASKPLANNEDBASE_FK_PERSON_ID,FK_TASKPLANNEDBASE_FK_MODULE_ID;FK_TASKPLANNEDBASE_FK_PERSON_ID;166;const;2;Using index condition; Using temporary; Using filesort
|
1;SIMPLE;ccmodule2_;eq_ref;PRIMARY;PRIMARY;166;taskplanne1_.FK_MODULE_ID;1;Using where
|
1;SIMPLE;taskperfor0_;ref;FK_TASKPERFORMED_BASE_AND_SYNC_ORDER,I_TASKPERFORMED_BASE_AND_CREATED_TIMESTAMP;FK_TASKPERFORMED_BASE_AND_SYNC_ORDER;166;taskplanne1_.ID;3;Using where
|
So in summary:
- The query produces different results depending on whether the particular index on a datetime column is being used.
- The correct result is the one that contains more results.
- OPTIMIZE TABLE as well as drop/create of the index both solve the problem for a short period of time, i.e. both queries return the same result, but the problem reappears after a fairly short period, i.e., within approximately two weeks.
- It is worth noting that all records being inserted always refer to dates of the current month which makes me wonder whether the issue has to do with this particular distribution of data.