MySQL Bugs: #86807: mysqldump export empty mysql.proc

Bug #86807	mysqldump export empty mysql.proc
Submitted:	23 Jun 2017 3:07	Modified:	30 Jun 2017 10:59
Reporter:	镇熙林	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S1 (Critical)
Version:	5.7.16	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	empty, mysql.proc

Description:
I want to make a copy of MySQL instance, so dump with script below: 
  mysqldump -uroot -p -h 127.0.0.1 -P  --flush-privileges \
 --routines \
 --create-options \
 --triggers \
 --add-drop-table \
 --add-drop-trigger \
 --add-drop-database \
 --default-character-set=utf8 \
 --single-transaction \
 --all-databases \
 --set-gtid-purged=ON >dump.sql 2>/dev/null

When I view the dump.sql, I see the script below, NO data in mysql.proc is exported!!
        DROP TABLE IF EXISTS `proc`;
        CREATE TABLE `proc` (
          ...
        )...
        LOCK TABLES `proc` WRITE;
        ...
        UNLOCK TABLES;
After I import the data to another instance
        mysql -uroot -p -h127.0.0.1 -P3306 --default-character-set=utf8 <dump.sql  2>/dev/null
I can see mysql.proc is empty.
If I run 'select * from sys.processlist', I get the error below:
        ERROR 1356 (HY000): View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
To resolve the problem , I have to run 'mysql_upgrade' which will fill in mysql.proc with record of sys schema, and everything get fine!
My problem is: Why mysqldump didn't export the data of mysql.proc?

How to repeat:
see description

Hi,

What warnings did mysqldump shown you when you tried to execute that dump?

If you remove the redirection for stderr you will see them.

all best
Bogdan Kecman

Warning message is:
mysqldump: [Warning] Using a password on the command line interface can be insecure.

Hi,
You were not adding --events so you should get a warning... 

[root@localhost ~]# /usr/local/mysql/bin/mysqldump -uroot --flush-privileges --routines  --create-options  --triggers  --add-drop-table  --add-drop-trigger  --add-drop-database  --default-character-set=utf8  --single-transaction  --all-databases  --set-gtid-purged=ON >dump.sql
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.
[root@localhost ~]#

Anyhow, you are right, this is a bug, when gitd is on mysqldump is not dumping proc table (--events or no --events does not matter)

Thanks for your submission!

all best
Bogdan

是大V