search

ansible-collections / community.mysql

MySQL Ansible Collection
https://galaxy.ansible.com/ui/repo/published/community/mysql/
Other
98 stars 87 forks source link

Packet sequence number wrong - got 1 expected 0 #73

Open ajcanlas-tip opened 3 years ago

ajcanlas-tip commented 3 years ago
SUMMARY

failed: [192.168.100.200] (item=127.0.0.1) => {"ansible_loop_var": "item", "changed": false, "item": "127.0.0.1", "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: Packet sequence number wrong - got 1 expected 0"}

Error in running mysql_user to change root password

ISSUE TYPE
COMPONENT NAME
ANSIBLE VERSION
ansible 2.10.3
  config file = /home/aj/Documents/Repository/raspberrypi-ansible/ansible.cfg
  configured module search path = ['/home/aj/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3.9/site-packages/ansible
  executable location = /usr/bin/ansible
  python version = 3.9.0 (default, Oct  7 2020, 23:09:01) [GCC 10.2.0]
CONFIGURATION
DEFAULT_BECOME(/home/aj/Documents/Repository/raspberrypi-ansible/ansible.cfg) = True
DEFAULT_BECOME_METHOD(/home/aj/Documents/Repository/raspberrypi-ansible/ansible.cfg) = sudo
DEFAULT_BECOME_USER(/home/aj/Documents/Repository/raspberrypi-ansible/ansible.cfg) = root
DEFAULT_HOST_LIST(/home/aj/Documents/Repository/raspberrypi-ansible/ansible.cfg) = ['/home/aj/Documents/Repository/raspberrypi-ansible/raspberrypi']
DEFAULT_REMOTE_USER(/home/aj/Documents/Repository/raspberrypi-ansible/ansible.cfg) = rpimgmt
OS / ENVIRONMENT

sample uname -a Linux rpi4b4-0.ajohnsc.com 5.4.0-1022-raspi #25-Ubuntu SMP PREEMPT Thu Oct 15 13:31:49 UTC 2020 aarch64 aarch64 aarch64 GNU/Linux

STEPS TO REPRODUCE

freshly installed mariadb in the server to emulate mysql_secure_installation

- name: mysql_secure_installation change root password   <-- this is where it stops
  community.mysql.mysql_user:
    name: root
    host: "{{ item }}"
    password: "{{ DB_PASS }}"
    login_user: root
    login_host: "{{ ansible_nodename }}"
    check_implicit_admin: True
  loop:
    - 127.0.0.1
    - ::1
    - localhost

- name: Create .my.cnf
  ansible.builtin.template:
    src: "client.my.cnf.j2"
    dest: "/root/.my.cnf"
    owner: root
    group: root
    mode: 0600

- name: mysql_secure_installation Delete anonymous MySQL user
  community.mysql.mysql_user:
    name: ""
    host_all: yes
    state: absent

- name: mysql_secure_installation Remove MySQL test database
  community.mysql.mysql_db:
    name: test
    state: absent
EXPECTED RESULTS

expected to emulate mysql_secure_installation

ACTUAL RESULTS

It detected that it uses multithreading but it uses only one thread

<192.168.100.200> (0, b'ansible-tmp-1607858702.2749367-54202-180470084319309=/home/rpimgmt/.ansible/tmp/ansible-tmp-1607858702.2749367-54202-180470084319309\n', b'')
Using module file /home/aj/.ansible/collections/ansible_collections/community/mysql/plugins/modules/mysql_user.py
<192.168.100.200> PUT /home/aj/.ansible/tmp/ansible-local-53535w2z0p2e_/tmpti57j9lk TO /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858702.2749367-54202-180470084319309/AnsiballZ_mysql_user.py
<192.168.100.200> 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="rpimgmt"' -o ConnectTimeout=10 -o ControlPath=/home/aj/.ansible/cp/7e03691f63 '[192.168.100.200]'
<192.168.100.200> (0, b'sftp> put /home/aj/.ansible/tmp/ansible-local-53535w2z0p2e_/tmpti57j9lk /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858702.2749367-54202-180470084319309/AnsiballZ_mysql_user.py\n', b'')
<192.168.100.200> ESTABLISH SSH CONNECTION FOR USER: rpimgmt
<192.168.100.200> 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="rpimgmt"' -o ConnectTimeout=10 -o ControlPath=/home/aj/.ansible/cp/7e03691f63 192.168.100.200 '/bin/sh -c '"'"'chmod u+x /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858702.2749367-54202-180470084319309/ /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858702.2749367-54202-180470084319309/AnsiballZ_mysql_user.py && sleep 0'"'"''
<192.168.100.200> (0, b'', b'')
<192.168.100.200> ESTABLISH SSH CONNECTION FOR USER: rpimgmt
<192.168.100.200> 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="rpimgmt"' -o ConnectTimeout=10 -o ControlPath=/home/aj/.ansible/cp/7e03691f63 -tt 192.168.100.200 '/bin/sh -c '"'"'sudo -H -S -n  -u root /bin/sh -c '"'"'"'"'"'"'"'"'echo BECOME-SUCCESS-nwcmdpjebzarwrvdpfascryplzltnaqx ; /usr/bin/python3 /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858702.2749367-54202-180470084319309/AnsiballZ_mysql_user.py'"'"'"'"'"'"'"'"' && sleep 0'"'"''
Escalation succeeded
<192.168.100.200> (1, b'\r\n{"failed": true, "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: Packet sequence number wrong - got 1 expected 0", "exception": "  File \\"/tmp/ansible_community.mysql.mysql_user_payload_vdx5gzsa/ansible_community.mysql.mysql_user_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_user.py\\", line 1066, in main\\n  File \\"/tmp/ansible_community.mysql.mysql_user_payload_vdx5gzsa/ansible_community.mysql.mysql_user_payload.zip/ansible_collections/community/mysql/plugins/module_utils/mysql.py\\", line 103, in mysql_connect\\n    db_connection = mysql_driver.connect(autocommit=autocommit, **config)\\n  File \\"/usr/lib/python3/dist-packages/pymysql/__init__.py\\", line 94, in Connect\\n    return Connection(*args, **kwargs)\\n  File \\"/usr/lib/python3/dist-packages/pymysql/connections.py\\", line 325, in __init__\\n    self.connect()\\n  File \\"/usr/lib/python3/dist-packages/pymysql/connections.py\\", line 598, in connect\\n    self._get_server_information()\\n  File \\"/usr/lib/python3/dist-packages/pymysql/connections.py\\", line 975, in _get_server_information\\n    packet = self._read_packet()\\n  File \\"/usr/lib/python3/dist-packages/pymysql/connections.py\\", line 669, in _read_packet\\n    raise err.InternalError(\\n", "invocation": {"module_args": {"name": "root", "host": "127.0.0.1", "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER", "login_user": "root", "login_host": "rpi4b4-0.ajohnsc.com", "check_implicit_admin": true, "user": "root", "login_port": 3306, "config_file": "/root/.my.cnf", "connect_timeout": 30, "encrypted": false, "host_all": false, "state": "present", "append_privs": false, "update_password": "always", "sql_log_bin": true, "login_password": null, "login_unix_socket": null, "client_cert": null, "client_key": null, "ca_cert": null, "check_hostname": null, "priv": null, "tls_requires": null, "plugin": null, "plugin_hash_string": null, "plugin_auth_string": null, "resource_limits": null}}}\r\n', b'Shared connection to 192.168.100.200 closed.\r\n')
<192.168.100.200> Failed to connect to the host via ssh: Shared connection to 192.168.100.200 closed.
<192.168.100.200> ESTABLISH SSH CONNECTION FOR USER: rpimgmt
<192.168.100.200> 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="rpimgmt"' -o ConnectTimeout=10 -o ControlPath=/home/aj/.ansible/cp/7e03691f63 192.168.100.200 '/bin/sh -c '"'"'rm -f -r /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858702.2749367-54202-180470084319309/ > /dev/null 2>&1 && sleep 0'"'"''
<192.168.100.200> (0, b'', b'')
The full traceback is:
  File "/tmp/ansible_community.mysql.mysql_user_payload_vdx5gzsa/ansible_community.mysql.mysql_user_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_user.py", line 1066, in main
  File "/tmp/ansible_community.mysql.mysql_user_payload_vdx5gzsa/ansible_community.mysql.mysql_user_payload.zip/ansible_collections/community/mysql/plugins/module_utils/mysql.py", line 103, in mysql_connect
    db_connection = mysql_driver.connect(autocommit=autocommit, **config)
  File "/usr/lib/python3/dist-packages/pymysql/__init__.py", line 94, in Connect
    return Connection(*args, **kwargs)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 325, in __init__
    self.connect()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 598, in connect
    self._get_server_information()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 975, in _get_server_information
    packet = self._read_packet()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 669, in _read_packet
    raise err.InternalError(
failed: [192.168.100.200] (item=127.0.0.1) => {
    "ansible_loop_var": "item",
    "changed": false,
    "invocation": {
        "module_args": {
            "append_privs": false,
            "ca_cert": null,
            "check_hostname": null,
            "check_implicit_admin": true,
            "client_cert": null,
            "client_key": null,
            "config_file": "/root/.my.cnf",
            "connect_timeout": 30,
            "encrypted": false,
            "host": "127.0.0.1",
            "host_all": false,
            "login_host": "rpi4b4-0.ajohnsc.com",
            "login_password": null,
            "login_port": 3306,
            "login_unix_socket": null,
            "login_user": "root",
            "name": "root",
            "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "plugin": null,
            "plugin_auth_string": null,
            "plugin_hash_string": null,
            "priv": null,
            "resource_limits": null,
            "sql_log_bin": true,
            "state": "present",
            "tls_requires": null,
            "update_password": "always",
            "user": "root"
        }
    },
    "item": "127.0.0.1",
    "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: Packet sequence number wrong - got 1 expected 0"
}
<192.168.100.200> ESTABLISH SSH CONNECTION FOR USER: rpimgmt
<192.168.100.200> 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="rpimgmt"' -o ConnectTimeout=10 -o ControlPath=/home/aj/.ansible/cp/7e03691f63 192.168.100.200 '/bin/sh -c '"'"'echo ~rpimgmt && sleep 0'"'"''
<192.168.100.200> (0, b'/home/rpimgmt\n', b'')
<192.168.100.200> ESTABLISH SSH CONNECTION FOR USER: rpimgmt
<192.168.100.200> 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="rpimgmt"' -o ConnectTimeout=10 -o ControlPath=/home/aj/.ansible/cp/7e03691f63 192.168.100.200 '/bin/sh -c '"'"'( umask 77 && mkdir -p "` echo /home/rpimgmt/.ansible/tmp `"&& mkdir "` echo /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858703.85798-54202-203947800729853 `" && echo ansible-tmp-1607858703.85798-54202-203947800729853="` echo /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858703.85798-54202-203947800729853 `" ) && sleep 0'"'"''
<192.168.100.200> (0, b'ansible-tmp-1607858703.85798-54202-203947800729853=/home/rpimgmt/.ansible/tmp/ansible-tmp-1607858703.85798-54202-203947800729853\n', b'')
Using module file /home/aj/.ansible/collections/ansible_collections/community/mysql/plugins/modules/mysql_user.py
<192.168.100.200> PUT /home/aj/.ansible/tmp/ansible-local-53535w2z0p2e_/tmp_31epkb1 TO /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858703.85798-54202-203947800729853/AnsiballZ_mysql_user.py
<192.168.100.200> 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="rpimgmt"' -o ConnectTimeout=10 -o ControlPath=/home/aj/.ansible/cp/7e03691f63 '[192.168.100.200]'
<192.168.100.200> (0, b'sftp> put /home/aj/.ansible/tmp/ansible-local-53535w2z0p2e_/tmp_31epkb1 /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858703.85798-54202-203947800729853/AnsiballZ_mysql_user.py\n', b'')
<192.168.100.200> ESTABLISH SSH CONNECTION FOR USER: rpimgmt
<192.168.100.200> 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="rpimgmt"' -o ConnectTimeout=10 -o ControlPath=/home/aj/.ansible/cp/7e03691f63 192.168.100.200 '/bin/sh -c '"'"'chmod u+x /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858703.85798-54202-203947800729853/ /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858703.85798-54202-203947800729853/AnsiballZ_mysql_user.py && sleep 0'"'"''
<192.168.100.200> (0, b'', b'')
<192.168.100.200> ESTABLISH SSH CONNECTION FOR USER: rpimgmt
<192.168.100.200> 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="rpimgmt"' -o ConnectTimeout=10 -o ControlPath=/home/aj/.ansible/cp/7e03691f63 -tt 192.168.100.200 '/bin/sh -c '"'"'sudo -H -S -n  -u root /bin/sh -c '"'"'"'"'"'"'"'"'echo BECOME-SUCCESS-riaadcucrgmdbwqimrlwnmetyxkvniju ; /usr/bin/python3 /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858703.85798-54202-203947800729853/AnsiballZ_mysql_user.py'"'"'"'"'"'"'"'"' && sleep 0'"'"''
Escalation succeeded
<192.168.100.200> (1, b'\r\n{"failed": true, "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: Packet sequence number wrong - got 1 expected 0", "exception": "  File \\"/tmp/ansible_community.mysql.mysql_user_payload_cquoa0hv/ansible_community.mysql.mysql_user_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_user.py\\", line 1066, in main\\n  File \\"/tmp/ansible_community.mysql.mysql_user_payload_cquoa0hv/ansible_community.mysql.mysql_user_payload.zip/ansible_collections/community/mysql/plugins/module_utils/mysql.py\\", line 103, in mysql_connect\\n    db_connection = mysql_driver.connect(autocommit=autocommit, **config)\\n  File \\"/usr/lib/python3/dist-packages/pymysql/__init__.py\\", line 94, in Connect\\n    return Connection(*args, **kwargs)\\n  File \\"/usr/lib/python3/dist-packages/pymysql/connections.py\\", line 325, in __init__\\n    self.connect()\\n  File \\"/usr/lib/python3/dist-packages/pymysql/connections.py\\", line 598, in connect\\n    self._get_server_information()\\n  File \\"/usr/lib/python3/dist-packages/pymysql/connections.py\\", line 975, in _get_server_information\\n    packet = self._read_packet()\\n  File \\"/usr/lib/python3/dist-packages/pymysql/connections.py\\", line 669, in _read_packet\\n    raise err.InternalError(\\n", "invocation": {"module_args": {"name": "root", "host": "::1", "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER", "login_user": "root", "login_host": "rpi4b4-0.ajohnsc.com", "check_implicit_admin": true, "user": "root", "login_port": 3306, "config_file": "/root/.my.cnf", "connect_timeout": 30, "encrypted": false, "host_all": false, "state": "present", "append_privs": false, "update_password": "always", "sql_log_bin": true, "login_password": null, "login_unix_socket": null, "client_cert": null, "client_key": null, "ca_cert": null, "check_hostname": null, "priv": null, "tls_requires": null, "plugin": null, "plugin_hash_string": null, "plugin_auth_string": null, "resource_limits": null}}}\r\n', b'Shared connection to 192.168.100.200 closed.\r\n')
<192.168.100.200> Failed to connect to the host via ssh: Shared connection to 192.168.100.200 closed.
<192.168.100.200> ESTABLISH SSH CONNECTION FOR USER: rpimgmt
<192.168.100.200> 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="rpimgmt"' -o ConnectTimeout=10 -o ControlPath=/home/aj/.ansible/cp/7e03691f63 192.168.100.200 '/bin/sh -c '"'"'rm -f -r /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858703.85798-54202-203947800729853/ > /dev/null 2>&1 && sleep 0'"'"''
<192.168.100.200> (0, b'', b'')
The full traceback is:
  File "/tmp/ansible_community.mysql.mysql_user_payload_cquoa0hv/ansible_community.mysql.mysql_user_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_user.py", line 1066, in main
  File "/tmp/ansible_community.mysql.mysql_user_payload_cquoa0hv/ansible_community.mysql.mysql_user_payload.zip/ansible_collections/community/mysql/plugins/module_utils/mysql.py", line 103, in mysql_connect
    db_connection = mysql_driver.connect(autocommit=autocommit, **config)
  File "/usr/lib/python3/dist-packages/pymysql/__init__.py", line 94, in Connect
    return Connection(*args, **kwargs)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 325, in __init__
    self.connect()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 598, in connect
    self._get_server_information()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 975, in _get_server_information
    packet = self._read_packet()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 669, in _read_packet
    raise err.InternalError(
failed: [192.168.100.200] (item=::1) => {
    "ansible_loop_var": "item",
    "changed": false,
    "invocation": {
        "module_args": {
            "append_privs": false,
            "ca_cert": null,
            "check_hostname": null,
            "check_implicit_admin": true,
            "client_cert": null,
            "client_key": null,
            "config_file": "/root/.my.cnf",
            "connect_timeout": 30,
            "encrypted": false,
            "host": "::1",
            "host_all": false,
            "login_host": "rpi4b4-0.ajohnsc.com",
            "login_password": null,
            "login_port": 3306,
            "login_unix_socket": null,
            "login_user": "root",
            "name": "root",
            "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "plugin": null,
            "plugin_auth_string": null,
            "plugin_hash_string": null,
            "priv": null,
            "resource_limits": null,
            "sql_log_bin": true,
            "state": "present",
            "tls_requires": null,
            "update_password": "always",
            "user": "root"
        }
    },
    "item": "::1",
    "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: Packet sequence number wrong - got 1 expected 0"
}
<192.168.100.200> ESTABLISH SSH CONNECTION FOR USER: rpimgmt
<192.168.100.200> 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="rpimgmt"' -o ConnectTimeout=10 -o ControlPath=/home/aj/.ansible/cp/7e03691f63 192.168.100.200 '/bin/sh -c '"'"'echo ~rpimgmt && sleep 0'"'"''
<192.168.100.200> (0, b'/home/rpimgmt\n', b'')
<192.168.100.200> ESTABLISH SSH CONNECTION FOR USER: rpimgmt
<192.168.100.200> 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="rpimgmt"' -o ConnectTimeout=10 -o ControlPath=/home/aj/.ansible/cp/7e03691f63 192.168.100.200 '/bin/sh -c '"'"'( umask 77 && mkdir -p "` echo /home/rpimgmt/.ansible/tmp `"&& mkdir "` echo /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858705.205759-54202-268659631050579 `" && echo ansible-tmp-1607858705.205759-54202-268659631050579="` echo /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858705.205759-54202-268659631050579 `" ) && sleep 0'"'"''
<192.168.100.200> (0, b'ansible-tmp-1607858705.205759-54202-268659631050579=/home/rpimgmt/.ansible/tmp/ansible-tmp-1607858705.205759-54202-268659631050579\n', b'')
Using module file /home/aj/.ansible/collections/ansible_collections/community/mysql/plugins/modules/mysql_user.py
<192.168.100.200> PUT /home/aj/.ansible/tmp/ansible-local-53535w2z0p2e_/tmpgxkxcxok TO /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858705.205759-54202-268659631050579/AnsiballZ_mysql_user.py
<192.168.100.200> 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="rpimgmt"' -o ConnectTimeout=10 -o ControlPath=/home/aj/.ansible/cp/7e03691f63 '[192.168.100.200]'
<192.168.100.200> (0, b'sftp> put /home/aj/.ansible/tmp/ansible-local-53535w2z0p2e_/tmpgxkxcxok /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858705.205759-54202-268659631050579/AnsiballZ_mysql_user.py\n', b'')
<192.168.100.200> ESTABLISH SSH CONNECTION FOR USER: rpimgmt
<192.168.100.200> 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="rpimgmt"' -o ConnectTimeout=10 -o ControlPath=/home/aj/.ansible/cp/7e03691f63 192.168.100.200 '/bin/sh -c '"'"'chmod u+x /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858705.205759-54202-268659631050579/ /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858705.205759-54202-268659631050579/AnsiballZ_mysql_user.py && sleep 0'"'"''
<192.168.100.200> (0, b'', b'')
<192.168.100.200> ESTABLISH SSH CONNECTION FOR USER: rpimgmt
<192.168.100.200> 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="rpimgmt"' -o ConnectTimeout=10 -o ControlPath=/home/aj/.ansible/cp/7e03691f63 -tt 192.168.100.200 '/bin/sh -c '"'"'sudo -H -S -n  -u root /bin/sh -c '"'"'"'"'"'"'"'"'echo BECOME-SUCCESS-zivdjyjiyfpnthgxtkxxvofrbplzlwov ; /usr/bin/python3 /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858705.205759-54202-268659631050579/AnsiballZ_mysql_user.py'"'"'"'"'"'"'"'"' && sleep 0'"'"''
Escalation succeeded
<192.168.100.200> (1, b'\r\n{"failed": true, "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: Packet sequence number wrong - got 1 expected 0", "exception": "  File \\"/tmp/ansible_community.mysql.mysql_user_payload_3aevcmc0/ansible_community.mysql.mysql_user_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_user.py\\", line 1066, in main\\n  File \\"/tmp/ansible_community.mysql.mysql_user_payload_3aevcmc0/ansible_community.mysql.mysql_user_payload.zip/ansible_collections/community/mysql/plugins/module_utils/mysql.py\\", line 103, in mysql_connect\\n    db_connection = mysql_driver.connect(autocommit=autocommit, **config)\\n  File \\"/usr/lib/python3/dist-packages/pymysql/__init__.py\\", line 94, in Connect\\n    return Connection(*args, **kwargs)\\n  File \\"/usr/lib/python3/dist-packages/pymysql/connections.py\\", line 325, in __init__\\n    self.connect()\\n  File \\"/usr/lib/python3/dist-packages/pymysql/connections.py\\", line 598, in connect\\n    self._get_server_information()\\n  File \\"/usr/lib/python3/dist-packages/pymysql/connections.py\\", line 975, in _get_server_information\\n    packet = self._read_packet()\\n  File \\"/usr/lib/python3/dist-packages/pymysql/connections.py\\", line 669, in _read_packet\\n    raise err.InternalError(\\n", "invocation": {"module_args": {"name": "root", "host": "localhost", "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER", "login_user": "root", "login_host": "rpi4b4-0.ajohnsc.com", "check_implicit_admin": true, "user": "root", "login_port": 3306, "config_file": "/root/.my.cnf", "connect_timeout": 30, "encrypted": false, "host_all": false, "state": "present", "append_privs": false, "update_password": "always", "sql_log_bin": true, "login_password": null, "login_unix_socket": null, "client_cert": null, "client_key": null, "ca_cert": null, "check_hostname": null, "priv": null, "tls_requires": null, "plugin": null, "plugin_hash_string": null, "plugin_auth_string": null, "resource_limits": null}}}\r\n', b'Shared connection to 192.168.100.200 closed.\r\n')
<192.168.100.200> Failed to connect to the host via ssh: Shared connection to 192.168.100.200 closed.
<192.168.100.200> ESTABLISH SSH CONNECTION FOR USER: rpimgmt
<192.168.100.200> 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="rpimgmt"' -o ConnectTimeout=10 -o ControlPath=/home/aj/.ansible/cp/7e03691f63 192.168.100.200 '/bin/sh -c '"'"'rm -f -r /home/rpimgmt/.ansible/tmp/ansible-tmp-1607858705.205759-54202-268659631050579/ > /dev/null 2>&1 && sleep 0'"'"''
<192.168.100.200> (0, b'', b'')
The full traceback is:
  File "/tmp/ansible_community.mysql.mysql_user_payload_3aevcmc0/ansible_community.mysql.mysql_user_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_user.py", line 1066, in main
  File "/tmp/ansible_community.mysql.mysql_user_payload_3aevcmc0/ansible_community.mysql.mysql_user_payload.zip/ansible_collections/community/mysql/plugins/module_utils/mysql.py", line 103, in mysql_connect
    db_connection = mysql_driver.connect(autocommit=autocommit, **config)
  File "/usr/lib/python3/dist-packages/pymysql/__init__.py", line 94, in Connect
    return Connection(*args, **kwargs)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 325, in __init__
    self.connect()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 598, in connect
    self._get_server_information()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 975, in _get_server_information
    packet = self._read_packet()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 669, in _read_packet
    raise err.InternalError(
failed: [192.168.100.200] (item=localhost) => {
    "ansible_loop_var": "item",
    "changed": false,
    "invocation": {
        "module_args": {
            "append_privs": false,
            "ca_cert": null,
            "check_hostname": null,
            "check_implicit_admin": true,
            "client_cert": null,
            "client_key": null,
            "config_file": "/root/.my.cnf",
            "connect_timeout": 30,
            "encrypted": false,
            "host": "localhost",
            "host_all": false,
            "login_host": "rpi4b4-0.ajohnsc.com",
            "login_password": null,
            "login_port": 3306,
            "login_unix_socket": null,
            "login_user": "root",
            "name": "root",
            "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "plugin": null,
            "plugin_auth_string": null,
            "plugin_hash_string": null,
            "priv": null,
            "resource_limits": null,
            "sql_log_bin": true,
            "state": "present",
            "tls_requires": null,
            "update_password": "always",
            "user": "root"
        }
    },
    "item": "localhost",
    "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: Packet sequence number wrong - got 1 expected 0"
}
Andersson007 commented 3 years ago

@ajcanlas-tip hi, thanks for reporting this.

File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 975, in _get_server_information packet = self._read_packet() File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 669, in _read_packet raise err.InternalError(

Looks like it's not a problem of the module itself but rather as a problem of something behind, e.g. the connector, etc. People get the same error using pymysql (in cases not related to Ansible) and seems that with no solution.

ajcanlas-tip commented 3 years ago

HI @Andersson007 ,

Not sure if this helps it says https://github.com/PyMySQL/PyMySQL/issues/422 here that multi-threaded is not supported thus we might redo the whole thing.

Regards

fourstepper commented 3 years ago

I am hitting the same error on two Ansible versions

The full traceback is:
  File "/tmp/ansible_mysql_db_payload_demn32ts/ansible_mysql_db_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_db.py", line 640, in main
  File "/tmp/ansible_mysql_db_payload_demn32ts/ansible_mysql_db_payload.zip/ansible_collections/community/mysql/plugins/module_utils/mysql.py", line 103, in mysql_connect
    db_connection = mysql_driver.connect(autocommit=autocommit, **config)
  File "/usr/local/lib/python3.6/site-packages/pymysql/__init__.py", line 94, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 327, in __init__
    self.connect()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 587, in connect
    self._get_server_information()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 969, in _get_server_information
    packet = self._read_packet()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 660, in _read_packet
    % (packet_number, self._next_seq_id))
fatal: [example.com]: FAILED! => {
    "changed": false,
    "invocation": {
        "module_args": {
            "ca_cert": null,
            "check_hostname": null,
            "check_implicit_admin": false,
            "client_cert": null,
            "client_key": null,
            "collation": "",
            "config_file": "/root/.my.cnf",
            "config_overrides_defaults": false,
            "connect_timeout": 30,
            "dump_extra_args": null,
            "encoding": "",
            "force": false,
            "hex_blob": false,
            "ignore_tables": [],
            "login_host": "localhost",
            "login_password": null,
            "login_port": 3306,
            "login_unix_socket": null,
            "login_user": null,
            "master_data": 0,
            "name": [
                "test"
            ],
            "quick": true,
            "restrict_config_file": false,
            "single_transaction": false,
            "skip_lock_tables": false,
            "state": "absent",
            "target": null,
            "unsafe_login_password": false,
            "use_shell": false
        }
    },
    "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: Packet sequence number wrong - got 1 expected 0"
}

The credentials are correct as I can connect to the mysql database from the shell, I think the error might not be handled correctly.

Andersson007 commented 3 years ago

@fourstepper thanks for reporting this, what do you mean? (Did you see my previous comment?)

fourstepper commented 3 years ago

Hi @Andersson007, I have seen it. Perhaps it could be worked around?

Andersson007 commented 3 years ago

Hi @fourstepper , sure, you could try to use mysqlclient https://pypi.org/project/mysqlclient/ or mysqldb driver. If it helps, please let me know

Andersson007 commented 3 years ago

@fourstepper of course, remove pymysql previously

fourstepper commented 3 years ago

What's interesting also is that when I run the same playbook against a CI container with centos 8 (and systemd), there are no issues with the pymysql driver. It's a bit puzzling. Same Ansible version, same OS (up to date) and the results differ

Andersson007 commented 3 years ago

Yes, it's interesting.

iangregory commented 3 years ago

I also experienced this issue - can confirm using mysqlclient vs pymysql resolves this on OpenBSD 6.9

Andersson007 commented 3 years ago

@iangregory thanks for the feedback!

jnm27 commented 3 years ago

With mysqlclient, we get this error instead:

'unable to find /****/.my.cnf. Exception message: (2013, "Lost connection to MySQL server at ''reading initial communication packet'', system error: 0")'

So we are blocked here. Is there any outlook for a fix? Is Ansible reusing a MySQL connection over multiple threads/processes?

Andersson007 commented 3 years ago

@jnm27 do you think if it's related issues? https://stackoverflow.com/questions/5755819/lost-connection-to-mysql-server-at-reading-initial-communication-packet-syste

Andersson007 commented 3 years ago

if anyone tries to run the playbooks where the issue appears with PyMySQL and the --forks 1 option (the default is 5) and provides the feedback, It would be nice.

Andersson007 commented 3 years ago

@jnm27 ^ could you please do this?

fourstepper commented 3 years ago

I will go for it @Andersson007

EDIT: Just tried, unfortunately doesn't work :(

Andersson007 commented 3 years ago

@fourstepper thanks!

Andersson007 commented 3 years ago

@fourstepper if it doesn't help, we could in addition add serial: 1 to the playbook

fourstepper commented 3 years ago

@Andersson007 no bueno either

Andersson007 commented 3 years ago

@fourstepper thanks for the feedback!

Andersson007 commented 3 years ago

yeah, as we can see in the provided traceback, everything seems to happen on the target machine:

The full traceback is:
  File "/tmp/ansible_community.mysql.mysql_user_payload_cquoa0hv/ansible_community.mysql.mysql_user_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_user.py", line 1066, in main
  File "/tmp/ansible_community.mysql.mysql_user_payload_cquoa0hv/ansible_community.mysql.mysql_user_payload.zip/ansible_collections/community/mysql/plugins/module_utils/mysql.py", line 103, in mysql_connect
    db_connection = mysql_driver.connect(autocommit=autocommit, **config)
  File "/usr/lib/python3/dist-packages/pymysql/__init__.py", line 94, in Connect
    return Connection(*args, **kwargs)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 325, in __init__
    self.connect()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 598, in connect
    self._get_server_information()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 975, in _get_server_information
    packet = self._read_packet()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 669, in _read_packet
    raise err.InternalError(

and we don't use any things related to multiprocessing in our modules.. strange. And it's even stranger because, as @fourstepper mentioned, everything works fine in docker. I asked the folks in ansible-community IRC channel. Maybe they have ideas.

fourstepper commented 3 years ago

It works in CI even for the upstream project that I use, in our CI, but not in my prod :D its kinda sad

https://github.com/geerlingguy/ansible-role-mysql/actions/runs/833828534

jnm27 commented 3 years ago

Sorry but any updates? Anything we can do to help? What is the next step to investigate?

Andersson007 commented 3 years ago

@jnm27 I ran out of my ideas. The folks in IRC didn't have any ideas too. Anyway it doesn't seem to be problem of the code in this collection. Our modules don't fork anything and, in particular, the code related to PyMySQL, so the object is not shared. And I don't think it's a problem of Ansible-core (which is out of the collection scope) because, as @fourstepper mentioned, same tasks run well in docker. Both mysqlclient and PyMySQL are actively developed. I see an only solution to switch to the first one.

@bmalynovytch @Jorge-Rodriguez do you have anything on your mind?

beckjkl commented 3 years ago

I encountered the same error today. I tested it on two almost identical machines, one of them got the error but not the other. They are both Ubuntu servers running Mariadb 10.4 with pymysql 0.9.3 installed. Are there any tests I can do that could help you diagnose the issue? Output from the control machine: ansible --version ansible 2.9.21 config file = /etc/ansible/ansible.cfg configured module search path = [u'/home/my_user/.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.17 (default, Feb 27 2021, 15:10:58) [GCC 7.5.0]

Andersson007 commented 3 years ago

@beckjkl hi, thanks for the information! And sorry for the late response, was busy with the mysql_role new module. (BTW if someone is interesting to see it, please review https://github.com/ansible-collections/community.mysql/pull/189).

This case generally looks like something magic. What are the differences of the target machines (because the module itself is executed there). Could you provide the following information for both?

Thanks for offering help!

beckjkl commented 3 years ago

@Andersson007 Hi, I've tried to get as much information as possible:

Andersson007 commented 3 years ago

@beckjkl thanks for the feedback!

  • ansible is not installed on these hosts and I won't install it.

Ah, of course... I woke up feeling a bit underslept today:) Sorry. Now, after 3 coffee I realized that my question was strange:)

Both the systems look configured basically same way. The last point looks a bit suspicious - why the first system doesn't show this error? Obviously the working one uses python3 by default and one of the required libs installed via pip3.

The last error look really suspicious. Maybe this is the key.

Thanks for your help!

beckjkl commented 3 years ago

@Andersson007 I've check the problem system again. I remember having some issues when I set up db connections for ansible for the first time. There were some old python2 versions of mysql-client and pymysql still laying around. I removed those. Now both systems only have pymysql installed, but the error persists: root@working-host:~# pip3 list | grep -i mysql PyMySQL (1.0.2)

root@problem-host:~# pip3 list | grep -i mysql PyMySQL (1.0.2)

These are also the only packages installed via pip. The other packages listed by pip were installed via apt.

ansible problem-host -m mysql_replication -a "mode=getmaster login_user=test login_password=..." problem-host | 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: Packet sequence number wrong - got 1 expected 0" } ansible working-host -m mysql_replication -a "mode=getmaster login_user=test login_password=..." working-host | SUCCESS => { ... "changed": false }

Also explicitly setting ansible_python_interpreter to python3 changes nothing.

Andersson007 commented 3 years ago

@beckjkl so nothing has changed after the purging, right? looks like absolute magic to me as both the systems use same software... I have no ideas then why this happens.

It still looks as something underlying as the function uses high level connector interfaces and the code runs in a single thread on a target machine. As far as I remember we founded that to use mysqlclient instead of pymysql should solve the issue.

zlobniyshurik commented 2 years ago

I got this error too. In slow environment (cloud VM) my script worked perfectly, but on fast VMs from local server I got problem. So I tried to slowdown script on my fast server :)

Script before: ... task "Create replication user" task "Get master status" <-- got error here :(

Script after: ... task "Create replication user" task "Sleep 15s" task "Get master status" <-- no error here :)

This is not ideal solution, but it may help for somebody.

Andersson007 commented 2 years ago

@zlobniyshurik thanks for sharing the workaround!