MySQL database password-changing failure during fresh install of IIAB [using "sudo iiab"] on Ubuntu 19.04 [and now 18.04 too...whereas 19.10 & Debian 10.x work!] -- Ansible Python 3 needs python3-distutils

[holta]

@tim-moody @georgejhunt this is the error I got the 1st time running a fresh install of "sudo iiab" on Ubuntu Server 19.04 VM @ 10.8.0.38:

TASK [mysql : Update MySQL root password for all remaining root accounts (127.0.0.1, ::1) if mysql_enabled] ***
failed: [127.0.0.1] (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 /home/iiab-admin/.my.cnf has the credentials. Exception message: (1045, "Access denied for user 'root'@'localhost' (using password: NO)")"}
failed: [127.0.0.1] (item=::1) => {"ansible_loop_var": "item", "changed": false, "item": "::1", "msg": "unable to connect to database, check login_user and login_password are correct or /home/iiab-admin/.my.cnf has the credentials. Exception message: (1045, "Access denied for user 'root'@'localhost' (using password: NO)")"}

This is the error I got the 2nd time I ran "sudo iiab" :

TASK [mysql : Update MySQL root password for localhost root accounts, if mysql_enabled] ***
fatal: [127.0.0.1]: FAILED! => {"changed": false, "msg": "unable to connect to database, check login_user and login_password are correct or /home/iiab-admin/.my.cnf has the credentials. Exception message: (1045, "Access denied for user 'root'@'localhost' (using password: NO)")"}

FWIW the above-mentioned file /home/iiab-admin/.my.cnf does not exist (certainly at this point).

During the 3rd run of "sudo iiab" the error seems to have gone away, presumably as a result of me running sudo su - to change from user iiab-admin to root?

Is installing IIAB when logged in as a non-root user something we can hopefully fix? (Reason: instructions tell implementers to install IIAB using curl <script> | sudo bash which has worked extremely well over the past ~2 years, opening doors for less-technical and more security-conscious implementers.)

[holta]

@jvonau recommended Step 5 of https://vitux.com/how-to-install-and-configure-mysql-in-ubuntu-18-04-lts/ to try to diagnose this...

iiab-admin@box:~$ sudo mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

root@box:~# sudo mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 197
Server version: 10.3.13-MariaDB-2 Ubuntu 19.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)]> SELECT user,authentication_string,plugin,host FROM mysql.user;
+---------------------+-----------------------+--------+-----------+
| user                | authentication_string | plugin | host      |
+---------------------+-----------------------+--------+-----------+
| root                |                       |        | localhost |
| root                |                       |        | 127.0.0.1 |
| root                |                       |        | ::1       |
| iiab_mediawiki_user |                       |        | localhost |
| nextcloud           |                       |        | localhost |
| nextcloud           |                       |        | 127.0.0.1 |
| nextcloud           |                       |        | ::1       |
| iiab_wp             |                       |        | localhost |
| iiab_commenter      |                       |        | localhost |
+---------------------+-----------------------+--------+-----------+
9 rows in set (0.001 sec)

As compared to Ubuntu 18.04 where the output looks more promising...

iiab-admin@box:~$ sudo mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 203
Server version: 10.1.38-MariaDB-0ubuntu0.18.04.2 Ubuntu 18.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)]> SELECT user,authentication_string,plugin,host FROM mysql.user;
+---------------------+-----------------------+-------------+-----------+
| user                | authentication_string | plugin      | host      |
+---------------------+-----------------------+-------------+-----------+
| root                |                       | unix_socket | localhost |
| root                |                       |             | 127.0.0.1 |
| root                |                       |             | ::1       |
| iiab_mediawiki_user |                       |             | localhost |
| Admin               |                       |             | 127.0.0.1 |
| Admin               |                       |             | ::1       |
| Admin               |                       |             | localhost |
| nextcloud           |                       |             | localhost |
| nextcloud           |                       |             | 127.0.0.1 |
| nextcloud           |                       |             | ::1       |
| iiab_wp             |                       |             | localhost |
| iiab_commenter      |                       |             | localhost |
+---------------------+-----------------------+-------------+-----------+
12 rows in set (0.00 sec)

@jvonau suggests:

Think iiab-admin might need a group added to the user rights for DB access
On 19.04
Root would belong to all groups while iiab-admin would need groups added to the profile

@holta asked:

On RPi, people typically run 'sudo iiab' as user pi (instead of as user iiab-admin) ...does that affect things?
i.e. would it be viable to make 'sudo iiab' run from any user that has sudoer powers?

@jvonau:

Would depend on how sudo is configured

@holta:

Is there something I should try on 19.04 (10.8.0.38) just to check if "sudo mysql" or similar can be run by iiab-admin there?

[holta]

@m-anish appears to be having much the same issue on Ubuntu 18.04.2

Two possible solutions are outlined herein:
https://stackoverflow.com/questions/39281594/error-1698-28000-access-denied-for-user-rootlocalhost

Summary:

2 ways to solve this:

1. You can set the root user to use the mysql_native_password plugin
2. You can create a new db_user with you system_user (recommended)

#1729

[tim-moody]

or simply sudo mysql -u root works for me on 18.04.2 as iiab-admin
I think sudo mysql doesn't work because it tries to be iiab-admin. I know the message says otherwise, but it says the same just issuing mysql as iiab-admin

[tim-moody]

in general I prefer the approach we are taking for apps such as nextcloud, wp where we create a user in ansible, create an empty db in ansible, and give that user all rights on the db.

[holta]

@jvonau suggests @tim-moody's above suggestions might be insufficient with Ansible: perhaps instead user iiab-admin (or user pi?) might need to be added to a db group to solve this?

@georgejhunt: should we use Ansible's become to escalate privilege?

[holta]

Back-ref:

Outstanding IIAB issues (network & Node-RED) on Ubuntu Server 19.04 [released 2019-04-18]
#1586

[holta]

Does this have anything to do with?

#1833 Lost connection to MySQL server at 'sending authentication information', system error: 32 "Broken pipe"

[holta]

@georgejhunt: should we use Ansible's become to escalate privilege?

@jvonau agrees this is likely the best way to solve it.

Surprisingly http://download.iiab.io installed Stages 0-to-5 onto Ubuntu Server 19.10 Final Freeze (BIG-sized IIAB) without problems!

So I'm closing this tkt, as Ubuntu 19.04 support is not required or even desired.

(But do re-open if/as nec.)

[holta]

Fresh install of IIAB 7.1/master (http://download.iiab.io) on Ubuntu 19.10 failed as follows:

TASK [mysql : Update MySQL root password for localhost root accounts, if mysql_enabled] ***
fatal: [127.0.0.1]: FAILED! => {"changed": false, "msg": "The PyMySQL (Python 2.7 and Python 3.X) or MySQL-python (Python 2.X) module is required."}

FYI the rrror went away when I re-ran 'sudo iiab' -- whereas last week it succeeded on the very 1st pass.

[holta]

@jvonau believes Ansible's Python 3 warnings on Ubuntu 19.10 may be related...pasting in from https://paste.ubuntu.com/p/TTRh4xQvJK/ :

./iiab-install  BEGUN IN /opt/iiab/iiab

Placed /etc/ansible/facts.d/local_facts.fact into position.
Found Kernel 5.3.0-18-generic
Found Ansible 2.8.3

TRY TO RERUN './iiab-install' IF IT FAILS DUE TO CONNECTIVITY ISSUES ETC!

Running local playbooks....Stage 0 will now run....followed by Stages 1-9
 [WARNING]: Skipping plugin (/usr/lib/python3/dist-
packages/ansible/plugins/callback/osx_say.py) as it seems to be invalid: No
module named 'distutils.spawn'
 [WARNING]: Skipping plugin (/usr/lib/python3/dist-
packages/ansible/plugins/callback/say.py) as it seems to be invalid: No module
named 'distutils.spawn'
 [WARNING]: Skipping plugin (/usr/lib/python3/dist-
packages/ansible/plugins/connection/docker.py) as it seems to be invalid: No
module named 'distutils.spawn'

 [WARNING]: Skipping plugin (/usr/lib/python3/dist-
packages/ansible/plugins/connection/iocage.py) as it seems to be invalid: No
module named 'distutils.spawn'

 [WARNING]: Skipping plugin (/usr/lib/python3/dist-
packages/ansible/plugins/connection/jail.py) as it seems to be invalid: No
module named 'distutils.spawn'

 [WARNING]: Skipping plugin (/usr/lib/python3/dist-
packages/ansible/plugins/connection/kubectl.py) as it seems to be invalid: No
module named 'distutils.spawn'

 [WARNING]: Skipping plugin (/usr/lib/python3/dist-
packages/ansible/plugins/connection/libvirt_lxc.py) as it seems to be invalid:
No module named 'distutils.spawn'

 [WARNING]: Skipping plugin (/usr/lib/python3/dist-
packages/ansible/plugins/connection/lxd.py) as it seems to be invalid: No
module named 'distutils.spawn'

 [WARNING]: Skipping plugin (/usr/lib/python3/dist-
packages/ansible/plugins/connection/oc.py) as it seems to be invalid: No module
named 'distutils.spawn'

 [WARNING]: Skipping plugin (/usr/lib/python3/dist-
packages/ansible/plugins/connection/zone.py) as it seems to be invalid: No
module named 'distutils.spawn'

 [WARNING]: Skipping plugin (/usr/lib/python3/dist-
packages/ansible/plugins/callback/osx_say.py) as it seems to be invalid: No
module named 'distutils.spawn'

 [WARNING]: Skipping plugin (/usr/lib/python3/dist-
packages/ansible/plugins/callback/say.py) as it seems to be invalid: No module
named 'distutils.spawn'



PLAY [all] *********************************************************************

TASK [Gathering Facts] *********************************************************
ok: [127.0.0.1]

TASK [0-init] ******************************************************************

TASK [0-init : ...IS BEGINNING ============================================] ***
ok: [127.0.0.1]

...

Ref:
#1810 "Python 2 -> Python 3 IIAB strategic planning to prepare for 2020+"

[holta]

@jvonau helped me confirm that "apt install python3-distutils" (on a clean Ubuntu 19.10 system) make the above ~10 warnings go away.

[holta]

Likewise running apt install python3-pymysql might help solve this / the original MySQL install issue? (OTOH re-running sudo iiab alone worked last time, so tracing the root cause of this apparently intermittent issue ain't easy!)

root@box:~# apt install python3-pymysql
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  libestr0 libfastjson4
Use 'apt autoremove' to remove them.
Suggested packages:
  python-pymysql-doc
The following NEW packages will be installed:
  python3-pymysql
0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
Need to get 38.8 kB of archives.
After this operation, 178 kB of additional disk space will be used.
Get:1 http://us.archive.ubuntu.com/ubuntu eoan/main amd64 python3-pymysql all 0.9.3-1 [38.8 kB]
Fetched 38.8 kB in 0s (519 kB/s)
Selecting previously unselected package python3-pymysql.
(Reading database ... 108833 files and directories currently installed.)
Preparing to unpack .../python3-pymysql_0.9.3-1_all.deb ...
Unpacking python3-pymysql (0.9.3-1) ...
Setting up python3-pymysql (0.9.3-1) ...

[holta]

@jvonau suggests the following might be the underlying cause?

ansible/ansible#47736 "mysql_user broken in [Ansible] 2.7.1 when using /root/.my.cnf"

[holta]

@jvonau should we close this now?

(Or better to leave it open as a reference until upstream/all associated issues are more fully resolved?)

[tstauch]

I am getting a similar error:

TASK [mysql : Update MySQL root password for localhost root accounts, if mysql_enabled] ***
fatal: [127.0.0.1]: FAILED! => {"changed": false, "msg": "unable to connect to database: (1698, "Access denied for user 'root'@'localhost')")"}

Environment:
Fresh install of Ubuntu 18.0.4 Desktop (minimal), disabled GUI, run update and upgrade, then reboot and run iiab installer from terminal

Have tried sudio iiab a few times

Other points:

• Ubuntu running off an SSD on USB
• Lenovo laptop

I thought I would pause at this point in case it is helpful to provide debugging - before I start trying to find a workaround myself.

Is this an open issue? Any suggestions?

[jvonau]

The work around noted is it run 'iiab' as root and not to use sudo:
sudo su
iiab
instead of
sudo iiab

hope it helps.

[tstauch]

Thank you @jvonau that solved the problem easily.

Any idea how long before that work-around is addressed in the main ansible install script?

[tim-moody]

on 18.04 vm, newly upgraded I hit (running iiab-install as root) using #2042

TASK [mysql : Update MySQL root password for localhost root accounts, if mysql_enabled] ******************************************************************************************************
fatal: [127.0.0.1]: FAILED! => {"changed": false, "msg": "The PyMySQL (Python 2.7 and Python 3.X) or MySQL-python (Python 2.X) module is required."}

I have never seen this before.

[tim-moody]

Previous run I did not upgrade.
I also notice that pip and pip3 are both absent

[tim-moody]

- name: "Install packages: mysql, MySQL-python and 9 php packages (OS's other than debuntu)"
  package:
    name:
      - MySQL-python
      - mysql
      - php
      - php-mysql
      - php-pear
      - php-gd
      - php-imap
      - php-ldap
      - php-odbc
      - php-xml
      - php-xmlrpc
    state: present
  when: not is_debuntu

[tim-moody]

reran scripts/ansible
NOW WORKS

[holta]

The work around noted is it run 'iiab' as root and not to use sudo:
sudo su
iiab
instead of
sudo iiab

hope it helps.

TK Kang is also suffering thru this regression, when trying to install IIAB 7.1 pre-release (master) on Ubuntu 18.04 Desktop.

(If we do not find a solution, this will be documented as a "Known Issue" with the upcoming IIAB 7.1)

[holta]

(If we do not find a solution, this will be documented as [an Ubuntu 18.04] "Known Issue" with the upcoming IIAB 7.1)

This is appearing more likely, towards releasing IIAB 7.1 on/around 2020-01-30.

[holta]

Now that IIAB 7.1 pre-releases are installing very cleanly onto Ubuntu 20.04 LTS pre-releases, this has become less of an issue — and it should become even less of an issue on 2020-04-23 when Ubuntu 20.04 LTS is officially released — whereupon we can de-support legacy OS Ubuntu 18.04.

[holta]

I'm declaring this "wontfix" as (1) we transition from Ubuntu 18.04 LTS to 20.04 LTS (2) those who insist on using 18.04 have been warned @ https://github.com/iiab/iiab/wiki/IIAB-7.1-Release-Notes#known-issues

[holta]

Just fyi IIAB no longer supports Ubuntu 18.04 as a mainline OS, now that Ubuntu 20.04 has been released.

Apologies! For more details, please see: https://github.com/iiab/iiab/wiki/IIAB-Platforms