Errors in the process of installing Magento 2 on Percona XtraDB Cluster

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

Hi, this might be helpful to someone.

Tried to install Magento 2 in Kubernetes using Helm chart Bitnami with an external database.

As a database I used Percona XtraDB with default installation with Percona Operator for MySQL.

Magento installation failed with errors “General error: 1105 Percona-XtraDB-Cluster prohibits use of DML command on a table” in several tables.

[Progress: 547 / 1331]
Module 'Magento_MessageQueue':

In Mysql.php line 109:

  SQLSTATE[HY000]: General error: 1105 Percona-XtraDB-Cluster prohibits use o
  f DML command on a table (magento3.queue_poison_pill) without an explicit p
  rimary key with pxc_strict_mode = ENFORCING or MASTER, query was: INSERT IN
  TO `queue_poison_pill` (`version`) VALUES (?)


In Mysql.php line 90:

  SQLSTATE[HY000]: General error: 1105 Percona-XtraDB-Cluster prohibits use o
  f DML command on a table (magento3.queue_poison_pill) without an explicit p
  rimary key with pxc_strict_mode = ENFORCING or MASTER
1 Like
2

The installation was successful after changing the pxc_strict_mode parameter to PERMISSIVE or DISABLED

SET GLOBAL pxc_strict_mode=PERMISSIVE;
1 Like
3

Thanks @daniil.bazhenov . I ran into the exact same issue as you. Thought about using SET GLOBAL pxc_strict_mode=PERMISSIVE; as well, but are concerned about the implications outlined in this Percona document.

The recommendation from that blog post is to ensure every table, and in this case, Magento’s magento3.queue_poison_pill has a primary key. So by setting the permission as PERMISSIVE during installation, I am concerned about data integrity issues in the Magento day-to-day operation post-installation, especially given the comment by @vadimtk here.

Do you set the pxc_strict_mode back to ENFORCING post-installation?

In a Github ticket in the Magento repo about this issue:
https://github.com/magento/magento2/issues/33822

the Magento dev said

The queue_poison_pill table will always have a single version entry, which is basically just a random hash. If some configuration value gets changed using the Magento backend, the poison pill version is changed. The running consumer processes check on this version, but only when a new message is in the queue. So in this case we have a natural key version that hold the single unique entry.
In code base the version is treated as the idFieldName.
protected function _construct()
{
$this->_init(self::QUEUE_POISON_PILL_TABLE, 'version');
}

In my somewhat limited DB vocabulary, it feels that they are saying a primary key isn’t needed since there is only one row/ entry in the table. But I want to share this in case your read is different. Thanks.

I don’t know yet if any other tables in Magento is missing the primary key. I will check tonight. Does PERMISSIVE affect the data integrity of other tables that already have primary keys?

1 Like