So, I was messing around with a Dev environment to simulate some strategies, doing some tests, and after a mysqldump exporting, dropping, and reimporting my whole database got this error.
If you arrived here from Google, Is this your case?
Well, this happens for a simple reason. The routines are not exported by mysqldump by default. Why? I don’t know either, this is an abomination to me. This would be very cheap to be the default right?
Happens that even for a new database, when importing a dump generated with –all-databases the sys/information_schema routines are deleted by the restore process. Well, at least this is recognized as a Bug (Bug 83259).
I noticed that when trying to query a sys view to get locks info, as per:
root@localhost-(none)-13:46:26>SELECT waiting_trx_id, waiting_pid, waiting_query, blocking_trx_id, -> blocking_pid, blocking_query FROM sys.innodb_lock_waits; ERROR 1356 (HY000): View 'sys.innodb_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Hm.. Weird message, let’s check for information_schema objects:
root@localhost-(none)-13:46:39>select count(*) from information_schema.ROUTINES where ROUTINE_SCHEMA='sys'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)
Ok, so, if you are already in this mess, how to quick recover?
Well, just run the mysql_upgrade (assuming you are in the top version/repository you have available on server):
root@localhost-(none)-13:47:34>exit Bye [root@greporasrv ~]# mysql_upgrade Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Checking system database. mysql.columns_priv OK mysql.db OK mysql.engine_cost OK mysql.event OK mysql.func OK mysql.general_log OK mysql.gtid_executed OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.server_cost OK mysql.servers OK mysql.slave_master_info OK mysql.slave_relay_log_info OK mysql.slave_worker_info OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK The sys schema is already up to date (version 1.5.1). Found 0 sys functions, but expected 22. Re-installing the sys schema. Upgrading the sys schema. Checking databases. mysqlslap.t1 OK [... my other databases...] sys.sys_config OK world.city OK world.country OK world.countrylanguage OK Upgrade process completed successfully. Checking if update is needed.
Fine, lets test it:
[root@greporasrv ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 32 Server version: 5.7.24-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@localhost-(none)-13:49:44>select count(*) from information_schema.ROUTINES where ROUTINE_SCHEMA='sys'; +----------+ | count(*) | +----------+ | 48 | +----------+ 1 row in set (0.00 sec) root@localhost-(none)-13:49:53>
Hope it helps.