Description:
With 8.0 persistent settings I thought I could change any setting I liked and persist it from the SQL command line. That's not the case. I noticed a go client was unable to reach my laptop which was running 8.0.11 so I've filed a PR for them to make the error message clearer and mention the plugin name if they don't support it.
See: https://github.com/go-sql-driver/mysql/pull/795
In the meantime I wanted to change the configuration using the new 8.0 settings.
Trying 2 different ways failed.
See below. I wasn't expecting this as it required me to edit /etc/my.cnf as in "the old days". 8.0 persistent settings are expected to make life easier yet at least with this setting that's not the case.
How to repeat:
root@127.0.0.1 [(none)]> set persist default_authentication_plugin = 'mysql_native_password';
ERROR 1238 (HY000): Variable 'default_authentication_plugin' is a read only variable
root@127.0.0.1 [(none)]> set persist_only default_authentication_plugin = 'mysql_native_password';
ERROR 1238 (HY000): Variable 'default_authentication_plugin' is a non persistent read only variable
Grants are:
root@127.0.0.1 [(none)]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
Suggested fix:
Make this work for the variable I want to change.
In general I'd expect this to work for almost all settings and those where it doesn't work should be really limited.
My current scripting for auto-configuring servers is aware of global variables which are read-only, it's partly aware of variables which may require you to restart the slave for the setting to become effective. Now it looks like I need to additionally track which variables are or are not persistable. Unless I'm mistaken none of this information is available in I_S which means I need to track by mysql version which settings apply.
I have another FR to expose this meta data. Please consider adding it if there are a number of non-persistable settings I need to be aware of so I don't need to store them in code.
