1# test code for state dump and import for mysql_db module
2# (c) 2014,  Wayne Rosario <wrosario@ansible.com>
3
4# This file is part of Ansible
5#
6# Ansible is free software: you can redistribute it and/or modify
7# it under the terms of the GNU General Public License as published by
8# the Free Software Foundation, either version 3 of the License, or
9# (at your option) any later version.
10#
11# Ansible is distributed in the hope that it will be useful,
12# but WITHOUT ANY WARRANTY; without even the implied warranty of
13# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
14# GNU General Public License for more details.
15#
16# You should have received a copy of the GNU General Public License
17# along with Ansible.  If not, see <http://www.gnu.org/licenses/>.
18
19# ============================================================
20- set_fact:
21    db_file_name: "{{ tmp_dir }}/{{ file }}"
22    wrong_sql_file: "{{ tmp_dir }}/wrong.sql"
23    dump_file1: "{{ tmp_dir }}/{{ file2 }}"
24    dump_file2: "{{ tmp_dir }}/{{ file3 }}"
25    db_user: "test"
26    db_user_unsafe_password: "pass!word"
27    config_file: "/root/.my.cnf"
28
29- name: create custom config file
30  shell: 'echo "[client]" > {{ config_file }}'
31
32- name: create user for test unsafe_login_password parameter
33  mysql_user:
34    login_user: '{{ mysql_user }}'
35    login_password: '{{ mysql_password }}'
36    login_host: 127.0.0.1
37    login_port: '{{ mysql_primary_port }}'
38    name: '{{ db_user }}'
39    password: '{{ db_user_unsafe_password }}'
40    priv: '*.*:ALL'
41    state: present
42
43- name: state dump/import - create database
44  mysql_db:
45    login_user: '{{ mysql_user }}'
46    login_password: '{{ mysql_password }}'
47    login_host: 127.0.0.1
48    login_port: '{{ mysql_primary_port }}'
49    name: '{{ db_name }}'
50    state: present
51    check_implicit_admin: yes
52
53- name: create database
54  mysql_db:
55    login_user: '{{ mysql_user }}'
56    login_password: '{{ mysql_password }}'
57    login_host: 127.0.0.1
58    login_port: '{{ mysql_primary_port }}'
59    name: '{{ db_name2 }}'
60    state: present
61    check_implicit_admin: no
62
63- name: state dump/import - create table department
64  command: "{{ mysql_command }} {{ db_name }} \"-e create table department(id int, name varchar(100))\""
65
66- name: state dump/import - create table employee
67  command: "{{ mysql_command }} {{ db_name }} \"-e create table employee(id int, name varchar(100))\""
68
69- name: state dump/import - insert data into table employee
70  command: "{{ mysql_command }} {{ db_name }} \"-e insert into employee value(47,'Joe Smith')\""
71
72- name: state dump/import - insert data into table department
73  command: "{{ mysql_command }} {{ db_name }} \"-e insert into department value(2,'Engineering')\""
74
75- name: state dump/import - file name should not exist
76  file:
77    name: '{{ db_file_name }}'
78    state: absent
79
80- name: database dump file1 should not exist
81  file:
82    name: '{{ dump_file1 }}'
83    state: absent
84
85- name: database dump file2 should not exist
86  file:
87    name: '{{ dump_file2 }}'
88    state: absent
89
90- name: state dump without department table.
91  mysql_db:
92    login_user: '{{ db_user }}'
93    login_password: '{{ db_user_unsafe_password }}'
94    login_host: 127.0.0.1
95    login_port: '{{ mysql_primary_port }}'
96    unsafe_login_password: yes
97    name: '{{ db_name }}'
98    state: dump
99    target: '{{ db_file_name }}'
100    ignore_tables:
101        - "{{ db_name }}.department"
102    force: yes
103    master_data: 1
104    skip_lock_tables: yes
105    dump_extra_args: --skip-triggers
106    config_file: '{{ config_file }}'
107    restrict_config_file: yes
108    check_implicit_admin: no
109  register: result
110
111- name: assert successful completion of dump operation
112  assert:
113    that:
114      - result is changed
115      - result.executed_commands[0] is search("mysqldump --defaults-file={{ config_file }} --user={{ db_user }} --password=\*\*\*\*\*\*\*\* --force --host=127.0.0.1 --port={{ mysql_primary_port }} {{ db_name }} --skip-lock-tables --quick --ignore-table={{ db_name }}.department --master-data=1 --skip-triggers")
116
117- name: state dump/import - file name should exist
118  file:
119    name: '{{ db_file_name }}'
120    state: file
121
122- name: state dump with multiple databases in comma separated form.
123  mysql_db:
124    login_user: '{{ mysql_user }}'
125    login_password: '{{ mysql_password }}'
126    login_host: 127.0.0.1
127    login_port: '{{ mysql_primary_port }}'
128    name: "{{ db_name }},{{ db_name2 }}"
129    state: dump
130    target: '{{ dump_file1 }}'
131    check_implicit_admin: yes
132  register: dump_result1
133
134- name: assert successful completion of dump operation (with multiple databases in comma separated form)
135  assert:
136    that:
137      - dump_result1 is changed
138      - dump_result1.executed_commands[0] is search(" --user=root --password=\*\*\*\*\*\*\*\*")
139
140- name: state dump - dump file1 should exist
141  file:
142    name: '{{ dump_file1 }}'
143    state: file
144
145- name: state dump with multiple databases in list form via check_mode
146  mysql_db:
147    login_user: '{{ mysql_user }}'
148    login_password: '{{ mysql_password }}'
149    login_host: 127.0.0.1
150    login_port: '{{ mysql_primary_port }}'
151    name:
152      - '{{ db_name }}'
153      - '{{ db_name2 }}'
154    state: dump
155    target: '{{ dump_file2 }}'
156  register: dump_result
157  check_mode: yes
158
159- name: assert successful completion of dump operation (with multiple databases in list form) via check mode
160  assert:
161    that:
162      - "dump_result.changed == true"
163
164- name: database dump file2 should not exist
165  stat:
166    path: '{{ dump_file2 }}'
167  register: stat_result
168
169- name: assert that check_mode does not create dump file for databases
170  assert:
171    that:
172      - stat_result.stat.exists is defined and not stat_result.stat.exists
173
174- name: state dump with multiple databases in list form.
175  mysql_db:
176    login_user: '{{ mysql_user }}'
177    login_password: '{{ mysql_password }}'
178    login_host: 127.0.0.1
179    login_port: '{{ mysql_primary_port }}'
180    name:
181      - '{{ db_name }}'
182      - '{{ db_name2 }}'
183    state: dump
184    target: '{{ dump_file2 }}'
185  register: dump_result2
186
187- name: assert successful completion of dump operation (with multiple databases in list form)
188  assert:
189    that:
190      - "dump_result2.changed == true"
191
192- name: state dump - dump file2 should exist
193  file:
194    name: '{{ dump_file2 }}'
195    state: file
196
197- name: state dump/import - remove database
198  mysql_db:
199    login_user: '{{ mysql_user }}'
200    login_password: '{{ mysql_password }}'
201    login_host: 127.0.0.1
202    login_port: '{{ mysql_primary_port }}'
203    name: '{{ db_name }}'
204    state: absent
205
206- name: remove database
207  mysql_db:
208    login_user: '{{ mysql_user }}'
209    login_password: '{{ mysql_password }}'
210    login_host: 127.0.0.1
211    login_port: '{{ mysql_primary_port }}'
212    name: '{{ db_name2 }}'
213    state: absent
214
215- name: test state=import to restore the database of type {{ format_type }} (expect changed=true)
216  mysql_db:
217    login_user: '{{ db_user }}'
218    login_password: '{{ db_user_unsafe_password }}'
219    login_host: 127.0.0.1
220    login_port: '{{ mysql_primary_port }}'
221    unsafe_login_password: yes
222    name: '{{ db_name }}'
223    state: import
224    target: '{{ db_file_name }}'
225    use_shell: yes
226  register: result
227
228- name: show the tables
229  command: "{{ mysql_command }} {{ db_name }} \"-e show tables\""
230  register: result
231
232- name: assert that the department table is absent.
233  assert:
234    that:
235      - "'department' not in result.stdout"
236
237- name: test state=import to restore a database from multiple database dumped file1
238  mysql_db:
239    login_user: '{{ mysql_user }}'
240    login_password: '{{ mysql_password }}'
241    login_host: 127.0.0.1
242    login_port: '{{ mysql_primary_port }}'
243    name: '{{ db_name2 }}'
244    state: import
245    target: '{{ dump_file1 }}'
246    use_shell: no
247  register: import_result
248
249- name: assert output message restored a database from dump file1
250  assert:
251    that:
252      - "import_result.changed == true"
253
254- name: remove database
255  mysql_db:
256    login_user: '{{ mysql_user }}'
257    login_password: '{{ mysql_password }}'
258    login_host: 127.0.0.1
259    login_port: '{{ mysql_primary_port }}'
260    name: '{{ db_name2 }}'
261    state: absent
262
263- name: run command to list databases
264  command: "{{ mysql_command }} \"-e show databases like 'data%'\""
265  register: mysql_result
266
267- name: assert that db_name2 database does not exist
268  assert:
269    that:
270      - "'{{ db_name2 }}' not in mysql_result.stdout"
271
272- name: test state=import to restore a database from dumped file2 (check mode)
273  mysql_db:
274    login_user: '{{ mysql_user }}'
275    login_password: '{{ mysql_password }}'
276    login_host: 127.0.0.1
277    login_port: '{{ mysql_primary_port }}'
278    name: '{{ db_name2 }}'
279    state: import
280    target: '{{ dump_file2 }}'
281  register: check_import_result
282  check_mode: yes
283
284- name: assert output message restored a database from dump file2 (check mode)
285  assert:
286    that:
287      - "check_import_result.changed == true"
288
289- name: run command to list databases
290  command: "{{ mysql_command }} \"-e show databases like 'data%'\""
291  register: mysql_result
292
293- name: assert that db_name2 database does not exist (check mode)
294  assert:
295    that:
296      - "'{{ db_name2 }}' not in mysql_result.stdout"
297
298- name: test state=import to restore a database from multiple database dumped file2
299  mysql_db:
300    login_user: '{{ mysql_user }}'
301    login_password: '{{ mysql_password }}'
302    login_host: 127.0.0.1
303    login_port: '{{ mysql_primary_port }}'
304    name: '{{ db_name2 }}'
305    state: import
306    target: '{{ dump_file2 }}'
307  register: import_result2
308
309- name: assert output message restored a database from dump file2
310  assert:
311    that:
312      - import_result2.changed == true
313      - import_result2.db_list == ['{{ db_name2 }}']
314
315- name: run command to list databases
316  command: "{{ mysql_command }} \"-e show databases like 'data%'\""
317  register: mysql_result
318
319- name: assert that db_name2 database does exist after import
320  assert:
321    that:
322      - "'{{ db_name2 }}' in mysql_result.stdout"
323
324- name: test state=dump to backup the database of type {{ format_type }} (expect changed=true)
325  mysql_db:
326    login_user: '{{ mysql_user }}'
327    login_password: '{{ mysql_password }}'
328    login_host: 127.0.0.1
329    login_port: '{{ mysql_primary_port }}'
330    name: '{{ db_name }}'
331    state: dump
332    target: '{{ db_file_name }}'
333  register: result
334
335- name: assert output message backup the database
336  assert:
337    that:
338      - "result.changed == true"
339      - "result.db =='{{ db_name }}'"
340
341# - name: assert database was backed up successfully
342#   command: "file {{ db_file_name }}"
343#   register: result
344#
345# - name: assert file format type
346#   assert:
347#     that:
348#       - "'{{ format_msg_type }}' in result.stdout"
349
350- name: update database table employee
351  command: "{{ mysql_command }} {{ db_name }} \"-e update employee set name='John Doe' where id=47\""
352
353- name: test state=import to restore the database of type {{ format_type }} (expect changed=true)
354  mysql_db:
355    login_user: '{{ mysql_user }}'
356    login_password: '{{ mysql_password }}'
357    login_host: 127.0.0.1
358    login_port: '{{ mysql_primary_port }}'
359    name: '{{ db_name }}'
360    state: import
361    target: '{{ db_file_name }}'
362  register: result
363
364- name: assert output message restore the database
365  assert:
366    that:
367      - "result.changed == true"
368
369- name: select data from table employee
370  command: "{{ mysql_command }} {{ db_name }} \"-e select * from  employee\""
371  register: result
372
373- name: assert data in database is from the restore database
374  assert:
375    that:
376      - "'47' in result.stdout"
377      - "'Joe Smith' in result.stdout"
378
379##########################
380# Test ``force`` parameter
381##########################
382
383- name: create wrong sql file
384  shell: echo 'CREATE TABLE hello (id int); CREATE ELBAT ehlo (int id);' >> '{{ wrong_sql_file }}'
385
386- name: try to import without force parameter, must fail
387  mysql_db:
388    login_user: '{{ mysql_user }}'
389    login_password: '{{ mysql_password }}'
390    login_host: 127.0.0.1
391    login_port: '{{ mysql_primary_port }}'
392    name: '{{ db_name }}'
393    state: import
394    target: '{{ wrong_sql_file }}'
395    force: no
396  register: result
397  ignore_errors: yes
398
399- assert:
400    that:
401      - result.failed == true
402
403- name: try to import with force parameter
404  mysql_db:
405    login_user: '{{ mysql_user }}'
406    login_password: '{{ mysql_password }}'
407    login_host: 127.0.0.1
408    login_port: '{{ mysql_primary_port }}'
409    name: '{{ db_name }}'
410    state: import
411    target: '{{ wrong_sql_file }}'
412    force: yes
413  register: result
414
415- assert:
416    that:
417      - result is changed
418
419##########
420# Clean up
421##########
422
423- name: remove database name
424  mysql_db:
425    login_user: '{{ mysql_user }}'
426    login_password: '{{ mysql_password }}'
427    login_host: 127.0.0.1
428    login_port: '{{ mysql_primary_port }}'
429    name: '{{ db_name }}'
430    state: absent
431
432- name: remove database
433  mysql_db:
434    login_user: '{{ mysql_user }}'
435    login_password: '{{ mysql_password }}'
436    login_host: 127.0.0.1
437    login_port: '{{ mysql_primary_port }}'
438    name: '{{ db_name2 }}'
439    state: absent
440
441- name: remove file name
442  file:
443    name: '{{ db_file_name }}'
444    state: absent
445
446- name: remove file name
447  file:
448    name: '{{ wrong_sql_file }}'
449    state: absent
450
451- name: remove dump file1
452  file:
453    name: '{{ dump_file1 }}'
454    state: absent
455
456- name: remove dump file2
457  file:
458    name: '{{ dump_file2 }}'
459    state: absent
460