ansible mysql_user error 1064 You have an error in your SQL syntax check the manual that corresponds to your MySQL server version for the right syntax

[rajcspsg]

SUMMARY

I'm using mysql_user ansible module to generate password and update in the mysql version 8.0 database. I'm expecting password to be updated successfully but I'm getting error.

ISSUE TYPE

• Bug Report

COMPONENT NAME

mysql_user

ANSIBLE VERSION

ansible [core 2.15.3]
  config file = None
  configured module search path = ['/home/rajkumar/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/rajkumar/.local/lib/python3.10/site-packages/ansible
  ansible collection location = /home/rajkumar/.ansible/collections:/usr/share/ansible/collections
  executable location = /home/rajkumar/.local/bin/ansible
  python version = 3.10.12 (main, Jun 11 2023, 05:26:28) [GCC 11.4.0] (/usr/bin/python3)
  jinja version = 3.0.3
  libyaml = True

COLLECTION VERSION

CONFIGURATION

CONFIG_FILE() = None

OS / ENVIRONMENT

 lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 22.04.3 LTS
Release:        22.04
Codename:       jammy

STEPS TO REPRODUCE

---
- hosts: all
  become: true
  tasks:
    - name: Make sure we can connect
      ping:
    - name: Update the apt cache
      apt: update_cache=yes cache_valid_time=3600
    - name: Install PHP
      apt: 
        name:
          - php
          - php-fpm
          - php-mysql
          - php-xml
        state: present
    - name: Remove apache2
      apt: name=apache2 state=absent
    - name: Install MySQL
      apt:
        name:
          - mysql-server-8.0
          - python3-mysqldb
    - name: Generate new root password
      command: openssl rand -hex 7
      register: mysql_new_root_password  
    - name: Remove anonymous users
      mysql_user: name="" state=absent
    - name: Remove test database
      mysql_db: name=test state=absent
    - name: Update root password
      mysql_user: 
        name: root
        host:
          - "{{ansible_hostname}}"
          - 127.0.0.1
          - ::1
          - localhost
        password: "{{mysql_new_root_password.stdout}}"
    - name: Output new root password
      debug: msg="new root password is {{mysql_new_root_password.stdout}}"

EXPECTED RESULTS

The root user password of mysql should be update successfully.

ACTUAL RESULTS

TASK [Update root password] ****************************************************

fatal: [default]: FAILED! => {"changed": false, "msg": "(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 'ubuntu-lunar', '127.0.0.1', '::1', 'localhost']'' at line 1")"}

Note: The complete code this here in github in this folder https://github.com/rajcspsg/ansible-beg-to-pro/tree/master/ch03/ansible-wordpress

Please let me know if you have any questions.

[Andersson007]

@rajcspsg hello, thanks for opening the issue.
Could you please add ignore_errors: true to the Update root password task and show what the Output new root password task show?
thanks

[rajcspsg]

I removed the below section and the error is gone. Seems like the error is due to it is converting list into string

host:
          - "{{ansible_hostname}}"
          - 127.0.0.1
          - ::1
          - localhost

[Andersson007]

@rajcspsg thanks for the feedback and closing the issue!