When I upgraded mysqld by yum update, then the following error came to be displayed when executing “show databases” .<\/p>\n\n\n\n
the user specified as a definer ('mysql.infoschema'@'localhost') does not exist<\/pre>\n\n\n\nSurvey<\/h2>\n\n\n\n
I entered the mysql console as a root user and ran the following command<\/p>\n\n\n\n
select User, plugin, authentication_string from mysql.user where user like 'mysql.%';<\/pre>\n\n\n\nThe results were as follows.<\/p>\n\n\n\n
+------------------+-----------------------+------------------------------------------------------------------------+\n| User | plugin | authentication_string |\n+------------------+-----------------------+------------------------------------------------------------------------+\n| mysql.infoschema | mysql_native_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |\n| mysql.session | mysql_native_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |\n| mysql.sys | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |\n+------------------+-----------------------+------------------------------------------------------------------------+<\/pre>\n\n\n\nauthentication_string stores a hash of the password, but the format “$A$005…” seems to be generated when using caching_sha2_password_sha2_password for encryption.
mysql.infoschema and mysql.session are encrypted with “mysql_ native_password” is used but “$A$005…” is set.
This is strange.<\/p>\n\n\n\n
Solution<\/h2>\n\n\n\n
This may seem a bit aggressive, but I ran the following SQL to make sure that the password encryption method matches the hash format.<\/p>\n\n\n\n
update mysql.user set plugin = 'caching_sha2_password' where User = 'mysql.infoschema'\nupdate mysql.user set plugin = 'caching_sha2_password' where User = 'mysql.session'<\/pre>\n\n\n\nAfter that, check the contents of mysql.user again in the aforementioned SQL. The contents should look like the following.<\/p>\n\n\n\n
+------------------+-----------------------+------------------------------------------------------------------------+\n| User | plugin | authentication_string |\n+------------------+-----------------------+------------------------------------------------------------------------+\n| mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |\n| mysql.session | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |\n| mysql.sys | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |\n+------------------+-----------------------+------------------------------------------------------------------------+<\/pre>\n\n\n\nAfter this, I restarted mysqld and executed “show databases” without any problems.<\/p>\n","protected":false},"excerpt":{"rendered":"