#At file:///home2/mydev/bzrroot/mysql-6.0-bug41578-3/ based on revid:rafal.somla@stripped
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.
added:
mysql-test/suite/backup/r/backup_table_grants.result
mysql-test/suite/backup/t/backup_table_grants.test
modified:
mysql-test/suite/backup/r/backup_db_grants_extra.result
mysql-test/suite/backup/t/backup_db_grants_extra.test
sql/sql_acl.cc
sql/sql_class.cc
sql/sql_class.h
sql/sql_parse.cc
=== modified file 'mysql-test/suite/backup/r/backup_db_grants_extra.result'
--- a/mysql-test/suite/backup/r/backup_db_grants_extra.result 2009-02-20 16:28:59 +0000
+++ b/mysql-test/suite/backup/r/backup_db_grants_extra.result 2009-02-24 19:45:05 +0000
@@ -465,14 +465,11 @@ backup_id
drop database and perform restore
DROP DATABASE db1;
DROP DATABASE db2;
-Restore will fail because of bug#41578
RESTORE FROM 'db12b.bak';
-ERROR HY000: Could not execute grant '`db1`.`<empty> #`'.
+backup_id
+#
SHOW WARNINGS;
Level Code Message
-Error # Unknown column 'b' in 't1'
-Error # Could not execute grant '`db1`.`<empty> #`'.
-Warning # Operation aborted - data might be corrupted
RESTORE FROM 'db12a.bak' OVERWRITE;
backup_id
#
@@ -518,14 +515,11 @@ backup_id
drop database and perform restore
DROP DATABASE db1;
DROP DATABASE db2;
-Restore will fail because of bug#41578
RESTORE FROM 'db12c.bak';
-ERROR HY000: Could not execute grant '`db2`.`<empty> #`'.
+backup_id
+#
SHOW WARNINGS;
Level Code Message
-Error # Table 'db2.t2' doesn't exist
-Error # Could not execute grant '`db2`.`<empty> #`'.
-Warning # Operation aborted - data might be corrupted
RESTORE FROM 'db12a.bak' OVERWRITE;
backup_id
#
@@ -607,12 +601,10 @@ drop database and perform restore
DROP DATABASE db1;
DROP DATABASE db2;
RESTORE FROM 'db12b.bak';
-ERROR HY000: Could not execute grant '`db2`.`<empty> #`'.
+backup_id
+#
SHOW WARNINGS;
Level Code Message
-Error # Table 'db2.t1' doesn't exist
-Error # Could not execute grant '`db2`.`<empty> #`'.
-Warning # Operation aborted - data might be corrupted
RESTORE FROM 'db12a.bak' OVERWRITE;
backup_id
#
@@ -663,12 +655,10 @@ drop database and perform restore
DROP DATABASE db1;
DROP DATABASE db2;
RESTORE FROM 'db12c.bak';
-ERROR HY000: Could not execute grant '`db1`.`<empty> #`'.
+backup_id
+#
SHOW WARNINGS;
Level Code Message
-Error # Unknown column 'a1' in 't2'
-Error # Could not execute grant '`db1`.`<empty> #`'.
-Warning # Operation aborted - data might be corrupted
RESTORE FROM 'db12a.bak' OVERWRITE;
backup_id
#
=== added file 'mysql-test/suite/backup/r/backup_table_grants.result'
--- a/mysql-test/suite/backup/r/backup_table_grants.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/r/backup_table_grants.result 2009-02-24 19:45:05 +0000
@@ -0,0 +1,63 @@
+DELETE FROM mysql.user WHERE user='mysqluser1';
+DELETE FROM mysql.db WHERE user="mysqluser1";
+DELETE FROM mysql.tables_priv WHERE user="mysqluser1";
+DELETE FROM mysql.columns_priv WHERE user="mysqluser1";
+FLUSH PRIVILEGES;
+DROP DATABASE IF EXISTS mysqltest1;
+#
+# Bug#41578 - Drop column/table with grants followed by restore fails.
+#
+# Create database and tables
+CREATE DATABASE mysqltest1;
+USE mysqltest1;
+CREATE TABLE t1(col1 INT, col2 CHAR(20));
+CREATE TABLE t2(col1 INT, col2 CHAR(20));
+#
+# Create user and privileges
+CREATE USER 'mysqluser1'@'%';
+GRANT SELECT(col1), UPDATE(col2) ON mysqltest1.t1 TO 'mysqluser1'@'%';
+GRANT INSERT ON mysqltest1.t2 TO 'mysqluser1'@'%';
+SHOW GRANTS FOR 'mysqluser1';
+Grants for mysqluser1@%
+GRANT USAGE ON *.* TO 'mysqluser1'@'%'
+GRANT INSERT ON `mysqltest1`.`t2` TO 'mysqluser1'@'%'
+GRANT SELECT (col1), UPDATE (col2) ON `mysqltest1`.`t1` TO 'mysqluser1'@'%'
+#
+# Drop objects
+ALTER TABLE mysqltest1.t1 DROP COLUMN col2;
+DROP TABLE t2;
+SHOW GRANTS FOR 'mysqluser1';
+Grants for mysqluser1@%
+GRANT USAGE ON *.* TO 'mysqluser1'@'%'
+GRANT INSERT ON `mysqltest1`.`t2` TO 'mysqluser1'@'%'
+GRANT SELECT (col1), UPDATE (col2) ON `mysqltest1`.`t1` TO 'mysqluser1'@'%'
+#
+# Backup
+BACKUP DATABASE mysqltest1 TO 'mysqltest1.bak';
+backup_id
+#
+# Remove database including tables and privileges for restore
+DROP DATABASE mysqltest1;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'mysqluser1'@'%';
+SHOW GRANTS FOR 'mysqluser1';
+Grants for mysqluser1@%
+GRANT USAGE ON *.* TO 'mysqluser1'@'%'
+#
+# Restore
+RESTORE FROM 'mysqltest1.bak';
+backup_id
+#
+SHOW GRANTS FOR 'mysqluser1';
+Grants for mysqluser1@%
+GRANT USAGE ON *.* TO 'mysqluser1'@'%'
+GRANT SELECT (col1), UPDATE (col2) ON `mysqltest1`.`t1` TO 'mysqluser1'@'%'
+GRANT INSERT ON `mysqltest1`.`t2` TO 'mysqluser1'@'%'
+SELECT * FROM t1;
+col1
+SELECT * FROM t2;
+ERROR 42S02: Table 'mysqltest1.t2' doesn't exist
+#
+# Cleanup
+USE test;
+DROP DATABASE mysqltest1;
+DROP USER 'mysqluser1'@'%';
=== modified file 'mysql-test/suite/backup/t/backup_db_grants_extra.test'
--- a/mysql-test/suite/backup/t/backup_db_grants_extra.test 2009-02-06 08:28:24 +0000
+++ b/mysql-test/suite/backup/t/backup_db_grants_extra.test 2009-02-24 19:45:05 +0000
@@ -401,10 +401,8 @@ BACKUP DATABASE db1, db2 TO 'db12b.bak';
DROP DATABASE db1;
DROP DATABASE db2;
---echo Restore will fail because of bug#41578
--replace_column 1 #
--replace_regex /<empty> [00-99]+/<empty> #/
---error ER_BACKUP_CANT_RESTORE_PRIV
RESTORE FROM 'db12b.bak';
--replace_column 2 #
--replace_regex /<empty> [00-99]+/<empty> #/
@@ -441,10 +439,8 @@ BACKUP DATABASE db1, db2 TO 'db12c.bak';
DROP DATABASE db1;
DROP DATABASE db2;
---echo Restore will fail because of bug#41578
--replace_column 1 #
--replace_regex /<empty> [00-99]+/<empty> #/
---error ER_BACKUP_CANT_RESTORE_PRIV
RESTORE FROM 'db12c.bak';
--replace_column 2 #
--replace_regex /<empty> [00-99]+/<empty> #/
@@ -500,7 +496,6 @@ DROP DATABASE db2;
--replace_column 1 #
--replace_regex /<empty> [00-99]+/<empty> #/
---error ER_BACKUP_CANT_RESTORE_PRIV
RESTORE FROM 'db12b.bak';
--replace_column 2 #
--replace_regex /<empty> [00-99]+/<empty> #/
@@ -540,7 +535,6 @@ DROP DATABASE db2;
--replace_column 1 #
--replace_regex /<empty> [00-99]+/<empty> #/
---error ER_BACKUP_CANT_RESTORE_PRIV
RESTORE FROM 'db12c.bak';
--replace_column 2 #
--replace_regex /<empty> [00-99]+/<empty> #/
=== added file 'mysql-test/suite/backup/t/backup_table_grants.test'
--- a/mysql-test/suite/backup/t/backup_table_grants.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_table_grants.test 2009-02-24 19:45:05 +0000
@@ -0,0 +1,66 @@
+#
+# Test the backup of table grants
+#
+
+--source include/not_embedded.inc
+
+# Preparatory cleanup
+--disable_warnings
+# Unfortunately we do not have a DROP USER IF EXISTS statement.
+DELETE FROM mysql.user WHERE user='mysqluser1';
+DELETE FROM mysql.db WHERE user="mysqluser1";
+DELETE FROM mysql.tables_priv WHERE user="mysqluser1";
+DELETE FROM mysql.columns_priv WHERE user="mysqluser1";
+FLUSH PRIVILEGES;
+#
+DROP DATABASE IF EXISTS mysqltest1;
+--enable_warnings
+
+--echo #
+--echo # Bug#41578 - Drop column/table with grants followed by restore fails.
+--echo #
+#
+--echo # Create database and tables
+CREATE DATABASE mysqltest1;
+USE mysqltest1;
+CREATE TABLE t1(col1 INT, col2 CHAR(20));
+CREATE TABLE t2(col1 INT, col2 CHAR(20));
+#
+--echo #
+--echo # Create user and privileges
+CREATE USER 'mysqluser1'@'%';
+GRANT SELECT(col1), UPDATE(col2) ON mysqltest1.t1 TO 'mysqluser1'@'%';
+GRANT INSERT ON mysqltest1.t2 TO 'mysqluser1'@'%';
+SHOW GRANTS FOR 'mysqluser1';
+#
+--echo #
+--echo # Drop objects
+ALTER TABLE mysqltest1.t1 DROP COLUMN col2;
+DROP TABLE t2;
+SHOW GRANTS FOR 'mysqluser1';
+#
+--echo #
+--echo # Backup
+--replace_column 1 #
+BACKUP DATABASE mysqltest1 TO 'mysqltest1.bak';
+#
+--echo # Remove database including tables and privileges for restore
+DROP DATABASE mysqltest1;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'mysqluser1'@'%';
+SHOW GRANTS FOR 'mysqluser1';
+#
+--echo #
+--echo # Restore
+--replace_column 1 #
+RESTORE FROM 'mysqltest1.bak';
+SHOW GRANTS FOR 'mysqluser1';
+SELECT * FROM t1;
+--error ER_NO_SUCH_TABLE
+SELECT * FROM t2;
+#
+--echo #
+--echo # Cleanup
+USE test;
+DROP DATABASE mysqltest1;
+DROP USER 'mysqluser1'@'%';
+
=== modified file 'sql/sql_acl.cc'
--- a/sql/sql_acl.cc 2009-02-18 10:23:38 +0000
+++ b/sql/sql_acl.cc 2009-02-24 19:45:05 +0000
@@ -2972,7 +2972,12 @@ int mysql_table_grant(THD *thd, TABLE_LI
column->column.ptr(), NULL, NULL,
NULL, TRUE, FALSE,
&unused_field_idx, FALSE, &dummy);
- if (f == (Field*)0)
+ /*
+ During RESTORE, we want to restore all privileges that existed
+ at backup time. This includes privileges for non-existing
+ colums.
+ */
+ if ((f == (Field*)0) && (thd->backup_in_progress != SQLCOM_RESTORE))
{
my_error(ER_BAD_FIELD_ERROR, MYF(0),
column->column.c_ptr(), table_list->alias);
@@ -2984,7 +2989,12 @@ int mysql_table_grant(THD *thd, TABLE_LI
}
close_thread_tables(thd);
}
- else
+ /*
+ During RESTORE, we want to restore all privileges that existed
+ at backup time. This includes privileges for non-existing
+ tables.
+ */
+ else if ((thd->backup_in_progress != SQLCOM_RESTORE))
{
if (!(rights & CREATE_ACL))
{
=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc 2009-02-20 16:40:19 +0000
+++ b/sql/sql_class.cc 2009-02-24 19:45:05 +0000
@@ -422,6 +422,7 @@ THD::THD()
*/
BML_exception(FALSE),
backup_wait_timeout(BACKUP_WAIT_TIMEOUT_DEFAULT),
+ backup_in_progress(0),
#if defined(ENABLED_DEBUG_SYNC)
debug_sync_control(0),
#endif /* defined(ENABLED_DEBUG_SYNC) */
=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h 2009-02-20 16:40:19 +0000
+++ b/sql/sql_class.h 2009-02-24 19:45:05 +0000
@@ -1803,6 +1803,12 @@ public:
*/
my_bool BML_exception; // Allow some DDL if there is an exception
ulong backup_wait_timeout;
+ /*
+ If online backup/restore in progress, set to SQLCOM_BACKUP or
+ SQLCOM_RESTORE, otherwise 0. This modifies the behavior of some
+ statements, e.g. GRANT.
+ */
+ int backup_in_progress;
Locked_tables_list locked_tables_list;
=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc 2009-02-20 16:40:19 +0000
+++ b/sql/sql_parse.cc 2009-02-24 19:45:05 +0000
@@ -2382,7 +2382,15 @@ mysql_execute_command(THD *thd)
goto error;
#else
{
- /*
+ /*
+ Stack the 'backup_in_progress' variable. If this is run inside
+ backup/restore, it will probably fail. But then we need to
+ return to the former value.
+ */
+ int saved_backup_in_progress= thd->backup_in_progress;
+ thd->backup_in_progress= lex->sql_command;
+
+ /*
Reset warnings for BACKUP and RESTORE commands. Note: this will
cause problems if BACKUP/RESTORE is allowed inside stored
routines and events. In that case, warnings should not be
@@ -2438,9 +2446,11 @@ mysql_execute_command(THD *thd)
/*
Note: execute_backup_command() sends a correct response to the client
(either ok, result set or error message).
- */
- if (execute_backup_command(thd, lex, &backupdir, overwrite_restore,
- skip_gap_event))
+ */
+ res= execute_backup_command(thd, lex, &backupdir, overwrite_restore,
+ skip_gap_event);
+ thd->backup_in_progress= saved_backup_in_progress;
+ if (res)
goto error;
break;
}
Attachment: [text/bzr-bundle] bzr/ingo.struewing@sun.com-20090224194505-2qlkkk31gnh0jab4.bundle
