Bug #77436 procs_priv table not restored with sql_mode traditional and only_full_group_by
Submitted: 22 Jun 2015 14:55 Modified: 23 Jun 2015 10:42
Reporter: Oli Sennhauser Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Options Severity:S2 (Serious)
Version:5.6.23, 5.6.26 OS:Linux
Assigned to: CPU Architecture:Any
Tags: mysqldump, ONLY_FULL_GROUP_BY, procs_priv, restore, SQL_MODE, traditional

[22 Jun 2015 14:55] Oli Sennhauser
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.
[23 Jun 2015 10:42] Umesh Shastry
Hello Oli Sennhauser,

Thank you for the report and repeatable steps.
Observed this with 5.6.26.

Thanks,
Umesh