MySQL Bugs: #41578: Drop column/table with grants followed by restore fails.

Bug #41578	Drop column/table with grants followed by restore fails.
Submitted:	18 Dec 2008 4:16	Modified:	30 Mar 2009 2:02
Reporter:	Hema Sridharan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S2 (Serious)
Version:	mysql-6.0-backup	OS:	Linux
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
1) Create user tom@'%'; 
2) Create database db1 and tables(t1 and t2) in db1.
3) Assign column level(or table level) grants to user on tables db1.t1 and db1.t2.
4) Perform backup database operation(db1.bak).
5) Drop a column from table t1 to which a grant is assigned. 
6) Perform backup database operation(db1a.bak)
7) Drop the database and perform restore. Restore will fail because of dropped column in table t1.

I can backup grants even without the specific columns/table, to which
the grants were created. If backup is possible, restore should also be possible.
Backup successful and restore failure is baffling. 

Create database db1;
use db1;
create user tom@'%';
Create table t1(id INT, a CHAR(20));
Create table t2(no INT, b CHAR(20));
Grant select(id), update(a) on db1.t1 to tom@'%';
Grant insert on db1.t2 to tom@'%';
show grants for tom;
backup database db1 to 'db1.bak';
alter table db1.t1 drop column a;
backup database db1 to 'db1a.bak';
show grants for tom;
drop database db1;
restore from 'db1a.bak';

 

How to repeat:
mysql> Create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> use db1;
Database changed
mysql> create user tom@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> Create table t1(id INT, a CHAR(20));
Query OK, 0 rows affected (0.00 sec)
mysql> Create table t2(no INT, b CHAR(20));
Query OK, 0 rows affected (0.00 sec)
mysql> Grant select(id), update(a) on db1.t1 to tom@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> Grant insert on db1.t2 to tom@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for tom;
+----------------------------------------------------------+
| Grants for tom@%                                         |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'%'                          |
| GRANT INSERT ON `db1`.`t2` TO 'tom'@'%'                  |
| GRANT SELECT (id), UPDATE (a) ON `db1`.`t1` TO 'tom'@'%' |
+----------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> backup database db1 to 'db1.bak';
+-----------+
| backup_id |
+-----------+
| 270       |
+-----------+
1 row in set (0.18 sec)

mysql> alter table db1.t1 drop column a;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> backup database db1 to 'db1a.bak';
+-----------+
| backup_id |
+-----------+
| 271       |
+-----------+
1 row in set (0.05 sec)

mysql> show grants for tom;
+----------------------------------------------------------+
| Grants for tom@%                                         |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'%'                          |
| GRANT INSERT ON `db1`.`t2` TO 'tom'@'%'                  |
| GRANT SELECT (id), UPDATE (a) ON `db1`.`t1` TO 'tom'@'%' |
+----------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> drop database db1;
Query OK, 2 rows affected (0.00 sec)

mysql> restore from 'db1a.bak';
ERROR 1054 (42S22): Unknown column 'a' in 't1'

Suggested fix:
Either there should be a warning or error message during backup, specifying the non-existence of columns/table with grants or the restore should complete successfully.

Our initial idea was to set lex->sql_command= SQLCOM_REVOKE before adding the privilege. However, this does not work in the context of restore. The meta data statements are fed through the si_objects interface to Ed_connection::execute_direct(). It takes a string only, parses and executes it. During parsing lex->sql_command is set to SQLCOM_GRANT. We cannot override it from the restore code.

An alternate attempt, to check thd->DDL_exception in mysql_execute_command(), transported the knowledge about an ongoing restore to that place. The error went away, but then it turned out that restore didn't create any grants any more.

The final solution seems to be to check thd->DDL_exception deep down in the ACL code, where it tries to reject adding grants due to missing objects.

The downside is that thd->DDL_exception is marked as being preliminary. One day it may go away. A new flag could then be used. If somebody knows of another existing condition to use, please speak up.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/63825

2755 Ingo Struewing	2009-01-22
      Bug#41578 - Drop column/table with grants followed by restore fails.
      
      When a database object like a table or column is dropped,
      privileges for these objects are not dropped.
      
      BACKUP includes all privileges that belong to the saved databases.
      So it includes privileges for objects that do not exist.
      
      On RESTORE the saved objects are re-created, followed by the
      privileges. RESTORE failed when trying to grant a privilege
      for a non-existent object.
      
      To be able to restore the same objects and privileges as they existed
      at backup time, we do now omit checks for object existence when
      granting privileges during RESTORE.

Good to push.

Waiting for a design decision. Please see email thread.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67412

2781 Ingo Struewing	2009-02-24
      Bug#41578 - Drop column/table with grants followed by restore fails.
      
      When a database object like a table or column is dropped,
      privileges for these objects are not dropped.
      
      BACKUP includes all privileges that belong to the saved databases.
      So it includes privileges for objects that do not exist.
      
      On RESTORE the saved objects are re-created, followed by the
      privileges. RESTORE failed when trying to grant a privilege
      for a non-existent object.
      
      To be able to restore the same objects and privileges as they existed
      at backup time, we do now omit checks for object existence when
      granting privileges during RESTORE.
     @ mysql-test/suite/backup/r/backup_db_grants_extra.result
        Bug#41578 - Drop column/table with grants followed by restore fails.
        Fixed test result.
     @ mysql-test/suite/backup/r/backup_table_grants.result
        Bug#41578 - Drop column/table with grants followed by restore fails.
        New test result.
     @ mysql-test/suite/backup/t/backup_db_grants_extra.test
        Bug#41578 - Drop column/table with grants followed by restore fails.
        Fixed test case. RESTORE does no longer fail at some places.
     @ mysql-test/suite/backup/t/backup_table_grants.test
        Bug#41578 - Drop column/table with grants followed by restore fails.
        New test case.
     @ sql/sql_acl.cc
        Bug#41578 - Drop column/table with grants followed by restore fails.
        Omit to check existence of tables and columns during restore.
     @ sql/sql_class.cc
        Bug#41578 - Drop column/table with grants followed by restore fails.
        Initialize new THD memeber in constructor.
     @ sql/sql_class.h
        Bug#41578 - Drop column/table with grants followed by restore fails.
        Added new THD member 'backup_in_progress'.
     @ sql/sql_parse.cc
        Bug#41578 - Drop column/table with grants followed by restore fails.
        Set thd->backup_in_progress to SQLCOM_BACKUP or SQLCOM_RESTORE.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67536

2781 Ingo Struewing	2009-02-25
      Bug#41578 - Drop column/table with grants followed by restore fails.
      
      When a database object like a table or column is dropped,
      privileges for these objects are not dropped.
      
      BACKUP includes all privileges that belong to the saved databases.
      So it includes privileges for objects that do not exist.
      
      On RESTORE the saved objects are re-created, followed by the
      privileges. RESTORE failed when trying to grant a privilege
      for a non-existent object.
      
      To be able to restore the same objects and privileges as they existed
      at backup time, we do now omit checks for object existence when
      granting privileges during RESTORE.
     @ mysql-test/suite/backup/r/backup_db_grants_extra.result
        Bug#41578 - Drop column/table with grants followed by restore fails.
        Fixed test result.
     @ mysql-test/suite/backup/r/backup_table_grants.result
        Bug#41578 - Drop column/table with grants followed by restore fails.
        New test result.
     @ mysql-test/suite/backup/t/backup_db_grants_extra.test
        Bug#41578 - Drop column/table with grants followed by restore fails.
        Fixed test case. RESTORE does no longer fail at some places.
     @ mysql-test/suite/backup/t/backup_table_grants.test
        Bug#41578 - Drop column/table with grants followed by restore fails.
        New test case.
     @ sql/si_objects.cc
        Bug#41578 - Drop column/table with grants followed by restore fails.
        Set SQL-mode NO_AUTO_CREATE_USER when running sub-statements from
        backup or restore.
     @ sql/sql_acl.cc
        Bug#41578 - Drop column/table with grants followed by restore fails.
        Omit to check existence of tables and columns during restore.
     @ sql/sql_class.cc
        Bug#41578 - Drop column/table with grants followed by restore fails.
        Initialize new THD memeber in constructor.
     @ sql/sql_class.h
        Bug#41578 - Drop column/table with grants followed by restore fails.
        Added new THD member 'backup_in_progress'.
     @ sql/sql_parse.cc
        Bug#41578 - Drop column/table with grants followed by restore fails.
        Set thd->backup_in_progress to SQLCOM_BACKUP or SQLCOM_RESTORE.

Approved, but asked to modify test so that numeric values of warnings do not show in result file.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67685

2781 Ingo Struewing	2009-02-26
      Bug#41578 - Drop column/table with grants followed by restore fails.
      
      When a database object like a table or column is dropped,
      privileges for these objects are not dropped.
      
      BACKUP includes all privileges that belong to the saved databases.
      So it includes privileges for objects that do not exist.
      
      On RESTORE the saved objects are re-created, followed by the
      privileges. RESTORE failed when trying to grant a privilege
      for a non-existent object.
      
      To be able to restore the same objects and privileges as they existed
      at backup time, we do now omit checks for object existence when
      granting privileges during RESTORE.
     @ mysql-test/suite/backup/r/backup_db_grants_extra.result
        Bug#41578 - Drop column/table with grants followed by restore fails.
        Fixed test result.
     @ mysql-test/suite/backup/r/backup_table_grants.result
        Bug#41578 - Drop column/table with grants followed by restore fails.
        New test result.
     @ mysql-test/suite/backup/t/backup_db_grants_extra.test
        Bug#41578 - Drop column/table with grants followed by restore fails.
        Fixed test case. RESTORE does no longer fail at some places.
     @ mysql-test/suite/backup/t/backup_table_grants.test
        Bug#41578 - Drop column/table with grants followed by restore fails.
        New test case.
     @ sql/si_objects.cc
        Bug#41578 - Drop column/table with grants followed by restore fails.
        Set SQL-mode NO_AUTO_CREATE_USER when running sub-statements from
        backup or restore.
     @ sql/sql_acl.cc
        Bug#41578 - Drop column/table with grants followed by restore fails.
        Omit to check existence of tables and columns during restore.
     @ sql/sql_class.cc
        Bug#41578 - Drop column/table with grants followed by restore fails.
        Initialize new THD memeber in constructor.
     @ sql/sql_class.h
        Bug#41578 - Drop column/table with grants followed by restore fails.
        Added new THD member 'backup_in_progress'.
     @ sql/sql_parse.cc
        Bug#41578 - Drop column/table with grants followed by restore fails.
        Set thd->backup_in_progress to SQLCOM_BACKUP or SQLCOM_RESTORE.

Queued to mysql-6.0-backup.

Pushed into 6.0.11-alpha (revid:alik@sun.com-20090326121822-pt84kzxxayzho4mn) (version source revid:rafal.somla@sun.com-20090302164601-znhm4tadplfi2iqu) (merge vers: 6.0.11-alpha) (pib:6)

Noted in 6.0.11 changelog.

RESTORE failed if it tried to restore a privilege for a non-existent
object.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/96429

3046 Chuck Bell	2010-01-08
      Bug#41578 - Drop column/table with grants followed by restore fails.
      
      When a database object like a table or column is dropped,
      privileges for these objects are not dropped.
      
      BACKUP includes all privileges that belong to the saved databases.
      So it includes privileges for objects that do not exist.
      
      On RESTORE the saved objects are re-created, followed by the
      privileges. RESTORE failed when trying to grant a privilege
      for a non-existent object.
      
      To be able to restore the same objects and privileges as they existed
      at backup time, we do now omit checks for object existence when
      granting privileges during RESTORE.
      
      original changeset: 2761.6.1 (mysql-6.0-backup)
     @ sql/sql_acl.cc
        Missing code port.