mysql_db: ProgrammingError when using quotes

[Andersson007]

Copied from ansible-collections/community.general#148
Initially reported by @Andersson007

Copied from ansible/ansible#47051
Initially reported by @azielke

SUMMARY

When using quoted datebase names (e.g. name contains a dot), the database is successfully created on the first ansible run, but subsequent runs return an ProgrammingError.

When not using quotes, the database is not created, because it contains a dot:

TASK [Create dummy db] ***************************************************************************************************************
An exception occurred during task execution. To see the full traceback, use -vvv. The error was: SQLParseError: MySQL does not support database with more than 1 dots
fatal: [localhost]: FAILED! => {"changed": false, "msg": "error creating database: MySQL does not support database with more than 1 dots"}

ISSUE TYPE

• Bug Report

COMPONENT NAME

mysql_db

ANSIBLE VERSION

ansible 2.7.0
  config file = /home/azielke/ansible/ansible.cfg
  configured module search path = [u'/home/azielke/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python2.7/dist-packages/ansible
  executable location = /usr/bin/ansible
  python version = 2.7.12 (default, Dec  4 2017, 14:50:18) [GCC 5.4.0 20160609]

CONFIGURATION

DEFAULT_BECOME(/home/azielke/ansible/ansible.cfg) = True
DEFAULT_BECOME_ASK_PASS(/home/azielke/ansible/ansible.cfg) = False
DEFAULT_BECOME_METHOD(/home/azielke/ansible/ansible.cfg) = sudo
DEFAULT_BECOME_USER(/home/azielke/ansible/ansible.cfg) = root
DEFAULT_FORKS(/home/azielke/ansible/ansible.cfg) = 10
DEFAULT_HOST_LIST(/home/azielke/ansible/ansible.cfg) = [u'/home/azielke/ansible/hosts']
DEFAULT_REMOTE_USER(/home/azielke/ansible/ansible.cfg) = azielke
DEFAULT_ROLES_PATH(/home/azielke/ansible/ansible.cfg) = [u'/home/azielke/ansible/galaxy_roles']

OS / ENVIRONMENT

OS: Ubuntu 16.04 LTS

mysql  Ver 15.1 Distrib 10.0.36-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

STEPS TO REPRODUCE

- hosts: all
  tasks:
    - name: "Create dummy db"
      mysql_db:
        name: "`dummy.tld`"

First run creates the database - 2nd run produces the error below.

EXPECTED RESULTS

task ok

ACTUAL RESULTS

TASK [Create dummy db] ***************************************************************************************************************
task path: /home/azielke/ansible/site.yml:3
<localhost> ESTABLISH SSH CONNECTION FOR USER: azielke
<localhost> SSH: EXEC ssh -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=azielke -o ConnectTimeout=10 -o ControlPath=/home/azielke/.ansible/cp/d75ede5cf8 localhost '/bin/sh -c '"'"'echo ~azielke && sleep 0'"'"''
<localhost> (0, '/home/azielke\n', '')
<localhost> ESTABLISH SSH CONNECTION FOR USER: azielke
<localhost> SSH: EXEC ssh -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=azielke -o ConnectTimeout=10 -o ControlPath=/home/azielke/.ansible/cp/d75ede5cf8 localhost '/bin/sh -c '"'"'( umask 77 && mkdir -p "` echo /home/azielke/.ansible/tmp/ansible-tmp-1539597525.47-108280598146597 `" && echo ansible-tmp-1539597525.47-108280598146597="` echo /home/azielke/.ansible/tmp/ansible-tmp-1539597525.47-108280598146597 `" ) && sleep 0'"'"''
<localhost> (0, 'ansible-tmp-1539597525.47-108280598146597=/home/azielke/.ansible/tmp/ansible-tmp-1539597525.47-108280598146597\n', '')
Using module file /usr/lib/python2.7/dist-packages/ansible/modules/database/mysql/mysql_db.py
<localhost> PUT /home/azielke/.ansible/tmp/ansible-local-25745avF4wJ/tmpYP1hby TO /home/azielke/.ansible/tmp/ansible-tmp-1539597525.47-108280598146597/AnsiballZ_mysql_db.py
<localhost> SSH: EXEC sftp -b - -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=azielke -o ConnectTimeout=10 -o ControlPath=/home/azielke/.ansible/cp/d75ede5cf8 '[localhost]'
<localhost> (0, 'sftp> put /home/azielke/.ansible/tmp/ansible-local-25745avF4wJ/tmpYP1hby /home/azielke/.ansible/tmp/ansible-tmp-1539597525.47-108280598146597/AnsiballZ_mysql_db.py\n', '')
<localhost> ESTABLISH SSH CONNECTION FOR USER: azielke
<localhost> SSH: EXEC ssh -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=azielke -o ConnectTimeout=10 -o ControlPath=/home/azielke/.ansible/cp/d75ede5cf8 localhost '/bin/sh -c '"'"'chmod u+x /home/azielke/.ansible/tmp/ansible-tmp-1539597525.47-108280598146597/ /home/azielke/.ansible/tmp/ansible-tmp-1539597525.47-108280598146597/AnsiballZ_mysql_db.py && sleep 0'"'"''
<localhost> (0, '', '')
<localhost> ESTABLISH SSH CONNECTION FOR USER: azielke
<localhost> SSH: EXEC ssh -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=azielke -o ConnectTimeout=10 -o ControlPath=/home/azielke/.ansible/cp/d75ede5cf8 -tt localhost '/bin/sh -c '"'"'sudo -H -S -n -u root /bin/sh -c '"'"'"'"'"'"'"'"'echo BECOME-SUCCESS-rgxvvekydkniwiefxzmpltfsszrnobdi; /usr/bin/python /home/azielke/.ansible/tmp/ansible-tmp-1539597525.47-108280598146597/AnsiballZ_mysql_db.py'"'"'"'"'"'"'"'"' && sleep 0'"'"''
Escalation succeeded
<localhost> (1, '\r\n\r\n{"msg": "error creating database: (1007, \\"Can\'t create database \'dummy.tld\'; database exists\\")", "failed": true, "exception": "Traceback (most recent call last):\\n  File \\"/tmp/ansible_mysql_db_payload_yBdsdw/__main__.py\\", line 381, in main\\n    changed = db_create(cursor, db, encoding, collation)\\n  File \\"/tmp/ansible_mysql_db_payload_yBdsdw/__main__.py\\", line 248, in db_create\\n    cursor.execute(query, query_params)\\n  File \\"/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py\\", line 226, in execute\\n    self.errorhandler(self, exc, value)\\n  File \\"/usr/lib/python2.7/dist-packages/MySQLdb/connections.py\\", line 36, in defaulterrorhandler\\n    raise errorvalue\\nProgrammingError: (1007, \\"Can\'t create database \'dummy.tld\'; database exists\\")\\n", "invocation": {"module_args": {"ssl_key": null, "login_port": 3306, "config_file": "/root/.my.cnf", "name": "`dummy.tld`", "encoding": "", "login_user": null, "login_host": "localhost", "ignore_tables": [], "ssl_ca": null, "login_unix_socket": null, "state": "present", "ssl_cert": null, "login_password": null, "collation": "", "quick": true, "target": null, "connect_timeout": 30, "single_transaction": false}}}\r\n', 'Shared connection to localhost closed.\r\n')
<localhost> ESTABLISH SSH CONNECTION FOR USER: azielke
<localhost> SSH: EXEC ssh -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o User=azielke -o ConnectTimeout=10 -o ControlPath=/home/azielke/.ansible/cp/d75ede5cf8 localhost '/bin/sh -c '"'"'rm -f -r /home/azielke/.ansible/tmp/ansible-tmp-1539597525.47-108280598146597/ > /dev/null 2>&1 && sleep 0'"'"''
<localhost> (0, '', '')
The full traceback is:
Traceback (most recent call last):
  File "/tmp/ansible_mysql_db_payload_yBdsdw/__main__.py", line 381, in main
    changed = db_create(cursor, db, encoding, collation)
  File "/tmp/ansible_mysql_db_payload_yBdsdw/__main__.py", line 248, in db_create
    cursor.execute(query, query_params)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 226, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorvalue
ProgrammingError: (1007, "Can't create database 'dummy.tld'; database exists")

fatal: [localhost]: FAILED! => {
    "changed": false,
    "invocation": {
        "module_args": {
            "collation": "",
            "config_file": "/root/.my.cnf",
            "connect_timeout": 30,
            "encoding": "",
            "ignore_tables": [],
            "login_host": "localhost",
            "login_password": null,
            "login_port": 3306,
            "login_unix_socket": null,
            "login_user": null,
            "name": "`dummy.tld`",
            "quick": true,
            "single_transaction": false,
            "ssl_ca": null,
            "ssl_cert": null,
            "ssl_key": null,
            "state": "present",
            "target": null
        }
    },
    "msg": "error creating database: (1007, \"Can't create database 'dummy.tld'; database exists\")"
}