MIgrating from MariaDB to Percona

I am testing a migration from our current MariaDB (v10.2.15) to Percona XtraDB cluster 5.7 (on CentOS7) and am running into many issues where queries are not returning the same results in Percona as they do in MariaDB or simply not functioning at all. We have strict mode disabled on both MariaDB and Percona and are using Innodb. Is there some setting that I’ve missed setting up Percona that would cause this, or is there something I need to do to the data from MariaDB before importing it into Percona?
tksintenn

Hello I probably need to move this to a different thread as the Questions and Discussions thread tends to get overlooked. But I need to find the right one.

I am curious though - are you going direct from MariaDB that’s NOT on galera cluster to Percona XtraDB Cluster, and not to Percona Server for MySQL, even as an interim step?

What method are you using for the migration? If you could provide some more information and then also:
[LIST]
[]any log files that are being written with errors etc
[
]the my.conf for each node of the cluster
[]confirmation of the exact version of Percona XtraDB Cluster
[
]likewise the exact version of the operating system
[]information of your import/export approach
[
]example of the kind of query that is not returning the same results and/or simply not functioning
[/LIST] I’ll happily pass this to the tech team for them to advise, but they definitely need a few more details to have a hope of helping you out. Also, just to say on the open source forum the answers are necessarily general rather than specific to your environment (other community members are welcome to chime in too), but as this is an ‘early’ problem in the migration I am sure we would have some pointers for you.

If MariaDB is in a cluster, then there are still some differences in GTID so we’d benefit from some outline as to how you are approaching the migration I think.

We are going from MariaDB not in a cluster straight into Percona XtraDB Cluster. Should we first move to Percona Server for MySQL?
Our MariaDB version is 10.2.15, our Percona XtraDB Cluster version is 5.7.23-31.31.2.el7, and our CentOS Version is 7.6.1810.

Our process for the migration is dumping the data from MariaDB with

mysqldump -u root -p test_database > test_database.sql

From here we copy the file to our test Percona node and import it with

mysql -u root -p "imported_database" < "test_database.sql

I have attached our wsrep.cnf, my.cnf, and mysqld.cnf. (We have made most of our changes in wsrep.cnf because changes to the my.cnf were being overwritten by the wsrep.cnf.)

We are not producing any errors in the logs but this query returns incorrect information in Percona and with slight changes it just loads indefinitely:

SELECT LMC.member_id, LMC.timestamp, MAX(T.assigned_on) AS date_of_last_task
FROM (
SELECT user_id, member_id, `timestamp`
FROM log_member_calls
WHERE user_id = :u_id
ORDER BY `timestamp` DESC
LIMIT 1
) LMC
INNER JOIN members M ON M.id = LMC.member_id
LEFT OUTER JOIN members_plans MP ON MP.member_id = M.id
LEFT OUTER JOIN log_members LM ON LM.timestamp > LMC.timestamp AND LM.changed_by_id = LMC.user_id AND LM.row = M.id
LEFT OUTER JOIN log_members_plans LMP ON LMP.timestamp > LMC.timestamp AND LMP.changed_by_id = LMC.user_id AND LMP.row = MP.id
LEFT OUTER JOIN tasks_entities TE ON TE.member_id = M.id
LEFT OUTER JOIN tasks T ON T.id = TE.task_id AND T.assigned_by = LMC.user_id
WHERE (LE.timestamp > LMC.timestamp)
OR (LMP.timestamp > LMC.timestamp)

my.cnf.txt (370 Bytes)

wsrep.cnf.txt (1.1 KB)

mysqld.cnf.txt (513 Bytes)

Could you check if sql_mode on both servers is the same, please? I’m also sharing this with our tech team in case they have something they can suggest.

Yes, ‘sql_mode’ is the same on both servers.

Can you post an output of EXPLAIN for this query. At first glance, it looks like the FROM subquery is non-deterministic. A duplicate value in timestamp can skew the results before the LIMIT. Can you try using this ORDER BY clause instead.

ORDER BY timestamp, user_id, member_id

Add the PRIMARY KEY column in there if it also make sense.

I’m very sorry for the delay. I’ve been trying to get back to this and appreciate all the help.

When run in our existing MariaDB system the EXPLAIN returns: [TABLE=“class: table_results ajax pma_table”]
[TR]
id select_type table type possible_keys key key_len ref rows Extra [/TR]
[TR]
[TD=“class: right data nowrap”]1[/TD]
[TD=“class: data not_null text”]PRIMARY[/TD]
[TD=“class: data text”][/TD]
[TD=“class: data text”]ALL[/TD]
[TD=“class: data text null”]NULL[/TD]
[TD=“class: data text null”]NULL[/TD]
[TD=“class: data text null”]NULL[/TD]
[TD=“class: data text null”]NULL[/TD]
[TD=“class: right data nowrap”]2[/TD]
[TD=“class: data not_null text nowrap”] [/TD]
[/TR]
[TR]
[TD=“class: right data nowrap”]1[/TD]
[TD=“class: data not_null text”]PRIMARY[/TD]
[TD=“class: data text”]M[/TD]
[TD=“class: data text”]eq_ref[/TD]
[TD=“class: data text”]PRIMARY[/TD]
[TD=“class: data text”]PRIMARY[/TD]
[TD=“class: data text”]8[/TD]
[TD=“class: data text”]LMC.member_id[/TD]
[TD=“class: right data nowrap”]1[/TD]
[TD=“class: data not_null text”]Using index[/TD]
[/TR]
[TR]
[TD=“class: right data nowrap”]1[/TD]
[TD=“class: data not_null text”]PRIMARY[/TD]
[TD=“class: data text”]MP[/TD]
[TD=“class: data text”]ref[/TD]
[TD=“class: data text”]member_id[/TD]
[TD=“class: data text”]member_id[/TD]
[TD=“class: data text”]8[/TD]
[TD=“class: data text”]LMC.member_id[/TD]
[TD=“class: right data nowrap”]2[/TD]
[TD=“class: data not_null text”]Using index[/TD]
[/TR]
[TR]
[TD=“class: right data nowrap”]1[/TD]
[TD=“class: data not_null text”]PRIMARY[/TD]
[TD=“class: data text”]LM[/TD]
[TD=“class: data text”]ref[/TD]
[TD=“class: data text”]timestamp,changed_by_id,row[/TD]
[TD=“class: data text”]row[/TD]
[TD=“class: data text”]8[/TD]
[TD=“class: data text”]LMC.member_id[/TD]
[TD=“class: right data nowrap”]16[/TD]
[TD=“class: data not_null text”]Using where[/TD]
[/TR]
[TR]
[TD=“class: right data nowrap”]1[/TD]
[TD=“class: data not_null text”]PRIMARY[/TD]
[TD=“class: data text”]LMP[/TD]
[TD=“class: data text”]ref[/TD]
[TD=“class: data text”]timestamp,changed_by_id,row[/TD]
[TD=“class: data text”]row[/TD]
[TD=“class: data text”]4[/TD]
[TD=“class: data text”]internal_prod.MP.id[/TD]
[TD=“class: right data nowrap”]4[/TD]
[TD=“class: data not_null text”]Using where[/TD]
[/TR]
[TR]
[TD=“class: right data nowrap”]1[/TD]
[TD=“class: data not_null text”]PRIMARY[/TD]
[TD=“class: data text”]TE[/TD]
[TD=“class: data text”]ref[/TD]
[TD=“class: data text”]member_id[/TD]
[TD=“class: data text”]member_id[/TD]
[TD=“class: data text”]9[/TD]
[TD=“class: data text”]LMC.member_id[/TD]
[TD=“class: right data nowrap”]1[/TD]
[TD=“class: data not_null text nowrap”] [/TD]
[/TR]
[TR]
[TD=“class: right data nowrap”]1[/TD]
[TD=“class: data not_null text”]PRIMARY[/TD]
[TD=“class: data text”]T[/TD]
[TD=“class: data text”]eq_ref[/TD]
[TD=“class: data text”]PRIMARY[/TD]
[TD=“class: data text”]PRIMARY[/TD]
[TD=“class: data text”]4[/TD]
[TD=“class: data text”]internal_test.TE.task_id[/TD]
[TD=“class: right data nowrap”]1[/TD]
[TD=“class: data not_null text”]Using where[/TD]
[/TR]
[TR]
[TD=“class: right data nowrap”]2[/TD]
[TD=“class: data not_null text”]DERIVED[/TD]
[TD=“class: data text”]log_member_calls[/TD]
[TD=“class: data text”]ref[/TD]
[TD=“class: data text”]user_id[/TD]
[TD=“class: data text”]user_id[/TD]
[TD=“class: data text”]4[/TD]
[TD=“class: data text”]const[/TD]
[TD=“class: right data nowrap”]25[/TD]
[TD=“class: data not_null text”]Using where; Using filesort[/TD]
[/TR]
[/TABLE]

When run in Percona cluster the EXPLAIN returns: [TABLE=“class: table_results ajax pma_table”]
[TR]
id select_type table partitions type possible_keys key key_len ref rows filtered Extra [/TR]
[TR]
[TD=“class: right data not_null nowrap”]1[/TD]
[TD=“class: data not_null text”]PRIMARY[/TD]
[TD=“class: data text”][/TD]
[TD=“class: data null”]NULL [/TD]
[TD=“class: data text”]system[/TD]
[TD=“class: data text null”]NULL [/TD]
[TD=“class: data text null”]NULL [/TD]
[TD=“class: data text null”]NULL [/TD]
[TD=“class: data text null”]NULL [/TD]
[TD=“class: right data nowrap”]1[/TD]
[TD=“class: right data nowrap”]100.00[/TD]
[TD=“class: data not_null text null”]NULL [/TD]
[/TR]
[TR]
[TD=“class: right data not_null nowrap”]1[/TD]
[TD=“class: data not_null text”]PRIMARY[/TD]
[TD=“class: data text”]M[/TD]
[TD=“class: data null”]NULL [/TD]
[TD=“class: data text”]const[/TD]
[TD=“class: data text”]PRIMARY[/TD]
[TD=“class: data text”]PRIMARY[/TD]
[TD=“class: data text”]8[/TD]
[TD=“class: data text”]const[/TD]
[TD=“class: right data nowrap”]1[/TD]
[TD=“class: right data nowrap”]100.00[/TD]
[TD=“class: data not_null text”]Using index[/TD]
[/TR]
[TR]
[TD=“class: right data not_null nowrap”]1[/TD]
[TD=“class: data not_null text”]PRIMARY[/TD]
[TD=“class: data text”]MP[/TD]
[TD=“class: data null”]NULL [/TD]
[TD=“class: data text”]ref[/TD]
[TD=“class: data text”]member_id[/TD]
[TD=“class: data text”]member_id[/TD]
[TD=“class: data text”]8[/TD]
[TD=“class: data text”]const[/TD]
[TD=“class: right data nowrap”]1[/TD]
[TD=“class: right data nowrap”]100.00[/TD]
[TD=“class: data not_null text”]Using where; Using index[/TD]
[/TR]
[TR]
[TD=“class: right data not_null nowrap”]1[/TD]
[TD=“class: data not_null text”]PRIMARY[/TD]
[TD=“class: data text”]LM[/TD]
[TD=“class: data null”]NULL [/TD]
[TD=“class: data text”]ref[/TD]
[TD=“class: data text”]timestamp,changed_by_id,row[/TD]
[TD=“class: data text”]row[/TD]
[TD=“class: data text”]8[/TD]
[TD=“class: data text”]const[/TD]
[TD=“class: right data nowrap”]1[/TD]
[TD=“class: right data nowrap”]5.00[/TD]
[TD=“class: data not_null text”]Using where[/TD]
[/TR]
[TR]
[TD=“class: right data not_null nowrap”]1[/TD]
[TD=“class: data not_null text”]PRIMARY[/TD]
[TD=“class: data text”]LMP[/TD]
[TD=“class: data null”]NULL [/TD]
[TD=“class: data text”]range[/TD]
[TD=“class: data text”]timestamp,changed_by_id,row[/TD]
[TD=“class: data text”]timestamp[/TD]
[TD=“class: data text”]4[/TD]
[TD=“class: data text null”]NULL [/TD]
[TD=“class: right data nowrap”]1[/TD]
[TD=“class: right data nowrap”]100.00[/TD]
[TD=“class: data not_null text”]Using where; Using join buffer (Block Nested Loop)[/TD]
[/TR]
[TR]
[TD=“class: right data not_null nowrap”]1[/TD]
[TD=“class: data not_null text”]PRIMARY[/TD]
[TD=“class: data text”]TE[/TD]
[TD=“class: data null”]NULL [/TD]
[TD=“class: data text”]ref[/TD]
[TD=“class: data text”]member_id[/TD]
[TD=“class: data text”]member_id[/TD]
[TD=“class: data text”]9[/TD]
[TD=“class: data text”]const[/TD]
[TD=“class: right data nowrap”]9[/TD]
[TD=“class: right data nowrap”]100.00[/TD]
[TD=“class: data not_null text”]Using where[/TD]
[/TR]
[TR]
[TD=“class: right data not_null nowrap”]1[/TD]
[TD=“class: data not_null text”]PRIMARY[/TD]
[TD=“class: data text”]T[/TD]
[TD=“class: data null”]NULL [/TD]
[TD=“class: data text”]eq_ref[/TD]
[TD=“class: data text”]PRIMARY[/TD]
[TD=“class: data text”]PRIMARY[/TD]
[TD=“class: data text”]4[/TD]
[TD=“class: data text”]internal_test.TE.task_id[/TD]
[TD=“class: right data nowrap”]1[/TD]
[TD=“class: right data nowrap”]100.00[/TD]
[TD=“class: data not_null text”]Using where[/TD]
[/TR]
[TR]
[TD=“class: right data not_null nowrap”]2[/TD]
[TD=“class: data not_null text”]DERIVED[/TD]
[TD=“class: data text”]log_member_calls[/TD]
[TD=“class: data null”]NULL [/TD]
[TD=“class: data text”]ref[/TD]
[TD=“class: data text”]user_id[/TD]
[TD=“class: data text”]user_id[/TD]
[TD=“class: data text”]4[/TD]
[TD=“class: data text”]const[/TD]
[TD=“class: right data nowrap”]20[/TD]
[TD=“class: right data nowrap”]100.00[/TD]
[TD=“class: data not_null text”]Using index condition; Using filesort[/TD]
[/TR]
[/TABLE]

Also, adding the statement

ORDER BY timestamp, user_id, member_id

to the subquery skews the results even further than the original query.

Thanks for the help.

When rewriting the query into three separate queries it returns the expected results.

SELECT M.id, MAX(LM.timestamp) as lm_timestamp, MAX(T.assigned_on) AS date_of_last_task
FROM tasks T
INNER JOIN tasks_entities TE ON TE.task_id = T.id
INNER JOIN members M ON M.id = TE.member_id
INNER JOIN log_members LM ON LM.row = M.id
WHERE T.assigned_by = :u_id;

SELECT M.id, MAX(LMP2.timestamp) as lmp_timestamp, MAX(T.assigned_on) AS date_of_last_task
FROM tasks T
INNER JOIN tasks_entities TE ON TE.task_id = T.id
INNER JOIN members M ON M.id = TE.member_id
INNER JOIN log_members LM ON LM.row = M.id
LEFT OUTER JOIN (
SELECT LMP.*, MP.member_id
FROM members_plans MP
INNER JOIN log_members_plans LMP ON LMP.row = MP.id
) LMP2 ON LMP2.member_id = M.id
WHERE T.assigned_by = :u_id;

SELECT M.id, MAX(LD.timestamp) as ld_timestamp, MAX(T.assigned_on) AS date_of_last_task
FROM tasks T
INNER JOIN tasks_entities TE ON TE.task_id = T.id
INNER JOIN members M ON M.id = TE.member_id
INNER JOIN log_members LM ON LM.row = M.id
LEFT OUTER JOIN (
SELECT LD2.*, D2.member_id
FROM dependents D2
INNER JOIN log_dependents LD2 ON D2.id = LD2.row
) D ON D.member_id = M.id
LEFT OUTER JOIN log_dependents LD ON LD.row = D.id
WHERE T.assigned_by = :u_id;

I’ve been looking through the documentation trying to understand why these joins behave differently in Percona than they do in MariaDB. Can you point me in the right direction? Do these differences stem from different underlying mysql versions between MariaDB and Percona?

I apologize for taking so long to reply, I appreciate the help. I know it’s near the holidays but I didn’t know if anyone browsing the forums had any insight into this?
Thanks

Hello,

First of all, yes, PXC is based on MySQL Community with addons and modifications specific to Percona Server and the underlying query optimizer code has many differences comparing to one in MariaDB. With that said, although it is expected that in some cases the EXPLAIN plan will be different between the two, however different result set is something different and in worst case, it may be a sign of a bug in one of them.
Can you create a fully reproducible test case, so that we can compare how the given queries behave on different MySQL flavors? You need to provide all related tables definition, and some example data that is enough to trigger the difference. Are you able to prepare such?

I’d love to help you track down this issue. I am really busy at the moment but I will get the needed tables to you as soon as I can. Thanks so much for your help.

I am still working on getting the requested data to you but I discovered that the issue resides in the WHERE statement. When you remove the where statement both MariaDB and Percona return the same results.

Still working on getting that data for you but wanted to let you know that selecting without the where statement into a temporary table and then doing a separate query on that temporary table with the where statement returns the expected results.