0

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

  • 1
    Without the view query text and source table structures, we can only sympathize with you. a view ... which has been working for years ... started giving erroneous results Most often, there are two possible reasons. 1 - index corruption. 2 - logical error, correct results obtained were a random. – Akina Feb 8 at 19:05
  • View and table definitions added. – gbr Feb 8 at 19:39
  • Failed to open the referenced table 'vol_scene', Failed to open the referenced table 'vol_account' – Akina Feb 8 at 19:59
  • Apologies, added those two. Thank you for looking at it. – gbr Feb 8 at 20:01
  • And again... Failed to open the referenced table 'vol_bbpost'... why you cannot post full tableset DDL (I don't even try to hint that I would like to see them in the correct order)? – Akina Feb 8 at 20:07
0

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.

  • CHECK and OPTIMIZE can be handy for MyISAM tables, but are rarely needed for InnoDB. Still, rebuilding the indexes may be a fix. – Rick James yesterday

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.