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

Primary key on a galera cluster

simtardifsimtardif ContributorCurrent User Role Novice
Hi, I read in the galera documentation that every tables must have a primary key in a galera cluster. I made some test and data seems to be replicated even on some table with no primary key ( i tried, insert update and delete). So what is the risk with tables with no primary key in a galera cluster? Thank you

Comments

  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Hi there, here's the info on that:
    When tables lack a primary key, rows can appear in different order on different nodes in your cluster. As such, queries like SELECT...LIMIT... can return different results. Additionally, on such tables the DELETE statement is unsupported.

    Note: If you have a table without a primary key, it is always possible to add anAUTO_INCREMENT column to the table without breaking your application.

    http://galeracluster.com/documentation-webpages/limitations.html#tables-without-primary-keys
  • simtardifsimtardif Contributor Current User Role Novice
    Hi thanks I had red that too. But I tried some delete and the delete have been replicated so I was wondering if if it is still the case with newer version
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    OK, let me check in with some of the Percona XtraDB Cluster team here and get back to you.
  • simtardifsimtardif Contributor Current User Role Novice
    Ok thank you!
  • vinodh.krishnaswamyvinodh.krishnaswamy Percona Percona Staff Role
    Hello,
    But I tried some delete and the delete have been replicated so I was wondering if if it is still the case with newer version
    The limitation on the table without PK still applies. But internally this is taken care when wsrep_certify_nonPK variable is enabled for the non PK tables by creating automatic PK internally by PXC. The variable is enabled by default, so you won't see the issue till the variable is disabled. And enabling the pxc_strict_mode=ENFORCING would prevent such writes. You can check this from a simple test below:
    mysql> set global pxc_strict_mode=PERMISSIVE;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> create table testnonPK (id int, name varchar(20));
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> insert into testnonPK values (2, "test insert"),(5, "test insert"),(4, "test insert"),(3, "test insert"), (1, "test insert");
    Query OK, 5 rows affected, 1 warning (0.00 sec)
    Records: 5  Duplicates: 0  Warnings: 1
    
    mysql> select * from testnonPK;
    +------+-------------+
    | id   | name        |
    +------+-------------+
    |    2 | test insert |
    |    5 | test insert |
    |    4 | test insert |
    |    3 | test insert |
    |    1 | test insert |
    +------+-------------+
    5 rows in set (0.00 sec)
    
    mysql> delete from testnonPK where id >3 limit 1;
    Query OK, 1 row affected, 1 warning (0.02 sec)
    
    mysql> select * from testnonPK;
    +------+-------------+
    | id   | name        |
    +------+-------------+
    |    2 | test insert |
    |    4 | test insert |
    |    3 | test insert |
    |    1 | test insert |
    +------+-------------+
    4 rows in set (0.00 sec)
    
    mysql> show global variables like 'wsrep_%PK';
    +---------------------+-------+
    | Variable_name       | Value |
    +---------------------+-------+
    | wsrep_certify_nonPK | ON    |
    +---------------------+-------+
    1 row in set (0.00 sec)
    
    mysql> set global wsrep_certify_nonPK=OFF;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from testnonPK;
    +----+-------------+
    | id | name        |
    +----+-------------+
    |  2 | test insert |
    |  4 | test insert |
    |  3 | test insert |
    |  1 | test insert |
    +----+-------------+
    4 rows in set (0.00 sec)
    
    mysql> delete from testnonPK where id >2 limit 1;
    ERROR 1213 (40001): WSREP detected deadlock/conflict and aborted the transaction. Try restarting the transaction
    mysql> delete from testnonPK where id >2 limit 1;
    ERROR 1213 (40001): WSREP detected deadlock/conflict and aborted the transaction. Try restarting the transaction
    mysql> set global wsrep_certify_nonPK=ON;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delete from testnonPK where id >2 limit 1;
    Query OK, 1 row affected, 1 warning (0.00 sec)
    
    mysql> select * from testnonPK;
    +----+-------------+
    | id | name        |
    +----+-------------+
    |  2 | test insert |
    |  3 | test insert |
    |  1 | test insert |
    +----+-------------+
    3 rows in set (0.01 sec)
    

    Hope this helps you!





    Best Regards,
    Vinodh Krishnaswamy,

    Interested in attending Percona Live Europe? Find out more here!
    Sponsorship opportunities can be found here.
  • simtardifsimtardif Contributor Current User Role Novice
    Hi thanks a lot for your answer. I did not know about this parameter!
  • przemekprzemek Percona Support Engineer Percona Staff Role
    Hi, it is very important to have PK also for performance reasons. Especially for a relatively big table without PK, large update or delete transaction would completely block your cluster for a very long time!
    It's the same issue as normal async slave with ROW replication with addition that Galera pauses on replication lag (Flow Control): https://bugs.mysql.com/bug.php?id=53375
    Therefore, having tables w/o PK is just dangerous.
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.