Magento 1 on Percona DB produces errors

by shemaya   Last Updated July 11, 2019 18:09 PM - source

We are trying to migrate our DB to MySQL 5.7 on Percona XtraDB cluster.

In the MySQL log file there are errors preventing some operations to be performed.

The errors visible with the current setting are of 2 types:

2019-06-24T08:00:03.187120Z 99531 [ERROR] WSREP: Percona-XtraDB-Cluster prohibits use of GET_LOCK with pxc_strict_mode = ENFORCING

or

2019-06-24T08:39:21.115870Z 100000 [ERROR] WSREP: Percona-XtraDB-Cluster prohibits use of DML command on a table (log_url) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER

Currently the DB cluster is set with

pxc_strict_mode=ENFORCING

Note, we have tried to set up a different mode and locked for stabilizing the BD using the following mode without success

pxc_strict_mode=PERMISSIVE

But the result is instability at the engine level ending in the whole database being unresponsive and requiring a reboot.

However when setting the mode to PERMISSIVE we have the following errors matching the one above:

2019-06-18T09:30:55.552852Z 753 [Warning] WSREP: Percona-XtraDB-Cluster doesn't recommend use of GET_LOCK with pxc_strict_mode = PERMISSIVE

2019-06-18T09:30:55.570212Z 753 [Warning] WSREP: Percona-XtraDB-Cluster doesn't recommend use of RELEASE_LOCK with pxc_strict_mode = PERMISSIVE

Or

2019-06-17T15:30:18.766308Z 764 [Warning] WSREP: Percona-XtraDB-Cluster doesn't recommend use of DML command on a table (catalog_category_product_index_idx) without an explicit primary key with pxc_strict_mode = PERMISSIVE

2019-06-17T15:30:18.770449Z 764 [Warning] WSREP: Percona-XtraDB-Cluster doesn't recommend use of DML command on a table (catalog_category_product_index_enbl_idx) without an explicit primary key with pxc_strict_mode = PERMISSIVE

2019-06-17T15:30:18.795257Z 764 [Warning] WSREP: Percona-XtraDB-Cluster doesn't recommend use of DML command on a table (catalog_category_anc_categs_index_idx) without an explicit primary key with pxc_strict_mode = PERMISSIVE

2019-06-17T15:30:18.797970Z 764 [Warning] WSREP: Percona-XtraDB-Cluster doesn't recommend use of DML command on a table (catalog_category_anc_categs_index_idx) without an explicit primary key with pxc_strict_mode = PERMISSIVE

Having this information shows that there are missing primary keys on at least these tables:

  • catalog_category_product_index_idx

  • catalog_category_product_index_enbl_idx

  • catalog_category_anc_categs_index_idx

  • … and possibly others

Can we add a primary key on these tables ? or is there a setting that I missed regarding the following configuration with the following environment ?`

Environment is composed of:

o MySQL 5.7

o Percona XtraDB cluster

o Magento 1.9.4.1



Related Questions


What is MySQl Pool?

Updated October 02, 2018 18:09 PM

AddIndex in magento2

Updated October 16, 2018 07:09 AM

magento 1.9.3.9 MySQL Database error

Updated July 07, 2018 12:09 PM