Open philippemilink opened 3 years ago
@philippemilink hello, thanks for reporting the issue!
I don't see any differences in how the collection's module utils work with pymysql and mysqldb that can be related. Could you please also
mysq_info
, is the result same?-vvv
option and put the traceback part in a comment?
Thanks!If someone from other contributors wants to investigate the case, welcome!
We had just experienced the same discrepancy. Could it be that the libraries disagree on the meaning of "localhost"
? Sometimes it means "unix socket" in the mysql ecosystem and that seems to be the case when we use python mysqldb. PyMySQL on the other hand seems to use TCP/IP.
Hi @philippemilink,
Could you please provide the feedback that @Andersson007 asked previously?
Does the file /root/.my.cnf
exist?
Sorry for the delay.
Does the file
/root/.my.cnf
exist?
No, it doesn't exist with both Python libraries.
check if you use it with another module, say, with mysq_info, is the result same?
I tried:
- name: Collect all info except settings and users by root
community.mysql.mysql_info:
filter: "!settings,!users"
after installing python3-pymysql
, I get the following error:
TASK [mysql : Collect all info except settings and users by root] **************
fatal: [test]: 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: (1698, "Access denied for user 'root'@'localhost'")
``` TASK [mysql : Collect all info except settings and users by root] ************** <127.0.0.1> ESTABLISH SSH CONNECTION FOR USER: vagrant <127.0.0.1> SSH: EXEC ssh -o UserKnownHostsFile=/dev/null -o IdentitiesOnly=yes -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=2222 -o 'IdentityFile="/media/linux/dev/zds/ansible-zestedesavoir/.vagrant/machines/test/virtualbox/private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="vagrant"' -o ConnectTimeout=30 -o 'ControlPath="/home/philippe/.ansible/cp/055b8f4af0"' 127.0.0.1 '/bin/sh -c '"'"'echo ~vagrant && sleep 0'"'"'' <127.0.0.1> (0, b'/home/vagrant\n', b'') <127.0.0.1> ESTABLISH SSH CONNECTION FOR USER: vagrant <127.0.0.1> SSH: EXEC ssh -o UserKnownHostsFile=/dev/null -o IdentitiesOnly=yes -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=2222 -o 'IdentityFile="/media/linux/dev/zds/ansible-zestedesavoir/.vagrant/machines/test/virtualbox/private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="vagrant"' -o ConnectTimeout=30 -o 'ControlPath="/home/philippe/.ansible/cp/055b8f4af0"' 127.0.0.1 '/bin/sh -c '"'"'( umask 77 && mkdir -p "` echo /home/vagrant/.ansible/tmp `"&& mkdir "` echo /home/vagrant/.ansible/tmp/ansible-tmp-1650729906.6765788-21908-273006842603843 `" && echo ansible-tmp-1650729906.6765788-21908-273006842603843="` echo /home/vagrant/.ansible/tmp/ansible-tmp-1650729906.6765788-21908-273006842603843 `" ) && sleep 0'"'"'' <127.0.0.1> (0, b'ansible-tmp-1650729906.6765788-21908-273006842603843=/home/vagrant/.ansible/tmp/ansible-tmp-1650729906.6765788-21908-273006842603843\n', b'') Using module file /home/philippe/.ansible/collections/ansible_collections/community/mysql/plugins/modules/mysql_info.py <127.0.0.1> PUT /home/philippe/.ansible/tmp/ansible-local-21685vybf1ex5/tmpt1y3j4t6 TO /home/vagrant/.ansible/tmp/ansible-tmp-1650729906.6765788-21908-273006842603843/AnsiballZ_mysql_info.py <127.0.0.1> SSH: EXEC sftp -b - -o UserKnownHostsFile=/dev/null -o IdentitiesOnly=yes -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=2222 -o 'IdentityFile="/media/linux/dev/zds/ansible-zestedesavoir/.vagrant/machines/test/virtualbox/private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="vagrant"' -o ConnectTimeout=30 -o 'ControlPath="/home/philippe/.ansible/cp/055b8f4af0"' '[127.0.0.1]' <127.0.0.1> (0, b'sftp> put /home/philippe/.ansible/tmp/ansible-local-21685vybf1ex5/tmpt1y3j4t6 /home/vagrant/.ansible/tmp/ansible-tmp-1650729906.6765788-21908-273006842603843/AnsiballZ_mysql_info.py\n', b'') <127.0.0.1> ESTABLISH SSH CONNECTION FOR USER: vagrant <127.0.0.1> SSH: EXEC ssh -o UserKnownHostsFile=/dev/null -o IdentitiesOnly=yes -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=2222 -o 'IdentityFile="/media/linux/dev/zds/ansible-zestedesavoir/.vagrant/machines/test/virtualbox/private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="vagrant"' -o ConnectTimeout=30 -o 'ControlPath="/home/philippe/.ansible/cp/055b8f4af0"' 127.0.0.1 '/bin/sh -c '"'"'chmod u+x /home/vagrant/.ansible/tmp/ansible-tmp-1650729906.6765788-21908-273006842603843/ /home/vagrant/.ansible/tmp/ansible-tmp-1650729906.6765788-21908-273006842603843/AnsiballZ_mysql_info.py && sleep 0'"'"'' <127.0.0.1> (0, b'', b'') <127.0.0.1> ESTABLISH SSH CONNECTION FOR USER: vagrant <127.0.0.1> SSH: EXEC ssh -o UserKnownHostsFile=/dev/null -o IdentitiesOnly=yes -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=2222 -o 'IdentityFile="/media/linux/dev/zds/ansible-zestedesavoir/.vagrant/machines/test/virtualbox/private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="vagrant"' -o ConnectTimeout=30 -o 'ControlPath="/home/philippe/.ansible/cp/055b8f4af0"' -tt 127.0.0.1 '/bin/sh -c '"'"'sudo -H -S -n -u root /bin/sh -c '"'"'"'"'"'"'"'"'echo BECOME-SUCCESS-bmsqqdulwsdjoinklkyrwxblgclallqt ; /usr/bin/python3 /home/vagrant/.ansible/tmp/ansible-tmp-1650729906.6765788-21908-273006842603843/AnsiballZ_mysql_info.py'"'"'"'"'"'"'"'"' && sleep 0'"'"'' Escalation succeeded <127.0.0.1> (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: (1698, \\"Access denied for user \'root\'@\'localhost\'\\")", "exception": " File \\"/tmp/ansible_community.mysql.mysql_info_payload_ez3hk_un/ansible_community.mysql.mysql_info_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_info.py\\", line 559, in main\\n File \\"/tmp/ansible_community.mysql.mysql_info_payload_ez3hk_un/ansible_community.mysql.mysql_info_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 599, in connect\\n self._request_authentication()\\n File \\"/usr/lib/python3/dist-packages/pymysql/connections.py\\", line 861, in _request_authentication\\n auth_packet = self._read_packet()\\n File \\"/usr/lib/python3/dist-packages/pymysql/connections.py\\", line 684, in _read_packet\\n packet.check_error()\\n File \\"/usr/lib/python3/dist-packages/pymysql/protocol.py\\", line 220, in check_error\\n err.raise_mysql_exception(self._data)\\n File \\"/usr/lib/python3/dist-packages/pymysql/err.py\\", line 109, in raise_mysql_exception\\n raise errorclass(errno, errval)\\n", "invocation": {"module_args": {"filter": ["!settings", "!users"], "login_host": "localhost", "login_port": 3306, "config_file": "/root/.my.cnf", "connect_timeout": 30, "return_empty_dbs": false, "login_user": null, "login_password": null, "login_unix_socket": null, "client_cert": null, "client_key": null, "ca_cert": null, "check_hostname": null, "login_db": null, "exclude_fields": null}}}\r\n', b'Shared connection to 127.0.0.1 closed.\r\n') <127.0.0.1> Failed to connect to the host via ssh: Shared connection to 127.0.0.1 closed. <127.0.0.1> ESTABLISH SSH CONNECTION FOR USER: vagrant <127.0.0.1> SSH: EXEC ssh -o UserKnownHostsFile=/dev/null -o IdentitiesOnly=yes -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=2222 -o 'IdentityFile="/media/linux/dev/zds/ansible-zestedesavoir/.vagrant/machines/test/virtualbox/private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="vagrant"' -o ConnectTimeout=30 -o 'ControlPath="/home/philippe/.ansible/cp/055b8f4af0"' 127.0.0.1 '/bin/sh -c '"'"'rm -f -r /home/vagrant/.ansible/tmp/ansible-tmp-1650729906.6765788-21908-273006842603843/ > /dev/null 2>&1 && sleep 0'"'"'' <127.0.0.1> (0, b'', b'') The full traceback is: File "/tmp/ansible_community.mysql.mysql_info_payload_ez3hk_un/ansible_community.mysql.mysql_info_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_info.py", line 559, in main File "/tmp/ansible_community.mysql.mysql_info_payload_ez3hk_un/ansible_community.mysql.mysql_info_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 599, in connect self._request_authentication() File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 861, in _request_authentication auth_packet = self._read_packet() File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 684, in _read_packet packet.check_error() File "/usr/lib/python3/dist-packages/pymysql/protocol.py", line 220, in check_error err.raise_mysql_exception(self._data) File "/usr/lib/python3/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception raise errorclass(errno, errval) fatal: [test]: FAILED! => { "changed": false, "invocation": { "module_args": { "ca_cert": null, "check_hostname": null, "client_cert": null, "client_key": null, "config_file": "/root/.my.cnf", "connect_timeout": 30, "exclude_fields": null, "filter": [ "!settings", "!users" ], "login_db": null, "login_host": "localhost", "login_password": null, "login_port": 3306, "login_unix_socket": null, "login_user": null, "return_empty_dbs": false } } } MSG: unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1698, "Access denied for user 'root'@'localhost'") ```
With python3-mysqldb
it works fine.
``` TASK [mysql : Collect all info except settings and users by root] ************** <127.0.0.1> ESTABLISH SSH CONNECTION FOR USER: vagrant <127.0.0.1> SSH: EXEC ssh -o UserKnownHostsFile=/dev/null -o IdentitiesOnly=yes -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=2222 -o 'IdentityFile="/media/linux/dev/zds/ansible-zestedesavoir/.vagrant/machines/test/virtualbox/private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="vagrant"' -o ConnectTimeout=30 -o 'ControlPath="/home/philippe/.ansible/cp/055b8f4af0"' 127.0.0.1 '/bin/sh -c '"'"'echo ~vagrant && sleep 0'"'"'' <127.0.0.1> (0, b'/home/vagrant\n', b'') <127.0.0.1> ESTABLISH SSH CONNECTION FOR USER: vagrant <127.0.0.1> SSH: EXEC ssh -o UserKnownHostsFile=/dev/null -o IdentitiesOnly=yes -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=2222 -o 'IdentityFile="/media/linux/dev/zds/ansible-zestedesavoir/.vagrant/machines/test/virtualbox/private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="vagrant"' -o ConnectTimeout=30 -o 'ControlPath="/home/philippe/.ansible/cp/055b8f4af0"' 127.0.0.1 '/bin/sh -c '"'"'( umask 77 && mkdir -p "` echo /home/vagrant/.ansible/tmp `"&& mkdir "` echo /home/vagrant/.ansible/tmp/ansible-tmp-1650731478.7989128-28629-135043520119095 `" && echo ansible-tmp-1650731478.7989128-28629-135043520119095="` echo /home/vagrant/.ansible/tmp/ansible-tmp-1650731478.7989128-28629-135043520119095 `" ) && sleep 0'"'"'' <127.0.0.1> (0, b'ansible-tmp-1650731478.7989128-28629-135043520119095=/home/vagrant/.ansible/tmp/ansible-tmp-1650731478.7989128-28629-135043520119095\n', b'') Using module file /home/philippe/.ansible/collections/ansible_collections/community/mysql/plugins/modules/mysql_info.py <127.0.0.1> PUT /home/philippe/.ansible/tmp/ansible-local-28375yirkub_i/tmp8f_4phqj TO /home/vagrant/.ansible/tmp/ansible-tmp-1650731478.7989128-28629-135043520119095/AnsiballZ_mysql_info.py <127.0.0.1> SSH: EXEC sftp -b - -o UserKnownHostsFile=/dev/null -o IdentitiesOnly=yes -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=2222 -o 'IdentityFile="/media/linux/dev/zds/ansible-zestedesavoir/.vagrant/machines/test/virtualbox/private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="vagrant"' -o ConnectTimeout=30 -o 'ControlPath="/home/philippe/.ansible/cp/055b8f4af0"' '[127.0.0.1]' <127.0.0.1> (0, b'sftp> put /home/philippe/.ansible/tmp/ansible-local-28375yirkub_i/tmp8f_4phqj /home/vagrant/.ansible/tmp/ansible-tmp-1650731478.7989128-28629-135043520119095/AnsiballZ_mysql_info.py\n', b'') <127.0.0.1> ESTABLISH SSH CONNECTION FOR USER: vagrant <127.0.0.1> SSH: EXEC ssh -o UserKnownHostsFile=/dev/null -o IdentitiesOnly=yes -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=2222 -o 'IdentityFile="/media/linux/dev/zds/ansible-zestedesavoir/.vagrant/machines/test/virtualbox/private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="vagrant"' -o ConnectTimeout=30 -o 'ControlPath="/home/philippe/.ansible/cp/055b8f4af0"' 127.0.0.1 '/bin/sh -c '"'"'chmod u+x /home/vagrant/.ansible/tmp/ansible-tmp-1650731478.7989128-28629-135043520119095/ /home/vagrant/.ansible/tmp/ansible-tmp-1650731478.7989128-28629-135043520119095/AnsiballZ_mysql_info.py && sleep 0'"'"'' <127.0.0.1> (0, b'', b'') <127.0.0.1> ESTABLISH SSH CONNECTION FOR USER: vagrant <127.0.0.1> SSH: EXEC ssh -o UserKnownHostsFile=/dev/null -o IdentitiesOnly=yes -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=2222 -o 'IdentityFile="/media/linux/dev/zds/ansible-zestedesavoir/.vagrant/machines/test/virtualbox/private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="vagrant"' -o ConnectTimeout=30 -o 'ControlPath="/home/philippe/.ansible/cp/055b8f4af0"' -tt 127.0.0.1 '/bin/sh -c '"'"'sudo -H -S -n -u root /bin/sh -c '"'"'"'"'"'"'"'"'echo BECOME-SUCCESS-dmfunarhadmcvuvksqrfwifzykftolwo ; /usr/bin/python3 /home/vagrant/.ansible/tmp/ansible-tmp-1650731478.7989128-28629-135043520119095/AnsiballZ_mysql_info.py'"'"'"'"'"'"'"'"' && sleep 0'"'"'' Escalation succeeded <127.0.0.1> (0, b'\r\n{"changed": false, "version": {"major": 10, "minor": 4, "release": 24, "suffix": "MariaDB-1:10", "full": "10.4.24-MariaDB-1:10.4.24+maria~buster-log"}, "databases": {"information_schema": {"size": 212992}, "mysql": {"size": 2293760}, "performance_schema": {"size": 0}}, "global_status": {"Aborted_clients": 0, "Aborted_connects": 0, "Aborted_connects_preauth": 0, "Access_denied_errors": 0, "Acl_column_grants": 0, "Acl_database_grants": 0, "Acl_function_grants": 0, "Acl_procedure_grants": 0, "Acl_package_spec_grants": 0, "Acl_package_body_grants": 0, "Acl_proxy_users": 1, "Acl_role_grants": 0, "Acl_roles": 0, "Acl_table_grants": 1, "Acl_users": 3, "Aria_pagecache_blocks_not_flushed": 0, "Aria_pagecache_blocks_unused": 15696, "Aria_pagecache_blocks_used": 10, "Aria_pagecache_read_requests": 15, "Aria_pagecache_reads": 12, "Aria_pagecache_write_requests": 0, "Aria_pagecache_writes": 0, "Aria_transaction_log_syncs": 0, "Binlog_commits": 0, "Binlog_group_commits": 0, "Binlog_group_commit_trigger_count": 0, "Binlog_group_commit_trigger_lock_wait": 0, "Binlog_group_commit_trigger_timeout": 0, "Binlog_snapshot_file": "mariadb-bin.000001", "Binlog_snapshot_position": 330, "Binlog_bytes_written": 0, "Binlog_cache_disk_use": 0, "Binlog_cache_use": 0, "Binlog_stmt_cache_disk_use": 0, "Binlog_stmt_cache_use": 0, "Busy_time": "0.000000", "Bytes_received": 6998, "Bytes_sent": 27861, "Column_compressions": 0, "Column_decompressions": 0, "Com_admin_commands": 0, "Com_alter_db": 0, "Com_alter_db_upgrade": 0, "Com_alter_event": 0, "Com_alter_function": 0, "Com_alter_procedure": 0, "Com_alter_server": 0, "Com_alter_sequence": 0, "Com_alter_table": 0, "Com_alter_tablespace": 0, "Com_alter_user": 0, "Com_analyze": 0, "Com_assign_to_keycache": 0, "Com_backup": 0, "Com_backup_lock": 0, "Com_begin": 0, "Com_binlog": 0, "Com_call_procedure": 0, "Com_change_db": 0, "Com_change_master": 0, "Com_check": 0, "Com_checksum": 0, "Com_commit": 0, "Com_compound_sql": 0, "Com_create_db": 0, "Com_create_event": 0, "Com_create_function": 0, "Com_create_index": 0, "Com_create_package": 0, "Com_create_package_body": 0, "Com_create_procedure": 0, "Com_create_role": 0, "Com_create_sequence": 0, "Com_create_server": 0, "Com_create_table": 0, "Com_create_temporary_table": 0, "Com_create_trigger": 0, "Com_create_udf": 0, "Com_create_user": 0, "Com_create_view": 0, "Com_dealloc_sql": 0, "Com_delete": 0, "Com_delete_multi": 0, "Com_do": 0, "Com_drop_db": 0, "Com_drop_event": 0, "Com_drop_function": 0, "Com_drop_index": 0, "Com_drop_procedure": 0, "Com_drop_package": 0, "Com_drop_package_body": 0, "Com_drop_role": 0, "Com_drop_server": 0, "Com_drop_sequence": 0, "Com_drop_table": 0, "Com_drop_temporary_table": 0, "Com_drop_trigger": 0, "Com_drop_user": 0, "Com_drop_view": 0, "Com_empty_query": 0, "Com_execute_immediate": 0, "Com_execute_sql": 0, "Com_flush": 0, "Com_get_diagnostics": 0, "Com_grant": 0, "Com_grant_role": 0, "Com_ha_close": 0, "Com_ha_open": 0, "Com_ha_read": 0, "Com_help": 0, "Com_insert": 0, "Com_insert_select": 0, "Com_install_plugin": 0, "Com_kill": 0, "Com_load": 0, "Com_lock_tables": 0, "Com_multi": 0, "Com_optimize": 0, "Com_preload_keys": 0, "Com_prepare_sql": 0, "Com_purge": 0, "Com_purge_before_date": 0, "Com_release_savepoint": 0, "Com_rename_table": 0, "Com_rename_user": 0, "Com_repair": 0, "Com_replace": 0, "Com_replace_select": 0, "Com_reset": 0, "Com_resignal": 0, "Com_revoke": 0, "Com_revoke_all": 0, "Com_revoke_role": 0, "Com_rollback": 0, "Com_rollback_to_savepoint": 0, "Com_savepoint": 0, "Com_select": 27, "Com_set_option": 2, "Com_show_authors": 0, "Com_show_binlog_events": 0, "Com_show_binlogs": 0, "Com_show_charsets": 0, "Com_show_collations": 0, "Com_show_contributors": 0, "Com_show_create_db": 0, "Com_show_create_event": 0, "Com_show_create_func": 0, "Com_show_create_package": 0, "Com_show_create_package_body": 0, "Com_show_create_proc": 0, "Com_show_create_table": 0, "Com_show_create_trigger": 0, "Com_show_create_user": 0, "Com_show_databases": 0, "Com_show_engine_logs": 0, "Com_show_engine_mutex": 0, "Com_show_engine_status": 0, "Com_show_errors": 0, "Com_show_events": 0, "Com_show_explain": 0, "Com_show_fields": 0, "Com_show_function_status": 0, "Com_show_generic": 0, "Com_show_grants": 0, "Com_show_keys": 0, "Com_show_master_status": 0, "Com_show_open_tables": 0, "Com_show_package_status": 0, "Com_show_package_body_status": 0, "Com_show_plugins": 0, "Com_show_privileges": 0, "Com_show_procedure_status": 0, "Com_show_processlist": 0, "Com_show_profile": 0, "Com_show_profiles": 0, "Com_show_relaylog_events": 0, "Com_show_slave_hosts": 0, "Com_show_slave_status": 0, "Com_show_status": 1, "Com_show_storage_engines": 0, "Com_show_table_status": 0, "Com_show_tables": 0, "Com_show_triggers": 0, "Com_show_variables": 2, "Com_show_warnings": 0, "Com_shutdown": 0, "Com_signal": 0, "Com_start_all_slaves": 0, "Com_start_slave": 0, "Com_stmt_close": 0, "Com_stmt_execute": 0, "Com_stmt_fetch": 0, "Com_stmt_prepare": 0, "Com_stmt_reprepare": 0, "Com_stmt_reset": 0, "Com_stmt_send_long_data": 0, "Com_stop_all_slaves": 0, "Com_stop_slave": 0, "Com_truncate": 0, "Com_uninstall_plugin": 0, "Com_unlock_tables": 0, "Com_update": 0, "Com_update_multi": 0, "Com_xa_commit": 0, "Com_xa_end": 0, "Com_xa_prepare": 0, "Com_xa_recover": 0, "Com_xa_rollback": 0, "Com_xa_start": 0, "Compression": "OFF", "Connection_errors_accept": 0, "Connection_errors_internal": 0, "Connection_errors_max_connections": 0, "Connection_errors_peer_address": 0, "Connection_errors_select": 0, "Connection_errors_tcpwrap": 0, "Connections": 36, "Cpu_time": "0.000000", "Created_tmp_disk_tables": 13, "Created_tmp_files": 5, "Created_tmp_tables": 85, "Delayed_errors": 0, "Delayed_insert_threads": 0, "Delayed_writes": 0, "Delete_scan": 0, "Empty_queries": 24, "Executed_events": 0, "Executed_triggers": 0, "Feature_application_time_periods": 0, "Feature_check_constraint": 1, "Feature_custom_aggregate_functions": 0, "Feature_delay_key_write": 0, "Feature_dynamic_columns": 0, "Feature_fulltext": 0, "Feature_gis": 0, "Feature_invisible_columns": 0, "Feature_json": 94, "Feature_locale": 0, "Feature_subquery": 0, "Feature_system_versioning": 0, "Feature_timezone": 0, "Feature_trigger": 0, "Feature_window_functions": 0, "Feature_xml": 0, "Flush_commands": 1, "Handler_commit": 0, "Handler_delete": 0, "Handler_discover": 52, "Handler_external_lock": 0, "Handler_icp_attempts": 0, "Handler_icp_match": 0, "Handler_mrr_init": 0, "Handler_mrr_key_refills": 0, "Handler_mrr_rowid_refills": 0, "Handler_prepare": 0, "Handler_read_first": 3, "Handler_read_key": 280, "Handler_read_last": 0, "Handler_read_next": 1, "Handler_read_prev": 0, "Handler_read_retry": 0, "Handler_read_rnd": 3, "Handler_read_rnd_deleted": 0, "Handler_read_rnd_next": 857, "Handler_rollback": 0, "Handler_savepoint": 0, "Handler_savepoint_rollback": 0, "Handler_tmp_delete": 0, "Handler_tmp_update": 159, "Handler_tmp_write": 839, "Handler_update": 0, "Handler_write": 0, "Innodb_buffer_pool_dump_status": "", "Innodb_buffer_pool_load_status": "Buffer pool(s) load completed at 220423 16:31:14", "Innodb_buffer_pool_resize_status": "", "Innodb_buffer_pool_load_incomplete": "OFF", "Innodb_buffer_pool_pages_data": 307, "Innodb_buffer_pool_bytes_data": 5029888, "Innodb_buffer_pool_pages_dirty": 0, "Innodb_buffer_pool_bytes_dirty": 0, "Innodb_buffer_pool_pages_flushed": 131, "Innodb_buffer_pool_pages_free": 15747, "Innodb_buffer_pool_pages_misc": 0, "Innodb_buffer_pool_pages_total": 16054, "Innodb_buffer_pool_read_ahead_rnd": 0, "Innodb_buffer_pool_read_ahead": 0, "Innodb_buffer_pool_read_ahead_evicted": 0, "Innodb_buffer_pool_read_requests": 1635, "Innodb_buffer_pool_reads": 177, "Innodb_buffer_pool_wait_free": 0, "Innodb_buffer_pool_write_requests": 843, "Innodb_data_fsyncs": 2, "Innodb_data_pending_fsyncs": 0, "Innodb_data_pending_reads": 0, "Innodb_data_pending_writes": 0, "Innodb_data_read": 2968064, "Innodb_data_reads": 192, "Innodb_data_writes": 133, "Innodb_data_written": 2147328, "Innodb_dblwr_pages_written": 0, "Innodb_dblwr_writes": 0, "Innodb_log_waits": 0, "Innodb_log_write_requests": 0, "Innodb_log_writes": 1, "Innodb_os_log_fsyncs": 2, "Innodb_os_log_pending_fsyncs": 0, "Innodb_os_log_pending_writes": 0, "Innodb_os_log_written": 512, "Innodb_page_size": 16384, "Innodb_pages_created": 131, "Innodb_pages_read": 176, "Innodb_pages_written": 131, "Innodb_row_lock_current_waits": 0, "Innodb_row_lock_time": 0, "Innodb_row_lock_time_avg": 0, "Innodb_row_lock_time_max": 0, "Innodb_row_lock_waits": 0, "Innodb_rows_deleted": 0, "Innodb_rows_inserted": 0, "Innodb_rows_read": 0, "Innodb_rows_updated": 0, "Innodb_system_rows_deleted": 0, "Innodb_system_rows_inserted": 0, "Innodb_system_rows_read": 0, "Innodb_system_rows_updated": 0, "Innodb_num_open_files": 8, "Innodb_truncated_status_writes": 0, "Innodb_available_undo_logs": 128, "Innodb_undo_truncations": 0, "Innodb_page_compression_saved": 0, "Innodb_num_index_pages_written": 0, "Innodb_num_non_index_pages_written": 0, "Innodb_num_pages_page_compressed": 0, "Innodb_num_page_compressed_trim_op": 0, "Innodb_num_pages_page_decompressed": 0, "Innodb_num_pages_page_compression_error": 0, "Innodb_num_pages_encrypted": 0, "Innodb_num_pages_decrypted": 0, "Innodb_have_lz4": "ON", "Innodb_have_lzo": "OFF", "Innodb_have_lzma": "OFF", "Innodb_have_bzip2": "OFF", "Innodb_have_snappy": "OFF", "Innodb_have_punch_hole": "ON", "Innodb_defragment_compression_failures": 0, "Innodb_defragment_failures": 0, "Innodb_defragment_count": 0, "Innodb_instant_alter_column": 0, "Innodb_onlineddl_rowlog_rows": 0, "Innodb_onlineddl_rowlog_pct_used": 0, "Innodb_onlineddl_pct_progress": 0, "Innodb_secondary_index_triggered_cluster_reads": 0, "Innodb_secondary_index_triggered_cluster_reads_avoided": 0, "Innodb_encryption_rotation_pages_read_from_cache": 0, "Innodb_encryption_rotation_pages_read_from_disk": 0, "Innodb_encryption_rotation_pages_modified": 0, "Innodb_encryption_rotation_pages_flushed": 0, "Innodb_encryption_rotation_estimated_iops": 0, "Innodb_encryption_key_rotation_list_length": 0, "Innodb_encryption_n_merge_blocks_encrypted": 0, "Innodb_encryption_n_merge_blocks_decrypted": 0, "Innodb_encryption_n_rowlog_blocks_encrypted": 0, "Innodb_encryption_n_rowlog_blocks_decrypted": 0, "Innodb_encryption_n_temp_blocks_encrypted": 0, "Innodb_encryption_n_temp_blocks_decrypted": 0, "Innodb_scrub_background_page_reorganizations": 0, "Innodb_scrub_background_page_splits": 0, "Innodb_scrub_background_page_split_failures_underflow": 0, "Innodb_scrub_background_page_split_failures_out_of_filespace": 0, "Innodb_scrub_background_page_split_failures_missing_index": 0, "Innodb_scrub_background_page_split_failures_unknown": 0, "Innodb_scrub_log": 0, "Innodb_encryption_num_key_requests": 0, "Key_blocks_not_flushed": 0, "Key_blocks_unused": 107163, "Key_blocks_used": 0, "Key_blocks_warm": 0, "Key_read_requests": 0, "Key_reads": 0, "Key_write_requests": 0, "Key_writes": 0, "Last_query_cost": "0.000000", "Master_gtid_wait_count": 0, "Master_gtid_wait_time": 0, "Master_gtid_wait_timeouts": 0, "Max_statement_time_exceeded": 0, "Max_used_connections": 1, "Memory_used": 344615448, "Memory_used_initial": 339979752, "Not_flushed_delayed_rows": 0, "Open_files": 58, "Open_streams": 0, "Open_table_definitions": 83, "Open_tables": 82, "Opened_files": 177, "Opened_plugin_libraries": 0, "Opened_table_definitions": 81, "Opened_tables": 88, "Opened_views": 2, "Performance_schema_accounts_lost": 0, "Performance_schema_cond_classes_lost": 0, "Performance_schema_cond_instances_lost": 0, "Performance_schema_digest_lost": 0, "Performance_schema_file_classes_lost": 0, "Performance_schema_file_handles_lost": 0, "Performance_schema_file_instances_lost": 0, "Performance_schema_hosts_lost": 0, "Performance_schema_locker_lost": 0, "Performance_schema_mutex_classes_lost": 0, "Performance_schema_mutex_instances_lost": 0, "Performance_schema_rwlock_classes_lost": 0, "Performance_schema_rwlock_instances_lost": 0, "Performance_schema_session_connect_attrs_lost": 0, "Performance_schema_socket_classes_lost": 0, "Performance_schema_socket_instances_lost": 0, "Performance_schema_stage_classes_lost": 0, "Performance_schema_statement_classes_lost": 0, "Performance_schema_table_handles_lost": 0, "Performance_schema_table_instances_lost": 0, "Performance_schema_thread_classes_lost": 0, "Performance_schema_thread_instances_lost": 0, "Performance_schema_users_lost": 0, "Prepared_stmt_count": 0, "Qcache_free_blocks": 1, "Qcache_free_memory": 67091072, "Qcache_hits": 0, "Qcache_inserts": 0, "Qcache_lowmem_prunes": 0, "Qcache_not_cached": 27, "Qcache_queries_in_cache": 0, "Qcache_total_blocks": 1, "Queries": 59, "Questions": 59, "Rows_read": 8, "Rows_sent": 668, "Rows_tmp_read": 998, "Rpl_semi_sync_master_clients": 0, "Rpl_semi_sync_master_get_ack": 0, "Rpl_semi_sync_master_net_avg_wait_time": 0, "Rpl_semi_sync_master_net_wait_time": 0, "Rpl_semi_sync_master_net_waits": 0, "Rpl_semi_sync_master_no_times": 0, "Rpl_semi_sync_master_no_tx": 0, "Rpl_semi_sync_master_request_ack": 0, "Rpl_semi_sync_master_status": "OFF", "Rpl_semi_sync_master_timefunc_failures": 0, "Rpl_semi_sync_master_tx_avg_wait_time": 0, "Rpl_semi_sync_master_tx_wait_time": 0, "Rpl_semi_sync_master_tx_waits": 0, "Rpl_semi_sync_master_wait_pos_backtraverse": 0, "Rpl_semi_sync_master_wait_sessions": 0, "Rpl_semi_sync_master_yes_tx": 0, "Rpl_semi_sync_slave_send_ack": 0, "Rpl_semi_sync_slave_status": "OFF", "Rpl_status": "AUTH_MASTER", "Rpl_transactions_multi_engine": 0, "Select_full_join": 0, "Select_full_range_join": 0, "Select_range": 0, "Select_range_check": 0, "Select_scan": 6, "Slave_connections": 0, "Slave_heartbeat_period": "0.000", "Slave_open_temp_tables": 0, "Slave_received_heartbeats": 0, "Slave_retried_transactions": 0, "Slave_running": "OFF", "Slave_skipped_errors": 0, "Slaves_connected": 0, "Slaves_running": 0, "Slow_launch_threads": 0, "Slow_queries": 0, "Sort_merge_passes": 0, "Sort_priority_queue_sorts": 0, "Sort_range": 0, "Sort_rows": 3, "Sort_scan": 1, "Ssl_accept_renegotiates": 0, "Ssl_accepts": 0, "Ssl_callback_cache_hits": 0, "Ssl_cipher": "", "Ssl_cipher_list": "", "Ssl_client_connects": 0, "Ssl_connect_renegotiates": 0, "Ssl_ctx_verify_depth": 0, "Ssl_ctx_verify_mode": 0, "Ssl_default_timeout": 0, "Ssl_finished_accepts": 0, "Ssl_finished_connects": 0, "Ssl_server_not_after": "", "Ssl_server_not_before": "", "Ssl_session_cache_hits": 0, "Ssl_session_cache_misses": 0, "Ssl_session_cache_mode": "NONE", "Ssl_session_cache_overflows": 0, "Ssl_session_cache_size": 0, "Ssl_session_cache_timeouts": 0, "Ssl_sessions_reused": 0, "Ssl_used_session_cache_entries": 0, "Ssl_verify_depth": 0, "Ssl_verify_mode": 0, "Ssl_version": "", "Subquery_cache_hit": 0, "Subquery_cache_miss": 0, "Syncs": 21, "Table_locks_immediate": 72, "Table_locks_waited": 0, "Table_open_cache_active_instances": 1, "Table_open_cache_hits": 67, "Table_open_cache_misses": 90, "Table_open_cache_overflows": 0, "Tc_log_max_pages_used": 0, "Tc_log_page_size": 0, "Tc_log_page_waits": 0, "Threadpool_idle_threads": 0, "Threadpool_threads": 0, "Threads_cached": 0, "Threads_connected": 1, "Threads_created": 1, "Threads_running": 6, "Transactions_gtid_foreign_engine": 0, "Transactions_multi_engine": 0, "Update_scan": 0, "Uptime": 4, "Uptime_since_flush_status": 4, "wsrep": 52, "wsrep_applier_thread_count": 0, "wsrep_cluster_capabilities": "", "wsrep_cluster_conf_id": 18446744073709551615, "wsrep_cluster_size": 0, "wsrep_cluster_state_uuid": "", "wsrep_cluster_status": "Disconnected", "wsrep_connected": "OFF", "wsrep_local_bf_aborts": 0, "wsrep_local_index": 18446744073709551615, "wsrep_provider_capabilities": "", "wsrep_provider_name": "", "wsrep_provider_vendor": "", "wsrep_provider_version": "", "wsrep_ready": "OFF", "wsrep_rollbacker_thread_count": 0, "wsrep_thread_count": 0}, "engines": {"CSV": {"Support": "YES", "Comment": "Stores tables as CSV files", "Transactions": "NO", "XA": "NO", "Savepoints": "NO"}, "MRG_MyISAM": {"Support": "YES", "Comment": "Collection of identical MyISAM tables", "Transactions": "NO", "XA": "NO", "Savepoints": "NO"}, "MEMORY": {"Support": "YES", "Comment": "Hash based, stored in memory, useful for temporary tables", "Transactions": "NO", "XA": "NO", "Savepoints": "NO"}, "Aria": {"Support": "YES", "Comment": "Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables", "Transactions": "NO", "XA": "NO", "Savepoints": "NO"}, "MyISAM": {"Support": "YES", "Comment": "Non-transactional engine with good performance and small data footprint", "Transactions": "NO", "XA": "NO", "Savepoints": "NO"}, "SEQUENCE": {"Support": "YES", "Comment": "Generated tables filled with sequential values", "Transactions": "YES", "XA": "NO", "Savepoints": "YES"}, "InnoDB": {"Support": "DEFAULT", "Comment": "Supports transactions, row-level locking, foreign keys and encryption for tables", "Transactions": "YES", "XA": "YES", "Savepoints": "YES"}, "PERFORMANCE_SCHEMA": {"Support": "YES", "Comment": "Performance Schema", "Transactions": "NO", "XA": "NO", "Savepoints": "NO"}}, "master_status": {"File": "mariadb-bin.000001", "Position": 330, "Binlog_Do_DB": "", "Binlog_Ignore_DB": ""}, "slave_hosts": {}, "slave_status": {}, "invocation": {"module_args": {"filter": ["!settings", "!users"], "login_host": "localhost", "login_port": 3306, "config_file": "/root/.my.cnf", "connect_timeout": 30, "return_empty_dbs": false, "login_user": null, "login_password": null, "login_unix_socket": null, "client_cert": null, "client_key": null, "ca_cert": null, "check_hostname": null, "login_db": null, "exclude_fields": null}}}\r\n', b'Shared connection to 127.0.0.1 closed.\r\n') <127.0.0.1> ESTABLISH SSH CONNECTION FOR USER: vagrant <127.0.0.1> SSH: EXEC ssh -o UserKnownHostsFile=/dev/null -o IdentitiesOnly=yes -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o Port=2222 -o 'IdentityFile="/media/linux/dev/zds/ansible-zestedesavoir/.vagrant/machines/test/virtualbox/private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="vagrant"' -o ConnectTimeout=30 -o 'ControlPath="/home/philippe/.ansible/cp/055b8f4af0"' 127.0.0.1 '/bin/sh -c '"'"'rm -f -r /home/vagrant/.ansible/tmp/ansible-tmp-1650731478.7989128-28629-135043520119095/ > /dev/null 2>&1 && sleep 0'"'"'' <127.0.0.1> (0, b'', b'') ok: [test] => { ```
provide the versions of the drivers?
What do you mean?
Hello,
SUMMARY
I noticed different behaviours between
pymysql
andmysqldb
. Withpython3-mysqldb
, I can interact with the database without taking care about database credentials, however withpython-pymysql
, errors are triggered because authentication to the MySQL server failed.ISSUE TYPE
COMPONENT NAME
mysql_db
ANSIBLE VERSION
COLLECTION VERSION
CONFIGURATION
OS / ENVIRONMENT
Debian Sid.
STEPS TO REPRODUCE
The working version:
If I install
python3-pymysql
instead ofpython3-mysqldb
:there is an error (see below).
EXPECTED RESULTS
Is it normal there is a different behaviour between these two libraries ?
ACTUAL RESULTS
The error with PyMySQL: