Description:
MySQL procs_priv table is not correctly restored or its content shown and thus user privileges are missing after restore.
How to repeat:
Reproducible test case:
* fresh MySQL intallation
mysql_install_db --datadir=/home/mysql/data/mysqld1 --user=mysql --basedir=/home/mysql/product/mysql-5.6.23
* sql_mode = TRADITIONAL,ONLY_FULL_GROUP_BY
* start
mysql> show global variables like 'sql_mode';
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
create user exec@localhost identified by 'exec';
delimiter //
CREATE PROCEDURE test.dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;
//
delimiter ;
grant execute on procedure test.dorepeat to exec@'localhost';
select * from mysql.procs_priv;
+-----------+------+------+--------------+--------------+----------------+-----------+---------------------+
| Host | Db | User | Routine_name | Routine_type | Grantor | Proc_priv | Timestamp |
+-----------+------+------+--------------+--------------+----------------+-----------+---------------------+
| localhost | test | exec | dorepeat | PROCEDURE | root@localhost | Execute | 0000-00-00 00:00:00 |
+-----------+------+------+--------------+--------------+----------------+-----------+---------------------+
--> Timestamp IMHO is wrong. Should be actual Timestamp!
CREATE TABLE `procs_priv` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Routine_name` char(64) CHARACTER SET utf8 NOT NULL DEFAULT '',
`Routine_type` enum('FUNCTION','PROCEDURE') COLLATE utf8_bin NOT NULL,
`Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '',
`Proc_priv` set('Execute','Alter Routine','Grant') CHARACTER SET utf8 NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`Host`,`Db`,`User`,`Routine_name`,`Routine_type`),
KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Procedure privileges'
* Then do dump:
mysqldump \
--user=root \
--all-databases \
--flush-privileges \
--single-transaction \
--master-data=2 \
--flush-logs \
--triggers \
--routines \
--events \
--hex-blob > /tmp/full_backup.sql
* Fresh installation:
mysql_install_db --datadir=/home/mysql/data/mysqld1 --user=mysql --basedir=/home/mysql/product/mysql-5.6.23
select * from mysql.procs_priv;
Empty set
-rw-rw---- 1 mysql mysql 8875 Jun 22 16:43 procs_priv.frm
-rw-rw---- 1 mysql mysql 0 Jun 22 16:43 procs_priv.MYD
-rw-rw---- 1 mysql mysql 4096 Jun 22 16:43 procs_priv.MYI
* Do restore
mysql -u root mysql < /tmp/full_backup.sql
echo $?
0
select * from mysql.procs_priv;
Empty set
-rw-rw---- 1 mysql mysql 8875 Jun 22 16:39 procs_priv.frm
-rw-rw---- 1 mysql mysql 850 Jun 22 16:39 procs_priv.MYD
-rw-rw---- 1 mysql mysql 8192 Jun 22 16:40 procs_priv.MYI
Data are there but not visible...
Suggested fix:
1. Procs_priv table should reflect timestamp correctly.
2. restore should complain an fail
3. privileges should be visible at all.
Description: MySQL procs_priv table is not correctly restored or its content shown and thus user privileges are missing after restore. How to repeat: Reproducible test case: * fresh MySQL intallation mysql_install_db --datadir=/home/mysql/data/mysqld1 --user=mysql --basedir=/home/mysql/product/mysql-5.6.23 * sql_mode = TRADITIONAL,ONLY_FULL_GROUP_BY * start mysql> show global variables like 'sql_mode'; sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION create user exec@localhost identified by 'exec'; delimiter // CREATE PROCEDURE test.dorepeat(p1 INT) BEGIN SET @x = 0; REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; END; // delimiter ; grant execute on procedure test.dorepeat to exec@'localhost'; select * from mysql.procs_priv; +-----------+------+------+--------------+--------------+----------------+-----------+---------------------+ | Host | Db | User | Routine_name | Routine_type | Grantor | Proc_priv | Timestamp | +-----------+------+------+--------------+--------------+----------------+-----------+---------------------+ | localhost | test | exec | dorepeat | PROCEDURE | root@localhost | Execute | 0000-00-00 00:00:00 | +-----------+------+------+--------------+--------------+----------------+-----------+---------------------+ --> Timestamp IMHO is wrong. Should be actual Timestamp! CREATE TABLE `procs_priv` ( `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '', `Routine_name` char(64) CHARACTER SET utf8 NOT NULL DEFAULT '', `Routine_type` enum('FUNCTION','PROCEDURE') COLLATE utf8_bin NOT NULL, `Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '', `Proc_priv` set('Execute','Alter Routine','Grant') CHARACTER SET utf8 NOT NULL DEFAULT '', `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`Host`,`Db`,`User`,`Routine_name`,`Routine_type`), KEY `Grantor` (`Grantor`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Procedure privileges' * Then do dump: mysqldump \ --user=root \ --all-databases \ --flush-privileges \ --single-transaction \ --master-data=2 \ --flush-logs \ --triggers \ --routines \ --events \ --hex-blob > /tmp/full_backup.sql * Fresh installation: mysql_install_db --datadir=/home/mysql/data/mysqld1 --user=mysql --basedir=/home/mysql/product/mysql-5.6.23 select * from mysql.procs_priv; Empty set -rw-rw---- 1 mysql mysql 8875 Jun 22 16:43 procs_priv.frm -rw-rw---- 1 mysql mysql 0 Jun 22 16:43 procs_priv.MYD -rw-rw---- 1 mysql mysql 4096 Jun 22 16:43 procs_priv.MYI * Do restore mysql -u root mysql < /tmp/full_backup.sql echo $? 0 select * from mysql.procs_priv; Empty set -rw-rw---- 1 mysql mysql 8875 Jun 22 16:39 procs_priv.frm -rw-rw---- 1 mysql mysql 850 Jun 22 16:39 procs_priv.MYD -rw-rw---- 1 mysql mysql 8192 Jun 22 16:40 procs_priv.MYI Data are there but not visible... Suggested fix: 1. Procs_priv table should reflect timestamp correctly. 2. restore should complain an fail 3. privileges should be visible at all.