MySQL Bugs: #42756: Backup: "backup database *" then restore does not preserve mtr state

Bug #42756	Backup: "backup database *" then restore does not preserve mtr state
Submitted:	11 Feb 2009 11:11	Modified:	26 Oct 2009 1:01
Reporter:	Jørgen Løland	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	Ingo Strüwing	CPU Architecture:	Any
Tags:	disabled

Description:
RESTORE... OVERWRITE is not able to restore all privileges of a backup image created for all databases. 

The reason is that there are a number of privileges on the test database given to user ''@'%'. This user does not exist, and restore will therefore not create them. This causes a state change mysql.proc, which makes mtr fail.

How to repeat:
Run this with mtr 2:
*******************
--source include/not_embedded.inc

let $MYSQLD_DATADIR= `select @@datadir`;

CREATE DATABASE mydb;

--replace_column 1 #
BACKUP DATABASE * TO 'all.bak';

--replace_column 1 #
RESTORE FROM 'all.bak' OVERWRITE;
*******************

Result:
*******
MTR's internal check of the test case 'main.bup_all' failed.
This means that the test case does not preserve the state that existed
before the test case was executed.  Most likely the test case did not
do a proper clean-up.
This is the diff of the states of the servers before and after the
test case was executed:
--- /mysql-6.0-backup-reenable/mysql-test/var/tmp/check-mysqld_1.result	2009-02-11 14:06:00.000000000 +0300
+++ /mysql-6.0-backup-reenable/mysql-test/var/tmp/check-mysqld_1.reject	2009-02-11 14:06:02.000000000 +0300
@@ -578,7 +578,7 @@
 mysql.help_keyword	0
 mysql.help_relation	0
 mysql.host	0
-mysql.proc	271022891
+mysql.proc	2379292599
 mysql.procs_priv	0
 mysql.tables_priv	0
 mysql.time_zone	2420313365

mysqltest: Result length mismatch

not ok

Suggested fix:
Either:
 * Fix Bug#41578: Drop column/table with grants followed by restore fails, or
 * Remove privileges that have no users

This is related to the test database, not "backup database *". This simpler script also reproduces the problem:

--------8<-----------
BACKUP DATABASE test TO 'test.bak';
backup_id
#
RESTORE FROM 'test.bak' OVERWRITE;
backup_id
#
Warnings:
#	1739	The grant 'ALTER ON test.*' for the user ''@'%' was skipped because the user does not exist.
#	1739	The grant 'CREATE ON test.*' for the user ''@'%' was skipped because the user does not exist.
#	1739	The grant 'CREATE ROUTINE ON test.*' for the user ''@'%' was skipped because the user does not exist.
(many more like this)
-------->8-----------

Thank  you for the report.

Verified as described.

May be fixed or simplified by BUG#41578.

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/68559

2791 Ingo Struewing	2009-03-06
      Bug#42756 - Backup: "backup database *" then restore does not preserve mtr state
      
      The problem was that BACKUP included the default privileges for the
      test database, which RESTORE could not re-create.
      
      The default privileges are created during database install.
      They refer to the non-existent special user ''@'%'.
      They cannot be influenced by GRANT or REVOKE statements,
      neither by DROP DATABASE 'test'.
      So BACKUP/RESTORE don't need to deal with them.
      
      Changed BACKUP so that it does not include these privileges in the backup image.
      
      Split backup_sync.test from backup.test.
     @ mysql-test/suite/backup/r/backup.result
        Bug#42756 - Backup: "backup database *" then restore does not preserve mtr state
        Updated test result.
     @ mysql-test/suite/backup/r/backup_sync.result
        Bug#42756 - Backup: "backup database *" then restore does not preserve mtr state
        New test result.
     @ mysql-test/suite/backup/t/backup.test
        Bug#42756 - Backup: "backup database *" then restore does not preserve mtr state
        Moved the test that uses DEBUG_SYNC to backup_sync.test.
        Restructured cleanup sequences.
        Added new test.
     @ mysql-test/suite/backup/t/backup_sync.test
        Bug#42756 - Backup: "backup database *" then restore does not preserve mtr state
        New test case for tests that need DEBUG_SYNC. Contents comes from backup.test.
     @ sql/si_objects.cc
        Bug#42756 - Backup: "backup database *" then restore does not preserve mtr state
        Fixed a compiler warning.
        Excluded default privileges for the test database (user ''@'%').

Patch approved.

Review approval removed pending resolution of reviewer 1's comments.

It turned out that my solution was wrong. It is possible to grant privileges to ''@'%'. So it is wrong to suppress such privileges during backup.

A different approach looks so that we grant another exception in the grant routines. We do already have the exception that we do not check for existing objects during restore. Now we won't check for existing users either. The restore kernel does this check anyway. Si_objects can be changed so that it pretends the user ''@'%' does always exist. These changes fixed the test case from the bug report. But there are a lot more of checks that may need to be overridden during restore. This requires a lot of tests. Otherwise we will receive one bug report after the other for refused restore of privileges.

Additionally I learned that the MySQL privilege system is based on the contents of the privilege tables. By INSERTs, UPDATEs, and DELETEs one can set privileges that may not be reproduced by GRANT statements. I have been told that this is especially true for changes of the mysql.host table. We need a couple of tests that try to set privileges which cannot be reproduced by GRANT statements. If this turns out to be possible, then we need to refactor backup and restore of privileges to use INSERT [UPDATE, DELETE?] statements.

The user manual is clear about the "host" table:

"
5.4.2. Privilege System Grant Tables
...
The host table must be modified directly with statements such as INSERT,
UPDATE, and DELETE. It is not affected by statements such as GRANT and
REVOKE that modify the grant tables indirectly. Most MySQL installations
need not use this table at all.
...

5.4.5. Access Control, Stage 2: Request Verification
...
Expressed in boolean terms, the ... user's privileges are calculated ...
like this:
global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
OR routine privileges
...
The host table is not affected by the GRANT or REVOKE statements, so it
is unused in most MySQL installations. If you modify it directly, you
can use it for some specialized purposes, such as to maintain a list of
secure servers on the local network that are granted all privileges.
...
"

The test result, attached as a file, shows a problem with the host table.
Explanations:

- The test does the following:

  - Create database, table, and user.
  - Grant privileges to the user.
  - Connect as the user and utilize the privileges.
  - Modify db and host tables.
  - Re-connect as the user and utilize the privileges again.
  - Back up.
  - Drop database, user, and db- and host entries for the database.
  - Re-create user.
  - Restore, which refuses db privileges.
  - Show that connect as the user fails.
  - Drop database, user, and db- and host entries for the database.
  - Re-create user.
  - Create another user with empty host name.
  - Restore.
  - Show that connect as the user fails again.

- The host table is used for database privileges if the host name column
  in the db table is empty. In the test I have an UPDATE, which
  accomplishes this.

- After every direct modification of the privilege tables, FLUSH
  PRIVILEGES is required.

- I disconnect and re-connect the user to be sure that it uses the
  current set of privileges.

- After modification of db- and host tables, SELECT, INSERT, and DELETE
  work as usual, but UPDATE fails, which was the intention of the change.

- After DROP DATABASE and DROP USER, database privileges still exist.
  They must be deleted explicitly from db- and host tables.

- For the second RESTORE attempt, I create a user with empty host name
  to force RESTORE to load the databse privilege with the empty host
  name. However, since RESTORE does not load the host table entry, the
  combination of db- and host tables does not have an entry for the
  current user, which has a non-empty host name.

I dare to conclude that we could meet a setup, which uses the host
table. Its entries cannot be backed up or restored using GRANT
statements. We may need to use DML on the privilege tables instead of
GRANT statements. Is this agreed by the reviewers?

Test result

Attachment: bug42756-2.result (application/octet-stream, text), 5.34 KiB.

Ingo,

I agree with your evaluation of the problem. In my words:

MySQL privilege system (for database-level privileges) uses information stored in mysql.host table. This information is not preserved by backup/restore operation.

The reason is that restore uses GRANT statements to re-create privileges, and these statements can not modify mysql.host table.

Possible solution is to use in restore explicit INSERT/UPDATE statements on mysql.host table to re-create required information.

Implementation Proposal

Add a special hack to si_objects, which pretends that user ''@'%' does always exist.

I've been a spectator for this debate for some time now. As the second reviewer, I think it past time that I contribute. I have considered the problem from a different vantage point (an arbitrator). I have concluded we are thinking about the solution from the wrong direction. Here are my independent observations and recommendations.

Observations
------------
First, I see it as a mission failure if restore violates the premise that we recreate in any form a user account or his rights if the user does not exists.

I see the problem described WRT MTR cleanup a problem with MTR or the tests, not the code.

I also think the anonymous user is a very dangerous thing -- one that if we do anything at all inside the backup or restore code it should be simply to prohibit the anonymous user more than anything.

I have had little or no success attempting direct inserts to the mysql database tables. Despite what is written in the code or the documentation, they are problematic and very risky.

I wonder if there are any other such MTR cleanup issues with user accounts other than the anonymous user. If there are, they should be stated in the bug report as it seems the issue is only with the anonymous user.

The anonymous user account is very strange and is treated differently. Indeed, I have seen where this user 'magically' appears without ever being created.

The problems described in the bug report neither limit the abilities of backup or restore (ignoring the anonymous user issue for now) nor does it result in a crash. The bug is really an internal issue WRT test results and not (IMHO) a reflection of quality or limitation of the feature.

Recommendations
---------------
I think the solution should be to either:
a) change MTR to not use the anonymous user
b) change the affected tests to drop the anonymous user manually
e.g. to insert this before every restore (or even backup):

DROP USER ''@'%';

I don't think any code should be changed for backup or restore to fix this bug. Nor do I think backup or restore should violate the premise that it neither backs up nor restores user accounts.

I concur with the observation that it is possible to create levels of access that cannot be reproduced with grants and that we should be testing these. However, I see this as future work and I recommend it be made a separate worklog task.

I think more research into understanding the anonymous user and its impact on backup and restore (and vice-versa) should be made a separate worklog and delayed until a later release. This bug should simply fix the MTR cleanup issues only and not change the behavior of backup or restore WRT user account creation or access levels. Specifically, it should not manually insert rows into the mysql database tables.

Notes
-----
I wonder if an explicit DROP DATABASE before the restore would solve the problem or if we really do need to drop the anonymous user. This should be checked.

Hi Chuck, thank you for chiming in. It's very appreciated. You have some valid points, but IMHO you missed a few small facts.

The "MTR cleanup problem" is not about a user missing or too much. It is about a changed checksum of the mysql.proc table. This means that the test modified (here removed) procedure privilege(s), which it didn't restore. Jørgen's detailed analyze shows that this happens during RESTORE, which refuses to grant permissions to ''@'%' as this user does not exist. DROP USER ''@'%' is not possible. Creating this user won't help either, as then we would probably have a checksum mismatch in mysql.user.

Some words regarding the mystery of the anonymous user. The default installation of a MySQL database system includes a "test" database. Every user has (almost?) all rights on that database. This is a "feature" of MySQL from the beginning. IMHO it's not an option to remove it. A user, who is concerned about possible abuse, can drop the non-existent user ''. This removes all default privileges from "test".

The installation is done by INSERTs into mysql.db as GRANTs to the nonexistent user are not possible. These special privileges from the default installation cause the problems in Jørgen's test. Initially he tried "BACKUP DATABASE *", which includes the test database. Later he narrowed the problem down to the test database alone.

The real problem turned out to be that the default privileges on "test" are backed up, though no such user exists, but could not be restored, as no such user exists.

During RESTORE ... OVERWRITE, the test database was dropped. With it, existing procedures were dropped. Dropping databases, tables, etc retains their privileges, while dropping routines removes their privileges. Since RESTORE bailed out on the default privileges, due to non-existent user, the rest of the privileges haven't be restored either, including the routine privileges. Hence the checksum mismatch in mysql.proc.

You wrote:
"I see it as a mission failure if restore ... recreate in
any form a user account or his rights if the user does not exists."
Ok. This means that RESTORE cannot restore the test database with its default privileges. Hence it must not appear in a backup file. So a possible approach would be to exclude "test" and "test_*" from BACKUP.

"I see the problem described WRT MTR cleanup a problem with MTR or the tests, not the code."
I hope, my above explanations make clear that this is not the case.

(Skipping discussion about the anonymous user itself. This is outside of our influence.)

"I have had little or no success attempting direct inserts to the mysql database tables. Despite what is written in the code or the documentation, they are problematic and very risky."
Sorry to hear about your misfortune. I didn't have problems whatsoever. Also was it Sergei Golubchik, one of the most experienced MySQL developers, who strongly recommended to re-create privileges by INSERTs instead of GRANTs. He did explicitly deny possible upgrade problems. The core structure of the tables is unchanged since the beginning. Just more columns have been added. Using INSERT ... SET instead of INSERT ... VALUES should be safe enough.

"The problems described in the bug report neither limit the abilities of backup or restore ..."
Well, it cannot BACKUP+RESTORE the test database. Isn't that a limitation?

"Recommendations ... a) change MTR to not use the anonymous user"
Hm. MTR does a default installation at test begin. This includes the test database with the default privileges to the anonymous user. I hope that you take that back after reading the above explanations.

"b) change the affected tests to drop the anonymous user manually"
Not possible. The user does not exist.

"I concur with the observation that it is possible to create levels of access that cannot be reproduced with grants and that we should be testing these. However, I see this as future work and I recommend it be made a separate worklog task."
Agree fully. Let us attack this later. Unless we decide to reproduce privileges with INSERTs. Then this should be done before users have created backup image files, which they might want to restore on a later version.
Another option would be to ignore mysql.host completely. Other features ignore it too (see Bug #17030).

"I think more research into understanding the anonymous user and its impact on backup and restore ... should be made a separate worklog"
IMHO we have sufficient understanding. I hope, you agree, after reading my above explanations.

Possible solutions:
1.) Do not backup privileges to the anonymous user. They could be assumed as being existent on the restoring server anyway. This was my first attempt. It has been rejected by Rafal (and later by you too).
2.) Change from GRANTs to INSERTs. This should fix all known and still unknown problems with privilege restoration. However, it may make existing backup images un-restorable. Also it would invert a decision, which has been made long ago.
3.) Declare the anonymous user as "always exists". RESTORE would recreate such privileges like the default privileges just fine. This was my recent "implementation proposal". Rafal hasn't accepted it.
4.) Do not backup "test" and "test_*". This is a new idea, developed during this writing. It addreses the requirement "never to create privileges to non-existent users". However, the problem could re-appear at a user's site, if he INSERTs similar privileges for other databases. We could however defeat this by refusing to backup any database with privileges to non-existent users.
5.) Ignore the issue. Avoid to backup/restore "test" databases in test cases. Address it in the manual.

Thanks for the explanations Ingo, that cleared some things up.

Now I want to recall my earlier recommendations now that I am better informed. I think we should do one or more of the following (borrowed from Ingo's responses):

1) Allow grant statements for the anonymous user. 

I realize this is dangerous, but it is clear to me that there are certain unique things about the anonymous user that cannot be worked around.

2) If an anonymous user is encountered in backup, throw a warning, do not save the grant statement, and keep going.

3) If an anonymous user is encountered in restore, throw a warning, do not apply the grant statement, and keep going.

4) Do not include the test database in the backup (along with mysql, information_schema) and document the limitation clearly so users will know the reason why.

I think the above is where we should focus our decision -- treating the anonymous user differently that other user accounts either by applying the grants on restore or building backup and restore so that they are ignored.

I vote for option 1.

I think that this isn't that dangerous. If the privilege system is reasonably maintained by the DBAs, no one should be able to illegally create privileges for an anonymous user.

This option is the only way to restore the database in full, with all its privileges (mysql.host ignored for now). Options 2 and 3 would possibly restore a database with a reduced set of privileges.

Option 4 could bite us when we try to approach a full instance backup.

Options 2-4 require careful documentation, which comes as a surprise to users, which we shoudl try to avoid, if possible.

Herewith I refresh my latest DESIGN PROPOSAL. Let us change si_objects so that the check for user existence of ''@'%' does always return true. This lets RESTORE restore privileges to the anonymous user.

I agree. I approve of this design decision.

Ingo,

I'm puzzled by this statement you made: "The installation is done by INSERTs into mysql.db as GRANTs to the nonexistent user are not possible". I think GRANTS to nonexistent users are possible in general and in that case the user is implicitly created. I also tested that one can do this:

DROP USER '';
CREATE DATABASE db1;
GRANT ALL ON db1.* TO '';

It works.

Probably obvious, but make sure that even if we make check_user_exists return true for anonymous user, the grants will still only be created for databases being restored.

The problem does no longer exist.
MTR seems to handle its prodecures differently now.
mysql.procs_priv is now empty at test begin. Nothing can be removed from it by dropping databases.

I read WL#4073. It clearly specifies that we must not restore privileges for non-existent users, not even for ''@'%'. So the behavior of BACKUP/RESTORE is correct, as Chuck said before.

Reopening this bug as the problem still persists in the latest tree.

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/82158

2866 Hema Sridharan	2009-09-01
      BUG#46982 (There is no test in backup suite that
      performs BACKUP DATABASE * TO 'image' and  
      RESTORE FROM 'image')
      This patch will add new test backup_all.test in 
      backup suite. This test is added as exprimental because
      of bug#42756.
      added:
        mysql-test/suite/backup/r/backup_all.result
        mysql-test/suite/backup/t/backup_all.test
      modified:
        mysql-test/collections/default.experimental

Once this bug is fixed, please remove the backup_all.test from experimental list

Hi Hema, the testcase backup_all does not fail on my Ubuntu x86_64. Nor do I see it fail on pushbuild. Can you please be more specific, how to repeat the problem?

When dropping the database 'mtr', its procedures are also dropped. The records are removed from mysql.proc. This leaves holes in the table. Even though the table contains only mtr records at that time, and thus becomes empty, there are still the record frames left, which are chained in the "delete link".

When the mtr database is restored, its procedures are re-created. This adds back the records into mysql.proc. Since the delete link contains the record frames in the reverse orser of their deletion, the records are added in a different order. It can also happen that the frame sizes do not match and frames need to be split. Also the re-created procedures get new creation and modification dates. Altogether the table checksum cannot be the same as before the test.

Optimizing or even truncating mysql.proc after dropping mtr does not bring the re-created records in the same order as in the original table. RESTORE might not re-create the procedures in the same order as their records existed before.

But even if the record order problem would be solvable, the timestamps in mysql.proc still break the checksum. However, I'm not sure if it would be a good idea if RESTORE would reset the timestamps of procedures.

Another fix of the problem could be that mysql-test-run performs a logical check of mysql.proc instead of the physical checksum.

Another suggestion: Accept the fact that backing-up and restoring all databases, including the mtr and test ones will not pass MTRs internal checks. Thus don't try doing it inside MTR tests. Instead, write a dedicated test for testing "BACKUP DATABASE *" command - for example a perl script. This can be easily added to our regression testing using pushbuild test collection file.

The MyISAM table checkum does not take the order of bytes into account. As long as the same bytes appear in the same quantities, the checksum is ok.

By optimizing a table, all records are defragmented and unused record frames removed. That way we can restore the original quantity of all bytes in the data file by optimizing mysql.proc after restore of the procedures. The order of records doesn't matter for the checksum calculation.

The problem reduces to restoring the original creation and modification dates. By copying mysql.proc at test begin, extracting the original dates from there, and updating them in mysql.proc at test end, we can achieve the original checksum.

This procedure is not trivial as there are some obstacles. I'll try to explain it in the upcoming patch.

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/84321

2872 Ingo Struewing	2009-09-23
      Bug#42756 - Backup: "backup database *" then restore
                  does not preserve mtr state
      
      The problem was a different checksum of mysql.proc before and after
      the test. That happened because of deletion and re-insertion of
      records due to drop and restore of the 'mtr' database. At first,
      the records are not restored in reverse order, so that the MyISAM
      "delete link" takes old record storage for different records and
      produces fragmentation. At second, the re-creation of the mtr
      procedures assigned new creation and modification dates to the
      records.
      
      The first problem is solved by optimizing mysql.proc after the
      test. That defragments the records and removes unused frames.
      
      The second problem is solved by explicitly updating the dates to
      their original values.
     @ mysql-test/suite/backup/t/backup_all.test
        Bug#42756 - Backup: "backup database *" then restore
                    does not preserve mtr state
        Added save and restore of original creation and modification dates of
        the records in mysql.proc.
        Added optimization of mysql.proc at test end.

Good to push.

Queued to mysql-6.0-backup.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091025133616-ca4inerav4vpdnaz) (version source revid:ingo.struewing@sun.com-20090928125502-9t9uqhzsp87vmgnx) (merge vers: 6.0.14-alpha) (pib:13)

Test case changes. No changelog entry needed.