Repeatedly getting galera timeout on PROXYSQL connected to PXC 8 Cluster

Hi Team,

I am repeatedly getting Timeout on Galera health check for almost all nodes in proxysql for PXC cluster nodes.
I have also updated mysql-monitor_galera_healthcheck_timeout from 800 to 2000 but still getting error after 2000, I get this error multiple times in a day and during a timeout the node is not accessble through proxysql.
I see no issues in ping from proxysql to all nodes during timeout.

Error LOgs

| 0 | timeout or error in creating new connection: Can't connect to MySQL server on 'xx.xx.xx.xx' (115) | | writer_hostgroup | reader_hostgroup | active | aurora_port | domain_name | max_lag_ms | check_interval_ms | check_timeout_ms | writer_is_also_reader | new_reader_weight | add_lag_ms | min_lag_ms | lag_num_checks | comment | | xx.xx.xx.xx | 3406 | 1672968302179538 | 0 | 0 | 1 | 0 | 0 | 1 | 1 | 1 | 0 | timeout or error in creating new connection: Can't connect to MySQL server on 'xx.xx.xx.xx' (115) | 2023-01-06 11:19:59 MySQL_Monitor.cpp:1893:monitor_galera_thread(): 
[ERROR] Timeout on Galera health check for xx.xx.xx.83:3406 after 2007ms. If the server is overload, increase mysql-monitor_galera_healthcheck_timeout. 2023-01-06 11:19:59 MySQL_Monitor.cpp:2162:monitor_galera_thread(): 
[ERROR] Got error. mmsd 0x7f25f6132580 , MYSQL 0x7f25fbac3200 , FD 40 : timeout check 2023-01-06 11:19:59 MySQL_Monitor.cpp:1893:monitor_galera_thread(): [ERROR] Timeout on Galera health check for xx.xx.xx.83:3406 after 2007ms. If the server is overload, increase mysql-monitor_galera_healthcheck_timeout. 2023-01-06 11:19:59 MySQL_Monitor.cpp:2162:monitor_galera_thread(): 
[ERROR] Got error. mmsd 0x7f25f61327c0 , MYSQL 0x7f25f8d62d00 , FD 39 : timeout check | writer_hostgroup | reader_hostgroup | active | aurora_port | domain_name | max_lag_ms | check_interval_ms | check_timeout_ms | writer_is_also_reader | new_reader_weight | add_lag_ms | min_lag_ms | lag_num_checks | comment | 2023-01-08 08:35:55 MySQL_Session.cpp:4164:handler_minus1_LogErrorDuringQuery(): 
[WARNING] Error during query on (10,xx.xx.xx.xx,3406,245): 1205, Lock wait timeout exceeded; try restarting transaction 2023-01-08 08:36:35 MySQL_Session.cpp:4164:handler_minus1_LogErrorDuringQuery(): 
[WARNING] Error during query on (10,xx.xx.xx.xx,3406,449): 1205, Lock wait timeout exceeded; try restarting transaction 2023-01-08 08:36:35 MySQL_Session.cpp:4164:handler_minus1_LogErrorDuringQuery(): 
[WARNING] Error during query on (10,xx.xx.xx.xx,3406,170): 1205, Lock wait timeout exceeded; try restarting transaction 2023-01-08 08:36:36 MySQL_Session.cpp:4164:handler_minus1_LogErrorDuringQuery(): 
[WARNING] Error during query on (10,xx.xx.xx.xx,3406,66): 1205, Lock wait timeout exceeded; try restarting transaction
2 Likes

Have you checked the ProxySQL Monitor and Ping tables for more logs? Please provide those here. Did you create the proper monitor check user in MySQL?

2 Likes

Hi Sir @matthewb, Sorry for the delay in reply, please find the requested logs, Also if any other logs are required can you please provide the table name containing logs? Sorry I am new to monitoring thing.

Select * from mysql_server_ping_log;
+-----------+------+------------------+----------------------+------------+
| hostname  | port | time_start_us    | ping_success_time_us | ping_error |
+-----------+------+------------------+----------------------+------------+
| x.x.x.3 | 3406 | 1673258971254071 | 216                  | NULL       |
| x.x.x.4 | 3406 | 1673258971333628 | 405                  | NULL       |
| x.x.x.5 | 3406 | 1673258971413225 | 389                  | NULL       |
| x.x.x.3 | 3406 | 1673258981253622 | 247                  | NULL       |
| x.x.x.4 | 3406 | 1673258981364203 | 467                  | NULL       |
| x.x.x.5 | 3406 | 1673258981474661 | 310                  | NULL       |
| x.x.x.4 | 3406 | 1673258991256415 | 410                  | NULL       |
| x.x.x.5 | 3406 | 1673258991343612 | 458                  | NULL       |
| x.x.x.3 | 3406 | 1673258991430836 | 303                  | NULL       |
| x.x.x.4 | 3406 | 1673259001255328 | 396                  | NULL       |
| x.x.x.5 | 3406 | 1673259001361940 | 254                  | NULL       |
| x.x.x.3 | 3406 | 1673259001468617 | 366                  | NULL       |
| x.x.x.5 | 3406 | 1673259011256818 | 281                  | NULL       |
| x.x.x.4 | 3406 | 1673259011349251 | 486                  | NULL       |
| x.x.x.3 | 3406 | 1673259011441643 | 477                  | NULL       |
| x.x.x.3 | 3406 | 1673259021256560 | 461                  | NULL       |
| x.x.x.4 | 3406 | 1673259021363255 | 564                  | NULL       |
| x.x.x.5 | 3406 | 1673259021469994 | 404                  | NULL       |
| x.x.x.5 | 3406 | 1673259031257131 | 785                  | NULL       |
| x.x.x.3 | 3406 | 1673259031370710 | 249                  | NULL       |
| x.x.x.4 | 3406 | 1673259031484308 | 410                  | NULL       |
| x.x.x.5 | 3406 | 1673259041256484 | 376                  | NULL       |
| x.x.x.4 | 3406 | 1673259041375859 | 482                  | NULL       |
| x.x.x.3 | 3406 | 1673259041495334 | 470                  | NULL       |
| x.x.x.4 | 3406 | 1673259051259412 | 500                  | NULL       |
| x.x.x.3 | 3406 | 1673259051382313 | 312                  | NULL       |
| x.x.x.5 | 3406 | 1673259051505187 | 349                  | NULL       |
| x.x.x.4 | 3406 | 1673259061258180 | 368                  | NULL       |
| x.x.x.3 | 3406 | 1673259061369460 | 284                  | NULL       |
| x.x.x.5 | 3406 | 1673259061480748 | 334                  | NULL       |
| x.x.x.3 | 3406 | 1673259071258812 | 228                  | NULL       |
| x.x.x.5 | 3406 | 1673259071391886 | 314                  | NULL       |
| x.x.x.4 | 3406 | 1673259071525035 | 488                  | NULL       |
| x.x.x.5 | 3406 | 1673259081258867 | 334                  | NULL       |
| x.x.x.3 | 3406 | 1673259081338036 | 269                  | NULL       |
| x.x.x.4 | 3406 | 1673259081417226 | 878                  | NULL       |
| x.x.x.3 | 3406 | 1673259091259518 | 334                  | NULL       |
| x.x.x.4 | 3406 | 1673259091372940 | 2901                 | NULL       |
| x.x.x.5 | 3406 | 1673259091486233 | 391                  | NULL       |
| x.x.x.3 | 3406 | 1673259101259765 | 326                  | NULL       |
| x.x.x.5 | 3406 | 1673259101349934 | 367                  | NULL       |
| x.x.x.4 | 3406 | 1673259101440106 | 388                  | NULL       |
| x.x.x.5 | 3406 | 1673259111261225 | 902                  | NULL       |
| x.x.x.3 | 3406 | 1673259111354912 | 380                  | NULL       |
| x.x.x.4 | 3406 | 1673259111448578 | 400                  | NULL       |
| x.x.x.4 | 3406 | 1673259121260848 | 501                  | NULL       |
| x.x.x.5 | 3406 | 1673259121366504 | 832                  | NULL       |
| x.x.x.3 | 3406 | 1673259121472414 | 418                  | NULL       |
| x.x.x.5 | 3406 | 1673259131261351 | 639                  | NULL       |
| x.x.x.3 | 3406 | 1673259131362899 | 296                  | NULL       |
| x.x.x.4 | 3406 | 1673259131464439 | 573                  | NULL       |
| x.x.x.5 | 3406 | 1673259141261727 | 410                  | NULL       |
| x.x.x.4 | 3406 | 1673259141352567 | 665                  | NULL       |
| x.x.x.3 | 3406 | 1673259141443036 | 223                  | NULL       |
| x.x.x.5 | 3406 | 1673259151263160 | 557                  | NULL       |
| x.x.x.3 | 3406 | 1673259151339585 | 578                  | NULL       |
| x.x.x.4 | 3406 | 1673259151415985 | 402                  | NULL       |
| x.x.x.3 | 3406 | 1673259161262358 | 438                  | NULL       |
| x.x.x.5 | 3406 | 1673259161367746 | 229                  | NULL       |
| x.x.x.4 | 3406 | 1673259161473142 | 670                  | NULL       |
| x.x.x.4 | 3406 | 1673259171264544 | 453                  | NULL       |
| x.x.x.5 | 3406 | 1673259171373618 | 406                  | NULL       |
| x.x.x.3 | 3406 | 1673259171482684 | 238                  | NULL       |
| x.x.x.5 | 3406 | 1673259181264129 | 306                  | NULL       |
| x.x.x.3 | 3406 | 1673259181371745 | 410                  | NULL       |
| x.x.x.4 | 3406 | 1673259181479385 | 401                  | NULL       |
| x.x.x.5 | 3406 | 1673259191264627 | 485                  | NULL       |
| x.x.x.4 | 3406 | 1673259191357567 | 369                  | NULL       |
| x.x.x.3 | 3406 | 1673259191450645 | 342                  | NULL       |
| x.x.x.5 | 3406 | 1673259201264915 | 258                  | NULL       |
| x.x.x.3 | 3406 | 1673259201354695 | 286                  | NULL       |
| x.x.x.4 | 3406 | 1673259201444424 | 629                  | NULL       |
| x.x.x.4 | 3406 | 1673259211266898 | 391                  | NULL       |
| x.x.x.5 | 3406 | 1673259211363784 | 375                  | NULL       |
| x.x.x.3 | 3406 | 1673259211460700 | 348                  | NULL       |
| x.x.x.3 | 3406 | 1673259221265175 | 173                  | NULL       |
| x.x.x.4 | 3406 | 1673259221378028 | 871                  | NULL       |
| x.x.x.5 | 3406 | 1673259221490817 | 285                  | NULL       |
| x.x.x.5 | 3406 | 1673259231267678 | 259                  | NULL       |
| x.x.x.4 | 3406 | 1673259231350888 | 442                  | NULL       |
| x.x.x.3 | 3406 | 1673259231434215 | 279                  | NULL       |
| x.x.x.5 | 3406 | 1673259241269108 | 529                  | NULL       |
| x.x.x.4 | 3406 | 1673259241341262 | 424                  | NULL       |
| x.x.x.3 | 3406 | 1673259241413454 | 416                  | NULL       |
| x.x.x.4 | 3406 | 1673259251268309 | 464                  | NULL       |
| x.x.x.3 | 3406 | 1673259251385938 | 256                  | NULL       |
| x.x.x.5 | 3406 | 1673259251503544 | 207                  | NULL       |
| x.x.x.3 | 3406 | 1673259261268128 | 380                  | NULL       |
| x.x.x.5 | 3406 | 1673259261368163 | 344                  | NULL       |
| x.x.x.4 | 3406 | 1673259261468190 | 1381                 | NULL       |
| x.x.x.5 | 3406 | 1673259271268943 | 437                  | NULL       |
| x.x.x.3 | 3406 | 1673259271342213 | 316                  | NULL       |
| x.x.x.4 | 3406 | 1673259271415503 | 468                  | NULL       |
| x.x.x.5 | 3406 | 1673259281268838 | 811                  | NULL       |
| x.x.x.4 | 3406 | 1673259281337712 | 466                  | NULL       |
| x.x.x.3 | 3406 | 1673259281406500 | 381                  | NULL       |
| x.x.x.5 | 3406 | 1673259291271516 | 517                  | NULL       |
| x.x.x.4 | 3406 | 1673259291379543 | 401                  | NULL       |
| x.x.x.3 | 3406 | 1673259291487596 | 418                  | NULL       |
| x.x.x.4 | 3406 | 1673259301272050 | 466                  | NULL       |
| x.x.x.5 | 3406 | 1673259301387474 | 302                  | NULL       |
| x.x.x.3 | 3406 | 1673259301502880 | 507                  | NULL       |
| x.x.x.4 | 3406 | 1673259311271753 | 637                  | NULL       |
| x.x.x.3 | 3406 | 1673259311381694 | 344                  | NULL       |
| x.x.x.5 | 3406 | 1673259311491684 | 206                  | NULL       |
| x.x.x.4 | 3406 | 1673259321274253 | 534                  | NULL       |
| x.x.x.3 | 3406 | 1673259321367008 | 542                  | NULL       |
| x.x.x.5 | 3406 | 1673259321459667 | 343                  | NULL       |
| x.x.x.4 | 3406 | 1673259331272935 | 437                  | NULL       |
| x.x.x.5 | 3406 | 1673259331361254 | 868                  | NULL       |
| x.x.x.3 | 3406 | 1673259331449574 | 466                  | NULL       |
| x.x.x.3 | 3406 | 1673259341272963 | 303                  | NULL       |
| x.x.x.5 | 3406 | 1673259341374432 | 299                  | NULL       |
| x.x.x.4 | 3406 | 1673259341475871 | 434                  | NULL       |
| x.x.x.4 | 3406 | 1673259351273875 | 497                  | NULL       |
| x.x.x.3 | 3406 | 1673259351405572 | 420                  | NULL       |
| x.x.x.5 | 3406 | 1673259351537252 | 361                  | NULL       |
| x.x.x.3 | 3406 | 1673259361274657 | 244                  | NULL       |
| x.x.x.5 | 3406 | 1673259361390698 | 993                  | NULL       |
| x.x.x.4 | 3406 | 1673259361506774 | 494                  | NULL       |
| x.x.x.5 | 3406 | 1673259371274875 | 301                  | NULL       |
| x.x.x.4 | 3406 | 1673259371373784 | 775                  | NULL       |
| x.x.x.3 | 3406 | 1673259371472681 | 299                  | NULL       |
| x.x.x.3 | 3406 | 1673259381275012 | 326                  | NULL       |
| x.x.x.4 | 3406 | 1673259381401904 | 318                  | NULL       |
| x.x.x.5 | 3406 | 1673259381529018 | 479                  | NULL       |
| x.x.x.5 | 3406 | 1673259391276139 | 429                  | NULL       |
| x.x.x.3 | 3406 | 1673259391361281 | 460                  | NULL       |
| x.x.x.4 | 3406 | 1673259391446489 | 564                  | NULL       |
| x.x.x.4 | 3406 | 1673259401275484 | 455                  | NULL       |
| x.x.x.3 | 3406 | 1673259401391736 | 344                  | NULL       |
| x.x.x.5 | 3406 | 1673259401507970 | 501                  | NULL       |
| x.x.x.4 | 3406 | 1673259411276939 | 444                  | NULL       |
| x.x.x.3 | 3406 | 1673259411370634 | 370                  | NULL       |
| x.x.x.5 | 3406 | 1673259411464313 | 294                  | NULL       |
| x.x.x.5 | 3406 | 1673259421279131 | 388                  | NULL       |
| x.x.x.3 | 3406 | 1673259421360811 | 273                  | NULL       |
| x.x.x.4 | 3406 | 1673259421442405 | 721                  | NULL       |
| x.x.x.5 | 3406 | 1673259431278221 | 244                  | NULL       |
| x.x.x.4 | 3406 | 1673259431384926 | 502                  | NULL       |
| x.x.x.3 | 3406 | 1673259431491683 | 1868                 | NULL       |
| x.x.x.5 | 3406 | 1673259441278705 | 367                  | NULL       |
| x.x.x.3 | 3406 | 1673259441406720 | 275                  | NULL       |
| x.x.x.4 | 3406 | 1673259441534708 | 583                  | NULL       |
| x.x.x.5 | 3406 | 1673259451279043 | 368                  | NULL       |
| x.x.x.3 | 3406 | 1673259451410772 | 378                  | NULL       |
| x.x.x.4 | 3406 | 1673259451542420 | 866                  | NULL       |
| x.x.x.3 | 3406 | 1673259461278354 | 384                  | NULL       |
| x.x.x.5 | 3406 | 1673259461378245 | 344                  | NULL       |
| x.x.x.4 | 3406 | 1673259461478084 | 499                  | NULL       |
| x.x.x.5 | 3406 | 1673259471280886 | 361                  | NULL       |
| x.x.x.4 | 3406 | 1673259471407279 | 369                  | NULL       |
| x.x.x.3 | 3406 | 1673259471533675 | 336                  | NULL       |
| x.x.x.5 | 3406 | 1673259481280614 | 336                  | NULL       |
| x.x.x.4 | 3406 | 1673259481395183 | 478                  | NULL       |
| x.x.x.3 | 3406 | 1673259481509747 | 469                  | NULL       |
| x.x.x.5 | 3406 | 1673259491281015 | 490                  | NULL       |
| x.x.x.4 | 3406 | 1673259491370860 | 476                  | NULL       |
| x.x.x.3 | 3406 | 1673259491460704 | 420                  | NULL       |
| x.x.x.5 | 3406 | 1673259501280688 | 499                  | NULL       |
| x.x.x.3 | 3406 | 1673259501397057 | 183                  | NULL       |
| x.x.x.4 | 3406 | 1673259501513569 | 431                  | NULL       |
| x.x.x.4 | 3406 | 1673259511282568 | 1249                 | NULL       |
| x.x.x.3 | 3406 | 1673259511394173 | 388                  | NULL       |
| x.x.x.5 | 3406 | 1673259511505499 | 370                  | NULL       |
| x.x.x.4 | 3406 | 1673259521281213 | 890                  | NULL       |
| x.x.x.3 | 3406 | 1673259521355261 | 350                  | NULL       |
| x.x.x.5 | 3406 | 1673259521429290 | 533                  | NULL       |
| x.x.x.4 | 3406 | 1673259531282560 | 514                  | NULL       |
| x.x.x.5 | 3406 | 1673259531394264 | 1962                 | NULL       |
| x.x.x.3 | 3406 | 1673259531505951 | 599                  | NULL       |
| x.x.x.3 | 3406 | 1673259541283216 | 280                  | NULL       |
| x.x.x.5 | 3406 | 1673259541377867 | 728                  | NULL       |
| x.x.x.4 | 3406 | 1673259541472428 | 759                  | NULL       |
| x.x.x.5 | 3406 | 1673259551284483 | 367                  | NULL       |
| x.x.x.3 | 3406 | 1673259551353045 | 230                  | NULL       |
| x.x.x.4 | 3406 | 1673259551421586 | 491                  | NULL       |
| x.x.x.5 | 3406 | 1673259561283599 | 406                  | NULL       |
| x.x.x.4 | 3406 | 1673259561395960 | 482                  | NULL       |
| x.x.x.3 | 3406 | 1673259561508083 | 361                  | NULL       |
+-----------+------+------------------+----------------------+------------+


select * from mysql_server_connect_log;
+-----------+------+------------------+-------------------------+---------------+
| hostname  | port | time_start_us    | connect_success_time_us | connect_error |
+-----------+------+------------------+-------------------------+---------------+
| x.x.x.4 | 3406 | 1673259100837141 | 11068                   | NULL          |
| x.x.x.5 | 3406 | 1673259101617002 | 11852                   | NULL          |
| x.x.x.3 | 3406 | 1673259102396804 | 7958                    | NULL          |
| x.x.x.3 | 3406 | 1673259160839982 | 6738                    | NULL          |
| x.x.x.4 | 3406 | 1673259161386390 | 11524                   | NULL          |
| x.x.x.5 | 3406 | 1673259161932659 | 10413                   | NULL          |
| x.x.x.5 | 3406 | 1673259220839345 | 7129                    | NULL          |
| x.x.x.4 | 3406 | 1673259221461201 | 9434                    | NULL          |
| x.x.x.3 | 3406 | 1673259222083257 | 13779                   | NULL          |
| x.x.x.5 | 3406 | 1673259280839773 | 8984                    | NULL          |
| x.x.x.3 | 3406 | 1673259281609604 | 7677                    | NULL          |
| x.x.x.4 | 3406 | 1673259282379602 | 10224                   | NULL          |
| x.x.x.4 | 3406 | 1673259340841077 | 14607                   | NULL          |
| x.x.x.5 | 3406 | 1673259341264678 | 8511                    | NULL          |
| x.x.x.3 | 3406 | 1673259341688369 | 10647                   | NULL          |
| x.x.x.5 | 3406 | 1673259400841371 | 10008                   | NULL          |
| x.x.x.4 | 3406 | 1673259401450834 | 13194                   | NULL          |
| x.x.x.3 | 3406 | 1673259402060421 | 13667                   | NULL          |
| x.x.x.4 | 3406 | 1673259460842489 | 14628                   | NULL          |
| x.x.x.3 | 3406 | 1673259461367615 | 12311                   | NULL          |
| x.x.x.5 | 3406 | 1673259461892615 | 8388                    | NULL          |
| x.x.x.4 | 3406 | 1673259520841850 | 11504                   | NULL          |
| x.x.x.3 | 3406 | 1673259521443045 | 14502                   | NULL          |
| x.x.x.5 | 3406 | 1673259522044465 | 12231                   | NULL          |
| x.x.x.4 | 3406 | 1673259580842200 | 9253                    | NULL          |
| x.x.x.5 | 3406 | 1673259581632958 | 12875                   | NULL          |
| x.x.x.3 | 3406 | 1673259582423583 | 7631                    | NULL          |
| x.x.x.3 | 3406 | 1673259640841919 | 8934                    | NULL          |
| x.x.x.4 | 3406 | 1673259641273691 | 11138                   | NULL          |
| x.x.x.5 | 3406 | 1673259641705424 | 9169                    | NULL          |
+-----------+------+------------------+-------------------------+---------------+

1 Like

The two log tables you show indicate no connection issues between ProxySQL and PXC. The “Lock wait timeout” is concerning. Are you running any ALTER or other DDL’s on the PXC?

Here is the query that proxysql is trying to execute: proxysql/MySQL_Monitor.cpp at v2.x · sysown/proxysql · GitHub

Can you run that query manually and see if you get the same timeouts?

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

1 Like

This table, stats_mysql_connection_pool_reset_errors, is showing all kinds of errors! You have permissions error, deadlocks, duplicate PKs, query rejection due to PERMISSIVE, etc. And missing tables! Fix all of these issues and I think your timeout issues will go away.

Are you sending write traffic to all nodes? If so, that is a bad design (I see deadlock errors in the log which indicates you are doing this). You should send all writes to a single node.