MySQL Bugs: #37103: mysql.online_backup.username is ambiguous

Bug #37103	mysql.online_backup.username is ambiguous
Submitted:	30 May 2008 13:55	Modified:	18 Mar 2010 7:07
Reporter:	Paul DuBois	Email Updates:
Status:	Patch queued	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
The mysql.online_backup table that is used in conjunction with the BACKUP TABLE and RESTORE statements has a username column:

mysql> desc mysql.online_backup username;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| username | char(30) | YES  |     | NULL    |       | 
+----------+----------+------+-----+---------+-------+

The value indicates the user who initiated the backup or restore column.

The value is ambiguous because MySQL accounts consist of a hostname/username pair.

How to repeat:
desc mysql.online_backup username;

Suggested fix:
Add a hostname column so that both the hostname and username of the initiating account can be recorded.

Alternatively, username could hold the account name in user@host format. But in that case, the column is too short, and that makes parsing the value more difficult if it contains special characters.

Thank you for the bug report.

This seems like a very old bug from version 6.0.7 or prior.
There is no online_backup table now.
There is a backup_history table which does have the hostname if required.

This is still a valid bug. The table was simply renamed, not restructured. The problem report is still in the renamed table. Specifically, the table should be storing user+host like all other tables.

The backup_history table now has:
desc mysql.backup_history host_or_server_name;
Field   Type    Null    Key     Default Extra
host_or_server_name     char(30)        NO

And if backup or restore is run this field does get populated using the security context.
You can get the user name by concatenating user and host_or_server_name. Is something more/different required?

host_or_server_name is the host where the backup/restore operation ran. It has nothing to do with the user who initiated the operation.

The column host_or_server_name currently stores the host name for the user.
But the heading of this column and documentation is out of date.

Will delete this column and modify the username column to store the hostname in the format <user>@<hostname>

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

3108 Satya B	2010-03-09
      BUG#37103 - mysql.online_backup.username is ambiguous
      
      The 'username' column in the table mysql.backup_history is not in the mysql
      user's format which is generally 'user'@'host'.
      
      The 'host_or_server_name' column in the table mysql.backup_history is the
      host from where the user initiated the operation and it is NOT the host
      where BACKUP/RESTORE operation ran.
      
      Fixed by removing the column 'host_or_server_name' and adjusting the column
      'username' to 'user'@'host' format in mysql.backup_history table.
     @ mysql-test/suite/backup/r/backup_logs.result
        BUG#37103 - mysql.online_backup.username is ambiguous
        
        Updated the result file for backup_logs test as the 
        column 'host_or_server_name' is dropped from the 
        mysql.backup_history table.
     @ mysql-test/suite/backup/t/backup_logs.test
        BUG#37103 - mysql.online_backup.username is ambiguous
        
        Updated the  backup_logs test as the column 'host_or_server_name'
        is dropped from the mysql.backup_history table and username is updated
        to 'user'@'host' format.
     @ mysql-test/suite/funcs_1/r/is_columns_mysql.result
        BUG#37103 - mysql.online_backup.username is ambiguous
        
        Fix the result file for the is_columns_mysql test as
        the number of columns in mysql.backup_history are reduced
        to 18 after deleting the 'host_or_server_name' column in
        mysql.backup_history
     @ scripts/mysql_system_tables.sql
        BUG#37103 - mysql.online_backup.username is ambiguous
        
        Update the CREATE TABLE statement for backup_history as the column 
        'host_or_server_name' is dropped.
     @ scripts/mysql_system_tables_fix.sql
        BUG#37103 - mysql.online_backup.username is ambiguous
        
        Add to check to fix the mysql.backup_history table for
        mysql_upgrade.
     @ sql/log.cc
        BUG#37103 - mysql.online_backup.username is ambiguous
        
        Update the username in methods Log_to_csv_event_handler::
        log_backup_history(..) and MYSQL_BACKUP_LOG::write(..) to the
        mysql user format 'user'@'host'.
        
        Added a new method create_mysql_user_format(..) to create
        the required user format.
     @ sql/log.h
        BUG#37103 - mysql.online_backup.username is ambiguous
        
        Removed ET_OBH_FILED_HOST_OR_SERVER from the enum
        enum_backup_history_log_field.

Asked for clarification of one issue (see my reply to commit).

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

3108 Satya B	2010-03-12
      BUG#37103 - mysql.online_backup.username is ambiguous
      
      The 'username' column in the table mysql.backup_history is not in the mysql
      user's format which is generally 'user'@'host'.
      
      The 'host_or_server_name' column in the table mysql.backup_history is the
      host from where the user initiated the operation and it is NOT the host
      where BACKUP/RESTORE operation ran.
      
      Fixed by removing the column 'host_or_server_name' and adjusting the column
      'username' to 'user'@'host' format in mysql.backup_history table
     @ mysql-test/suite/backup/r/backup_logs.result
        BUG#37103 - mysql.online_backup.username is ambiguous
        
        Updated the result file for backup_logs test as the 
        column 'host_or_server_name' is dropped from the 
        mysql.backup_history table.
     @ mysql-test/suite/backup/t/backup_logs.test
        BUG#37103 - mysql.online_backup.username is ambiguous
        
        Updated the  backup_logs test as the column 'host_or_server_name'
        is dropped from the mysql.backup_history table and username is updated
        to 'user'@'host' format.
     @ mysql-test/suite/funcs_1/r/is_columns_mysql.result
        BUG#37103 - mysql.online_backup.username is ambiguous
        
        Fix the result file for the is_columns_mysql test as
        the number of columns in mysql.backup_history are reduced
        to 18 after deleting the 'host_or_server_name' column in
        mysql.backup_history
     @ scripts/mysql_system_tables.sql
        BUG#37103 - mysql.online_backup.username is ambiguous
        
        Update the CREATE TABLE statement for backup_history as the column 
        'host_or_server_name' is dropped.
     @ scripts/mysql_system_tables_fix.sql
        BUG#37103 - mysql.online_backup.username is ambiguous
        
        Add to check to fix the mysql.backup_history table for
        mysql_upgrade.
     @ sql/log.cc
        BUG#37103 - mysql.online_backup.username is ambiguous
        
        Update the username in methods Log_to_csv_event_handler::
        log_backup_history(..) and MYSQL_BACKUP_LOG::write(..) to the
        mysql user format 'user'@'host'.
        
        Added a new method create_mysql_user_format(..) to create
        the required user format.
     @ sql/log.h
        BUG#37103 - mysql.online_backup.username is ambiguous
        
        Removed ET_OBH_FILED_HOST_OR_SERVER from the enum
        enum_backup_history_log_field.

Good to push.

Thanks Rafal and Thava for the reviews

Patch queued to mysql-next-mr-backup

Revno: 3111 in mysql-next-mr-backup
revid: satya.bn@sun.com-20100318065955-jk25b2izy6ymjsnk