# mysql\_db state=import only once **URL:** \ **Category:** Ansible Project\ **Created:** [November 26, 2013, 4:42pm UTC](https://forum.ansible.com/t/mysql-db-state-import-only-once/14005 "2013-11-26T16:42:42Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![Fred\_Badel](https://avatars.discourse-cdn.com/v4/letter/f/f07891/32.png) [@Fred\_Badel](https://forum.ansible.com/u/Fred_Badel)\ **Post date:** [November 26, 2013, 4:42pm UTC](https://forum.ansible.com/t/mysql-db-state-import-only-once/14005/1 "2013-11-26T16:42:42Z")
Hello, I have a playbook that create a mysql DB and import an empty schema: - name: create DB mysql\_db: name={{ db\_name }} state=present login\_host=127.0.0.1 - name: create schema mysql\_db: name={{ db\_name }} state=import login\_host=127.0.0.1 target=/create\_tables.sql From what i could notice, the sql file is imported each time the playbook is run … (aka, the task is marked as changed each time I run the playbook) is this an expected behavior? does someone have a recommended way of using the import feature and run it only once? Thanks. Fred ---
**Author:** ![Bruce\_Pennypacker](https://avatars.discourse-cdn.com/v4/letter/b/0ea827/32.png) [@Bruce\_Pennypacker](https://forum.ansible.com/u/Bruce_Pennypacker)\ **Post date:** [November 26, 2013, 6:15pm UTC](https://forum.ansible.com/t/mysql-db-state-import-only-once/14005/2 "2013-11-26T18:15:03Z")
It looks like the mysql\_db module will blindly run mysqldump wiht the specified target file if it’s specified, so yes it’s currently expected behavior. What you would probably need to do is run an action before you call mysql\_db that checks to see if the database already exists and register a variable so that you can call create DB only if you need to. This could be something along the lines of: -name: check if DB exists shell: mysql -u -p -h ‘show databases;’ | grep {{ db\_name }} register: dbstatus If you run that with -vvv you’ll see all the facts available in the dbstatus variable, and one of them can be used in a ‘when’ clause for your mysql\_DB call. -Bruce ---
**Author:** ![David\_Karban](https://avatars.discourse-cdn.com/v4/letter/d/90db22/32.png) [@David\_Karban](https://forum.ansible.com/u/David_Karban)\ **Post date:** [November 26, 2013, 6:18pm UTC](https://forum.ansible.com/t/mysql-db-state-import-only-once/14005/3 "2013-11-26T18:18:19Z")
Hi, you can use notify in create DB to create schema. That way, it will be called only on db creation. David Dne 26.11.2013 17:42 “Fred Badel” \<[fred@never-mind.ch](mailto:fred@never-mind.ch)\> napsal(a): ---
**Author:** ![Fred\_Badel](https://avatars.discourse-cdn.com/v4/letter/f/f07891/32.png) [@Fred\_Badel](https://forum.ansible.com/u/Fred_Badel)\ **Post date:** [November 26, 2013, 9:08pm UTC](https://forum.ansible.com/t/mysql-db-state-import-only-once/14005/4 "2013-11-26T21:08:22Z")
Hi, I was more thinking of checking the existence of the tables, as the database might exist but be empty … I guess the way to go would be to query the “information\_schema” for the existence of the tables and register the result … Thanks for the tip. Cheers, fred ---
**Author:** ![Fred\_Badel](https://avatars.discourse-cdn.com/v4/letter/f/f07891/32.png) [@Fred\_Badel](https://forum.ansible.com/u/Fred_Badel)\ **Post date:** [November 26, 2013, 9:12pm UTC](https://forum.ansible.com/t/mysql-db-state-import-only-once/14005/5 "2013-11-26T21:12:25Z")
I don’t know why, but in my mind, notifying handlers was only to restart a service … now I can see the light!! 🙂 Although, this would not help if the db exists but is empty, you just help me understand how I can use and how useful “notify” can be ! Thank you. fred ---
**Author:** ![Jeff\_Geerling](https://avatars.discourse-cdn.com/v4/letter/j/13edae/32.png) [@Jeff\_Geerling](https://forum.ansible.com/u/Jeff_Geerling)\ **Post date:** [February 7, 2014, 4:06am UTC](https://forum.ansible.com/t/mysql-db-state-import-only-once/14005/6 "2014-02-07T04:06:48Z")
Coming into this discussion a little late, but here’s an example to illustrate what’s being suggested: Play: - name: Ensure example database exists. mysql\_db: name=exampledb state=present notify: import example database Handler: - name: import example database mysql\_db: name=example state=import target=/path/to/example\_schema.sql This works great, and avoids errors I was getting when I tried doing the import straightaway; the import only seems to work if the database already exists. ---
**Author:** ![Andrew\_Udvare](https://avatars.discourse-cdn.com/v4/letter/a/ba8739/32.png) [@Andrew\_Udvare](https://forum.ansible.com/u/Andrew_Udvare)\ **Post date:** [June 11, 2014, 3:13am UTC](https://forum.ansible.com/t/mysql-db-state-import-only-once/14005/7 "2014-06-11T03:13:58Z")
I have pretty much the same thing in my playbook. It’s a bit nasty though IMO. Plus shell or command _always_ says changed which is useless for handlers. If the value becomes true, then I import in the next task. A conditional\_import (taking the place of import) option on the mysql\_db module would be cool. A few things it could do: 1. Verify there are no tables in the database 2. If there are tables, for each table, SELECT \* FROM table LIMIT 1 and keep a count. If any return 1 than the database is populated, no import. Else import. I don’t know how slow this would be if you have say 300+ tables but regardless it would be useful. An alternative to 2 could be something like: has\_rows=dbname.tablename so basically if tablename has any rows, the database is not considered empty and no import will happen. This could be required for such a conditional\_import flag. I know many other people may not be importing databases often (it is a bi tof a one off task between migrations usualy). But it is something to keep in mind at least with something like a Vagrant VM provisioned with Ansible. Being idempotent, you would want the user to be confident in typing `vagrant provision` knowing that they will not lose their data they have worked on and need in their test database. ---
**Author:** ![Paul\_Durivage](https://avatars.discourse-cdn.com/v4/letter/p/94ad74/32.png) [@Paul\_Durivage](https://forum.ansible.com/u/Paul_Durivage)\ **Post date:** [June 11, 2014, 7:33pm UTC](https://forum.ansible.com/t/mysql-db-state-import-only-once/14005/8 "2014-06-11T19:33:12Z")
Hi Andrew, Determining how a task should be treated as “changed” is can be controlled using the changed\_when directive. See the documentation: [http://docs.ansible.com/playbooks\_error\_handling.html#overriding-the-changed-result](http://docs.ansible.com/playbooks_error_handling.html#overriding-the-changed-result) -name: check if DB exists shell: mysql -u -p -h ‘show databases;’ | grep {{ db\_name }} register: dbstatus changed\_when: dbstatus.rc == 1 and not dbstatus.stdout Or something like that. Grep returns a 1 in no lines match, and there won’t be any standard out if no lines match. This might be a good opportunity to create a MySQL facts module that returns information about a server, from which you can use logic to decide how to implement tasks. Just a thought. ---
**Author:** ![Michael\_DeHaan1](https://avatars.discourse-cdn.com/v4/letter/m/5e9695/32.png) [@Michael\_DeHaan1](https://forum.ansible.com/u/Michael_DeHaan1)\ **Post date:** [June 11, 2014, 11:21pm UTC](https://forum.ansible.com/t/mysql-db-state-import-only-once/14005/9 "2014-06-11T23:21:59Z")
“This might be a good opportunity to create a MySQL facts module that returns information about a server,” While you’re free to do that, not sure we want this – Trying to control over-proliferation of ansible-facts modules if possible as here the register seems pretty easy. If it returns a giant ton of other things someone needs, that’s one thing – but if it just sounds useful – I’d suggest dragging your feet a bit 🙂 ---
**Author:** ![Michael\_Peters](https://avatars.discourse-cdn.com/v4/letter/m/c2a13f/32.png) [@Michael\_Peters](https://forum.ansible.com/u/Michael_Peters)\ **Post date:** [June 11, 2014, 11:32pm UTC](https://forum.ansible.com/t/mysql-db-state-import-only-once/14005/10 "2014-06-11T23:32:39Z")
I've wanted to create a mysql\_query module that would probably help with a lot of things like this. It could execute an arbitrary query and then you could register the results. Would make it easier than having to manipulate mysql command line stdout since you could use structured data. I just haven't had the time to tackle this yet. ---
**Author:** ![Paul\_Durivage](https://avatars.discourse-cdn.com/v4/letter/p/94ad74/32.png) [@Paul\_Durivage](https://forum.ansible.com/u/Paul_Durivage)\ **Post date:** [June 12, 2014, 4:36am UTC](https://forum.ansible.com/t/mysql-db-state-import-only-once/14005/11 "2014-06-12T04:36:09Z")
Nope, I’m good - I’m not super interested in MySQL so I’m not personally going to take part in writing this module. Might be useful and/or fun for someone to tackle. ---
**Author:** ![Jae\_Task](https://avatars.discourse-cdn.com/v4/letter/j/c68b51/32.png) [@Jae\_Task](https://forum.ansible.com/u/Jae_Task)\ **Post date:** [December 8, 2014, 7:14pm UTC](https://forum.ansible.com/t/mysql-db-state-import-only-once/14005/12 "2014-12-08T19:14:09Z")
Hi, First of all I want to thank you all for this thread, it helped me solve a problem that I was struggling with. This is my first post to the group and want to say hello 🙂 I have a slightly different solution to this problem as I was running several tasks in a row, all using with\_items on the same array and could not use a handler. I am building a multi wordpress installation playbook and solved the problem like this: - name: Create wordpress database mysql\_db: name={{ item.wp\_db\_name }} state=present with\_items: wordpress\_nginx\_sites register: create\_wordpress\_databases #- name: debug reg # debug: var=“{{ create\_wordpress\_databases }}” - name: Create wordpress database user mysql\_user: name={{ item.wp\_db\_user }} password={{ item.wp\_db\_password }} priv={{ item.wp\_db\_name }}.\*:ALL host=‘localhost’ state=present with\_items: wordpress\_nginx\_sites - name: Copy the wordpress mysql db copy: src=files/sites/{{ item.item.name }}/{{ item.item.wp\_db\_name }}.sql dest=/tmp when: item.changed == True with\_items: create\_wordpress\_databases.results - name: Import the database mysql\_db: name={{ item.item.wp\_db\_name }} state=import target=/tmp/{{ item.item.wp\_db\_name }}.sql when: item.changed == True with\_items: create\_wordpress\_databases.results As you can see. Because there are multiple databases being created via looping a dictionary, I could not see a way to call a handler and know which element of the dictionary had triggered the handler. The interesting part of this is when looping the result of the create task, you must use {{ item.item.name }} to get at the value. I am fairly new to ansible so feel free to advise on a better way of doing this if Its not best practice. Jae. ---
**Author:** ![Michael\_DeHaan1](https://avatars.discourse-cdn.com/v4/letter/m/5e9695/32.png) [@Michael\_DeHaan1](https://forum.ansible.com/u/Michael_DeHaan1)\ **Post date:** [December 12, 2014, 12:22am UTC](https://forum.ansible.com/t/mysql-db-state-import-only-once/14005/13 "2014-12-12T00:22:47Z")
" as I was running several tasks in a row, all using with\_items on the same array and could not use a handler." If using with\_items and a handler, you’ll get one notification if anything in the list reports changed. I think what you have is ok really, because you’ll want those things to happen “then” rather than at the end when handlers normally run, so good work!