java.sql.SQLException: Percona-XtraDB-Cluster prohibits use of DML command on a table (TABLE_NAME) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER.

Products

• VMware Tanzu SQL

Issue/Introduction

PXC Strict Mode (pxc_strict_mode ) is designed to help control behavior of cluster when an experimental/unsupported feature is used. It performs a number of validations at startup and during runtime.

In VMware Tanzu™ SQL with MySQL for VMs, the value of pxc-strict-mode is set as MASTER by default, which can be confirmed with either of below methods:

# Method-1: Check the file of /var/vcap/jobs/pxc-mysql/config/my.cnf
mysql/0f..36:~# cat /var/vcap/jobs/pxc-mysql/config/my.cnf | grep "strict"
pxc-strict-mode                 = MASTER

# Method-1: Connect to mysql server and check variables
mysql> SHOW VARIABLES LIKE "pxc_strict_mode";
+-----------------+--------+
| Variable_name   | Value  |
+-----------------+--------+
| pxc_strict_mode | MASTER |
+-----------------+--------+
1 row in set (0.00 sec)

So below error will be seen if there is no primary key assigned for a table.

for java.sql.SQLException: Percona-XtraDB-Cluster prohibits use of DML command on a table (TABLE_NAME) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER. 

Environment

Product Version: 2.10

Resolution

Although there is a temporary workaround to get rid of the error, which is to set the value of pxc-strict-mode to DISABLE manually: 

mysql> SET GLOBAL pxc_strict_mode=DISABLED;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE "pxc_strict_mode";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| pxc_strict_mode | DISABLED |
+-----------------+----------+
1 row in set (0.00 sec)

However, this value will be reverted back to MASTER is the VM is restarted or recreated. Also, disabling the pxc-strict-mode is not discouraged by Percona (The vendor that provides this HA topology) as the pxc-strict-mode is an important safety guard. 

Therefore, to safely work around this issue, add a primary key or a unique (not null) key to this table. There are other implications to not doing that that could have performance or data integrity implications for replication.