Test Kitchen issues found on password setting

[aalmenar]

While coding the percona support, i have added a .kitchen.yml to verify the installations:

On ubuntu 16.04 found this:

run_list:

• recipe[mysqld::mariadb_repository]
• recipe[mysqld::default]

And set the root_password attribute:

But password is set on authentication string not on password field, i have accesed this mariadb but the password was not set, solved it using:

mysql --defaults-file=/etc/mysql/debian.cnf mysql -e "UPDATE user SET Password = PASSWORD('abcdef01234567890') WHERE User = 'root'; FLUSH PRIVILEGES;"

I dont know if this happened because using the mariadb.org repository.

[aalmenar]

If more info is needed this is the select version()

MariaDB [(none)]> select version();
+--------------------------+
| version() |
+--------------------------+
| 10.1.18-MariaDB-1~xenial |
+--------------------------+
1 row in set (0.00 sec)

[chr4]

This is strange. Actually, in my understanding the new mariadb/ mysql packages should use authentication_string instead of the password column. We've discussed this change in this pull-request, and I'd welcome your thoughts on this. @Dens49, can you comment on this?

[Dens49]

That's basically what was trying to say in this comment: #6 (comment)
Mariadb contains both columns, but I was fearing that most users actually expect to use the password column instead of the authentication_string column.

I never used mariadb so I can only tell you what I've found in documentation and give you my assumptions based on that.
I just skimmed through a bit of mariadb documentation and, as far as I understand, the authentication_string column is used either when using an authentication-plugin for mariadb or when creating a new user with ... IDENTIFIED BY 'authentication_string'
mariadb 10.0.12 changelog
mariadb create user

So mysql needs the authentication_string column (version 5.7.6) but mariadb seems to expect the passwordcoulmn.
@aalmenar is it still somehow possible to authenticate as root user even when the password is set in authentication_string?

edit: Made a mistake. I actually don't understand when mariadb actually makes use of authentication_string. The column exists, but I can't find documentation that mentions how it's used.

[aalmenar]

My test didn't allowed me to login as root. mysql.user table had root user with 'Password' field empty and authentication_string with the password that i set as attribute.

Solved it by setting the Password with the modified query i put here: message 1

[aalmenar]

More logs:

     * execute[Assign mysql password for root user] action run
       - execute mysql --defaults-file\=/etc/mysql/debian.cnf mysql -e "UPDATE user SET authentication_string = PASSWORD('abcdef01234567890') WHERE User = 'root'; FLUSH PRIVILEGES;"
     * template[/etc/mysql/debian.cnf] action create (skipped due to only_if)
     * mysqld_password[debian-sys-maint] action set (up to date)
     * execute[Assign mysql password for debian-sys-maint user] action run
       - execute mysql --defaults-file\=/etc/mysql/debian.cnf mysql -e "UPDATE user SET authentication_string = PASSWORD('abcdef01234567890') WHERE User = 'debian-sys-maint'; FLUSH PRIVILEGES;"
     * template[/etc/mysql/debian.cnf] action create
       - update content in file /etc/mysql/debian.cnf from adbfa1 to c8d9d2
       --- /etc/mysql/debian.cnf    2016-11-02 09:22:38.062953468 +0000
       +++ /etc/mysql/.chef-debian20161102-28776-aa8e6l.cnf 2016-11-02 09:22:46.575207159 +0000
       @@ -2,12 +2,12 @@
        [client]
        host     = localhost
        user     = debian-sys-maint
       **-password = o1KC8XwCrGhnR9HY
       +password = abcdef01234567890**
        socket   = /var/run/mysqld/mysqld.sock
        [mysql_upgrade]
        host     = localhost
        user     = debian-sys-maint
       **-password = o1KC8XwCrGhnR9HY
       +password = abcdef01234567890**
        socket   = /var/run/mysqld/mysqld.sock
        basedir  = /usr

Then:

kitchen login mariadb-101-ubuntu-1604
Welcome to Ubuntu 16.04.1 LTS (GNU/Linux 4.4.0-38-generic x86_64)

• Documentation: https://help.ubuntu.com
• Management: https://landscape.canonical.com
• Support: https://ubuntu.com/advantage

0 packages can be updated.
0 updates are security updates.

*** System restart required ***
Last login: Wed Nov 2 09:22:05 2016 from 10.0.2.2
vagrant@mariadb-101-ubuntu-1604:$ mysql -uroot -pabcdef01234567890
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
vagrant@mariadb-101-ubuntu-1604:$ mysql -udebian-sys-maint -pabcdef01234567890
ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)
vagrant@mariadb-101-ubuntu-1604:$ mysql -udebian-sys-maint -po1KC8XwCrGhnR9HY
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.1.18-MariaDB-1xenial mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

More data on mysql.user:

MariaDB [mysql]> select User,Password,authentication_string from user;
+------------------+-------------------------------------------+-------------------------------------------+
| User | Password | authentication_string |
+------------------+-------------------------------------------+-------------------------------------------+
| root | | *DAAB22A8BC4FB4076CF8476386C388AAC0D3FAA1 |
| root | | *DAAB22A8BC4FB4076CF8476386C388AAC0D3FAA1 |
| root | | *DAAB22A8BC4FB4076CF8476386C388AAC0D3FAA1 |
| root | | *DAAB22A8BC4FB4076CF8476386C388AAC0D3FAA1 |
| debian-sys-maint | *34F6B613D6121FA6263CA6F8C9CAFE151B7A0812 | *DAAB22A8BC4FB4076CF8476386C388AAC0D3FAA1 |
+------------------+-------------------------------------------+-------------------------------------------+

[chr4]

Thanks you guys for the report and for looking into it! As far as I can see it, #13 and #14 address these issues? If so, please close the issue in favor of the pull-requests.

[aalmenar]

I included the fix of #13 on #14.