# Error when setting MariaDB root credentials

**URL:** <https://forum.ansible.com/t/error-when-setting-mariadb-root-credentials/18199>\
**Category:** Ansible Project\
**Created:** [May 9, 2015, 3:38pm UTC](https://forum.ansible.com/t/error-when-setting-mariadb-root-credentials/18199 "2015-05-09T15:38:55Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Carlos\_A\_Carnero\_Del](https://avatars.discourse-cdn.com/v4/letter/c/7ea924/32.png) [@Carlos\_A\_Carnero\_Del](https://forum.ansible.com/u/Carlos_A_Carnero_Del)\
**Post date:** [May 9, 2015, 3:38pm UTC](https://forum.ansible.com/t/error-when-setting-mariadb-root-credentials/18199/1 "2015-05-09T15:38:55Z")

</div>

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](http://mariadb.example.com) | |
> 
> > root | 127.0.0.1 | |
> 
> > root | ::1 | |
> 
> > > localhost | |
> 
> > > [mariadb.example.com](http://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](http://mariadb.example.com)] =\>  
> (item=[mariadb.example.com](http://mariadb.example.com)) =\>  
> {“failed”: true, “item”: “[mariadb.example.com](http://mariadb.example.com)”}
> 
> msg: (1133, “Can’t find any matching row in the user table”)
> 
> changed: [[mariadb.example.com](http://mariadb.example.com)] =\> (item=127.0.0.1)
> 
> changed: [[mariadb.example.com](http://mariadb.example.com)] =\> (item=::1)
> 
> changed: [[mariadb.example.com](http://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](http://mariadb.example.com) | |
> 
> > root | 127.0.0.1 | \*1018BCB9A91D… |
> 
> > root | ::1 | \*1018BCB9A91D… |
> 
> > > localhost | |
> 
> > > [mariadb.example.com](http://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.

---

<div class="post-metadata">

**Author:** ![Marcus\_Franke](https://avatars.discourse-cdn.com/v4/letter/m/cdc98d/32.png) [@Marcus\_Franke](https://forum.ansible.com/u/Marcus_Franke)\
**Post date:** [May 11, 2015, 6:34am UTC](https://forum.ansible.com/t/error-when-setting-mariadb-root-credentials/18199/2 "2015-05-11T06:34:44Z")

</div>

Hi Carlos,

> > MariaDB [mysql]\> select User,Host,Password from user;
> > 
> > ±-----±--------------------±---------+
> > 
> > > User | Host | Password |
> > 
> > ±-----±--------------------±---------+
> > 
> > > root | localhost | |
> > 
> > > root | [mariadb.example.com](http://mariadb.example.com) | |
> > 
> > > root | 127.0.0.1 | |
> > 
> > > root | ::1 | |
> > 
> > > > localhost | |
> > 
> > > > [mariadb.example.com](http://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](http://mariadb.example.com)] =\>  
> > (item=[mariadb.example.com](http://mariadb.example.com)) =\>  
> > {“failed”: true, “item”: “[mariadb.example.com](http://mariadb.example.com)”}
> > 
> > msg: (1133, “Can’t find any matching row in the user table”)
> > 
> > changed: [[mariadb.example.com](http://mariadb.example.com)] =\> (item=127.0.0.1)
> > 
> > changed: [[mariadb.example.com](http://mariadb.example.com)] =\> (item=::1)
> > 
> > changed: [[mariadb.example.com](http://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

---

<div class="post-metadata">

**Author:** ![Carlos\_A\_Carnero\_Del](https://avatars.discourse-cdn.com/v4/letter/c/7ea924/32.png) [@Carlos\_A\_Carnero\_Del](https://forum.ansible.com/u/Carlos_A_Carnero_Del)\
**Post date:** [May 11, 2015, 5:29pm UTC](https://forum.ansible.com/t/error-when-setting-mariadb-root-credentials/18199/3 "2015-05-11T17:29:30Z")

</div>

Hi Marcus!