Bug #114529 SHOW PLUGINS and mysql.plugin table show inconsistent views
Submitted: 2 Apr 14:35 Modified: 3 Apr 10:06
Reporter: Simon Mudd (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[2 Apr 14:35] Simon Mudd
Description:
I notice plugin listings to be inconsistent on 8.0.36 in the outputs shown via:
- SHOW PLUGINS
- SELECT * from mysql.plugin

For managing plugins in MySQL it's inconvenient to have to look at both outputs to decide if a plugin really is loaded or not. My code has been looking at the output from SHOW PLUGINS which seems to be more complete but it seems that is not enough.

How to repeat:
root@myhost [(none)]> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.36    |
+-----------+
1 row in set (0.00 sec)

root@myhost [(none)]> select * from mysql.plugin;
+----------------------+--------------------+
| name                 | dl                 |
+----------------------+--------------------+
| auth_pam_compat      | auth_pam_compat.so |
| clone                | mysql_clone.so     |
| myplugin1            | myplugin1.so       |
| maxdiskusage         | maxdiskusage.so    |
| rpl_semi_sync_master | semisync_master.so |
| rpl_semi_sync_slave  | semisync_slave.so  |
+----------------------+--------------------+
6 rows in set (0.00 sec)

root@myhost [(none)]> show plugins;
+----------------------------------+----------+--------------------+--------------------+-------------+
| Name                             | Status   | Type               | Library            | License     |
+----------------------------------+----------+--------------------+--------------------+-------------+
| binlog                           | ACTIVE   | STORAGE ENGINE     | NULL               | GPL         |
| mysql_native_password            | ACTIVE   | AUTHENTICATION     | NULL               | GPL         |
| sha256_password                  | ACTIVE   | AUTHENTICATION     | NULL               | GPL         |
| caching_sha2_password            | ACTIVE   | AUTHENTICATION     | NULL               | GPL         |
| sha2_cache_cleaner               | ACTIVE   | AUDIT              | NULL               | GPL         |
| daemon_keyring_proxy_plugin      | ACTIVE   | DAEMON             | NULL               | GPL         |
| CSV                              | ACTIVE   | STORAGE ENGINE     | NULL               | GPL         |
| MEMORY                           | ACTIVE   | STORAGE ENGINE     | NULL               | GPL         |
| InnoDB                           | ACTIVE   | STORAGE ENGINE     | NULL               | GPL         |
| INNODB_TRX                       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_CMP                       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_CMP_RESET                 | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_CMPMEM                    | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_CMPMEM_RESET              | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_CMP_PER_INDEX             | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_CMP_PER_INDEX_RESET       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_BUFFER_PAGE               | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_BUFFER_PAGE_LRU           | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_BUFFER_POOL_STATS         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_TEMP_TABLE_INFO           | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_METRICS                   | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_FT_DEFAULT_STOPWORD       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_FT_DELETED                | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_FT_BEING_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_FT_CONFIG                 | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_FT_INDEX_CACHE            | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_FT_INDEX_TABLE            | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_TABLES                    | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_TABLESTATS                | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_INDEXES                   | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_TABLESPACES               | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_COLUMNS                   | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_VIRTUAL                   | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_CACHED_INDEXES            | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| INNODB_SESSION_TEMP_TABLESPACES  | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL         |
| MyISAM                           | ACTIVE   | STORAGE ENGINE     | NULL               | GPL         |
| MRG_MYISAM                       | ACTIVE   | STORAGE ENGINE     | NULL               | GPL         |
| PERFORMANCE_SCHEMA               | ACTIVE   | STORAGE ENGINE     | NULL               | GPL         |
| TempTable                        | ACTIVE   | STORAGE ENGINE     | NULL               | GPL         |
| ARCHIVE                          | ACTIVE   | STORAGE ENGINE     | NULL               | GPL         |
| BLACKHOLE                        | ACTIVE   | STORAGE ENGINE     | NULL               | GPL         |
| FEDERATED                        | DISABLED | STORAGE ENGINE     | NULL               | GPL         |
| ndbcluster                       | DISABLED | STORAGE ENGINE     | NULL               | GPL         |
| ndbinfo                          | DISABLED | STORAGE ENGINE     | NULL               | GPL         |
| ndb_transid_mysql_connection_map | DISABLED | INFORMATION SCHEMA | NULL               | GPL         |
| ngram                            | ACTIVE   | FTPARSER           | NULL               | GPL         |
| mysqlx_cache_cleaner             | ACTIVE   | AUDIT              | NULL               | GPL         |
| mysqlx                           | ACTIVE   | DAEMON             | NULL               | GPL         |
| rpl_semi_sync_master             | ACTIVE   | REPLICATION        | semisync_master.so | GPL         |
| rpl_semi_sync_slave              | ACTIVE   | REPLICATION        | semisync_slave.so  | GPL         |
| maxdiskusage                     | ACTIVE   | AUDIT              | maxdiskusage.so    | GPL         |
| auth_pam_compat                  | ACTIVE   | AUTHENTICATION     | auth_pam_compat.so | GPL         |
| myplugin2                        | ACTIVE   | AUDIT              | myplugin2.so       | PROPRIETARY |
| clone                            | ACTIVE   | CLONE              | mysql_clone.so     | GPL         |
+----------------------------------+----------+--------------------+--------------------+-------------+
54 rows in set (0.01 sec)

root@myhost [(none)]>

These are 2 different custom plugins I use.

myplugin1 is shown in the select statement but not show plugins.
myplugin2 is shown in the show plugins but not the select statement.

This is a real production master host (names changed) using traditional replication.

This is completely inconsistent.  Plugin names have been changed as this is a public forum but it then
leads to code to handle plugin loading failing as if it uses SHOW PLUGINS and does not see the plugin
it will fail.

[user@myhost ~]$ check_cnf_settings.pl -v # this script checks /etc/my.cnf against the running configuration and modifies the running configuration as needed.
2024-04-02 15:58:09 myhost check_cnf_settings.pl[34558]: Verbosity has been set.
2024-04-02 15:58:09 myhost check_cnf_settings.pl[34558]: Connecting to database...
2024-04-02 15:58:09 myhost check_cnf_settings.pl[34558]: _parse_cnf
2024-04-02 15:58:09 myhost check_cnf_settings.pl[34558]: get_product_version(8.0.36)
2024-04-02 15:58:09 myhost check_cnf_settings.pl[34558]: product: MySQL, major: 8.0
2024-04-02 15:58:09 myhost check_cnf_settings.pl[34558]: get_product_version(8.0.36)
2024-04-02 15:58:09 myhost check_cnf_settings.pl[34558]: product: MySQL, major: 8.0
2024-04-02 15:58:09 myhost check_cnf_settings.pl[34558]: MySQL is systemd enabled
2024-04-02 15:58:09 myhost check_cnf_settings.pl[34558]: System is systemd enabled
2024-04-02 15:58:09 myhost check_cnf_settings.pl[34558]: plugin auth_pam_compat is already loaded, skipping
2024-04-02 15:58:09 myhost check_cnf_settings.pl[34558]: plugin myplugin2 is already loaded, skipping
2024-04-02 15:58:09 myhost check_cnf_settings.pl[34558]: plugin clone is already loaded, skipping
2024-04-02 15:58:09 myhost check_cnf_settings.pl[34558]: Found plugin myplugin1 with SO name myplugin1.so in config, ensuring it is installed
2024-04-02 15:58:09 myhost check_cnf_settings.pl[34558]: query: INSTALL PLUGIN myplugin1 SONAME 'myplugin1.so'
2024-04-02 15:58:09 myhost check_cnf_settings.pl[34558]: Unable to install plugin myplugin1: 3883: Error installing plugin 'myplugin1': got 'Operation not permitted' writing to mysql.plugin
2024-04-02 15:58:09 myhost check_cnf_settings.pl[34558]: plugin maxdiskusage is already loaded, skipping
2024-04-02 15:58:09 myhost check_cnf_settings.pl[34558]: plugin rpl_semi_sync_master is already loaded, skipping
2024-04-02 15:58:09 myhost check_cnf_settings.pl[34558]: plugin rpl_semi_sync_slave is already loaded, skipping
[user@myhost ~]$

Clearly I can work around this but I would expect the output of the 2 statements to be consistent.
It is not.

Related to: https://bugs.mysql.com/bug.php?id=102240 which is reported as fixed in 8.0.22.
It seems there is still some odd behaviour in 8.0.36.

For reference:

root@myhost [(none)]> show global status like 'uptime';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Uptime        | 851628 |
+---------------+--------+
1 row in set (0.00 sec)

root@myhost [(none)]> exit
Bye
[user@myhost ~]$ rpm -qi mysql-community-server
Name        : mysql-community-server
Version     : 8.0.36
Release     : 1.el8
Architecture: x86_64
Install Date: Mon 18 Mar 2024 01:50:38 PM CET
...

Suggested fix:
- Correct the inconsistencies.
- Potentially use a single "view" which can be accessed via the use of:

SHOW PLUGINS or
SELECT * FROM mysql.plugin

and ensure the output is consistent.
[2 Apr 14:46] MySQL Verification Team
HI Mr. Mudd,

Thank you for your bug report.

We also get inconsistent outputs from those two commands. 

"select * from mysql.plugins" returns empty result, while "show plugins" returns:

+----------------------------------+----------+--------------------+---------+---------+
| Name                             | Status   | Type               | Library | License |
+----------------------------------+----------+--------------------+---------+---------+
| binlog                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha256_password                  | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| caching_sha2_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha2_cache_cleaner               | ACTIVE   | AUDIT              | NULL    | GPL     |
| daemon_keyring_proxy_plugin      | ACTIVE   | DAEMON             | NULL    | GPL     |
| CSV                              | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM                    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX_RESET       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE_LRU           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_STATS         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TEMP_TABLE_INFO           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_METRICS                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DEFAULT_STOPWORD       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DELETED                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_BEING_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_CONFIG                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_CACHE            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_TABLE            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLES                    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLESTATS                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_INDEXES                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLESPACES               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_COLUMNS                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_VIRTUAL                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CACHED_INDEXES            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SESSION_TEMP_TABLESPACES  | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| MyISAM                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                       | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| PERFORMANCE_SCHEMA               | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| TempTable                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                          | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                        | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| ndbcluster                       | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| ndbinfo                          | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| ndb_transid_mysql_connection_map | DISABLED | INFORMATION SCHEMA | NULL    | GPL     |
| ngram                            | ACTIVE   | FTPARSER           | NULL    | GPL     |
| mysqlx_cache_cleaner             | ACTIVE   | AUDIT              | NULL    | GPL     |
| mysqlx                           | ACTIVE   | DAEMON             | NULL    | GPL     |
+----------------------------------+----------+--------------------+---------+---------+

This is now a verified bug report for MySQL 8.0 and higher.

Thank you.
[2 Apr 15:14] Simon Mudd
Just to confirm:

- removing the offending row in mysql.plugin and then rerunning my script (which installs the plugin) allows the plugin to be loaded as needed.

I can work around this but do not think it should be necessary.
[2 Apr 15:17] Simon Mudd
I think it's a bit more about the output being inconsistent. Some plugins may show in one "view" while others show in the other one. If the list of plugins is intended to be consistent (ignoring the number of columns) then that still looks odd.  Either way thanks for confirming my report.
[2 Apr 15:29] MySQL Verification Team
You are truly welcome.
[3 Apr 7:23] Georgi Kodinov
Posted by developer:
 
The mysql.plugin table is a *system table* that contains a list of plugin libraries to be loaded. 
This is just one of the possible ways to load plugins.
Plugins can also be loaded via the command line options.
Also the server loads a bunch of "statically linked" plugins. 

Note also that each plugin library can have multiple logical plugins. ha_innodb is a great example of that since it consists of about 10 or so "plugins". 

All of the above contribute to the runtime list of loaded plugins that is displayed via SHOW PLUGINS.

So the two of them being different is by design. 

Do you have a particular feature request here? If you do, please reopen the bug as a FR stating what you need.
[3 Apr 9:57] Simon Mudd
The feature request is quite simple. I want to see a consistent view of the plugin information so I do not need to check 1 of 3 possible sources of information to figure out if a plugin is loaded or not loaded.

This is important if I want to load a plugin or unload a plugin and on systems I use this does actually happen.

Different types of servers run different plugins (for a multitude of reasons which is not really relevant here but is true in practice), e.g. install some plugins only on a master/GR primary where writes are directed but ensure they are uninstalled on other members of a GR group or asynchronous replication chain.

I may also want to load or unload a plugin to update the version being used and to ensure uptime stays high I want to do this on servers which are running and taking production traffic.  Most of these plugins are not "visible" to the user but used for more internal management of what the user does.

Currently as reported in this bug report I get a list of plugins which are loaded in mysql.plugin which differs to what's shown in SHOW PLUGINS, so code that manages these plugins sees no plugin is loaded, tries to load it and gets an error.  Having to query SHOW PLUGINS and mysql.plugin seems to me to be a very inefficient way of figuring out if a plugin really is loaded or not.

It may also be that there's a bug in some code which leaves "stale" data in the mysql.plugin table.  I'm not sure of the history of what triggered the behaviour reported here but am reporting it because it happened and believe the inconsistency is confusing and not needed.

So as stated I'd like to request this inconsistency is resolved, to make plugin management simpler, and ideally come up with a "plugin view" which comes from a single source and potentially includes the combined information from the current 3 sources of plugin information: SELECT * from mysql.plugin, SELECT * FROM INFORMATION_SCHEMA.PLUGINS and SHOW PLUGINS, probably all extracted from the exact same source (for consistency).
[3 Apr 10:04] Simon Mudd
Again the main specific issue that I was seeing and I wanted to report was that:

root@myhost [(none)]> select * from mysql.plugin;
+----------------------+--------------------+
| name                 | dl                 |
+----------------------+--------------------+
...
| myplugin1            | myplugin1.so       |
...
+----------------------+--------------------+
6 rows in set (0.00 sec)

myplugin1 is shown above, yet 

root@myhost [(none)]> show plugins;
+----------------------------------+----------+--------------------+--------------------+-------------+
| Name                             | Status   | Type               | Library            | License     |
+----------------------------------+----------+--------------------+--------------------+-------------+
... skip as myplugin1 is not shown here ....
+----------------------------------+----------+--------------------+--------------------+-------------+
54 rows in set (0.01 sec)

I have seen audit plugins shown with status DELETED, which was not the case here, but I've seen in the past. myplugin1 was a custom built audit plugin.

Also if I need to be aware of what information is shown in the 3 possible outputs then please point out at where this is defined and confirm that it provides the necessary information to identify any possible states while plugins are being loaded or unloaded, including potential (expected) error situations.

My current code was simply inspecting SHOW PLUGIN output but that's clearly insufficient.