Hi Sir,
@matthewb, While trying to find more logs I stepped on stats_mysql_connection_pool_reset_errors table and found below logs, Also we are running the cluster with pxc_strict_mode = Permissive but we bring up nodes in enforcing and change it to permissive as in PXC docs itâs mentioned to bring up node with Enforcing. Have to run in permissive due to architecture and support challenges we canât add a primary key to 2 databases of application.
select * from stats_mysql_connection_pool_reset;;;;;;;;;;;;;;;;;;;;;;e;r;r;o;r;s;
+-----------+-----------+------+----------+----------------+--------------------+-------+------------+------------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| hostgroup | hostname | port | username | client_address | schemaname | errno | count_star | first_seen | last_seen | last_error |
+-----------+-----------+------+----------+----------------+--------------------+-------+------------+------------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 10 | x.x.x.5 | 3406 | root | x.x.x..34 | information_schema | 1053 | 1 | 1672708392 | 1672708392 | Server shutdown in progress |
| 10 | x.x.x.4 | 3406 | user1 | x.x.x.11 | db1 | 1213 | 2 | 1671550836 | 1672182250 | Deadlock found when trying to get lock; try restarting transaction |
| 10 | x.x.x.4 | 3406 | user1 | x.x.x.11 | db2 | 1213 | 2 | 1671550836 | 1671645475 | Deadlock found when trying to get lock; try restarting transaction |
| 10 | x.x.x.5 | 3406 | user2 | x.x.x.11 | information_schema | 1227 | 2 | 1670908327 | 1671081161 | Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation |
| 10 | x.x.x.5 | 3406 | user3 | x.x.x.11 | information_schema | 1227 | 1 | 1670842020 | 1670842020 | Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation |
| 10 | x.x.x.5 | 3406 | user1 | x.x.x.11 | db1 | 1213 | 16 | 1670317267 | 1671645575 | Deadlock found when trying to get lock; try restarting transaction |
| 10 | x.x.x.5 | 3406 | user4 | x.x.x.11 | db3 | 1062 | 2 | 1670307638 | 1673205755 | Duplicate entry 'corezoid_oauth-corezoid_oauth_cache' for key 'oauth_session.PRIMARY' |
| 10 | x.x.x.3 | 3406 | user2 | x.x.x.11 | information_schema | 1227 | 3 | 1668675578 | 1668938231 | Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation |
| 10 | x.x.x.3 | 3406 | user1 | x.x.x.11 | db1 | 1105 | 3 | 1668673977 | 1668674100 | Percona-XtraDB-Cluster prohibits use of DML command on a table (db1.cluster_master) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER |
| 10 | x.x.x.3 | 3406 | user1 | x.x.x.11 | db2 | 1105 | 6 | 1668674038 | 1668674100 | Percona-XtraDB-Cluster prohibits use of DML command on a table (ssgdmz.cluster_master) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER |
| 10 | x.x.x.3 | 3406 | user1 | x.x.x.11 | db2 | 1047 | 5 | 1668673578 | 1668673578 | WSREP has not yet prepared node for application use |
| 10 | x.x.x.3 | 3406 | user2 | x.x.x.11 | information_schema | 1142 | 4 | 1668938233 | 1668938235 | SELECT command denied to user 'user2'@'proxy-server1.domain.com' for table 'version' |
| 10 | x.x.x.5 | 3406 | user1 | x.x.x.11 | db1 | 1205 | 36 | 1669892063 | 1671954717 | Lock wait timeout exceeded; try restarting transaction |
| 10 | x.x.x.5 | 3406 | user1 | x.x.x.11 | db2 | 1062 | 71 | 1670225855 | 1670817588 | Duplicate entry 'com.l7tech.external.assertions.apiportalintegration.server.Porta' for key 'generic_entity.i_classname_name' |
| 10 | x.x.x.5 | 3406 | user1 | x.x.x.11 | db1 | 1105 | 1061 | 1669095667 | 1669099719 | Percona-XtraDB-Cluster prohibits use of DML command on a table (db1.cluster_master) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER |
| 10 | x.x.x.3 | 3406 | root | x.x.x..34 | information_schema | 1146 | 1 | 1668873749 | 1668873749 | Table 'performance_schema.setup_timers' doesn't exist |
| 10 | x.x.x.5 | 3406 | user1 | x.x.x.11 | db2 | 1213 | 75 | 1669230774 | 1671994922 | Deadlock found when trying to get lock; try restarting transaction |
| 10 | x.x.x.5 | 3406 | user1 | x.x.x.11 | db2 | 1105 | 1064 | 1669095611 | 1669099717 | Percona-XtraDB-Cluster prohibits use of DML command on a table (ssgdmz.cluster_master) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER |
| 10 | x.x.x.5 | 3406 |user5 | x.x.x.11 | db4 | 1053 | 2 | 1669600935 | 1669600935 | Server shutdown in progress |
| 10 | x.x.x.5 | 3406 | user2 | x.x.x.11 | information_schema | 1142 | 15 | 1670908114 | 1671086346 | SELECT command denied to user 'user2'@'proxy-server1.domain.com' for table 'events_waits_history_long' |
| 10 | x.x.x.5 | 3406 | user1 | x.x.x.11 | db1 | 1047 | 7 | 1669600932 | 1669600933 | WSREP has not yet prepared node for application use |
| 10 | x.x.x.3 | 3406 | user1 | x.x.x.11 | db1 | 1047 | 5 | 1668673577 | 1668673579 | WSREP has not yet prepared node for application use |
| 10 | x.x.x.5 | 3406 | root | x.x.x..34 | information_schema | 1146 | 1 | 1669798420 | 1669798420 | Table 'performance_schema.setup_timers' doesn't exist |
| 10 | x.x.x.5 | 3406 | user1 | x.x.x.11 | db1 | 1062 | 11 | 1670336392 | 1670405330 | Duplicate entry '2d52f4be6af34db63b4e9e5e8d48-\x00\x00\x00\x00\x00\x00\x00\x0' for key 'service_metrics.nodeid' |
| 10 | x.x.x.5 | 3406 | user1 | x.x.x.11 | db2 | 1205 | 22 | 1669996221 | 1673156196 | Lock wait timeout exceeded; try restarting transaction |
+-----------+-----------+------+----------+----------------+--------------------+-------+------------+------------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
The link you have provided should I run all the queries in Test galera section and provide output?
TEST_GALERA
char *q1 = (char *)"SELECT wsrep_local_state , read_only , wsrep_local_recv_queue , wsrep_desync , wsrep_reject_queries , wsrep_sst_donor_rejects_queries , "
" wsrep_cluster_status, pxc_maint_mode FROM HOST_STATUS_GALERA WHERE hostgroup_id=%d AND hostname='%s' AND port=%d";
char *q2 = (char *)malloc(strlen(q1)+strlen(mmsd->hostname)+32);
sprintf(q2,q1, mmsd->writer_hostgroup, mmsd->hostname, mmsd->port);
mmsd->async_exit_status = mysql_query_start(&mmsd->interr, mmsd->mysql, q2);
free(q2);
#else
char *sv = mmsd->mysql->server_version;
if (strncmp(sv,(char *)"5.7",3)==0 || strncmp(sv,(char *)"8",1)==0) {
// the backend is either MySQL 5.7 or MySQL 8 : INFORMATION_SCHEMA.GLOBAL_STATUS is deprecated
mmsd->async_exit_status=mysql_query_start(&mmsd->interr,mmsd->mysql,"SELECT (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='WSREP_LOCAL_STATE') "
"wsrep_local_state, @@read_only read_only, (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='WSREP_LOCAL_RECV_QUEUE') wsrep_local_recv_queue , "
"@@wsrep_desync wsrep_desync, @@wsrep_reject_queries wsrep_reject_queries, @@wsrep_sst_donor_rejects_queries wsrep_sst_donor_rejects_queries, "
"(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='WSREP_CLUSTER_STATUS') wsrep_cluster_status , "
"(SELECT COALESCE(MAX(VARIABLE_VALUE),'DISABLED') FROM performance_schema.global_variables WHERE variable_name='pxc_maint_mode') pxc_maint_mode ");
} else {
// any other version
mmsd->async_exit_status=mysql_query_start(&mmsd->interr,mmsd->mysql,"SELECT (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='WSREP_LOCAL_STATE') "
"wsrep_local_state, @@read_only read_only, (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='WSREP_LOCAL_RECV_QUEUE') wsrep_local_recv_queue , "
"@@wsrep_desync wsrep_desync, @@wsrep_reject_queries wsrep_reject_queries, @@wsrep_sst_donor_rejects_queries wsrep_sst_donor_rejects_queries, "
"(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='WSREP_CLUSTER_STATUS') wsrep_cluster_status , (SELECT 'DISABLED') pxc_maint_mode");
Thanks
Aditya