MySQL Bugs: #50287: MySQL Backup: missed altered procedure in backup_bml_not

Bug #50287	MySQL Backup: missed altered procedure in backup_bml_not_falcon.test
Submitted:	12 Jan 2010 18:50	Modified:	27 Jan 2010 17:13
Reporter:	Ingo Strüwing	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0.14-alpha	OS:	Any
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
In backup_bml_not_falcon.test,
########################################################
# Running BML test for the following statements:
#
# DDL1= ALTER DATABASE bml_test_db1 CHARACTER SET = utf8
# DDL2= ALTER TABLE t1 ADD INDEX `i` (a)
# DDL3= ALTER VIEW v1 AS SELECT 1
# DDL4= ALTER FUNCTION f1 COMMENT 'testing alter'
#
# BML is activated by RESTORE FROM 'bml_test.bkp' OVERWRITE.
########################################################
after
== Checkpoint E ==
procedure test.p1() does not have a COMMENT characteristic.

The COMMENT characteristic should come from the backup image file
bml_test.bkp, which is restored in this test.
The file had been created by BACKUP of the preceeding test.
In this test, ALTER PROCEDURE p1 COMMENT 'testing alter' is started
before the BACKUP and finished before BACKUP should retrieve the
metadata. The timing seems to be fine, but the non-altered CREATE
PROCEDURE statement is retrieved anyway. Verified by
  --exec $MYSQL_BACKUP --metadata-statements $bdir/bml_test.bkp 2>&1
before the RESTORE.

Note that the problem is in the preceeding test:
########################################################
# Running BML test for the following statements:
#
# DDL1= ALTER PROCEDURE p1 COMMENT 'testing alter'
# DDL2= ALTER EVENT e1 RENAME TO e2
# DDL3= ALTER DATABASE bml_test_db1 CHARACTER SET = utf8
# DDL4= ALTER TABLE t1 ADD INDEX `i` (a)
#
# BML is activated by BACKUP DATABASE bml_test TO 'bml_test.bkp'.
########################################################
where the BACKUP is done.

This has been detected during analyze of a difference in test result
between 6.0 and 5.6. The behavior in 5.6 looks fine.
Compare Bug#50279 (MySQL Backup: Result differences in backup_bml_not_falcon).

How to repeat:
Analyze the result file, which shows wrong behavior.

This is perhaps a timing problem and thus won't hit many users.

This is not a bug. There is a behavior difference between 6.0 and 5.6.

5.6:
  sp_show_create_routine() first calls
    sp_cache_routine(), which calls
      sp_cache_lookup(). If successful, it calls
        sp_cache_flush_obsolete(), which removes altered routines from cache,
        db_find_routine(), which reloads from mysql.proc, and
        sp_cache_insert(), which re-inserts the new definition into the cache,
        and sets sp_head::m_sp_cache_version from current cache version,
        which is used to detect altered routines.

6.0:
  sp_show_create_routine() calls
    sp_find_routine(), which calls
      sp_cache_lookup() and
      returns, if a cache hit, thus delivering old data.
  6.0 does not have a sp_head::m_sp_cache_version member,
  it cannot detect altered routines in the cache.

Hence it is correct that in 6.0, BACKUP backs up an outdated version of the
CREATE PROCEDURE statement.