[BUG] MySql cache implementation has issues at scale using pymysql

[bluesliverx]

Description

We use the mysql cache backend for coordinating the cache between a MoM and 5-20 syndics in several locations. When using the current cache/mysql_cache.py implementation with pymysql as the database library. We encountered InterfaceErrors across all syndics and the MoM. See here for more information about threading issues in PyMySQL which is what we were encountering.

Setup

We run the salt masters on physical devices in datacenters around the world. Our Salt version is 3004.1 currently. It is installed via a custom RPM of Salt that creates and packages a virtualenv for python 3.6. We are currently running on CentOS 7 and so python 3.6 is the latest available there but will be moving to Rocky 8 soon. We are using python36-PyMySQL-0.9.3-1.el7.noarch, which I know is not the latest version of PyMySQL and maybe not even the recommended library to use for mysql_cache, but it is the latest that we currently have available. Our cache config looks something like this (with obviously fake values for the important info):

cache: mysql
mysql.database: db1
mysql.host: host1
mysql.password: password1
mysql.table_name: cache1
mysql.user: user1

• on-prem machine
• VM (Virtualbox, KVM, etc. please specify)
• VM running on a cloud service, please be explicit and add details
• container (Kubernetes, Docker, containerd, etc. please specify)
• or a combination, please be explicit
• jails if it is FreeBSD
• classic packaging
• onedir packaging
• used bootstrap to install

Steps to Reproduce the behavior

This is one of many custom patches that we maintain for our Salt infrastructure supporting many thousands of Salt minions. We are in the process of submitting these custom patches to the Salt project to help improve it for others. I'm not sure what you could do to replicate this behavior besides matching the OS, installed pymysql version, cache config above, and Salt version. It may also depend on a specific Maria/MySQL version. We use Maria 10.4.

Expected behavior

The mysql cache should work without issues.

Screenshots

I no longer have these screenshots, but the problem involved InterfaceErrors being thrown in the master logs.

Versions Report

salt --versions-report

(Provided by running salt --versions-report. Please also mention any differences in master/minion versions.)

Salt Version:
          Salt: 3004.1

Dependency Versions:
          cffi: Not Installed
      cherrypy: Not Installed
      dateutil: Not Installed
     docker-py: Not Installed
         gitdb: Not Installed
     gitpython: Not Installed
        Jinja2: 3.1.2
       libgit2: Not Installed
      M2Crypto: 0.35.2
          Mako: Not Installed
       msgpack: 1.0.4
  msgpack-pure: Not Installed
  mysql-python: Not Installed
     pycparser: Not Installed
      pycrypto: Not Installed
  pycryptodome: 3.15.0
        pygit2: Not Installed
        Python: 3.6.8 (default, Nov 16 2020, 16:55:22)
  python-gnupg: Not Installed
        PyYAML: 6.0
         PyZMQ: 21.0.2
         smmap: Not Installed
       timelib: Not Installed
       Tornado: 4.5.3
           ZMQ: 4.3.3

System Versions:
          dist: centos 7 Core
        locale: UTF-8
       machine: x86_64
       release: 3.10.0-1160.36.2.el7.x86_64
        system: Linux
       version: CentOS Linux 7 Core

We are running the same version for minions.

Additional context

The fix we found was to create a fresh connection for each MySQL connection. I have a PR I will submit momentarily that adds a config option to enable this behavior. This does not seem to negatively affect our Salt performance as far as we can tell, and we are running thousands of minions.