mysql_db state=import only once

[Fred Badel]

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

[Bruce Pennypacker]

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 <user> -p <password> -h <host> '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

[David Karban]

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" <fr...@never-mind.ch> napsal(a):

--
You received this message because you are subscribed to the Google Groups "Ansible Project" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ansible-proje...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Frédéric Badel]

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

[Frédéric Badel]

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

[Jeff Geerling]

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.

[Andrew Udvare]

On Tuesday, 26 November 2013 10:15:03 UTC-8, Bruce Pennypacker wrote:

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 <user> -p <password> -h <host> 'show databases;' | grep {{ db_name }}

 register: dbstatus

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.

[Paul Durivage]

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

-name: check if DB exists

 shell: mysql -u <user> -p <password> -h <host> '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.

[Michael DeHaan]

"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 :)

--
You received this message because you are subscribed to the Google Groups "Ansible Project" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ansible-proje...@googlegroups.com.

To post to this group, send email to ansible...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/CAK6JQEGtNRAcsdnTpAg7wFneZkKuBXifdh8_P%3DhHA-HenSqY6Q%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

[Michael Peters]

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.

> https://groups.google.com/d/msgid/ansible-project/CA%2BnsWgz1H%3DxJOK18kK0MLoUiYfyjJYvyi2MEN2aZso2%3DSCCkcw%40mail.gmail.com.

[Paul Durivage]

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. 

To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/CA%2BnsWgz1H%3DxJOK18kK0MLoUiYfyjJYvyi2MEN2aZso2%3DSCCkcw%40mail.gmail.com.

[Jae Task]

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.

[Michael DeHaan]

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

To post to this group, send email to ansible...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/c622bc6c-d7c9-4440-81bb-1d048b8df7c3%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.