mysql sys schema / mysqldump failure

mysql sys schema / mysqldump failure

  • Written by
    Walter Doekes
  • Published on

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…


Back to overview Newer post: tmpfs files not found / systemd Older post: copy-pasting into java applications / x11