Hello all!
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.
Cheers!
