No way to connect via TCP?

[SturmB]

SUMMARY

I receive an error message when using the mysql_db module to connect to a MySQL Docker container.

ISSUE TYPE

• Bug Report

COMPONENT NAME

mysql_db

ANSIBLE VERSION

ansible 2.10.3
  config file = /home/sturm/devops/ansible.cfg
  configured module search path = ['/home/sturm/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/sturm/.local/lib/python3.8/site-packages/ansible
  executable location = /home/sturm/.local/bin/ansible
  python version = 3.8.5 (default, Jul 28 2020, 12:59:40) [GCC 9.3.0]

CONFIGURATION

DEFAULT_HOST_LIST(/home/sturm/devops/ansible.cfg) = ['/home/sturm/devops/inventories/main']
DEFAULT_LOAD_CALLBACK_PLUGINS(/home/sturm/devops/ansible.cfg) = True
DEFAULT_ROLES_PATH(/home/sturm/devops/ansible.cfg) = ['/home/sturm/devops/roles']
DEFAULT_STDOUT_CALLBACK(/home/sturm/devops/ansible.cfg) = yaml
DEFAULT_VAULT_PASSWORD_FILE(/home/sturm/devops/ansible.cfg) = /home/sturm/devops/.vault_pass
HOST_KEY_CHECKING(/home/sturm/devops/ansible.cfg) = False
INTERPRETER_PYTHON(/home/sturm/devops/ansible.cfg) = /usr/bin/python3

OS / ENVIRONMENT

Host: Windows 10 v2004 with WSL2 running Ubuntu 20.04 Target: Ubuntu 18.04

STEPS TO REPRODUCE

1. Ensure that a MySQL Docker container is up and running on the target with some data.
2. Write a simple playbook using the task below. (Replace the variables with your own info.)
3. Run the playbook and watch the error message appear:

fatal: [skyubuntu]: FAILED! => changed=false
  msg: |-
    mysqldump: [Warning] Using a password on the command line interface can be insecure.
    mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect

Here's the task for calling the mysqldump process:

  tasks:
    - name: Dump Sky Schedule database
      community.mysql.mysql_db:
        name: "{{ db_database }}"
        state: dump
        target: /tmp/{{ db_database }}.sql
        login_host: localhost
        login_port: 3306
        login_user: "{{ vault_db_username }}"
        login_password: "{{ vault_db_password }}"

EXPECTED RESULTS

I expect the module to connect to the MySQL server via TCP.

ACTUAL RESULTS

The module appears to be trying to connect to the MySQL container via a unix socket. Per this SO answer, the only way to connect to a MySQL container is to specifically set the protocol to TCP. I see no way to do so with mysql_db.

 ___________________________________
< TASK [Dump Sky Schedule database] >
 -----------------------------------
        \   ^__^
         \  (oo)\_______
            (__)\       )\/\
                ||----w |
                ||     ||

[WARNING]: Module did not set no_log for unsafe_login_password
fatal: [skyubuntu]: FAILED! => changed=false
  msg: |-
    mysqldump: [Warning] Using a password on the command line interface can be insecure.
    mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect

[SturmB]

Follow-Up

I've discovered the issue and it seems to be built-in to MySQL itself. It all depends on how you reference localhost with the login_host parameter.

• If you set it to localhost, mysql_db uses a unix socket by default.
• If you set it to 127.0.0.1, however, then mysql_db will use TCP.

This isn't a fault of mysql_db, as the msql CLI client does the same thing. Of course, with the client, you could also specify --protocol=TCP to force the use of that protocol, regardless of the host setting. However, this argument is not possible with mysql_db right now, unfortunately.

Thus, I'll keep this issue open, but perhaps someone could convert it into a feature request? The feature of adding a module parameter for explicitly setting the protocol of the command. Or perhaps just a new extra_args parameter, which would just pass any arguments listed therein to the command, similar to how dump_extra_args currently works.

[Andersson007]

@SturmB thanks for reporting this and for the investigation! For folks who want to implement this, before going ahead, please mention your intention here to avoid parallel work. Thanks!

[BuhtigithuB]

Follow-Up

I've discovered the issue and it seems to be built-in to MySQL itself. It all depends on how you reference localhost with the login_host parameter.

* If you set it to `localhost`, `mysql_db` uses a unix socket by default.

* If you set it to `127.0.0.1`, however, then `mysql_db` will use TCP.

This isn't a fault of mysql_db, as the msql CLI client does the same thing. Of course, with the client, you could also specify --protocol=TCP to force the use of that protocol, regardless of the host setting. However, this argument is not possible with mysql_db right now, unfortunately.

Thus, I'll keep this issue open, but perhaps someone could convert it into a feature request? The feature of adding a module parameter for explicitly setting the protocol of the command. Or perhaps just a new extra_args parameter, which would just pass any arguments listed therein to the command, similar to how dump_extra_args currently works.

I have the exact same issue, trying to restore a dump in a container instance of mysql official container image... I only been able to login mysql while specifying the protocol switch with TCP value... I would suggest replacing dump_extra_args with extra_args that could be use either with state: dump or state: import

[BuhtigithuB]

I think I a can easily add the feature and sending a PR, although I would need to know if we want to keep things separate (dump_extra_args and import_extra_args) or we convert dump_extra_args into extra_args??

[Andersson007]

@BuhtigithuB hi, thank you for the feedback!

1. I would avoid renaming of the existing parameter if possible because it would be a breaking change (if 100% needed, we can but it would require planning, announcing, grey hair of our users, keeping a related PR open and merge it right ~~after~~ before the next major release and we've released 3.0.0 only recently..).
2. We can pass the parameter via dump_extra_args but there's no way to do it when the state is import, right?
3. Would hardcoding maybe be a safe option?
4. I don't have time to look at the code now, hope will be able to do it on Monday-Tuesday.

[BuhtigithuB]

A bit more complicated then anticipated... We have to specified extra args to mysql at connection and not to mysqldump :(

I will try to render it possible...

[BuhtigithuB]

I am not sure what is the best way between implementing protocol=<MYSQL_CONNECTION_PROTOCOLS> or allowing mysql extra_args to be passed... Is there any guidance here?

[Andersson007]

A bit more complicated then anticipated... We have to specified extra args to mysql at connection and not to mysqldump :(

I will try to render it possible...

@BuhtigithuB thanks for investigating!

Not sure I fully understand. As i can see here, --protocol can be passed to mysqldump via the dump_extra_args argument of the db_dump function. Could you please elaborate a bit more? I think adding a separate argument, say protocol, to pass a protocol would be an overkill but we could think of adding import_extra_arguments as a more general solution.

@BuhtigithuB @bmalynovytch @rsicart @Jorge-Rodriguez what do you think? (if you're busy folks, feel free to ignore the ping ... though it's about the mysql_db module!:)

[BuhtigithuB]

Yes you right, but I don't have the issue with mysqldump, I have it at load time with mysql client... mysql-db uses mysql client to start a session then pass the sql commands to it... So, I can't just add "extra_args" parameters to db_import() : https://github.com/ansible-collections/community.mysql/blob/main/plugins/modules/mysql_db.py#L505

So my questioning, shall I add extra_args to the underlying mysql.py : https://github.com/ansible-collections/community.mysql/blob/main/plugins/module_utils/mysql.py

Or a "protocol" switch to it like "ssl_ca", "ssl_cert", etc.

I think protocol switch make more sens and is more secure as input will be validated and it can more easily assess if if conflict with other switches setted in an ansible role with mysql_db...

I will try to submit a draft of what I have in mind it migth help the discussion...

Thanks

[Andersson007]

@BuhtigithuB OK, please go ahead, thanks:)

[BuhtigithuB]

@Andersson007, when you have time to look at #280 please comment... I didn't test anything, but it shall be near to what has to be done to support --protocol=TCP everywhere.

Thanks

[Andersson007]

@Andersson007, when you have time to look at #280 please comment... I didn't test anything, but it shall be near to what has to be done to support --protocol=TCP everywhere.

Thanks

@BuhtigithuB done, thanks!