| Bug #114529 | SHOW PLUGINS and mysql.plugin table show inconsistent views | ||
|---|---|---|---|
| Submitted: | 2 Apr 2024 14:35 | Modified: | 3 Apr 2024 10:06 |
| Reporter: | Simon Mudd (OCA) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[2 Apr 2024 14:35]
Simon Mudd
[2 Apr 2024 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 2024 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 2024 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 2024 15:29]
MySQL Verification Team
You are truly welcome.
[3 Apr 2024 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 2024 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 2024 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.
