Advice concerning inconsistent query results
Hello,
I am looking for advice concering inconsistent query results.
Problem: 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, we will have to wait to see whether the problem occurs again.
CHECK TABLE returned no erros for the affected table. The problem does not seem to be limited to one installation, we are seing it on different MariaDB installations.
Questions: - Has anybody seen such behaviour in the past? - How can we diagnose the problem?
Environment: - MariaDB Version: MariaDB 10.5.13 - MariaDB Storage engine: InnoDB - MariaDB Collation: utf8mb4_unicode_ci - OS: Windows Server
Kind regards, Jef
Hi, Jef,
Could you be more specific, please?
Like, show the query and rows that are not returned when the query uses an index, but are returned otherwise?
Even better if you'd first remove those columns from the SELECT clause and parts of the WHERE clause that aren't affecting this inconsistent behavior.
Regards, Sergei Chief Architect, MariaDB Server and security@mariadb.org
On Mar 04, Jef Van Loon via discuss wrote:
Hello,
I am looking for advice concering inconsistent query results.
Problem: 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, we will have to wait to see whether the problem occurs again.
CHECK TABLE returned no erros for the affected table. The problem does not seem to be limited to one installation, we are seing it on different MariaDB installations.
Questions: - Has anybody seen such behaviour in the past? - How can we diagnose the problem?
Environment: - MariaDB Version: MariaDB 10.5.13 - MariaDB Storage engine: InnoDB - MariaDB Collation: utf8mb4_unicode_ci - OS: Windows Server
Hi Sergei,
I am currently not able to provide further details.
System 1: Unfortunately I have not saved the queries and results from the analysis. I have not seen the problem since the OPTIMIZE table. I will investigate during the month (we will have to wait for users entering data in this table) and get back to this discussion if I encounter the issue again. What still makes me uncomfortable is that the problem went away after dropping/recreating an index and reappeared later. Also importing a dump of the database into a dev environment did not reveal the same problem.
Do you have any insight if there are cases where index malfunctioning may cause inconsistent results (depending on whether the index is being used) and a drop/recreate does not fully serve the problem. I would be surprised if drop/recreate leads to different results than optimize table. As far as I understand, optimize does also rebuild indexes in addition to rebuilding the entire table file on disk (which would be more or less the same as importing a dump into another system).
System 2: The problem appeared to be the same as on system 1, but further analysis revealed that this was completely unrelated even though the symptoms initially lead me to the conclusion that this would be the same issue.
Kind regards, Jef
I guess I experienced this behaviour a few times when the index was broken or out of sync. I guess that some index checks returned, that it's not in a healthy state. That was a few years back so I don't recall better details...
On Tue, 5 Mar 2024 at 11:03, Jef Van Loon via discuss < discuss@lists.mariadb.org> wrote:
Hi Sergei,
I am currently not able to provide further details.
System 1: Unfortunately I have not saved the queries and results from the analysis. I have not seen the problem since the OPTIMIZE table. I will investigate during the month (we will have to wait for users entering data in this table) and get back to this discussion if I encounter the issue again. What still makes me uncomfortable is that the problem went away after dropping/recreating an index and reappeared later. Also importing a dump of the database into a dev environment did not reveal the same problem.
Do you have any insight if there are cases where index malfunctioning may cause inconsistent results (depending on whether the index is being used) and a drop/recreate does not fully serve the problem. I would be surprised if drop/recreate leads to different results than optimize table. As far as I understand, optimize does also rebuild indexes in addition to rebuilding the entire table file on disk (which would be more or less the same as importing a dump into another system).
System 2: The problem appeared to be the same as on system 1, but further analysis revealed that this was completely unrelated even though the symptoms initially lead me to the conclusion that this would be the same issue.
Kind regards, Jef _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
Keeping an eye on cardinality stats of your indexes, after creation, alters or large data changes (which OPTIMIZE TABLE will cover) is a good idea when you see inconsistent results. So perhaps what led to this fluctuation in your data, perhaps its even a new logical volume. I have not seen it in years, but i also no longer work with large and VERY active datasets since I used to see it in our query results.
On Tue, Mar 5, 2024 at 2:18 PM Jan Křístek via discuss < discuss@lists.mariadb.org> wrote:
I guess I experienced this behaviour a few times when the index was broken or out of sync. I guess that some index checks returned, that it's not in a healthy state. That was a few years back so I don't recall better details...
On Tue, 5 Mar 2024 at 11:03, Jef Van Loon via discuss < discuss@lists.mariadb.org> wrote:
Hi Sergei,
I am currently not able to provide further details.
System 1: Unfortunately I have not saved the queries and results from the analysis. I have not seen the problem since the OPTIMIZE table. I will investigate during the month (we will have to wait for users entering data in this table) and get back to this discussion if I encounter the issue again. What still makes me uncomfortable is that the problem went away after dropping/recreating an index and reappeared later. Also importing a dump of the database into a dev environment did not reveal the same problem.
Do you have any insight if there are cases where index malfunctioning may cause inconsistent results (depending on whether the index is being used) and a drop/recreate does not fully serve the problem. I would be surprised if drop/recreate leads to different results than optimize table. As far as I understand, optimize does also rebuild indexes in addition to rebuilding the entire table file on disk (which would be more or less the same as importing a dump into another system).
System 2: The problem appeared to be the same as on system 1, but further analysis revealed that this was completely unrelated even though the symptoms initially lead me to the conclusion that this would be the same issue.
Kind regards, Jef _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
Hello,
The problem has reappeared again after I had performed OPTIMIZE TABLE last month. I am now able to provide some more details.
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). 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
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 date column is being used. - The correct result is the one that contains more results. - OPTIMIZE TABLE solves the problem for a short period of time, i.e. both queries return the same result, but the problem reappears again, after a fairly short period.
Regards, Jef
Hi, Jef,
Could you please report a bug with all that information? Hopefully, we'll be able to repeat it based on what you've provided.
On Mar 20, Jef Van Loon via discuss wrote:
Hello,
The problem has reappeared again after I had performed OPTIMIZE TABLE last month. I am now able to provide some more details.
Regards, Sergei Chief Architect, MariaDB Server and security@mariadb.org
Hi Sergei,
Thanks, I have reported this as MDEV-33759. I am really hoping to get a hint on what's happening. The issue is concerning given that invoicing related data is produced using this query.
Regards, Jef
participants (4)
-
Jan Křístek -
Jef Van Loon -
Jeff Dyke -
Sergei Golubchik