Bug #46765 Restore throws warnings when performing backup of test database.
Submitted: 17 Aug 2009 19:03 Modified: 8 Sep 2009 22:45
Reporter: Hema Sridharan Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Backup Severity:S3 (Non-critical)
Version:5.4 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[17 Aug 2009 19:03] Hema Sridharan
Description:
Perform backup of test database
Execute restore. This will generate lots of warning messages for non-existent users.

How to repeat:
--source include/not_embedded.inc

let $MYSQLD_DATADIR= `select @@datadir`;

CREATE DATABASE mydb;

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

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

DROP DATABASE mydb;
--exit

Result
------

BACKUP DATABASE test TO 'all.bak';
backup_id
#
RESTORE FROM 'all.bak' OVERWRITE;
backup_id
#
Warnings:
#       1752    The grant 'ALTER ON `test`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'BACKUP ON `test`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'CREATE ON `test`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'CREATE ROUTINE ON `test`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'CREATE TEMPORARY TABLES ON `test`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'CREATE VIEW ON `test`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'DELETE ON `test`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'DROP ON `test`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'EVENT ON `test`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'INDEX ON `test`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'INSERT ON `test`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'LOCK TABLES ON `test`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'REFERENCES ON `test`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'RESTORE ON `test`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'SELECT ON `test`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'SHOW VIEW ON `test`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'TRIGGER ON `test`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'UPDATE ON `test`.*' for the user ''@'%' was skipped because the user does not exist.

This bug is related to BUG#42756
[17 Aug 2009 19:29] Peter Laursen
I propose you try a `test_99` database too?
Refer to: http://bugs.mysql.com/bug.php?id=44311

.. where Valeriy quoted

Really this behavior is documented at
http://dev.mysql.com/doc/refman/5.1/en/default-privileges.html:
----<q>----
Two anonymous-user accounts are created, each with an empty user name. The anonymous accounts have no password, so anyone can use them to connect to the MySQL server.

* On Windows, one anonymous account is for connections from the local host. It has no global privileges. (Before MySQL 5.1.16, it has all global privileges, just like the root accounts.) The other is for connections from any host and has all privileges for the test database and for other databases with names that start with test.
* On Unix, both anonymous accounts are for connections from the local host.
Connections must be made from the local host by specifying a host name of localhost for one of the accounts, or the actual host name or IP number for the other. These accounts have all privileges for the test database and for other databases with names that start with test_.

The implementation quoted is hopeless in my opinion - and now it goes wrong here too. 'test' user should have access to `test` database only. 'test' should not have other access options as default and no other users (also not 'test') should have access to `test` (and not at all `test%` or `test_%`).
[17 Aug 2009 19:32] Sveta Smirnova
Thank you for the report.

Verified as described. Bug #41721 was marked as duplicate of this one.

Really this is repeatable not only with test database:

If modify test to

--source include/not_embedded.inc

let $MYSQLD_DATADIR= `select @@datadir`;

CREATE DATABASE mydb;

insert into mysql.db values('%', 'mydb', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'Y', 'Y', 'Y', 'Y');
flush privileges;

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

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

DROP DATABASE mydb;
--exit

I get:

CREATE DATABASE mydb;
insert into mysql.db values('%', 'mydb', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'Y', 'Y', 'Y', 'Y');
flush privileges;
BACKUP DATABASE mydb TO 'all.bak';
backup_id
#
RESTORE FROM 'all.bak' OVERWRITE;
backup_id
#
Warnings:
#       1752    The grant 'ALTER ON `mydb`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'BACKUP ON `mydb`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'CREATE ON `mydb`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'CREATE ROUTINE ON `mydb`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'CREATE TEMPORARY TABLES ON `mydb`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'CREATE VIEW ON `mydb`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'DELETE ON `mydb`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'DROP ON `mydb`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'EVENT ON `mydb`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'INDEX ON `mydb`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'INSERT ON `mydb`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'LOCK TABLES ON `mydb`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'REFERENCES ON `mydb`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'RESTORE ON `mydb`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'SELECT ON `mydb`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'SHOW VIEW ON `mydb`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'TRIGGER ON `mydb`.*' for the user ''@'%' was skipped because the user does not exist.
#       1752    The grant 'UPDATE ON `mydb`.*' for the user ''@'%' was skipped because the user does not exist.
DROP DATABASE mydb;
[17 Aug 2009 20:05] Bugs System
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/80924

2860 Hema Sridharan	2009-08-17
      BUG#46758 (backup_no_data fails in MTR)
      The backup_no_data fails because of BUG#46765 where test database produces warnings during restore.
      This bug fix will not use BACKUP DATABASE * in the testcase.
      modified:
        mysql-test/suite/backup/r/backup_no_data.result
        mysql-test/suite/backup/t/backup_no_data.test
        mysql-test/suite/backup/t/disabled.def
[18 Aug 2009 7:12] Rafal Somla
Note that all warning are about the anonymous user ''. Most probably, the logic in backup code which checks if the user to whom privileges are granted during RESTORE does not recognize the special status of the anonymous user '' which really denotes "any user".
[18 Aug 2009 16:52] Bugs System
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/81005

2860 Hema Sridharan	2009-08-18
      BUG#46758 (backup_no_data test fails in mtr)
      The backup_no_data fails because of BUG#46765
      where test database produces warnings during restore.
      This bug fix will not use BACKUP DATABASE * in the test case.
      modified:
        mysql-test/suite/backup/r/backup_no_data.result
        mysql-test/suite/backup/t/backup_no_data.test
        mysql-test/suite/backup/t/disabled.def
[28 Aug 2009 10:01] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090828100112-r73xkx8dhekz5bbb) (version source revid:jorgen.loland@sun.com-20090819062832-mzgo92b5zw4lfqkc) (merge vers: 5.4.4-alpha) (pib:11)
[1 Sep 2009 15:58] Hema Sridharan
Once this bug is fixed, please make necessary changes to test backup_all.test in backup suite (Remove the disable_warning and enable_warnings when restore is performed)
[8 Sep 2009 22:45] Chuck Bell
From 8 September backup meeting:

DECISION: 
   - [ ] ACTION: Chuck to make sure it is documented that non-existing
         user grants are not restored.
   - Go with b. (Make this a duplicate of BUG#43836 and let that solution fix it.)
   - It is likely that BUG#43836 will involve a patch to only add
     grants for existing users.

Duplicate of BUG#43836.