Too many connections mysql or mysql hangs with replication Enabled at mysql 8.0.27 / 8.0.27.1

Products

• CA API Gateway

Issue/Introduction

We encountered issues with "Too many connections mysql" when trying to login to mysql.  We also see that mysql is hanging and not providing a response.

We are noticing this after applying the November / December / January monthly platform patch where mysql is at 8.0.27 and greater.

# rpm -qa | grep mysql

mysql-commercial-server-8.0.28-1.1.el7.x86_64

Environment

Release : 10.0 10.1

Component : API GATEWAY

Resolution

A major change in 8.0.27 was this changing of replication to default to the multithreader which was new.
According to the MySQL docs setting replica_parallel_workers=0 disables it.

Our Current Monthly Platform Patch of February 2022 addresses this.

If you are unable to apply the Monthly Patch you can follow the below manual steps instead.

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-27.html
 
Replication: Multithreading is now enabled by default for replica servers

Steps to remediate:

1) Stop SSG service
         # service ssg stop

2) Stop replication
         2A) Stop slave replication on both nodes: mysqladmin stop-slave
         2B) Reset the master configuration on both nodes: mysql -e "reset master"
         2C) Reset the slave configuration on both nodes: mysql -e "reset slave; reset slave all"

3) Update /etc/mysql.cnf to include replica_parallel_workers=0 in the [mysqld] section on both nodes.
         # vi /etc/my.cnf                   on both nodes
 

         **** my.cnf to look as below.
         [mysqld]
         # Added on this date mm/dd/yyyy to use legacy replication
         replica_parallel_workers=0

4) Restart mysql on both nodes.

         # service mysql restart

5) Re-establish replication 
         Follow article:

         https://knowledge.broadcom.com/external/article?articleId=44402

Additional Information

Starting with the monthly Platform patch Layer7_API_PlatformUpdate_64bit_v10.X-CentOS-2022-03-29.L7P the fix in this KB addressed changes to /etc/my.cnf (Gateway Appliance only)

#Added to disable multithreaded replication from mysql 8.0.27 version

replica_parallel_workers=0