Bug #85710 Improper handling of database names when locking tables for trigger execution.

• Submitted: 30 Mar 2017 14:05
• Modified: 24 Apr 2017 17:55
• Reporter: Dmitry Lenev
• Status: Closed
• Category: MySQL Server: Locking
• Severity: S3 (Non-critical)
• Version: 5.7.19-git
• OS: Any
• CPU Architecture: Any

[30 Mar 2017 14:05] Dmitry Lenev

Description:
Algorithm which opens and locks tables used by triggers does not differentiate
triggers with the same name belonging to databases which names differ only in
case (e.g. 'a' and 'A').

As result tables which are needed by such "duplicate" triggers won't be correctly
opened and locked and will be erroneously reported as missing.

This bugs affects 5.7 and probably all versions since triggers were implemented.

How to repeat:
# Script for mysqltest tool.
create table t1 (i int);
create table t2 (i int);
create database A;
use A;
create table t1 (i int);
create trigger trg before insert on t1 for each row insert into test.t1 values (1);
create database a;
use a;
create table t2 (i int);
create trigger trg before insert on t2 for each row insert into test.t2 values (1);
use test;
delimiter |;
create function f1() returns int
begin
  insert into A.t1 values (1);
  insert into a.t2 values (1);
  return 0;
end|
delimiter ;|
# The below select fails with "Table 'test.t1' doesn't exist" error.
select f1();

[20 Apr 2017 10:31] Dmitry Lenev

Posted by developer:
 
The following patch has been pushed to mysql-trunk / 8.0.2:

commit 26a39921cd5598f593e6e50cff08611bb23f9683
Author: Dmitry Lenev <[e-mail]>
Date:   Thu Mar 30 17:39:50 2017 +0300

    Pre-requisite patch for WL#6049 "Meta-data locking for FOREIGN KEY tables".
    
    Also includes fix for bug #25807393 "IMPROPER HANDLING OF DATABASE NAMES WHEN
    LOCKING TABLES FOR TRIGGER EXECUTION".
    
    Changed Query_tables_list::sroutines set/hash to use custom key instead of
    MDL_key. This should allow addition of new entry types for this set without
    adding new artificial namespaces to MDL subsystem.
    
    New custom keys are compatible with MDL_key format, so new initializer for
    MDL_key was introduced, which allows quickly produce MDL_key from 'sroutines'
    keys when needed.
    
    New custom keys should also consume less memory, as unlike MDL_key, they are
    stored in variable-length buffer allocated along with entries for 'sroutines'
    set on memory root.
    
    Changed 'sroutines' hash to use binary comparison for the keys. This ensures
    that comparison of database component of the key is done correctly, in
    case-sensitive fashion, on case-sensitive filesystems.
    Since routine and trigger names are case-insensitive, we always lowercase
    their names when generating keys.
    
    This fixes bug #25807393, which occurred because prelocking algorithm was
    not able to differentiate triggers belonging to databases which names only
    differed in case (e.g. 'a' and 'A') and as result ignored tables and
    routines needed by one of 'duplicate' triggers.
    
    Finally, fill_dd_view_routines() code was changed to store only routines
    which were directly used by view. Indirectly used routines should not
    affect view columns/validity status in I_S.

[24 Apr 2017 17:55] Paul DuBois

Posted by developer:
 
Noted in 8.0.2 changelog.

Table locking failures could occur if tables were used by a trigger
for which a trigger of the the same name existed in another database
and the database names differed only in lettercase.