I'm seeing some odd behaviour on MariaDB 10.1.22 that's got me scratching my
head. My production servers (a master and two slaves) are returning incorrect
query results, but when I run the same query on the same data elsewhere I
always get the correct results, even on a VM with matching OS, H/W arch and
MariaDB version.
The problem is triggered by using "ORDER BY x DESC LIMIT 1" in a dependent
subquery on a single table. The query is being run on a small InnoDB table
that very rarely changes. Each row contains a date, an integer identifier, a
sequence number and a handful of applicable settings that are all integers.
Unfortunately I can't create a simplified reproducible test case, but the
following example shows the sort of query I'm working with. Imagine a table
that looks like this:
CREATE TABLE `settings` (
`date` date NOT NULL,
`id` int(11) NOT NULL,
`seq` int(11) NOT NULL,
`value` int(11) NOT NULL,
UNIQUE KEY `date_id_seq` (`date`,`id`,`seq`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
The table is designed to store a sequence of values belonging to something
identified by the ID. Each sequence of values has a date stamp, and can be
replaced at a later date by a new sequence of values:
+------------+----+-----+-------+
| date | id | seq | value |
+------------+----+-----+-------+
| 2011-11-01 | 1 | 0 | 6 |
| 2011-11-01 | 1 | 1 | 3 |
| 2011-11-01 | 1 | 2 | 9 |
| 2011-11-01 | 2 | 0 | 6 |
| 2011-11-01 | 2 | 1 | 1 |
| 2011-11-01 | 3 | 0 | 6 |
| 2011-11-01 | 3 | 1 | 8 |
| 2012-04-01 | 1 | 0 | 4 |
| 2012-04-01 | 1 | 1 | 3 |
| 2013-06-01 | 3 | 0 | 5 |
+------------+----+-----+-------+
The query is asking for all the IDs that had a particular first value set as of
a particular date, in this case a first value of 6 as of '2012-07-01':
SELECT s1.id
FROM settings s1
WHERE s1.seq = 0 and
s1.value = 6 and
s1.date =
(
SELECT s2.date
FROM settings s2
WHERE s2.id = s1.id and
s2.date <= '2012-07-01'
ORDER BY s2.date DESC
LIMIT 1
);
This query correctly returns IDs 2 and 3, and not ID 1 since the data for
'2012-04-01' overrides the data for '2011-11-01'. In my equivalent situation,
my production servers are returning 1, 2 and 3. Everywhere else I try to run
it I get just 2 and 3 as expected.
The table in question changes rarely, so it's trivial to show that the test
system I've set up is working on the same data as the production systems. I've
selected the entire table to out-files and diffed them, I've restored the test
system from a mysqldump backup of production, and I've even run XtraBackup and
prepared it with --export, then followed the instructions to import the table
files into my test system just in case there's something weird in the
production table's .ibd file. No matter what I do, I can't reproduce the
problem anywhere but on my production systems.
Testing has shown that minor changes to the query will fix the result set.
Changing it to use "max()" instead of "order/limit" fixes it. Removing the
date test from the where clause also fixes it.
I don't want to just change all my queries like this to use MAX() and be done
with it, because this weirdness will always be in the back of my mind. Is my
query insufficiently specific? Have my queries of this type only ever returned
the right results by accident until I moved to MariaDB recently?
I haven't tried adding an index to the date column yet, because it too might
make the problem vanish and I'll be none the wiser. I'd rather work out how to
make the problem reproducible then dig in further to expose the cause. I
thought that my InnoDB file could have been corrupt, but preparing the
XtraBackup didn't detect it. Perhaps I've triggered a bug in MariaDB that
needs very special conditions to appear? That seems unlikely but I'm at a bit
of a loss now.
Any suggestions anyone might have would be very gratefully received!
Kind regards,
Sam
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
