Mysql – Mariadb started giving erroneous query results for 1 view

mariadb-10.1MySQL

10.1.44-MariaDB-0ubuntu0.18.04.1

Have a view volv_action which does a few joins which has been working for years and was not changed. But last night Mariadb started giving erroneous results if its datetime column is included in the where clause. This started happening in more than one database with this particular view on the same instance each with their own data in the tables (details on that part at bottom).

Data that's there:

SELECT scene_id, action_date_created FROM volv_action 
WHERE scene_id=11020;

scene_id     action_date_created
11020    2020-02-08 07:10:16
11020    2020-02-08 07:14:28
11020    2020-02-08 07:25:04
11020    2020-02-08 07:30:05
11020    2020-02-08 07:39:49
11020    2020-02-08 07:44:10
11020    2020-02-08 07:49:38
11020    2020-02-08 07:53:32
11020    2020-02-08 07:56:49
11020    2020-02-08 08:00:28
11020    2020-02-08 08:05:15
11020    2020-02-08 08:11:19
11020    2020-02-08 08:14:22

With action_date_created doing a comparison in where clause like this gets no rows returned:

SELECT scene_id, action_date_created FROM volv_action 
WHERE scene_id=11020 and action_date_created > '2020-01-01'

Change ">" to "<" and ask for records before 2020 which clearly there are none, and it will return exactly 1 record which is not before 2020.

SELECT scene_id, action_date_created FROM volv_action 
WHERE scene_id=11020 and action_date_created < '2020-01-01'

scene_id    action_date_created
11020     2020-02-08 07:14:28

Deleting that particular record will cause the next record to be retrieved instead.

Adding "order by 1" on the end causes a query to give correct results

SELECT scene_id, action_date_created FROM volv_action 
WHERE scene_id=11020 and action_date_created > '2020-01-01'
order by 1

scene_id     action_date_created
11020    2020-02-08 07:10:16
11020    2020-02-08 07:14:28
11020    2020-02-08 07:25:04
11020    2020-02-08 07:30:05
11020    2020-02-08 07:39:49
11020    2020-02-08 07:44:10
11020    2020-02-08 07:49:38
11020    2020-02-08 07:53:32
11020    2020-02-08 07:56:49
11020    2020-02-08 08:00:28
11020    2020-02-08 08:05:15
11020    2020-02-08 08:11:19
11020    2020-02-08 08:14:22

A query where "action_date_created is not null" without other clauses on that column gives correct results, but not if there is one of the ">" clauses that causes failure.

Querying the underlying table with action_date_created works fine. Spot-checking other views that have similar datetime columns does not reproduce the problem. Analyzed all tables in the database. Have recreated the view, problem persists.

Later found it is not just this database on the instance with the problem for this specific view.

There is a separate dev database on the same Mariadb instance with an identical data model but different data in it. Checked and it has started showing the same exact problem with its data as well.

The queries had worked for years. I know it worked successfully at 11pm last night, but was broken when someone ran one around 2am.

We've done a service mysql restart, but same problem persists. I'm perplexed. Any help would be appreciated.

Edit to give link to schema code:
https://raw.githubusercontent.com/volundmush/mushcode/master/Schema.sql

Best Answer

You get one wrong row and when that row is removed, you get the next wrong row in your result - means there are mismatches between table data and index data. You had checked through two clients and getting the same wrong result which means...

Option 1

If at all something happened to your databases around your mentioned 2 a.m. try running (for all databases)

mysqlcheck -o --all-databases

in commandline and

analyze table tablename;

Finally

optimize [no_write_to_binlog | local] table tablename;

The analyze / optimize are used again to ensure if everything is fine for your specific tables.

Option 2

Check your log files past previous night 11 p.m. If you can not find anything odd, post it here, we shall help.

Related Solutions

Mysql – Two MySQL query results are different, but shouldn’t be

Here's a simpler way of looking at your queries

select * 
from A
where A.did = 1988

-- returns record where card = 2157

and

select * 
from B join A on A.card = B.id
where B.userid = 2157

Since you want the record where a.card = 2157 and a.card = b.id then either

Change your where statement so it is looking for b.id = 2157 or a.card = 2157
Or change your join so you're joining a.card = b.userid

Mysql – getting different results from a query vs. a view

You're using GROUP BY in a non-deterministic way. You GROUP BY tagepc, but what value for the other columns do you expect?

MySQL permits this kind of query, but MySQL chooses a row from the group arbitrarily. In practice, it tends to be the first row in the group, as they are stored physically.

But in the case when you query through the view, the view stores its results in a temporary table, probably in a different physical order than the data in the original table.

Note that http://dev.mysql.com/doc/refman/5.6/en/create-view.html says:

ORDER BY is permitted in a view definition, but it is ignored if you select from a view using a statement that has its own ORDER BY.

So the query run by the view to populate the temp table ignores your view's ORDER BY, and since you used GROUP BY it probably takes values from arbitrary rows in the underlying table.

How to fix this? Stop running ambiguous GROUP BY queries. Any column referenced in your select-list should either by (a) listed in the GROUP BY clause, or (b) inside a grouping function like MAX() or something.

I don't know what you expect this query to do, or why you're using GROUP BY in the first place. So I can't offer any more specific advice.

Re your comment:

Thanks for the clarification. This is a pretty common type of SQL question. I created the tag greatest-n-per-group on StackOverflow and now it has close to a thousand questions. The tag exists on dba.stackexchange.com, but with fewer questions so far.

Here's a solution:

SELECT i.*
FROM inventoryrecords AS i
JOIN (SELECT TagEPC, MAX(StateDateUTC) AS StateDateUTC
      FROM inventoryrecords
      GROUP BY TagEPC ORDER BY NULL) AS maxi USING (TagEPC, StateDateUTC);

Best Answer

Related Solutions

Mysql – Two MySQL query results are different, but shouldn’t be

Mysql – getting different results from a query vs. a view

Related Question