Error occurrence
When I upgraded mysqld by yum update, then the following error came to be displayed when executing “show databases” .
the user specified as a definer ('mysql.infoschema'@'localhost') does not exist
Survey
I entered the mysql console as a root user and ran the following command
select User, plugin, authentication_string from mysql.user where user like 'mysql.%';
The results were as follows.
+------------------+-----------------------+------------------------------------------------------------------------+ | User | plugin | authentication_string | +------------------+-----------------------+------------------------------------------------------------------------+ | mysql.infoschema | mysql_native_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | mysql.session | mysql_native_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | mysql.sys | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | +------------------+-----------------------+------------------------------------------------------------------------+
authentication_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.
Solution
This may seem a bit aggressive, but I ran the following SQL to make sure that the password encryption method matches the hash format.
update mysql.user set plugin = 'caching_sha2_password' where User = 'mysql.infoschema' update mysql.user set plugin = 'caching_sha2_password' where User = 'mysql.session'
After that, check the contents of mysql.user again in the aforementioned SQL. The contents should look like the following.
+------------------+-----------------------+------------------------------------------------------------------------+ | User | plugin | authentication_string | +------------------+-----------------------+------------------------------------------------------------------------+ | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | mysql.session | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | mysql.sys | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | +------------------+-----------------------+------------------------------------------------------------------------+
After this, I restarted mysqld and executed “show databases” without any problems.
