Output to see why my playbook is failing?

[itinneed2022]

I have the following playbook:

---
- hosts: localhost
  gather_facts: False
  vars:
    host_name: "myserver.domain.com"
    database_name: "mydb"
    database_port: "5501"
    database_user: "sql_svc"
    database_password: "Test1234"
  tasks:
    - name: Simple select query to db
      community.mysql.mysql_query:
        login_host: "{{ host_name }}"
        login_db: "{{ database_name }}"
        login_port: "{{ database_port }}"
        login_user: "{{ database_user }}"
        login_password: "{{ database_password }}"
        query: 
        - SELECT * FROM SERVER_QUEUE
        single_transaction: true

When I run it, I only get this error:

*******************************************************************************************************************************************************************************************************************
fatal: [localhost]: FAILED! => {"changed": false, "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (2013, 'Lost connection to MySQL server during query ([Errno 104] Connection reset by peer)')"}

But if I run it with sqlcmd, it works:

sqlcmd -S myserver.domain.com,5501 -U sql_svc -d mydb -Q "SELECT * FROM SERVER_QUEUE;" -C
# lots of output

What am I missing here?

[Andersson007]

@itinneed2022 you don't run this playbook on your ansible control node, right? (at least, I don't see delegate_to: localhost)

The "[Errno 104] Connection reset by peer" error indicates that there's some connection configuration issue. The reason why your sqlcmd works is probably because you run it on another server but that ansible task runs on your DB host (ansible sends it via ssh on that DB host and executes it there on the python interpreter, so it tries to connect from that host to the same host).
Take a look at logs and check your connection configuration on the DB server side to be sure connection is allowed.

[itinneed2022]

@Andersson007 That threw a different error:

    "msg": "Unsupported parameters for (community.mysql.mysql_query) module: delegate_to. Supported parameters include: connect_timeout, login_host, ca_cert (ssl_ca), single_transaction, login_db, login_unix_socket, positional_args, named_args, login_user, config_file, check_hostname, query, client_cert (ssl_cert), login_port, client_key (ssl_key), login_password."
}

The new playbook is:

---
- hosts: localhost
  gather_facts: False
  vars:
    host_name: "myserver.domain.com"
    database_name: "mydb"
    database_port: "5501"
    database_user: "sql_svc"
    database_password: "Test1234"
  tasks:
    - name: Simple select query to db
      community.mysql.mysql_query:
        login_host: "{{ host_name }}"
        login_db: "{{ database_name }}"
        login_port: "{{ database_port }}"
        login_user: "{{ database_user }}"
        login_password: "{{ database_password }}"
        query: 
        - SELECT * FROM SERVER_QUEUE
        single_transaction: true
        delegate_to: localhost

[itinneed2022]

It might be pymysql. I'm trying to create a connection with it but nothing works. pyodbc works but it can't be used with the community.mysql collection.

[itinneed2022]

The dba I've been messaging with for two days just got back to me. My database isn't mysql, its mssql. >:(
I switched collections and now it works.

[Andersson007]

@itinneed2022 delegate_to is not a module parameter, it's a task level parameter, so you need to move it like in the following:

    - name: Simple select query to db
      delegate_to: localhost
      community.mysql.mysql_query:
        login_host: "{{ host_name }}"
        login_db: "{{ database_name }}"
        login_port: "{{ database_port }}"
        login_user: "{{ database_user }}"
        login_password: "{{ database_password }}"
        query: 
        - SELECT * FROM SERVER_QUEUE
        single_transaction: true

[Andersson007]

Please let me know if it ^ helps (UPDATE: now i see your last comment)

[Andersson007]

The dba I've been messaging with for two days just got back to me. My database isn't mysql, its mssql. >:( I switched collections and now it works.

Ah, i see:) No problem. Please take this comment into consideration for future cases.
So feel free to close the discussion as solved:)