introspect server's replication settings from SQL stored routines

Background

Historically, the only command available for showing complete information on replication status in MySQL and MariaDB has been the SHOW REPLICA STATUS command (formerly known as SHOW SLAVE STATUS). See MySQL docs and MariaDB docs.

This command has a major downside: its output cannot be captured and used in a SQL SELECT query, subquery, or SQL stored routine. All of the following result in syntax errors:

• SHOW REPLICA STATUS LIKE 'Last_Errno
• SHOW SLAVE STATUS INTO Last_Errno, Master_Log_File
• SELECT Last_Errno, Master_Log_File FROM (SHOW SLAVE STATUS)
• SET @array = (SHOW SLAVE STATUS)

The frequently-cited workaround is to execute the SHOW SLAVE STATUS command from some external program via a connector/driver; at that layer, the output of SHOW REPLICA STATUS can be read and manipulated in a way that's equivalent to the output of a SELECT query.

Problems this causes

However, because of this gap between the SQL syntax and the information sought, it was impossible to write a SQL stored routine which introspects the replication status of the MySQL/MariaDB server on which it's running.

Numerous blog posts and StackOverflow questions over the course of decades attest to frustration and confusion over this situation, e.g.:

• "Can we capture only Slave_IO_Running in SHOW SLAVE STATUS in MySQL" (2011)
  https://dba.stackexchange.com/questions/12554
• "What is the SELECT statement equivalent of SHOW ALL SLAVES STATUS?" (2021)
  https://dba.stackexchange.com/questions/287263

Incomplete fixes in MySQL 5.7 and MariaDB 10.5

Starting in MySQL 5.7 and MariaDB 10.5, some of the information conveyed in the output of SHOW SLAVE STATUS became available via SELECT queries on the PERFORMANCE_SCHEMA.REPLICATION* views.

However, even with the addition of the relevant performance-schema views, not all of the information available in the output of SHOW SLAVE STATUS is available via normal SELECT queries.

The MariaDB documentation does not explicitly note this, but the MySQL documentation does:

several SHOW SLAVE STATUS columns are not preserved in the Performance Schema replication tables:

[list of such columns]

A 2018 blog post by @wisborg helpfully shows the gaps in the form of a table mapping SHOW SLAVE STATUS output fields to performance-schema tables and columns:

Potential solutions

1. Improve the syntactic flexibility of SHOW SLAVE STATUS, so that it's possible to do e.g. SELECT field FROM (SHOW SLAVE STATUS).
2. Ensure that all of the internal replication-related metadata that is exposed in SHOW SLAVE STATUS is also available in performance_schema views.

(1) depends on extending a non-standard and inconsistent syntax syntax. I strongly prefer (2).