MySQL Bugs: #47804: Backup catalog does not preserve object relationships

Bug #47804	Backup catalog does not preserve object relationships
Submitted:	2 Oct 2009 22:17	Modified:	8 Mar 2010 1:11
Reporter:	Chuck Bell	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	5.4.4	OS:	Any
Assigned to:	Chuck Bell	CPU Architecture:	Any

Description:
The backup catalog stores the objects in a backup image grouping objects into databases whereby each item can be associated with a database.

However, trigger objects belong to tables and therefore should be stored as belonging to a specific table.

The problem is there is no way to determine what table a trigger belongs to when performing operations on the items in the catalog. For example, it is not possible to perform a access check (e.g. check_access()) for a specific trigger. This is because the access check mechanism requires not only the database but also the table.

This results in not being able to detect whether a user has the trigger privilege for a given table forcing the user to have the trigger privilege at the database-level instead. For example, if a user has all rights to a database including triggers but the TRIGGER privilege is something like:

GRANT TRIGGER ON db1.t1 ...

Then the new privilege checking for restore (see BUG#44787) will fail even though the user has the privileges to read all objects in the database. Instead, the user will be forced to have:

GRANT TRIGGER ON db1.* ...

How to repeat:
Code review

Suggested fix:
Refactor the catalog to include per-object object relationships. For example, allow tables to 'contain' triggers or allow triggers to 'belong to' tables so that the item entry in the catalog preserves this relationship.

I will not comment in detail on server-side backup - only this 

"However, trigger objects belong to tables and therefore should be stored as belonging to a specific table." 

.. I think is wrong.  It was the case in very early 5.0 but was changed (one reason I think was because SQL standards define TRIGGERS as database objects and not table objects). In early 5.0 two triggers defined ON different tables could have identical names.  Now they cannot.

So maybe rather the change from table object >> database object was not done completely?

You can ignore this if it is irrelevant - but I just do not think you should write that 'TRIGGERS are table objects'.

I referred to this:
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-10.html

"Incompatible Change: The namespace for triggers has changed. Previously, trigger names had to be unique per table. Now they must be unique within the schema (database). An implication of this change is that DROP TRIGGER syntax now uses a schema name instead of a table name (schema name is optional and, if omitted, the current schema will be used)."

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

2886 Chuck Bell	2009-10-29
      BUG#47804 : Backup catalog does not preserve object relationships
      
      The backup catalog contains a reference to triggers that does not
      contain all of the information needed to perform object-level
      privilege checking. Specifically, the table name for a trigger
      is needed to allow object-level privilege checking for triggers.
      
      This patch adds the coordinates for locating a table in the 
      catalog for all database items. It is currently only used for
      triggers but could be helpful for other objects. For all other
      objects, the coordinates saved are NULL.
     @ mysql-test/suite/backup/r/backup_logs.result
        Corrected result file because size of image grew by the size
        of the coordinates saved.
     @ mysql-test/suite/backup/t/disabled.def
        Must disable the restore tests for cross-platform restore tests
        because they use a pre-generated backup image. A new patch will
        be created once the pushbuild machines are complete and new
        backup images pushed to the tree with the tests reenabled.
     @ sql/backup/image_info.cc
        Added code to find the table for a trigger, locate it in the
        catalog, and save its coordinates in the trigger's database
        item structure.
     @ sql/backup/image_info.h
        Added method headers for helper methods to locate a database
        and a table in the catalog.
     @ sql/backup/stream_v1.c
        Added code to read and write the table coordinates for a
        trigger from/to the backup image.
     @ sql/backup/stream_v1.h
        Extended the st_bstream_dbitem_info struct to include the
        coordinates for a table. This is only used for triggers.
     @ sql/si_objects.cc
        Added a new method to find the table for a given trigger.
     @ sql/si_objects.h
        Added method header for new find table for trigger method.

This patch shall ensure a table reference is stored in the catalog that allows one to find the table name that a trigger fires on. This will be needed by BUG#44787 to implement the privilege pre-check for permissions.

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

2886 Chuck Bell	2009-10-29
      BUG#47804 : Backup catalog does not preserve object relationships
      
      The backup catalog contains a reference to triggers that does not
      contain all of the information needed to perform object-level
      privilege checking. Specifically, the table name for a trigger
      is needed to allow object-level privilege checking for triggers.
      
      This patch adds the coordinates for locating a table in the 
      catalog for all database items. It is currently only used for
      triggers but could be helpful for other objects. For all other
      objects, the coordinates saved are NULL.
     @ mysql-test/suite/backup/r/backup_logs.result
        Corrected result file because size of image grew by the size
        of the coordinates saved.
     @ mysql-test/suite/backup/t/disabled.def
        Must disable the restore tests for cross-platform restore tests
        because they use a pre-generated backup image. A new patch will
        be created once the pushbuild machines are complete and new
        backup images pushed to the tree with the tests reenabled.
     @ sql/backup/image_info.cc
        Added code to find the table for a trigger, locate it in the
        catalog, and save its coordinates in the trigger's database
        item structure.
     @ sql/backup/image_info.h
        Added method headers for helper methods to locate a database
        and a table in the catalog.
     @ sql/backup/stream_v1.c
        Added code to read and write the table coordinates for a
        trigger from/to the backup image.
     @ sql/backup/stream_v1.h
        Extended the st_bstream_dbitem_info struct to include the
        coordinates for a table. This is only used for triggers.
     @ sql/si_objects.cc
        Added a new method to find the table for a given trigger.
     @ sql/si_objects.h
        Added method header for new find table for trigger method.

Approved pending changes. Please see email.

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

2886 Chuck Bell	2009-11-03
      BUG#47804 : Backup catalog does not preserve object relationships
      
      The backup catalog contains a reference to triggers that does not
      contain all of the information needed to perform object-level
      privilege checking. Specifically, the table name for a trigger
      is needed to allow object-level privilege checking for triggers.
      
      This patch adds the coordinates for locating a table in the 
      catalog for all database items. It is currently only used for
      triggers but could be helpful for other objects. For all other
      objects, the coordinates saved are NULL.
     @ mysql-test/suite/backup/r/backup_errors_debug_2.result
        Corrected test result file.
     @ mysql-test/suite/backup/r/backup_logs.result
        Corrected result file because size of image grew by the size
        of the coordinates saved.
     @ mysql-test/suite/backup/t/backup_errors_debug_2.test
        Added test cases for new code to fail if table name for
        trigger not found, database not found, or query from
        IS fails.
     @ mysql-test/suite/backup/t/disabled.def
        Must disable the restore tests for cross-platform restore tests
        because they use a pre-generated backup image. A new patch will
        be created once the pushbuild machines are complete and new
        backup images pushed to the tree with the tests reenabled.
     @ sql/backup/image_info.cc
        Added code to find the table for a trigger, locate it in the
        catalog, and save its coordinates in the trigger's database
        item structure.
     @ sql/backup/image_info.h
        Added method headers for helper methods to locate a database
        and a table in the catalog.
     @ sql/backup/stream_v1.c
        Added code to read and write the table coordinates for a
        trigger from/to the backup image.
     @ sql/backup/stream_v1.h
        Extended the st_bstream_dbitem_info struct to include the
        coordinates for a table. This is only used for triggers.
     @ sql/si_objects.cc
        Added a new method to find the table for a given trigger.
     @ sql/si_objects.h
        Added method header for new find table for trigger method.

Approved pending minor fix in doxygen documentation.

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

2886 Chuck Bell	2009-11-04
      BUG#47804 : Backup catalog does not preserve object relationships
      
      The backup catalog contains a reference to triggers that does not
      contain all of the information needed to perform object-level
      privilege checking. Specifically, the table name for a trigger
      is needed to allow object-level privilege checking for triggers.
      
      This patch adds the coordinates for locating a table in the 
      catalog for all database items. It is currently only used for
      triggers but could be helpful for other objects. For all other
      objects, the coordinates saved are NULL.
     @ mysql-test/suite/backup/r/backup_errors_debug_2.result
        Corrected test result file.
     @ mysql-test/suite/backup/r/backup_logs.result
        Corrected result file because size of image grew by the size
        of the coordinates saved.
     @ mysql-test/suite/backup/t/backup_errors_debug_2.test
        Added test cases for new code to fail if table name for
        trigger not found, database not found, or query from
        IS fails.
     @ mysql-test/suite/backup/t/disabled.def
        Must disable the restore tests for cross-platform restore tests
        because they use a pre-generated backup image. A new patch will
        be created once the pushbuild machines are complete and new
        backup images pushed to the tree with the tests reenabled.
     @ sql/backup/image_info.cc
        Added code to find the table for a trigger, locate it in the
        catalog, and save its coordinates in the trigger's database
        item structure.
     @ sql/backup/image_info.h
        Added method headers for helper methods to locate a database
        and a table in the catalog.
     @ sql/backup/stream_v1.c
        Added code to read and write the table coordinates for a
        trigger from/to the backup image.
     @ sql/backup/stream_v1.h
        Extended the st_bstream_dbitem_info struct to include the
        coordinates for a table. This is only used for triggers.
     @ sql/si_objects.cc
        Added a new method to find the table for a given trigger.
     @ sql/si_objects.h
        Added method header for new find table for trigger method.

I downloaded all the images from pushbuild and created the Linux x86 (32-bit) ones myself. The result is this:

backup.backup_xpfm_compat_restore_lctn0  [ pass ]   6236
backup.backup_xpfm_compat_restore_lctn1  [ fail ]
        Test ended at 2009-11-13 16:00:20

CURRENT_TEST: backup.backup_xpfm_compat_restore_lctn1
mysqltest: In included file "./suite/backup/include/backup_xpfm_compat_restore.inc": At line 103: query 'CALL BUP_XPFM_COMPAT_DB2.proc1' failed: 1305: PROCEDURE bup_xpfm_compat_db2.proc1 does not exist

This happens for the Windows 32 lctn2 image. It seems that after RESTORE, only the procedures from bup_xpfm_compat_db1 are present.

When I comment out the test for the Windows 32 lctn2 image, it fails in the same way for the Windows 64 lctn2 image.

mysqlbackup shows that the two images don't have procs, funcs, and events for db2 and db3. So the problem lies in BACKUP on Windows. Or the images are not ok. I used the ones from the 2009-11-11 16:10:49 push from mysql-6.0-backup.

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

2894 Chuck Bell	2009-11-17
      BUG#47804 : Backup catalog does not preserve object relationships
      
      The backup_xpfm_compat_backup test fails on pushbuild on Windows
      platforms.
      
      While investigating a problem found with this test, it was 
      discovered that while using lower_case_table_names = 2 on 
      Windows that the SELECTs on the INFORMATION_SCHEMA.ROUTINES
      and INFORMATION_SCHEMA.EVENTS tables failed to find the events
      for databases with uppercase names.
      
      This patch corrects this behavior.
      
      It was also discovered that the test hangs on Windows. A new bug
      was opened to address this - BUG#48850. This patch adds a 
      workaround to ensure the databases can be dropped and the test
      will not hang.
     @ mysql-test/suite/backup/include/backup_xpfm_compat_backup.inc
        Added flush for workaround to hang on PB2.
     @ sql/si_objects.cc
        Added code to use lower case names if running on Windows and
        lctn=2.

Approved the latest patch. Please see email for suggestions.

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

2894 Chuck Bell	2009-11-18
      BUG#47804 : Backup catalog does not preserve object relationships
      
      The backup_xpfm_compat_backup test fails on pushbuild on Windows
      platforms.
      
      While investigating a problem found with this test, it was 
      discovered that while using lower_case_table_names = 2 on 
      Windows that the SELECTs on the INFORMATION_SCHEMA.ROUTINES
      and INFORMATION_SCHEMA.EVENTS tables failed to find the events
      for databases with uppercase names.
      
      This patch corrects this behavior by using a case insensitive
      compare.
      
      It was also discovered that the test hangs on Windows. A new bug
      was opened to address this - BUG#48850. This patch adds FLUSH
      TABLES as a workaround to ensure the databases can be dropped 
      and the test will not hang.
     @ mysql-test/suite/backup/include/backup_xpfm_compat_backup.inc
        Added flush for workaround to hang on PB2.
     @ sql/si_objects.cc
        Added code to use case insensitive compare if running on Windows
        and lctn=2.

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

2891 Chuck Bell	2009-11-19
      BUG#47804 : 
      
      This patch corrects an error found in pushbuild where the
      collation changed the order of the events in SHOW EVENTS.
      
      Patch changed to use LOWER() along with original collation
      option in the SQL commands.
     @ sql/si_objects.cc
        Use LOWER() instead of utf8_general_ci which changed order
        of subsequent SHOW commands.

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

2891 Chuck Bell	2009-11-19
      BUG#47804 : Backup catalog does not preserve object relationships.
      
      This patch corrects an error found in pushbuild where the
      collation changed the order of the events in SHOW EVENTS.
      
      Patch changed to use LOWER() along with original collation
      option in the SQL commands.
     @ sql/si_objects.cc
        Use LOWER() instead of utf8_general_ci which changed order
        of subsequent SHOW commands.

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

Pushed into 6.0.14-alpha (revid:ingo.struewing@sun.com-20100218152520-s4v1ld76bif06eqn) (version source revid:ingo.struewing@sun.com-20100119103538-wtp5alpz4p2jayl5) (merge vers: 6.0.14-alpha) (pib:16)

Noted in 6.0.14 changelog.

The backup catalog did not contain sufficient information to perform
privilege checking for triggers.