PXC 5.7 on Ubuntu 18.04 ignores pxc_strict_mode setting

Hello,

I’ve installed another PXC on a Ubuntu 18.04 VPS and it works very well.

But I have a little problem: I have to run a large legacy software (= do not touch!) that requires

pxc_strict_mode = PERMISSIVE

to run.

Now, there is not a lot of documentation about this setting, I have found it at this page: PXC Strict Mode.

The PXC version I use is this:


mysql -V
mysql Ver 14.14 Distrib 5.7.22-22, for debian-linux-gnu (x86_64) using 7.0

I can succesfully set pxc_strict_mode to PERMISSIVE with the MySQL CLI:


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

mysql> show variables like 'pxc%';
+-----------------------------+------------+
| Variable_name | Value |
+-----------------------------+------------+
| pxc_encrypt_cluster_traffic | OFF |
| pxc_maint_mode | DISABLED |
| pxc_maint_transition_period | 10 |
| pxc_strict_mode | PERMISSIVE |
+-----------------------------+------------+
4 rows in set (0.01 sec)

However I am not going to handle the server forever, I need to make this setting persistent.
All the PXC configuration files are located under
As of now the server is still completely devoid of any database or table.

As usual I have put the setting into the same configuration file where there is everything else, at:

/etc/mysql/percona-xtradb-cluster.conf.d/mysqld.cnf

Here is the anonymised configuration file (I’ll configure more stuff later), including the setting:


[mysqld]
server-id=1
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7
ssl-ca=/etc/mysql/certs/ca.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem
wsrep_provider_options="socket.ssl=yes;socket.ssl_key=/etc/mysql/certs/server-key.pem;socket.ssl_cert=/etc/mysql/certs/server-cert.pem;socket.ssl_ca=/etc/mysql/certs/ca.pem"
bind-address=0.0.0.0
innodb_file_per_table=ON

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

wsrep_cluster_address=gcomm://10.CENSORED
wsrep_node_address=10.CENSORED
wsrep_sst_auth="sstuser:CENSORED"

[B]pxc_strict_mode=PERMISSIVE[/B]

[client]
ssl-ca=/etc/mysql/certs/ca.pem
ssl-cert=/etc/mysql/certs/client-cert.pem
ssl-key=/etc/mysql/certs/client-key.pem

[sst]
encrypt=4
ssl-ca=/etc/mysql/certs/ca.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem

When I start the server, I get no relevant warnings onscreen or in the log file. However if I start the MySQL CLI and I query the setting, I get the following:


mysql> show variables like 'pxc%';
+-----------------------------+-----------+
| Variable_name | Value |
+-----------------------------+-----------+
| pxc_encrypt_cluster_traffic | OFF |
| pxc_maint_mode | DISABLED |
| pxc_maint_transition_period | 10 |
| pxc_strict_mode |[B]ENFORCING[/B]|
+-----------------------------+-----------+
4 rows in set (0.00 sec)

That is, for some reason, it is stuck to ENFORCING despite it’s configured as PERMISSIVE.

I have tried setting it as DISABLED but it’ll still show up as ENFORCING after a restart.

Now, I don’t know if I am typoing something of if there are restrictions I don’t know about, that forcibly set to ENFORCING.

Could I please get an expert opinion on this issue?

Best regards,
Dario Fumagalli

Hello,

any chance to get a response?
I am really having issues with this setting not being taken (no error given in the log), I have company software that won’t run because of this.

Hello,

could you please check with:

grep -iR pxc_strict_mode /etc/mysql

if you don’t overwrite your setting in any other cnf file?

Hello,

I definitely think you’ve found the issue:

grep -iR pxc_strict_mode /etc/mysql

/etc/mysql/percona-xtradb-cluster.conf.d/mysqld.cnf:pxc_strict_mode=PERMISSIVE
/etc/mysql/percona-xtradb-cluster.conf.d/wsrep.cnf:#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
/etc/mysql/percona-xtradb-cluster.conf.d/wsrep.cnf:pxc_strict_mode=ENFORCING

It’s months I set pxc_strict_mode in mysqld.cnf and it works, I guess it’s time to stop doing that and set it in wsrep.cnf.

Thank you for this so simple yet practical suggestion!