mysql sys schema / mysqldump failure

After upgrading the mysql-server to 5.7 and enabling GTIDs, the mysql-backup script started spewing errors.

Warning: A partial dump from a server that has GTIDs will by default include
  the GTIDs of all transactions, even those that changed suppressed parts of the
  database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To
  make a complete dump, pass --all-databases --triggers --routines --events.
(...repeated for every database schema...)

mysqldump: Couldn't execute 'SHOW FIELDS FROM `host_summary`':
  View 'sys.host_summary' references invalid table(s) or column(s) or function(s)
  or definer/invoker of view lack rights to use them (1356)

That’s two errors for the price of one.

The first was easily fixed by doing as suggested:

• Add --set-gtid-purged=OFF to the per-schema mysqldump.

• Add a tailing dump for ALL; this is only viable if your databases are small or few. (If they are few, you could consider skipping the per-schema dump.)

  mysqldump $mysqlargs --quick --all-databases --triggers --routines \
      --events >"$dstpath/ALL.$day.sql" &&
    rm -f "$dstpath/ALL.$day.sql.bz2" &&
    bzip2 "$dstpath/ALL.$day.sql"
  

The second error was a bit more strange. For some reason the mysql upgrade had created the tables, but not the triggers and the functions. Or they got lost during a dump restore. In any case, debugging went like this:

# /usr/local/bin/mysql-backup
mysqldump: Couldn't execute 'SHOW FIELDS FROM `host_summary`':
  View 'sys.host_summary' references invalid table(s) or column(s) or function(s)
  or definer/invoker of view lack rights to use them (1356)

# mysql --defaults-file=/etc/mysql/debian.cnf sys
...
mysql> show create view host_summary\G
...
*************************** 1. row ***************************
                View: host_summary
         Create View: CREATE ALGORITHM=TEMPTABLE DEFINER=`mysql.sys`@`localhost`
                      SQL SECURITY INVOKER VIEW `host_summary` AS
                      select if(isnull(`performance_schema`.`accounts`.`HOST`)
...

mysql> select * from host_summary;
ERROR 1356 (HY000): View 'sys.host_summary' references invalid table(s) or
column(s) or function(s) or definer/invoker of view lack rights to use them

mysql> select if(isnull(`performance_schema`.`accounts`.`HOST`),'background',...
...
ERROR 1305 (42000): FUNCTION sys.format_time does not exist

A-ha, a missing function.

# dpkg -S /usr/share/mysql/mysql_sys_schema.sql
mysql-server-5.7: /usr/share/mysql/mysql_sys_schema.sql

# mysql --defaults-file=/etc/mysql/debian.cnf < /usr/share/mysql/mysql_sys_schema.sql
ERROR 1064 (42000) at line 43: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to
use near '' at line 1

Invalid SQL? Nah, just inline semi-colons within statements. Since MySQL doesn’t mind multiple statements at once, the fix was to surround the entire SQL with a new delimiter:

# ( cat /usr/share/mysql/mysql_sys_schema.sql; echo '####' ) |
    mysql --defaults-file=/etc/mysql/debian.cnf --delimiter='####' sys

Fixed! Now, what is that sys database anyway?

It’s a collection of views, functions and procedures to help MySQL administrators get insight into MySQL Database usage, according to the MySQL sys schema.

That might come in handy later…