INSERT to table without PK possible with insertable VIEW when pxc_strict_mode=ENFORCING Edit
General
Escalation
General
Escalation
Description
Environment
None
Smart Checklist
Activity
Show:
KennT November 1, 2017 at 9:44 AM
PXC 5.7
commit e1d955b17449ba885ecaf34b07a13fec62380d1d
Author: Kenn Takara <kenn.takara@percona.com>
Date: Tue Oct 24 23:40:48 2017 -1000
: INSERT to table without PK possible with insertable VIEW when pxc_strict_mode=ENFORCING Edit
Issue:
With pxc_strict_mode set to ENFORCING or MASTER, PXC does not allow inserts into tables
without a primary key. However, if a view was created on a table without a primary key,
the insert was still able to execute through the view.
Solution:
On INSERT, check that the table, that the view is using, has a primary key.
KennT October 16, 2017 at 3:15 PM
Investigating a fix, where we allow inserts only when all tables involved in the view have primary keys.
Launchpad bug: https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1722493
When pxc_strict_mode is set to enforcing inserting data into a table without a primary key is still possible with insertable views.
mysql> select @@GLOBAL.pxc_strict_mode,@@pxc_strict_mode;
---------------------------------------------+
@@GLOBAL.pxc_strict_mode
@@pxc_strict_mode
---------------------------------------------+
ENFORCING
ENFORCING
---------------------------------------------+
mysql> create table t_wo_pk (id int);
mysql> INSERT INTO t_wo_pk SET id =1;
ERROR 1105 (HY000): Percona-XtraDB-Cluster prohibits use of DML command on a table (test.t_wo_pk) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER
mysql> CREATE VIEW v_wo_pk AS SELECT id FROM t_wo_pk;
mysql> INSERT INTO v_wo_pk SET id = 1;
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_wo_pk;
------
id
------
1
------
Tested against 5.7.19.