today i moved from Percona XtraDB-Cluster v5.6 to v5.7
the new version has Stricting Mode Enforced by default, so i found several errors,
some related to missing PRIMARY KEYS and i solved adding them to the tables were missing them
but other are about prohibits use of LOCK TABLE
but i don’t know which query or on which DB these locks were required
my log_error_verbosity = 1
moving it to 2 will give more information.
Or how i can discover on which table/database these query are requested ?
here some logs
Feb 16 15:20:40 xxxxxx mysqld[28815]: WSREP: Percona-XtraDB-Cluster prohibits use of LOCK TABLE/FLUSH TABLE <table> WITH READ LOCK/FOR EXPORT with pxc_strict_mode = ENFORCING
Feb 16 15:20:43 xxxxxx mysqld[28815]: WSREP: Percona-XtraDB-Cluster prohibits use of LOCK TABLE/FLUSH TABLE <table> WITH READ LOCK/FOR EXPORT with pxc_strict_mode = ENFORCING
Feb 16 15:20:51 xxxxxx mysqld[28815]: WSREP: Percona-XtraDB-Cluster prohibits use of LOCK TABLE/FLUSH TABLE <table> WITH READ LOCK/FOR EXPORT with pxc_strict_mode = ENFORCING
Feb 16 15:21:01 xxxxxx mysqld[28815]: WSREP: Percona-XtraDB-Cluster prohibits use of LOCK TABLE/FLUSH TABLE <table> WITH READ LOCK/FOR EXPORT with pxc_strict_mode = ENFORCING
Feb 16 15:21:11 xxxxxx mysqld[28815]: WSREP: Percona-XtraDB-Cluster prohibits use of LOCK TABLE/FLUSH TABLE <table> WITH READ LOCK/FOR EXPORT with pxc_strict_mode = ENFORCING
Feb 16 15:21:27 xxxxxx mysqld[28815]: WSREP: Percona-XtraDB-Cluster prohibits use of LOCK TABLE/FLUSH TABLE <table> WITH READ LOCK/FOR EXPORT with pxc_strict_mode = ENFORCING
@soprano, how are you taking backups with your PXC? Also, you can enable the general query log to see every query being executed. Don’t leave this on for too long.
Hi, i don’t think the problem is how i take backups, but in normal use, during the daytime, the backups are scheduled on night.
So something is doing those operations.
I have over 100 databases running in production, so i think if i turn querylog on i will have GB of query going on, and i don’t think it will be so easy to find the queries those are doing this.
If i install PMM i will have a chance to find these query related to the errors i got ?
@soprano
I think you already found queries. this is what you see in error.log.
Application likely executes LOCK TABLES or FLUSH TABLE WITH READ LOCK queries.
You need to find a place in application that executes these queries.
@soprano
you can find queries by either enabling general_log or slow_query_log.
What you do with these queries when you find them, it is totally different question.
If CMS relies on these queries to work properly, it might be not a good idea to disable them.
Easiest way to solve errors would be to set pxc_strict_mode into PERMISSIVE mode,
but again, if your CMS relies on these queries and you need to make sure your application accesses only one PXC node, and does not work with all 3 of them, because of the latter case your application can get broken.
@soprano
You need to make sure you work only with one node at the time, otherwise LOCK TABLE will not work properly and the extension can corrupt your data.
If you are sure that your application works only with one node you can:
ignore error, it just will flood your error.log
set pxc_strict_mode to PERMISSIVE, but in this case you will miss all possible warnings in the future
The feature to use Percona XtraDB Cluster is to have multi master nodes, if i need to use only a node like writer i don’t need to use Percona XtraDB Cluster.
With v5.6 the same databases worked like a charm for years up to 3 days ago when i switched to v5.7, so i don’t know but i suspect that the LOCK TABLES always worked before today, before i suppose v5.6 has no ENFORCING enabled, i didn’t have it my configuration files, but with v5.7 it is set by dafault.
In my setup, an application connect to a virtual host called percona,
at that address there is a Load Balancer that direct the request to two nodes of percona basing on number of requests, so each query is executed always against the same node (up to it is up).
If node1 or node2 go down, the node3 will be inserted in the hosts pool that will receive the requests.
So usually requests to percona → node1 or node2
if for client1 the load balancer choose node1, his requests will go always to node1
if for client2 the load balancer choose node2, his requests will go always to node2
in case node1 or node2 will go down, node3 will be put in the requests pool so
request to percona → node1/node2 or node3
(node1/node2 means which of two is still up).
Backups are taken at night from node3 only (that usually is out of the requests pool, like if it was a slave).
I suspect that i can move the pxc_strict_mode to PERMISSIVE because each request go to only one node and possibily always the same node for the same client.
If two clients will try to LOCK the same TABLE on the different nodes, the statement will be executed and there is possibility that the clients will try to update the same data.
Now Percona XtraDB Cluster will prevent that, but it is hard to predict how your application will react to that. I am not familiar with the extension you mentioned.
hi @soprano, PXC/Galera is not a write-scaling solution. In fact there is no writing scaling solution in MySQL other than sharding. Writing 100 tx/sec to 2 nodes is the same thing as writing 200 tx/sec to 1 node. Writing to multiple PXC/Galera nodes is allowed, but only in cases where you and the application can properly handle the situation, such as not using cluster-conflicting DML like LOCK TABLES. Your load balancer should only send write queries to the same node and not attempt to balance writes. The real feature that you want is synchronous replication, so that if node1 goes offline, node2 and node3 have the same exact data as node1. This is not the case when using traditional async replication.
Correct, 5.6 did not have pxc_strict_mode. This was introduced in 5.7 as a way to prevent non-cluster-style DML, like LOCK TABLES, from being executed and thus causing problems in common applications.
As Vadim said, your only options are set pxc_strict_mode to PERMISSIVE, or remove the DML from the code.
In actuality, you should not being using LOCK TABLES if all of your tables are InnoDB as InnoDB uses row-level locking to handle multiple transactions at the same time.
All my table are InnoDB, default storage-engine is InnoDB, any DB will be automatically created like InnoDB also if different specified.
Anyway i switched to permissive mode, no more messages about LOCK TABLE, but i know they are running, i would like to have them like a warning message anyway (in permissive mode).