Bug #101217 Thoughts on 13.4.2.10 Functions which Configure the Source List
Submitted: 17 Oct 2020 10:08 Modified: 21 Oct 2020 17:52
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S4 (Feature request)
Version:8.0.22 OS:Any
Assigned to: CPU Architecture:Any
Tags: Docs, multiple_sources, udf

[17 Oct 2020 10:08] Simon Mudd
Description:
Thoughts on 13.4.2.10 Functions which Configure the Source List

See: https://dev.mysql.com/doc/refman/8.0/en/replication-functions-source-list.html

A new feature in 8.0.22 is the fact you can choose multiple servers to be used as the source of replication. This might be useful if replicating from a GR cluster or on a leaf slave in a large asynchronous cluster with intermediate masters.

The way to configure this new functionality is a little unusual using the CHANGE MASTER TO SOURCE_CONNECTION_AUTO_FAILOVER
setting in conjunction with the following UDF function calls: asynchronous_connection_failover_add_source() and asynchronous_connection_failover_delete_source().

This new functionality requires changes in configuration management.  Usually we configure MySQL using a my.cnf, using persistent settings, setting some values in performance_schema tables, using global or session variables or using commands like CHANGE MASTER TO which record the settings provided.

Adding yet another mechanism is most unhelpful, but this is what has been provided.

Related to: https://bugs.mysql.com/bug.php?id=101216

How to repeat:
Read the URL above and consider how you might use and configure this.
Suggestions for improvement are below.

Suggested fix:
1. Provide a UDF to list the current sources, asynchronous_connection_failover_list_sources()

You provide a mechanism to add or remove a source but no mechanism to query the current sources. Consider adding such a UDF so that the sources can be retrieved.

Expectation would be to return a list of 0 or more sources that could/would be used.

2. Provide examples using more than one source.

Given this is to allow for failover between multiple sources, e.g. GR members, it makes sense to show an example
where the GR members are configured. Providing an example with a single source is pointless.

Therefore provide examples where more than one source is used.

3. Make the return values of the UDF functions easier to parse.

Both current UDFs return 2 rows which have text in them. If you want to use the results you need
to parse the text to identify the results. This is really unhelpful.

I would suggest if you want a reasonably free form return value to return a single JSON value.

That value would have 2 parts:

3.1. the result of the call (successful / failed, a code and a description), so maybe something like:

[ 0, "Source configuration details successfully inserted." ]
or 
[ 0, "Source configuration details successfully deleted." ]

3.2. a list of the resultant sources

Examples might be:

- no sources
[]

- 3 sources
[
    ['channel1', '10.0.0.1', 3306, 'blue', 80],
    ['channel1', '10.0.0.2', 3306, 'blue', 80],
    ['channel1', '10.0.0.3', 3306, 'blue', 80]
]

So the resultant JSON returned to the user would be something like:

[
    [ 0, "Source configuration details successfully inserted." ],
    [
        ['channel1', '10.0.0.1', 3306, 'blue', 80],
        ['channel1', '10.0.0.2', 3306, 'blue', 80],
        ['channel1', '10.0.0.3', 3306, 'blue', 80]
    ]
]

[
    [ 1234, "invalid port number provided." ],
    []
]

If an error occurs it is likely that the source list would not be returned.
If you need that you could call asynchronous_connection_failover_list_sources()
which would return something like:

[
    ['channel1', '10.0.0.1', 3306, 'blue', 80],
    ['channel1', '10.0.0.2', 3306, 'blue', 80],
    ['channel1', '10.0.0.3', 3306, 'blue', 80]
]

This structured return value allows a user to call the UDF, collect the JSON
value and then examine the result for success/failure and get the values required
all in a structured manner (using different JSON parsing functions) without
having to resort to manually parsing the return values.

It might be more straight forward to make the add/ delete UDFs only return a status and have the list udf return the current settings but that is likely to change any existing tooling which might try to parse the current results, so returning the sources when calling add or delete probably needs to be maintained.
[17 Oct 2020 11:36] Simon Mudd
Related: https://bugs.mysql.com/bug.php?id=101218
[18 Oct 2020 5:47] MySQL Verification Team
Hello Simon,

Thank you for the reasonable feature request!

regards,
Umesh
[21 Oct 2020 17:38] Kenny Gryp
Hello Simon, 

One of the suggestions in this bug was to provide a UDF to list the sources. 

It is possible to query the replication_asynchronous_connection_failover performance schema table to get the configuration https://dev.mysql.com/doc/refman/8.0/en/performance-schema-replication-asynchronous-connec...

What benefit do you see in having the UDF to list the config compared to just querying the pfs table?
[21 Oct 2020 17:50] Simon Mudd
Hi Kenny,

One of the reasons to suggest the 3rd UDF is for consistency.
The second is there was no mention of this table in the documentation I saw.

Now I see that there's a performance_schema.replication_asynchronous_connection_failover table personally I'd be tempted to ask why you don't simply allow configuration changes to this table (via INSERT/UPDATE/DELETE) and ensure that DMLs are not replicated which has been the cases since I reported a bug in 5.6.5 where I saw this happen unintentionally.

So yes, now I know the structure of the P_S table that could be used. Please ensure there's a reference to this in the section mentioned for people who need to verify the settings.

Looking at the table definition of replication_asynchronous_connection_failover I see we still use utf8 rather than utf8mb4 which seems somewhat surprising.
[21 Oct 2020 17:52] Simon Mudd
Other p_s tables are used for configuration such as the setup_* tables. Why does replication_asynchronous_connection_failover require special treatment?