Not the answer you need?
Register and ask your own question!

MIgrating from MariaDB to Percona

jRob.52jRob.52 ContributorCurrent User Role Novice
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

Comments

  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    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:
    • 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
    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.
  • jRob.52jRob.52 Contributor Current User Role Novice
    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)
    
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    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.
  • jRob.52jRob.52 Contributor Current User Role Novice
    Yes, 'sql_mode' is the same on both servers.
  • revinrevin Contributor Current User Role Beginner
    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.
  • jRob.52jRob.52 Contributor Current User Role Novice
    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:

    id select_type table type possible_keys key key_len ref rows Extra

    1
    PRIMARY
    <derived2>
    ALL
    NULL
    NULL
    NULL
    NULL
    2



    1
    PRIMARY
    M
    eq_ref
    PRIMARY
    PRIMARY
    8
    LMC.member_id
    1
    Using index


    1
    PRIMARY
    MP
    ref
    member_id
    member_id
    8
    LMC.member_id
    2
    Using index


    1
    PRIMARY
    LM
    ref
    timestamp,changed_by_id,row
    row
    8
    LMC.member_id
    16
    Using where


    1
    PRIMARY
    LMP
    ref
    timestamp,changed_by_id,row
    row
    4
    internal_prod.MP.id
    4
    Using where


    1
    PRIMARY
    TE
    ref
    member_id
    member_id
    9
    LMC.member_id
    1



    1
    PRIMARY
    T
    eq_ref
    PRIMARY
    PRIMARY
    4
    internal_test.TE.task_id
    1
    Using where


    2
    DERIVED
    log_member_calls
    ref
    user_id
    user_id
    4
    const
    25
    Using where; Using filesort



    When run in Percona cluster the EXPLAIN returns:

    id select_type table partitions type possible_keys key key_len ref rows filtered Extra

    1
    PRIMARY
    <derived2>
    NULL
    system
    NULL
    NULL
    NULL
    NULL
    1
    100.00
    NULL


    1
    PRIMARY
    M
    NULL
    const
    PRIMARY
    PRIMARY
    8
    const
    1
    100.00
    Using index


    1
    PRIMARY
    MP
    NULL
    ref
    member_id
    member_id
    8
    const
    1
    100.00
    Using where; Using index


    1
    PRIMARY
    LM
    NULL
    ref
    timestamp,changed_by_id,row
    row
    8
    const
    1
    5.00
    Using where


    1
    PRIMARY
    LMP
    NULL
    range
    timestamp,changed_by_id,row
    timestamp
    4
    NULL
    1
    100.00
    Using where; Using join buffer (Block Nested Loop)


    1
    PRIMARY
    TE
    NULL
    ref
    member_id
    member_id
    9
    const
    9
    100.00
    Using where


    1
    PRIMARY
    T
    NULL
    eq_ref
    PRIMARY
    PRIMARY
    4
    internal_test.TE.task_id
    1
    100.00
    Using where


    2
    DERIVED
    log_member_calls
    NULL
    ref
    user_id
    user_id
    4
    const
    20
    100.00
    Using index condition; Using filesort




    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.
  • jRob.52jRob.52 Contributor Current User Role Novice
    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?
  • jRob.52jRob.52 Contributor Current User Role Novice
    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
  • przemekprzemek Percona Support Engineer Percona Staff Role
    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?
  • jRob.52jRob.52 Contributor Current User Role Novice
    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.
  • jRob.52jRob.52 Contributor Current User Role Novice
    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.
  • jRob.52jRob.52 Contributor Current User Role Novice
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.