Ansible, Mysql: How to clean a database when you cannot drop it ?

Context

Ansible is used to deploy and configure an application which relies on Mysql for manipulating data. Sadly, this application is PCI related, and tons of other security regulation, therefore, applicative user is not granted the right to drop database. INSERT, UPDATE, DELETE, and DROP table, but not DROP database.

As im still testing Ansible deployement code, i need to clean 3 databases so that i can troubleshoot any issue with the database initialisation part of the code.

The main procedure

Well nothing is really magic here, this is a two steps procedure:

First, grab the full list of tables from the databases.

Second, drop those tables by itering over the table list.

Another point is that no mysql module can be used in this process, cause you know Ansible modules are bad for security. (This is a Joke).

The code of the first step is

- name: bddeploy | Get Table list from database
  shell: /bin/mysql -u {{ dbadmin }} --password={{ dbadminpassword }} -h {{ item.dbhost }} {{ item.dbname }} -s -N -e "select table_name from information_schema.tables where table_schema = '{{ item.dbname }}' ;"
  register: table_list1
  with_items:
    - { dbname: "{{ dbname_1 }}", dbhost: "{{ dbhost_1 }}" }
    - { dbname: "{{ dbname_2 }}", dbhost: "{{ dbhost_2 }}" }
    - { dbname: "{{ dbname_3 }}", dbhost: "{{ dbhost_3 }}" }

As you can see, i use the shell module to run the mysql command, to grab full table list from provided database.Im also itering on three items, a pair of database/host.

Finally, all results are stored into the variable table_list1 which becomes a list of dictionnary.

The second step is a little more tricky. I can only drop one table from one database from one host at a time. So i need to build a list of dictionnary so that it can feed with_items.

But first let’s see the Ansible code:

- name: bddeploy | Deleting all tables from all databases
  shell:  /bin/mysql -u {{ dbadmin }} --password={{ dbadminpassword }} -h {{ item.dbhost }} {{ item.dbname }} -s -N -e "set foreign_key_checks = 0 ; drop table if exists {{ item.table }}; "
  with_items:  "{{  table_list1.results | clean_table_output }}"

Nothing very difficult here, only you can see the use of three elements per items :

  • dbhost
  • dbname
  • table

How to get this input, from the content of the registered results of the previous task stored in table_list1 ?

This is where Ansible Filter enters the game

clean_table_output the filter

Ansible filter is a powerfull tool, which allow to run custom python code and interract with Ansible at runtime. Ansible can be a little limited to manipulate advanced datastructure, this is not it’s main goal anyway.

So using the filter interface, Python snippet can be used to format data as wanted and feed it back to Ansible for easier digestion.

from jinja2.utils import soft_unicode

def clean_table_output (arglist):
     dictlist = []
     for index in arglist:
         dbname=  index['item']['dbname']
         dbhost=  index['item']['dbhost']
         stdout= index['stdout_lines']
         for elem in stdout:
            dict = {'dbname': dbname, 'dbhost' : dbhost, 'table': elem}
            dictlist.append(dict)
     return dictlist

class FilterModule(object):
    ''' jinja2 filters '''
    def filters(self):
        return{
		'clean_table_output' : clean_table_output,
        }

The second function is the standard interface with Ansible while, clean_table_output is the main function doing the job.

First it defines a list of dict. Second, it will loop on the list, containing three elements, augmented output of the three calls of mysql. It grab the dbnameand the dbhostwhich were arguments of the previous commands. And it loops over each lines of the mysql command which was stored in the stdout_lines element.

This way it build a lists of dictionnaries that will be used by with_items for dropping table by table, datase by database, host by host.

Happy Hacking !