# Primary key on a galera cluster **URL:** \ **Category:** Percona XtraDB Cluster 5.x\ **Created:** [September 5, 2018, 8:32am UTC](https://forums.percona.com/t/primary-key-on-a-galera-cluster/6562 "2018-09-05T08:32:08Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![simtardif](https://avatars.discourse-cdn.com/v4/letter/s/6de8d8/32.png) [@simtardif](https://forums.percona.com/u/simtardif)\ **Post date:** [September 5, 2018, 8:32am UTC](https://forums.percona.com/t/primary-key-on-a-galera-cluster/6562/1 "2018-09-05T08:32:08Z")
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 ---
**Author:** ![lorraine.pocklington](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lorraine.pocklington/32/37_2.png) [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)\ **Post date:** [September 5, 2018, 9:15am UTC](https://forums.percona.com/t/primary-key-on-a-galera-cluster/6562/2 "2018-09-05T09:15:08Z")
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](http://galeracluster.com/documentation-webpages/limitations.html#tables-without-primary-keys) ---
**Author:** ![simtardif](https://avatars.discourse-cdn.com/v4/letter/s/6de8d8/32.png) [@simtardif](https://forums.percona.com/u/simtardif)\ **Post date:** [September 5, 2018, 11:54am UTC](https://forums.percona.com/t/primary-key-on-a-galera-cluster/6562/3 "2018-09-05T11:54:00Z")
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 ---
**Author:** ![lorraine.pocklington](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lorraine.pocklington/32/37_2.png) [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)\ **Post date:** [September 5, 2018, 1:08pm UTC](https://forums.percona.com/t/primary-key-on-a-galera-cluster/6562/4 "2018-09-05T13:08:54Z")
OK, let me check in with some of the Percona XtraDB Cluster team here and get back to you. ---
**Author:** ![simtardif](https://avatars.discourse-cdn.com/v4/letter/s/6de8d8/32.png) [@simtardif](https://forums.percona.com/u/simtardif)\ **Post date:** [September 6, 2018, 10:35am UTC](https://forums.percona.com/t/primary-key-on-a-galera-cluster/6562/5 "2018-09-06T10:35:09Z")
Ok thank you! ---
**Author:** ![Vinodh\_Krishnaswamy](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vinodh_krishnaswamy/32/46_2.png) [@Vinodh\_Krishnaswamy](https://forums.percona.com/u/Vinodh_Krishnaswamy)\ **Post date:** [September 6, 2018, 3:18pm UTC](https://forums.percona.com/t/primary-key-on-a-galera-cluster/6562/6 "2018-09-06T15:18:44Z")
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](https://www.percona.com/doc/percona-xtradb-cluster/5.7/wsrep-system-index.html#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](https://www.percona.com/doc/percona-xtradb-cluster/LATEST/features/pxc-strict-mode.html#tables-without-primary-keys) would prevent such writes. You can check this from a simple test below: ```auto 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!](https://www.percona.com/live/e18/registration-information) Sponsorship opportunities can be found [here.](https://www.percona.com/live/e18/be-a-sponsor) ---
**Author:** ![simtardif](https://avatars.discourse-cdn.com/v4/letter/s/6de8d8/32.png) [@simtardif](https://forums.percona.com/u/simtardif)\ **Post date:** [September 7, 2018, 6:59am UTC](https://forums.percona.com/t/primary-key-on-a-galera-cluster/6562/7 "2018-09-07T06:59:10Z")
Hi thanks a lot for your answer. I did not know about this parameter! ---
**Author:** ![przemek](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/przemek/32/3_2.png) [@przemek](https://forums.percona.com/u/przemek)\ **Post date:** [September 20, 2018, 6:21am UTC](https://forums.percona.com/t/primary-key-on-a-galera-cluster/6562/8 "2018-09-20T06:21:00Z")
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): [url][MySQL Bugs: #53375: RBR + no PK =\> High load on slave (table scan/cpu) =\> slave failure](https://bugs.mysql.com/bug.php?id=53375%5B/url%5D) Therefore, having tables w/o PK is just dangerous.