Error when setting MariaDB root credentials

[Carlos A. Carnero Delgado]

Hi,

I'm having problems with a playbook for setting up MariaDB, using Ansible 1.9.1 against a Centos 7.1.1503 host. I'll try and go step by step with the playbook, which starts thusly:

- name: Install MariaDB

  yum:

    name: "{{ item }}"

    state: present

  with_items:

  - mariadb

  - mariadb-server

  - MySQL-python

- name: Enable MariaDB

  service:

    name: mariadb

    enabled: yes

    state: started

Running the above will correctly install MariaDB and start running the service. Now, from the actual MariaDB host, if I connect to the service using the mysql client I can get:

MariaDB [mysql]> select User,Host,Password from user;

+------+---------------------+----------+

| User | Host                | Password |

+------+---------------------+----------+

| root | localhost           |          |

| root | mariadb.example.com |          |

| root | 127.0.0.1           |          |

| root | ::1                 |          |

|      | localhost           |          |

|      | mariadb.example.com |          |

+------+---------------------+----------+

6 rows in set (0.00 sec)

Which is expected in a default unattended install: root password is blank and there are a couple of anonymous credentials. Note, however, that there are credentials for the host FQDN, so I'm inclined to use ansible_fqdn in the following.

Now comes the part of the playbook that's giving me problems, when setting up the credentials:

- name: Privileged credentials

  mysql_user:

    name: root

    host: "{{ item }}"

    password: "{{ mariadb.password }}"

  with_items:

  - "{{ ansible_fqdn }}"

  - 127.0.0.1

  - ::1

  - localhost

- name: Privileged credentials console access

  template:

    src: root/my.cnf.j2

    dest: /root/.my.cnf

    owner: root

    group: root

    mode: 0600

(Obviously, mariadb.password, above, is a variable which is also used on the .my.cnf template.) The above was taken from several sources online; I only changed the use of ansible_host to ansible_fqdn. Running the playbook will fail in that task:

failed: [mariadb.example.com] =>

  (item=mariadb.example.com) =>

  {"failed": true, "item": "mariadb.example.com"}

msg: (1133, "Can't find any matching row in the user table")

changed: [mariadb.example.com] => (item=127.0.0.1)

changed: [mariadb.example.com] => (item=::1)

changed: [mariadb.example.com] => (item=localhost)

If I run the previous MariaDB command I get:

MariaDB [mysql]> select User,Host,Password from user;

+------+---------------------+------------------+

| User | Host                | Password         |

+------+---------------------+------------------+

| root | localhost           | *1018BCB9A91D... |

| root | mariadb.example.com |                  |

| root | 127.0.0.1           | *1018BCB9A91D... |

| root | ::1                 | *1018BCB9A91D... |

|      | localhost           |                  |

|      | mariadb.example.com |                  |

+------+--------------------+-------------------+

6 rows in set (0.01 sec)

Which shows that all the credentials were changed except the one with the FQDN host part. So, my question is: what am I doing wrong?

TIA.

[Marcus Franke]

Hi Carlos,

This one works for me. All four occurrences of root are being updated with the new password. Looks like {{ ansible_fqdn }} does not contain the host string from your

mysql.user table. Have you checked the output of running ansible -m setup <host>

for the ansible_fqdn variable to verify its content?

I had another problem with this, the mysql_user module isn’t idempotent itself. It

would fail badly in the second run, as your database is now password secured and

it could not login. You should wrap your two tasks like this:

- name: root user lock

  stat: path=/root/.my.cnf

  register: mysql_root_my_cnf

- name: Privileged credentials

  [skipped lines]

  when: not mysql_root_my_cnf.stat.exists

 

This way your root user will receive a password only once, until the .my.cnf file

was written from your second task.

Regards,

Marcus

-- 

May you always grok in fullness

[Carlos A. Carnero Delgado]

Hi Marcus!

On Mon, May 11, 2015 at 2:34 AM, Marcus Franke <marcus...@gmail.com> wrote:

... Looks like {{ ansible_fqdn }} does not contain the host string from your mysql.user table. Have you checked the output of running ansible -m setup <host> for the ansible_fqdn variable to verify its content?

I just checked, and the variable gets set properly, with the host's FQDN, the same as what's in MariaDB's privilege table.

In any case, I talked to the team and we're sidestepping this issue completely: we're just keeping the localhost and 127.0.0.1 records as those are the ones that really matter to us right now. When we're done with this, I'll throw more time and energy into solving our problem.

Regards,

Marcus

Thanks a lot for your time!