Closed mario-pranjic closed 2 years ago
I verified on target server, .my.cnf file is in place and root user can connect by using it.
# ls -l /root/.my.cnf
-rw------- 1 root root 99 Sep 11 08:39 /root/.my.cnf
root@repa:~# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 549407
Server version: 10.3.31-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
Tried to explicitly set it in task - didn't help. I also tried to use login_user and login_password instead - didn't help.
Target database is mariadb. Seems like root auth is required to go via unix socket.
Solution: adding following parameter to mysql_user task:
login_unix_socket: "/var/run/mysqld/mysqld.sock"
This resolves the issue for mysqluser as well ass for other mysql modules when it comes to root auth.
This issue can be closed.
@mario-pranjic hi, welcome to the project and thank you for the issue and the solution! Let's keep it open for some time. If there are similar issues appear, we should definitely investigate the code.
Ah, you're using MariaDB. I released community.mysql 2.2.0-a1
a month ago that is available on Galaxy here.
Would you like to contribute more and help testing it. It would be much appreciated. It contains a new module called mysql_role
.
Help needed because we unfortunately don't test against MariaDB in CI.
I checked it locally but I'm not a DBA, so my testing might not be complete.
I also moved a lot of code from mysql_user
to share it with mysql_role
, so would be nice to test mysql_user
as well.
The doc for mysql_role
is available in the DOCUMENTATION and EXAMPLES sections here.
Hi Andrew,
I will certianly take a look. Can test both on mariadb and mysql.
Hi Mario, great, thanks! We'll be waiting for your feedback then!
Just an interesting link about "Connection refused" error messages: https://dev.mysql.com/doc/refman/8.0/en/can-not-connect-to-server.html
Any updates about this issue @mario-pranjic @Andersson007 ?
I've found that on Debian Bullseye with MariaDB the community.mysql
modules cannot be used with either a login_password
or a config_file
when the root authentication plugin is set to mysql_native_password
-- they simply fail.
I've a MariaDB Galaxy role that has tasks to set the root auth plugin to a password when using a socket and tasks to set the root auth plugin to a socket when using a password, the existing auth method is queried using a Bash facts.d script.
All the tasks use the command
and shell
modules when the auth plugin is mysql_native_password
and the community.mysql
modules when the auth plugin is unix_socket
.
I haven't tested this when there are multiple root auth plugins (but it might work!) and I haven't recently tested this role with Debian versions older than Bullseye, however the 1.9.1 version doesn't use community.mysql
modules and probably works on older versions, older versions of this role have been used when upgrading servers from Debian Jessie and Stretch.
@rsicart no updates from me and thank you for the link!
@chriscroome thank you for the feedback!
@Andersson007 sorry I wasn't clear, everything works fine with socket authentication, the problem is if you disable socket authentication then neither specifying a valid /root/.my.cnf
file or the password works.
I've not worked with RedHat based distros for many years so I'm sorry that I don't know what the situation is with these modules and those distros these days.
I don't think the problem I have described is a separate issue from the one above, this issue explains the bug, but if it would help if I created a separate issue with an example I could.
This issue appears to be related, #262, I think you will find that the root user can only connect using a socket if tests like these are run with MariaDB.
@chriscroome thanks for the info!
I'll put the help_wanted
label as i don't know when i can find enough time to dive into it.
two things. Any ideas on
1) can it relate to the server-side recent changes? Or
2) can it relate to our code changes (i.e. someone introduced a bug)? To figure out this, can someone try to use older Ansible version, say Ansible 2.9
and community.mysql 1.0.0
?
help with p.2 would be much appreciated
@Andersson007 I'll look at 2. but it might not be until next week.
@Andersson007 I'll look at 2. but it might not be until next week.
@chriscroome sure, no rush, thanks!
@Andersson007 sorry this took longer that planned, I've created a GitLab repo with a CI configuration that installs several Ansible versions on Debian Bullseye and then tests switching between password, file and socket authentication to create a new MariaDB user using using mysql_user
and the results are written to a file, Ansible was installed as follows:
python3 -m pip install ansible==2.9.27
python3 -m pip install ansible-base==2.10.17
ansible-galaxy collection install community.mysql:==1.2.0
python3 -m pip install ansible-core==2.11.11
ansible-galaxy collection install community.mysql:==1.4.6
python3 -m pip install ansible-core==2.12.5
ansible-galaxy collection install community.mysql:==2.3.7
python3 -m pip install ansible-core==2.13.0
ansible-galaxy collection install community.mysql:==3.2.1
The results didn't general any errors, which isn't what I was expecting! So either I have made some mistakes in the tests or there isn't an issue here...
mysql_user
sucessfully used to create a new user using socket /var/run/mysqld/mysqld.sock
at 2022-05-19T20:24:54Zmysql_user
sucessfully used to create a new user using config file /root/.my.cnf
at 2022-05-19T20:25:05Zmysql_user
sucessfully used to create a new user using a password at 2022-05-19T20:25:05Zmysql_user
sucessfully used to create a new user using socket /var/run/mysqld/mysqld.sock
at 2022-05-19T20:25:17Zmysql_user
sucessfully used to create a new user using config file /root/.my.cnf
at 2022-05-19T20:25:27Zmysql_user
sucessfully used to create a new user using a password at 2022-05-19T20:25:27Z
mysql_user
sucessfully used to create a new user using socket /var/run/mysqld/mysqld.sock
at 2022-05-19T20:27:29Zmysql_user
sucessfully used to create a new user using config file /root/.my.cnf
at 2022-05-19T20:27:40Zmysql_user
sucessfully used to create a new user using a password at 2022-05-19T20:27:40Zmysql_user
sucessfully used to create a new user using socket /var/run/mysqld/mysqld.sock
at 2022-05-19T20:27:53Zmysql_user
sucessfully used to create a new user using config file /root/.my.cnf
at 2022-05-19T20:28:04Zmysql_user
sucessfully used to create a new user using a password at 2022-05-19T20:28:04Z
mysql_user
sucessfully used to create a new user using socket /var/run/mysqld/mysqld.sock
at 2022-05-19T20:30:00Zmysql_user
sucessfully used to create a new user using config file /root/.my.cnf
at 2022-05-19T20:30:11Zmysql_user
sucessfully used to create a new user using a password at 2022-05-19T20:30:11Zmysql_user
sucessfully used to create a new user using socket /var/run/mysqld/mysqld.sock
at 2022-05-19T20:30:24Zmysql_user
sucessfully used to create a new user using config file /root/.my.cnf
at 2022-05-19T20:30:34Zmysql_user
sucessfully used to create a new user using a password at 2022-05-19T20:30:34Z
mysql_user
sucessfully used to create a new user using socket /var/run/mysqld/mysqld.sock
at 2022-05-19T20:32:42Zmysql_user
sucessfully used to create a new user using config file /root/.my.cnf
at 2022-05-19T20:32:53Zmysql_user
sucessfully used to create a new user using a password at 2022-05-19T20:32:53Zmysql_user
sucessfully used to create a new user using socket /var/run/mysqld/mysqld.sock
at 2022-05-19T20:33:06Zmysql_user
sucessfully used to create a new user using config file /root/.my.cnf
at 2022-05-19T20:33:16Zmysql_user
sucessfully used to create a new user using a password at 2022-05-19T20:33:16Z
mysql_user
sucessfully used to create a new user using socket /var/run/mysqld/mysqld.sock
at 2022-05-19T20:35:08Zmysql_user
sucessfully used to create a new user using config file /root/.my.cnf
at 2022-05-19T20:35:19Zmysql_user
sucessfully used to create a new user using a password at 2022-05-19T20:35:19Zmysql_user
sucessfully used to create a new user using socket /var/run/mysqld/mysqld.sock
at 2022-05-19T20:35:32Zmysql_user
sucessfully used to create a new user using config file /root/.my.cnf
at 2022-05-19T20:35:42Zmysql_user
sucessfully used to create a new user using a password at 2022-05-19T20:35:42ZI've added a create database test as well and still it appears that everything is working without issues, the latest results:
mysql_user
and mysql_db
sucessfully used to create a new user and database using unix_socket
at /var/run/mysqld/mysqld.sock
at 2022-05-20T08:59:37Zmysql_user
and mysql_db
sucessfully used to create a new user and database using mysql_native_password
and config file /root/.my.cnf
at 2022-05-20T08:59:49Zmysql_user
and mysql_db
sucessfully used to create a new user and database using mysql_native_password
and a password at 2022-05-20T08:59:49Zmysql_user
and mysql_db
sucessfully used to create a new user and database using unix_socket
at /var/run/mysqld/mysqld.sock
at 2022-05-20T09:00:03Zmysql_user
and mysql_db
sucessfully used to create a new user and database using mysql_native_password
and config file /root/.my.cnf
at 2022-05-20T09:00:14Zmysql_user
and mysql_db
sucessfully used to create a new user and database using mysql_native_password
and a password at 2022-05-20T09:00:14Z
mysql_user
and mysql_db
sucessfully used to create a new user and database using unix_socket
at /var/run/mysqld/mysqld.sock
at 2022-05-20T09:02:20Zmysql_user
and mysql_db
sucessfully used to create a new user and database using mysql_native_password
and config file /root/.my.cnf
at 2022-05-20T09:02:32Zmysql_user
and mysql_db
sucessfully used to create a new user and database using mysql_native_password
and a password at 2022-05-20T09:02:32Zmysql_user
and mysql_db
sucessfully used to create a new user and database using unix_socket
at /var/run/mysqld/mysqld.sock
at 2022-05-20T09:02:47Zmysql_user
and mysql_db
sucessfully used to create a new user and database using mysql_native_password
and config file /root/.my.cnf
at 2022-05-20T09:02:58Zmysql_user
and mysql_db
sucessfully used to create a new user and database using mysql_native_password
and a password at 2022-05-20T09:02:58Z
mysql_user
and mysql_db
sucessfully used to create a new user and database using unix_socket
at /var/run/mysqld/mysqld.sock
at 2022-05-20T09:05:04Zmysql_user
and mysql_db
sucessfully used to create a new user and database using mysql_native_password
and config file /root/.my.cnf
at 2022-05-20T09:05:16Zmysql_user
and mysql_db
sucessfully used to create a new user and database using mysql_native_password
and a password at 2022-05-20T09:05:16Zmysql_user
and mysql_db
sucessfully used to create a new user and database using unix_socket
at /var/run/mysqld/mysqld.sock
at 2022-05-20T09:05:31Zmysql_user
and mysql_db
sucessfully used to create a new user and database using mysql_native_password
and config file /root/.my.cnf
at 2022-05-20T09:05:42Zmysql_user
and mysql_db
sucessfully used to create a new user and database using mysql_native_password
and a password at 2022-05-20T09:05:42Z
mysql_user
and mysql_db
sucessfully used to create a new user and database using unix_socket
at /var/run/mysqld/mysqld.sock
at 2022-05-20T09:07:47Zmysql_user
and mysql_db
sucessfully used to create a new user and database using mysql_native_password
and config file /root/.my.cnf
at 2022-05-20T09:07:59Zmysql_user
and mysql_db
sucessfully used to create a new user and database using mysql_native_password
and a password at 2022-05-20T09:07:59Zmysql_user
and mysql_db
sucessfully used to create a new user and database using unix_socket
at /var/run/mysqld/mysqld.sock
at 2022-05-20T09:08:14Zmysql_user
and mysql_db
sucessfully used to create a new user and database using mysql_native_password
and config file /root/.my.cnf
at 2022-05-20T09:08:25Zmysql_user
and mysql_db
sucessfully used to create a new user and database using mysql_native_password
and a password at 2022-05-20T09:08:25Z
mysql_user
and mysql_db
sucessfully used to create a new user and database using unix_socket
at /var/run/mysqld/mysqld.sock
at 2022-05-20T09:10:28Zmysql_user
and mysql_db
sucessfully used to create a new user and database using mysql_native_password
and config file /root/.my.cnf
at 2022-05-20T09:10:40Zmysql_user
and mysql_db
sucessfully used to create a new user and database using mysql_native_password
and a password at 2022-05-20T09:10:40Zmysql_user
and mysql_db
sucessfully used to create a new user and database using unix_socket
at /var/run/mysqld/mysqld.sock
at 2022-05-20T09:10:55Zmysql_user
and mysql_db
sucessfully used to create a new user and database using mysql_native_password
and config file /root/.my.cnf
at 2022-05-20T09:11:06Zmysql_user
and mysql_db
sucessfully used to create a new user and database using mysql_native_password
and a password at 2022-05-20T09:11:06ZI suspect that the reason I thought there was a problem here is related to the issue with defaults files without [client]
sections.
@chriscroome ah, i missed your comments since https://github.com/ansible-collections/community.mysql/issues/215#issuecomment-1132183832, sorry, maybe was on PTO..
Thanks for the investigation and feedback!
So, i think we could close the issue then?
Yes it is fine to close this issue as far as I'm concerned.
Thank you folks! If needed we can re-open it
The full traceback is: File "/tmp/ansible_community.mysql.mysql_db_payloadh3x3ryg/ansible_community.mysql.mysql_db_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_db.py", line 639, in main File "/tmp/ansible_community.mysql.mysql_db_payloadh3x3ryg/ansible_community.mysql.mysql_db_payload.zip/ansible_collections/community/mysql/plugins/module_utils/mysql.py", line 106, in mysql_connect db_connection = mysql_driver.connect(autocommit=autocommit, **config) File "/openstack/venvs/utility-25.5.1.dev6/lib/python3.8/site-packages/pymysql/connections.py", line 353, in init self.connect() File "/openstack/venvs/utility-25.5.1.dev6/lib/python3.8/site-packages/pymysql/connections.py", line 664, in connect raise exc failed: [infra1_keystone_container-a297f966 -> infra1_utility_container-ef743dfd(172.16.105.197)] (item={'name': 'keystone', 'users': [{'username': 'keystone', 'password': '1940fa859922fb2053369aa42ea11b8cd0a35060f0b184d32b'}]}) => { "ansible_loop_var": "item", "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": "172.16.104.99", "login_password": null, "login_port": 3306, "login_unix_socket": null, "login_user": null, "master_data": 0, "name": [ "keystone" ], "quick": true, "restrict_config_file": false, "single_transaction": false, "skip_lock_tables": false, "state": "present", "target": null, "unsafe_login_password": false, "use_shell": false } }, "item": { "name": "keystone", "users": [ { "password": "1940fa859922fb2053369aa42ea11b8cd0a35060f0b184d32b", "username": "keystone" } ] }, "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (2003, \"Can't connect to MySQL server on '172.16.104.99' ([Errno 113] No route to host)\")" }
Hi Can somebody help me with this issue? I was in the middle of OpenStack-Ansible setup-openstack.yml Thanks in advance
SUMMARY
Ansible code, environment and target server did not change. However, on re-run of Ansible code with latest version, I am getting:
ISSUE TYPE
COMPONENT NAME
mysql_user
ANSIBLE VERSION
COLLECTION VERSION
CONFIGURATION
OS / ENVIRONMENT
Ansible controler:
Ansible target node:
STEPS TO REPRODUCE
Run task defined below.
Presumption is we have .my.cnf file in place
EXPECTED RESULTS
Task was not failing until recently with latest ansible version. No changes in ansible code, or on target server.
ACTUAL RESULTS