[BUG] mysql_query.run output=grain - Whitespace in dictionary key

[Darkentik]

Description I'm writing a state for automate Icingaweb2 database stuff. For checking if someone has changed the password of an account "admin" i will check the pasword_hash and must save the mysq_query.run output to the grains to be able to use the content of the grain for later checks. My problem now is that in the dictionary of the grain is a key called "rows returned" that has a whitespace in his name. So i'm not able to call it with salt-call grains.get icingaweb2:mysql_check_user:rows returned. But if this key would be called "rows_returned" then i can call it with salt-call grains.get icingaweb2:mysql_check_user:rows_returned.

Setup State:

Check_user_{{ username }}_and_password:
  mysql_query.run:
    - database: icingaweb2
    - query: "Select name,password_hash FROM icingaweb_user WHERE name = 'admin' AND password_hash = '{{ user['pwdhash'] }}';"
    - output: grain
    - grain: icingaweb2
    - key: mysql_check_user

debianserver: ~/ $ salt-call grains.get icingaweb2:mysql_check_user

local:
    ----------
    columns:
        - name
        - password_hash
    query time:
        ----------
        human:
            0.2ms
        raw:
            0.00018
    results:
    rows returned:
        0

Expected behavior debianserver: ~/ $ salt-call grains.get icingaweb2:mysql_check_user

local:
    ----------
    columns:
        - name
        - password_hash
    query time:
        ----------
        human:
            0.2ms
        raw:
            0.00018
    results:
    rows_returned:
        0

Versions Report

salt --versions-report

(Provided by running salt --versions-report. Please also mention any differences in master/minion versions.) Salt Version: Salt: 2019.2.5 Dependency Versions: cffi: Not Installed cherrypy: Not Installed dateutil: 2.7.3 docker-py: Not Installed gitdb: Not Installed gitpython: Not Installed ioflo: Not Installed Jinja2: 2.10 libgit2: Not Installed libnacl: Not Installed M2Crypto: Not Installed Mako: Not Installed msgpack-pure: Not Installed msgpack-python: 0.5.6 mysql-python: 1.3.10 pycparser: Not Installed pycrypto: 2.6.1 pycryptodome: Not Installed pygit2: Not Installed Python: 3.7.3 (default, Jul 25 2020, 13:03:44) python-gnupg: Not Installed PyYAML: 3.13 PyZMQ: 17.1.2 RAET: Not Installed smmap: Not Installed timelib: Not Installed Tornado: 4.5.3 ZMQ: 4.3.1 System Versions: dist: debian 10.5 locale: UTF-8 machine: x86_64 release: 4.19.0-9-amd64 system: Linux version: debian 10.5

[garethgreenaway]

@Darkentik Thanks for the report. We should handle in this code, most likely with a configurable option to convert white space. As a workaround if you wrap the grains key in quotes you'll be able to retrieve the value.

[Darkentik]

@garethgreenaway Hey, thanks for reply. I struggled into weired behavior. First i got it running with your mentioned idea to use quotes in the pillar path but then after some days it don't work anymore and i figured out that it was running ok after removing the quotes. I'm working out a complex automation for Icinga2 with Icingaweb2 and am trying to handle all the database stuff. Because there are no state modules to check data in DB tables i must write my own logic using mysql_query modul from salt. This is a snip of my Salt code for handling DB stuff to check if a DB schema is present or must be imported:

Check_icingaweb2_schema_exists:
  mysql_query.run:
    - database: icingaweb2
    - query: "SHOW tables;"
    - output: grain
    - grain: icingaweb2
    - key: mysql_check_tables

Check download folder for Icingaweb2 mysql schema is present:
  file.directory:
    - name: /srv/icingaweb2
    - user: root
    - group: root

{%- set schema = salt['pillar.get']('icingaweb2:install_files:mysql_schema') %}
Download icingaweb2 MySQL Schema file from aptcache Server:
  cmd.run:
    - name: wget -O /srv/icingaweb2/icingaweb2.schema.sql {{ schema.srcpath }}{{ schema.filename }}
    - require:
      - file: Check download folder for Icingaweb2 mysql schema is present

{%- set checkreturn = salt['grains.get']('icingaweb2:mysql_check_tables:rows returned') %}

{%- if checkreturn is defined and checkreturn == 0 %}

Import DB schema for Icingaweb2:
  cmd.run:
    - name: mysql -u root -D icingaweb2 < /srv/icingaweb2/icingaweb2.schema.sql
    - onfail:
      - mysql_query: Check_icingaweb2_schema_exists
    - require:
      - cmd: "Download icingaweb2 MySQL Schema file from aptcache Server"

{%- endif %}

After this code i also checking with the state modul "mysql_query.run" if a user is stored in a table for Icingaweb2 and then ask some use cases with if and do other stuff. Maybe this is interesting why i was run into this scenario to find out about the whitespace in key from the return of the state modul "mysql_query.run" storing data into the GRAINS.

Best regards. Darkentik