Error when setting MariaDB root credentials

Archives Ansible Project
1

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.

2

Hi Carlos,

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)

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
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

3

Hi Marcus!