saltstack / saltstack/salt

[BUG] MySQL Version caching ignores connection_args breaking states the use multiple MySQL servers

Open
#66,572 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug needs-triage
Dominant language
Python
Stars
15.7k
Forks
5.6k
Avg merge
2d 44m
Merged PRs (30d)
80

Description

Description
Running multiple MySQL server versions on the same server, but on different ports, leads to broken states due to the caching of the MySQL version in the __context__ dunder.

Setup
A SLS similar to below exists in a macro, so multiple calls will have different mgmt_port values to connect to the different MySQL server versions:

{% for host in client_hosts %}
# allow access from {{ host }} to this database
mysql-user-{{ user }}-{{ host }}:
  mysql_user.present:
    - host: {{ host }}
    - password: {{ pass }}
    - name: {{ user }}
    - connection_host: 127.0.0.1
    - connection_port: {{ mgmt_port }}
    - connection_user: {{ mgmt_user }}
    - connection_pass: {{ mgmt_pass }}

  mysql_grants.present:
    - grant: {{ grants }}
    - database: '{{ database }}.*'
    - user: {{ user }}
    - host: {{ host }}
    - connection_host: 127.0.0.1
    - connection_port: {{ mgmt_port }}
    - connection_user: {{ mgmt_user }}
    - connection_pass: {{ mgmt_pass }}

{% endfor %}

This allows multiple MySQL servers of different versions to be running on the same server, with different users setup based on other states.

Please be as specific as possible and give set-up details.

  • VM running on a cloud service, please be explicit and add details - Google Cloud Platform

Steps to Reproduce the behavior
Attempt to setup users on a MySQL 5.7.44 database, and also on a MySQL 8.0.31 database, in the same salt run.

Output is from a mysql_user.present state, that is now connecting to the 8.0.31 server, right after a state that connected to the 5.7.44 server.

[INFO    ] Running state [wp_leaderstogo] at time 14:07:40.226054                                      
[INFO    ] Executing state mysql_user.present for [wp_leaderstogo]                                                                                                                                            
[DEBUG   ] Doing query: SELECT plugin FROM mysql.user WHERE User=%(user)s and Host=%(host)s args: {'user': 'wp_leaderstogo', 'host': 'cloudsqlproxy~%'}                                                       
[DEBUG   ] ()                                                                                                                                                                                                 
[DEBUG   ] Doing query: SELECT User,Host FROM mysql.user WHERE User = %(user)s AND Host = %(host)s AND authentication_string = PASSWORD(%(password)s) args: {'user': 'wp_leaderstogo', 'host': 'cloudsqlproxy~
%', 'password': 'SOME PASSWORD'}                                                                                                                                                                   
[DEBUG   ] An exception occurred in this state: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('SOME PASSWORD')' at line 1")                                                                                                                                                                                    
Traceback (most recent call last):         
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/state.py", line 2422, in call                                                                                                                   
    ret = self.states[cdata["full"]]( 
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/loader/lazy.py", line 159, in __call__   
    ret = self.loader.run(run_func, *args, **kwargs)
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/loader/lazy.py", line 1245, in run
    return self._last_context.run(self._run_as, _func_or_method, *args, **kwargs)
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/loader/lazy.py", line 1260, in _run_as
    return _func_or_method(*args, **kwargs)
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/loader/lazy.py", line 1293, in wrapper
    return f(*args, **kwargs)
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/states/mysql_user.py", line 153, in present
    if __salt__["mysql.user_exists"](
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/loader/lazy.py", line 159, in __call__
    ret = self.loader.run(run_func, *args, **kwargs)
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/loader/lazy.py", line 1245, in run
    return self._last_context.run(self._run_as, _func_or_method, *args, **kwargs)
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/loader/lazy.py", line 1260, in _run_as
    return _func_or_method(*args, **kwargs)
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/modules/mysql.py", line 1597, in user_exists
    _execute(cur, qry, args)
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/modules/mysql.py", line 739, in _execute
    return cur.execute(qry, args)
  File "/opt/saltstack/salt/extras-3.10/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
  File "/opt/saltstack/salt/extras-3.10/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/opt/saltstack/salt/extras-3.10/pymysql/connections.py", line 558, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/opt/saltstack/salt/extras-3.10/pymysql/connections.py", line 822, in _read_query_result
    result.read()
  File "/opt/saltstack/salt/extras-3.10/pymysql/connections.py", line 1200, in read
    first_packet = self.connection._read_packet()
  File "/opt/saltstack/salt/extras-3.10/pymysql/connections.py", line 772, in _read_packet
    packet.raise_for_error()
  File "/opt/saltstack/salt/extras-3.10/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/opt/saltstack/salt/extras-3.10/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('SOME PASSWORD')' at 
line 1")
[ERROR   ] An exception occurred in this state: Traceback (most recent call last):
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/state.py", line 2422, in call
    ret = self.states[cdata["full"]](
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/loader/lazy.py", line 159, in __call__
    ret = self.loader.run(run_func, *args, **kwargs)
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/loader/lazy.py", line 1245, in run
    return self._last_context.run(self._run_as, _func_or_method, *args, **kwargs)
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/loader/lazy.py", line 1260, in _run_as
    return _func_or_method(*args, **kwargs)
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/loader/lazy.py", line 1293, in wrapper
    return f(*args, **kwargs)
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/states/mysql_user.py", line 153, in present
    if __salt__["mysql.user_exists"](
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/loader/lazy.py", line 159, in __call__
    ret = self.loader.run(run_func, *args, **kwargs)
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/loader/lazy.py", line 1245, in run
    return self._last_context.run(self._run_as, _func_or_method, *args, **kwargs)
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/loader/lazy.py", line 1260, in _run_as
    return _func_or_method(*args, **kwargs)
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/modules/mysql.py", line 1597, in user_exists
    _execute(cur, qry, args)
  File "/opt/saltstack/salt/lib/python3.10/site-packages/salt/modules/mysql.py", line 739, in _execute
    return cur.execute(qry, args)
  File "/opt/saltstack/salt/extras-3.10/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
  File "/opt/saltstack/salt/extras-3.10/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/opt/saltstack/salt/extras-3.10/pymysql/connections.py", line 558, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/opt/saltstack/salt/extras-3.10/pymysql/connections.py", line 822, in _read_query_result
    result.read()
  File "/opt/saltstack/salt/extras-3.10/pymysql/connections.py", line 1200, in read
    first_packet = self.connection._read_packet()
  File "/opt/saltstack/salt/extras-3.10/pymysql/connections.py", line 772, in _read_packet
    packet.raise_for_error()
  File "/opt/saltstack/salt/extras-3.10/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/opt/saltstack/salt/extras-3.10/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('SOME PASSWORD')' at 
line 1")

[INFO    ] Completed state [wp_leaderstogo] at time 14:07:40.257665 (duration_in_ms=31.611)

Expected behavior
Users should be created with the correct SQL syntax based on server version. I expect that based on the connection_args variable, we'll use a different __context__ value for server version.

In salt/modules/mysql.py in the version function, which check mysql.version in __context__ without consideration to the connection_args that are currently set, so we'll return the last server version even if it's not valid for these connection_args

def version(**connection_args):
    """
    Return the version of a MySQL server using the output from the ``SELECT
    VERSION()`` query.

    CLI Example:

    .. code-block:: bash

        salt '*' mysql.version
    """
    if "mysql.version" in __context__:
        return __context__["mysql.version"]

Screenshots
If applicable, add screenshots to help explain your problem.

Versions Report

salt --versions-report (Provided by running salt --versions-report. Please also mention any differences in master/minion versions.) 
Salt Version:
          Salt: 3006.8
 
Python Version:
        Python: 3.10.14 (main, Apr  3 2024, 21:30:09) [GCC 11.2.0]
 
Dependency Versions:
          cffi: 1.14.6
      cherrypy: 18.6.1
      dateutil: 2.8.1
     docker-py: 5.0.3
         gitdb: Not Installed
     gitpython: Not Installed
        Jinja2: 3.1.3
       libgit2: Not Installed
  looseversion: 1.0.2
      M2Crypto: Not Installed
          Mako: Not Installed
       msgpack: 1.0.2
  msgpack-pure: Not Installed
  mysql-python: Not Installed
     packaging: 22.0
     pycparser: 2.21
      pycrypto: Not Installed
  pycryptodome: 3.19.1
        pygit2: Not Installed
  python-gnupg: 0.4.8
        PyYAML: 6.0.1
         PyZMQ: 23.2.0
        relenv: 0.16.0
         smmap: Not Installed
       timelib: 0.2.4
       Tornado: 4.5.3
           ZMQ: 4.3.4
 
System Versions:
          dist: ubuntu 22.04.3 jammy
        locale: utf-8
       machine: x86_64
       release: 6.5.0-1020-gcp
        system: Linux
       version: Ubuntu 22.04.3 jammy

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start in salt/modules/mysql.py at the version function and inspect how mysql.version is cached in context without considering connection_args. Reproduce the issue against MySQL 5.7.44 and 8.0.31 using different ports, then verify that mysql_user.present selects syntax matching each server and no longer reuses the wrong version.

Written by the indexing model from the issue text.

Assessment

Tech stack
mysql, python
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Clearly specified
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.