Database initialisation is not idempotent

[cameronkerrnz]

Using mariadb_init_scripts will call the SQL every time; thereby re-initialising the database (as per the SQL) every time.

This is not the expected thing, and the documentation should clearly highlight this behaviour. I would have expected the script to only execute if the database was missing.

I think a 'when' clause is needed in the following:

- name: Initialise databases
  mysql_db:
    name: "{{ item.database }}"
    state: import
    target: "/tmp/{{ item.script|basename }}"
  with_items: "{{ mariadb_init_scripts }}"
  tags: mariadb

(RHEL7.3 mariadb from package; Ansible 2.2.1.0)

[cameronkerrnz]

Having a look at this, the root issue is with mysql_db, but I couldn't figure out how to make idempotency work in the context of having a list of dictionaries describing schemas which you want to interate over, all the while consulting another list of dictionaries being the results of database creation promises.

I settled on trying to put the idempotency into the SQL instead, which helps to make the schema management potentially more idempotent -- depending on how good your MySQL is.

Example schema commands:

--
-- NOTE NOTE NOTE
-- Ensure this schema definition is idempotent. Ansible will run it every bloody time you deploy.
--

--
-- Table structure for table `StorageRecords`
--

CREATE TABLE IF NOT EXISTS `StorageRecords` (
  `context` varchar(255) NOT NULL,
  `id` varchar(255) NOT NULL,
  `expires` bigint(20) DEFAULT NULL,
  `value` longtext NOT NULL,
  `version` bigint(20) NOT NULL,
  PRIMARY KEY (`context`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `tb_st`
--

CREATE TABLE IF NOT EXISTS `tb_st` (
  `uid` varchar(100) NOT NULL,
  `sharedToken` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[bertvv]

I'll check out if it's possible to run the init scripts only if the database was created in the previous task. Would that be a valid strategy?

[cameronkerrnz]

Sounds reasonable. I tried doing that, but couldn't get it to work. Thanks for providing this module.

[bertvv]

I'll need to reorder the variables a bit, I think. Specifically, mariadb_databases and mariadb_init_scripts need to be merged into a single list of dicts. I'll be in touch...

https://docs.ansible.com/ansible/playbooks_loops.html#using-register-with-a-loop