Primary key on a galera cluster

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

Hi there, here’s the info on that:

http://galeracluster.com/documentation-webpages/limitations.html#tables-without-primary-keys

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

OK, let me check in with some of the Percona XtraDB Cluster team here and get back to you.

Ok thank you!

Hello,

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.

Hi thanks a lot for your answer. I did not know about this parameter!

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
Therefore, having tables w/o PK is just dangerous.