Different Query Output for Same Data in Percona vs MariaDB

MySQL & MariaDB Percona Server for MySQL 5.7
1

Dear All

Copied database from Percona 5.5.57-38.9 to MariaDB 10.11.2. But on Mariadb when running following query its returning two rows whereas on Percona same query was returning one row, so need help to investigate this change.

Thanks in advance,

Details -

Original Data on Source Percona DB Server -

mysql> SELECT f.id,f.leg_id,f.leg_srcid FROM Table1 f WHERE f.leg_id=2 AND f.id IN (1,2);
±----------±-------±----------+
| id| leg_id | leg_srcid |
±----------±-------±----------+
| 1 | 2 | 37214|
| 2 | 2 | 37214|
±----------±-------±----------+

When running following Query ‘A’ on Percona -

SELECT f.id,f.leg_id,f.leg_srcid FROM Table1 f WHERE f.leg_srcid=37214 and leg_id = 2;

Output on Percona is -

mysql> SELECT f.id,f.leg_id,f.leg_srcid FROM Table1 f WHERE f.leg_srcid=37214 and leg_id = 2;
±----------±-------±----------+
| id | leg_id | leg_srcid |
±----------±-------±----------+
| 2 | 2 | 37214|
±----------±-------±----------+
1 row in set (0.20 sec)

But when same Query ‘A’ run on MariaDB, output is -

MariaDB [DB1]> SELECT f.id,f.leg_id,f.leg_srcid FROM Table1 f WHERE f.leg_srcid=37214 and leg_id = 2;
±----------±-------±----------+
| id | leg_id | leg_srcid |
±----------±-------±----------+
| 1 | 2 | 37214|
| 2 | 2 | 37214|
±----------±-------±----------+
2 rows in set (1.138 sec)

Kindly check and suggest.

2

Percona 5.5 is incredibly old. Can you please test your comparison against Percona 8.0? Many changes were made to the query optimizer since 5.5 for both Percona and MariaDB.

3

Thanks Matthewbs for swift reply.

However there are no plans to perform upgrade soon, so do we have any other way to cross check and find any solution to it?

Any suggestions.

4

Check the value of optimizer_switch on both flavors.

I hope you realize that 5.5 is around 10+ years dead. 5.7 died in Oct’23 and 8.0.* is entering LTS with 8.3 the current GA. You have no security patches, no bug fixes, no improvements in performance, etc.

Again, by comparing an extremely out of date MySQL to modern MariaDB, that’s apples to hamburgers. (Remember that MariaDB isn’t MySQL. If you were comparing Community MySQL to Percona, that’d be plain ice cream to ice cream with fudge on top. Whereas MariaDB isn’t even ice cream.)

5

Thanks, yeah makes sense and perfectly understand your point of view.

Actually DBA team has been asked to perform a mock test to perform migration from Percona to MariaDB. But had some issues and were forced to do rollback, but strange part is after performing (taking backup of database from MariaDB and restored it to same old Percona server), this issue start coming. So raised this issue to check if something missing here.

As of now…we already checked Table structure, Indexes, keys on both database and all seems same on both databases.

Out of your extensive experience, Is there anything which you suggest to check which could help us to fix it.

6

There isn’t anything to “fix” because nothing is “broken.” Again, you are comparing the results of extremely-out-of-date-MySQL to modern-mariadb. If you want to accurately test, you need to upgrade your 5.5 to 8.0. Then, you can do a much closer comparison of "modern MySQL to “modern MariaDB”. But again, that’s mysql/maria, apples/oranges. MariaDB is not MySQL. MariaDB has made changes to the query optimizer that do not align with the rest of the community.

7

Thanks again for quick response, indeed agreed.

8

Why not just upgrade Percona to 8.0? Why swap out to MariaDB which is not MySQL? You said above “there are no plans to perform upgrade” but yet you are trying to move to a different database? That is very confusing.