mysql_db module does not detect mysqldump failure

[jsefferon]

SUMMARY

mysql_db module does not detect mysqldump failure when output is set to be a compressed file.

ISSUE TYPE

• Bug Report

COMPONENT NAME

mysql_db

ANSIBLE VERSION

ansible 2.10.15
  config file = None
  configured module search path = ['/root/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3.8/site-packages/ansible
  executable location = /usr/bin/ansible
  python version = 3.8.10 (default, May  6 2021, 00:05:59) [GCC 10.2.1 20201203]

COLLECTION VERSION

Collection        Version
----------------- -------
community.general 1.3.6

OS / ENVIRONMENT

Encountered on Debian GNU/Linux 9.13 (stretch).

STEPS TO REPRODUCE

You need to be able to get mysqldump to fail, in our case it was failing because records dumped exceeded max_allowed_packet default of 16MB which we verified by running mysqldump from CLI instead of via mysql_db. Perhaps setting invalid credentials may break it too.

E.g.:

- name: backup the database
  mysql_db:
    name: mydatabase
    state: dump
    target: /tmp/backup.gz
    login_host: ~
    login_port: ~
    login_user: ~
    login_password: ~
    dump_extra_args: "--max_allowed_packet=1MB"

EXPECTED RESULTS

Task fails as the value of max_allowed_packet doesn't permit it to successfully complete mysqldump when dumping an object bigger than 1MB.

ACTUAL RESULTS

Task completes successfully with /tmp/backup.gz file created which contains valid SQL file. However the backup is incomplete - i.e. missing data and/or structure as the mysqldump process exited early with an error.

I believe this is because the output is set to be gzip and ansible pipes the output from mysqldump into gzip, however unless set -o pipefail is set for that shell's environment, failure of mysqldump won't be noticed and a partial mysql dump file will be gzipped.

I regard this as a bug since it's expected that if the module provides automatic packaging, it should handle the dump failure regarding of what the default shell settings may be.

See https://github.com/ansible-collections/community.mysql/blob/main/plugins/modules/mysql_db.py#L420 for the relevant code.

[Andersson007]

@jsefferon hi, thank you for reporting and investigating this! Looks like a bug. I'll put the "help_wanted" label on the issue.

[laurent-indermuehle]

I encountered this issue using v3.2.0. If a view reference en missing table, the dump will contain only a single line: DROP TABLE IF EXISTS v1;.

Even if the output of mysqldump was very clear:

mysqldump: Couldn't execute 'SHOW FIELDS FROM `v1`': View 'someview.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)

This is a serious bug! It's even worse than a backup that doesn't works. It produce a SQL that can wipe your data.

[Andersson007]

hm, pipefail is unavailable in the standard shell.. this is a problem

[laurent-indermuehle]

I've a fork with an integration test to reproduce the issue: https://github.com/laurent-indermuehle/community.mysql/blob/fix_mysqldump_ignore_errors/tests/integration/targets/test_mysql_db/tasks/issue_256_mysqldump_errors.yml

I'm ready to explore solutions. I should have time for this in few hours.
I don't know yet if it is possible to set pipefail in Python subcommand. Any advise or pointer would help :)

If I'm right, the issue is here: https://github.com/ansible-collections/community.mysql/blob/main/plugins/modules/mysql_db.py#L425-L431

[Andersson007]

@laurent-indermuehle cool, please go ahead:)

[Andersson007]

@jsefferon thank you for reporting the issue!
@laurent-indermuehle fixed this by introducing a new parameter which will be available after the next release and you need to set it explicitly (in the next major release we'll change the default so that failing will be a default behavior).

Thank you folks for your contribution!

[ghost]

Is this issue really fixed? I'm seeing this issue in 3.6.0

[laurent-indermuehle]

@matiasbarrosinvgate do you see it on backup or restore?
The fix is for the backup only.
Are you able to reproduce the issue?