search

saltstack / salt

Software to automate the management and configuration of any infrastructure or application at scale. Get access to the Salt software package repository here:
https://repo.saltproject.io/
Apache License 2.0
13.98k stars 5.47k forks source link

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

Open timwsuqld opened 1 month ago

timwsuqld commented 1 month ago

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.

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.) ```yaml 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 ```