mysql_info returns an empty list for slave_status when using MariaDB with multiple replication channels

[wfelipew]

SUMMARY

The mysql_info returns an empty list for slave_status when using MariaDB with multiple named replication channels/sources.

This happens because the output of the show slave status is not consistent between MariaDB and MySQL.

According to MySQL documentation if we run SHOW SLAVE STATUS (without a specific replication channel) it should return all replication channels.
But In MariaDB, if we want to get all replication channels we need to use the SHOW ALL SLAVES STATUS otherwise it will only return the default channel.

ISSUE TYPE

• Bug Report

COMPONENT NAME

Collection: community.mysql
Module: mysql_info

ANSIBLE VERSION

ansible [core 2.11.12] 
  config file = None
  configured module search path = ['/home/example/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/example/.venv/ansible_2.10/lib/python3.8/site-packages/ansible
  ansible collection location = /home/example/.ansible/collections:/usr/share/ansible/collections
  executable location = /home/example/.venv/ansible_2.10/bin/ansible
  python version = 3.8.10 (default, Nov 22 2023, 10:22:35) [GCC 9.4.0]
  jinja version = 3.0.1
  libyaml = True

But it happens on any ansible version.

COLLECTION VERSION

$ ansible-galaxy collection list community.mysql
# /home/example/Documents/ansible_collections
Collection      Version
--------------- -------
community.mysql 3.8.0  

But it happens on any collection version.

CONFIGURATION

Not related to configuration

OS / ENVIRONMENT

The issue should only happen when using MariaDB

STEPS TO REPRODUCE

1. Create a MariaDB instance
2. Add the first replication source (CHANGE MASTER 'example1' TO ...; START SLAVE 'example1';)
3. Add the second replication source (CHANGE MASTER 'example2' TO ...; START SLAVE 'example2';)
4. Run community.mysql.mysql_info with slave_status filter
5. Check the output

- name: Get mysql_info
  community.mysql.mysql_info:
    filter: slave_status
  register: testing
  become: true
  tags:
    - always

- debug:
    msg: "{{ testing.slave_status }}"
  tags:
    - always

EXPECTED RESULTS

$ ansible-playbook -i inventories/ --check --diff  -l mariadb.example.com playbooks/mariadb.yml
PLAY [Install/Configure MariaDB] ****************************************************************************************************************************************

TASK [Gathering Facts] **************************************************************************************************************************************************
Thursday 04 January 2024  19:03:03 +0000 (0:00:00.035)       0:00:00.079 ****** 
ok: [mariadb.example.com]

TASK [example.mariadb : Populate facts] *************************************************************************************************************************************
Thursday 04 January 2024  19:03:23 +0000 (0:00:19.892)       0:00:19.971 ****** 
included: /home/example/Documents/example-ansible_base/roles/example.mariadb/tasks/facts.yml for mariadb.example.com

TASK [example.mariadb : Get mysql_info] *************************************************************************************************************************************
Thursday 04 January 2024  19:03:23 +0000 (0:00:00.022)       0:00:19.993 ****** 
ok: [mariadb.example.com]

TASK [example.mariadb : debug] **********************************************************************************************************************************************
Thursday 04 January 2024  19:03:42 +0000 (0:00:19.362)       0:00:39.355 ****** 
ok: [mariadb.example.com] => {
    "msg": {
        "replication.example.com": {
            "3306": {
                "replicate_db_001": {
                    "Connect_Retry": 60,
                    "Connection_name": "example1",
                    "Exec_Master_Log_Pos": 170041708,
                    "Executed_log_entries": 25973857,
                    "Gtid_IO_Pos": "0-8723-1512347238,366-8723-1534994784,466-233-464430931",
                    "Gtid_Slave_Pos": "0-22190-1512603882,366-8723-1536396267,466-233-464430931",
                    "Last_Errno": 0,
                    "Last_Error": "",
                    "Last_IO_Errno": 0,
                    "Last_IO_Error": "",
                    "Last_SQL_Errno": 0,
                    "Last_SQL_Error": "",
                    "Master_Log_File": "log-bin.006904",
                    "Master_SSL_Allowed": "No",
                    "Master_SSL_CA_File": "",
                    "Master_SSL_CA_Path": "",
                    "Master_SSL_Cert": "",
                    "Master_SSL_Cipher": "",
                    "Master_SSL_Crl": "",
                    "Master_SSL_Crlpath": "",
                    "Master_SSL_Key": "",
                    "Master_SSL_Verify_Server_Cert": "No",
                    "Master_Server_Id": 233,
                    "Max_relay_log_size": 1073741824,
                    "Parallel_Mode": "optimistic",
                    "Read_Master_Log_Pos": 170041708,
                    "Relay_Log_File": "relay-bin-example1.000028",
                    "Relay_Log_Pos": 101696731,
                    "Relay_Log_Space": 101697091,
                    "Relay_Master_Log_File": "log-bin.006904",
                    "Replicate_Do_DB": "",
                    "Replicate_Do_Domain_Ids": "",
                    "Replicate_Do_Table": "",
                    "Replicate_Ignore_DB": "mysql,performance_schema,information_schema",
                    "Replicate_Ignore_Domain_Ids": "",
                    "Replicate_Ignore_Server_Ids": "",
                    "Replicate_Ignore_Table": "",
                    "Replicate_Wild_Do_Table": "",
                    "Replicate_Wild_Ignore_Table": "mysql.%,performance_schema.%,information_schema.%",
                    "Retried_transactions": 0,
                    "SQL_Delay": 0,
                    "SQL_Remaining_Delay": null,
                    "Seconds_Behind_Master": 0,
                    "Skip_Counter": 0,
                    "Slave_DDL_Groups": 0,
                    "Slave_IO_Running": "Yes",
                    "Slave_IO_State": "Waiting for master to send event",
                    "Slave_Non_Transactional_Groups": 0,
                    "Slave_SQL_Running": "Yes",
                    "Slave_SQL_Running_State": "Slave has read all relay log; waiting for more updates",
                    "Slave_SQL_State": "Slave has read all relay log; waiting for more updates",
                    "Slave_Transactional_Groups": 1125363,
                    "Slave_heartbeat_period": 30,
                    "Slave_received_heartbeats": 0,
                    "Until_Condition": "None",
                    "Until_Log_File": "",
                    "Until_Log_Pos": 0,
                    "Using_Gtid": "Slave_Pos"
                }
            }
        },
        "master2.example.com": {
            "3306": {
                "replicate_db_001": {
                    "Connect_Retry": 60,
                    "Connection_name": "example2",
                    "Exec_Master_Log_Pos": 298086447,
                    "Executed_log_entries": 9608214,
                    "Gtid_IO_Pos": "0-8723-1512347238,366-8723-1536396267,466-233-463305575",
                    "Gtid_Slave_Pos": "0-22190-1512603882,366-8723-1536396267,466-233-464430931",
                    "Last_Errno": 0,
                    "Last_Error": "",
                    "Last_IO_Errno": 0,
                    "Last_IO_Error": "",
                    "Last_SQL_Errno": 0,
                    "Last_SQL_Error": "",
                    "Master_Log_File": "binlog.000784",
                    "Master_SSL_Allowed": "No",
                    "Master_SSL_CA_File": "",
                    "Master_SSL_CA_Path": "",
                    "Master_SSL_Cert": "",
                    "Master_SSL_Cipher": "",
                    "Master_SSL_Crl": "",
                    "Master_SSL_Crlpath": "",
                    "Master_SSL_Key": "",
                    "Master_SSL_Verify_Server_Cert": "No",
                    "Master_Server_Id": 273,
                    "Max_relay_log_size": 1073741824,
                    "Parallel_Mode": "optimistic",
                    "Read_Master_Log_Pos": 298086447,
                    "Relay_Log_File": "relay-bin-example2.000012",
                    "Relay_Log_Pos": 177215035,
                    "Relay_Log_Space": 177215386,
                    "Relay_Master_Log_File": "binlog.000784",
                    "Replicate_Do_DB": "",
                    "Replicate_Do_Domain_Ids": "",
                    "Replicate_Do_Table": "",
                    "Replicate_Ignore_DB": "mysql,performance_schema,information_schema",
                    "Replicate_Ignore_Domain_Ids": "",
                    "Replicate_Ignore_Server_Ids": "",
                    "Replicate_Ignore_Table": "",
                    "Replicate_Wild_Do_Table": "",
                    "Replicate_Wild_Ignore_Table": "mysql.%,performance_schema.%,information_schema.%",
                    "Retried_transactions": 0,
                    "SQL_Delay": 0,
                    "SQL_Remaining_Delay": null,
                    "Seconds_Behind_Master": 0,
                    "Skip_Counter": 0,
                    "Slave_DDL_Groups": 0,
                    "Slave_IO_Running": "Yes",
                    "Slave_IO_State": "Waiting for master to send event",
                    "Slave_Non_Transactional_Groups": 0,
                    "Slave_SQL_Running": "Yes",
                    "Slave_SQL_Running_State": "Slave has read all relay log; waiting for more updates",
                    "Slave_SQL_State": "Slave has read all relay log; waiting for more updates",
                    "Slave_Transactional_Groups": 1401474,
                    "Slave_heartbeat_period": 30,
                    "Slave_received_heartbeats": 0,
                    "Until_Condition": "None",
                    "Until_Log_File": "",
                    "Until_Log_Pos": 0,
                    "Using_Gtid": "Slave_Pos"
                }
            }
        }
    }
}

ACTUAL RESULTS

$ ansible-playbook -i inventories/ --check --diff  -l mariadb.example.com playbooks/mariadb.yml
PLAY [Install/Configure MariaDB] ****************************************************************************************************************************************

TASK [Gathering Facts] **************************************************************************************************************************************************
Thursday 04 January 2024  19:01:25 +0000 (0:00:00.035)       0:00:00.079 ****** 
ok: [mariadb.example.com]

TASK [example.mariadb : Populate facts] *************************************************************************************************************************************
Thursday 04 January 2024  19:01:44 +0000 (0:00:19.369)       0:00:19.448 ****** 
included: /home/example/Documents/example-ansible_base/roles/example.mariadb/tasks/facts.yml for mariadb.example.com

TASK [example.mariadb : Get mysql_info] *************************************************************************************************************************************
Thursday 04 January 2024  19:01:44 +0000 (0:00:00.023)       0:00:19.471 ****** 
ok: [mariadb.example.com]

TASK [example.mariadb : debug] **********************************************************************************************************************************************
Thursday 04 January 2024  19:02:04 +0000 (0:00:20.055)       0:00:39.526 ****** 
ok: [mariadb.example.com] => {
    "msg": {}
}

[wfelipew]

PR for the fix: #602

[laurent-indermuehle]

@wfelipew if I get it right, you're enforcing the MySQL way on MariaDB when using the ansible module? That seems odd to me. If MariaDB returns nothing when asking SHOW REPLICA STATUS, the module should to it too.
A more aggressive fix would be to fork the whole collection as discussed before to separate MySQL and MariaDB once an for all (but that's for another day). In the meantime, what about a new filter : "all_slave_status"?

[wfelipew]

Hey @laurent-indermuehle, I'm not exactly enforcing the MySQL way on MariaDB... I'm just trying to return what I think is expected from the module, If the module documentation says that the slave_status key will return Slave status information. I expect to have all the slave status information (The module documentation does not mention that the results will be collected from the SHOW SLAVE STATUS).

[laurent-indermuehle]

@wfelipew I think you're right. We ask the module to return the whole replication status, we don't need to know if MySQL and MariaDB would have act differently on the command line. Sorry for the misunderstanding on my side.