Bug #50924 Object names not resolved correctly on lctn2 systems

Description:
The code in si_objects.cc has a method to retrieve all of the triggers for a database:

"SELECT '" << db_name << "', trigger_name, event_object_table "
"FROM INFORMATION_SCHEMA.TRIGGERS "
"WHERE trigger_schema COLLATE utf8_bin = '" << db_name << "'";

However, this query is not returning all of the triggers for a database that has mixed case database and trigger names. Note: See BUG#50848.

When this query is issued in 6.0, all of the triggers are returned. When issued in 5.X, only some of the triggers are returned.

Note: See test backup_xpfm_compat_backup_lctn2.

How to repeat:
Here is a test case:

--disable_warnings
DROP DATABASE IF EXISTS bup_xpfm_compat_db1;
DROP DATABASE IF EXISTS BUP_XPFM_COMPAT_DB2;
DROP DATABASE IF EXISTS BupXPfmCompat_db3;
--enable_warnings

SET NAMES 'utf8';

CREATE DATABASE bup_xpfm_compat_db1;
CREATE DATABASE BUP_XPFM_COMPAT_DB2;
CREATE DATABASE BupXPfmCompat_db3;

CREATE TABLE bup_xpfm_compat_db1.table1 (c13 INT) DEFAULT CHARSET latin1;
INSERT INTO bup_xpfm_compat_db1.table1 VALUES (1), (2), (3);

CREATE TABLE bup_xpfm_compat_db1.TABLE2 (c13 INT) DEFAULT CHARSET latin1;
INSERT INTO bup_xpfm_compat_db1.TABLE2 VALUES (1), (2), (3);

CREATE TABLE bup_xpfm_compat_db1.TaBle3 (c13 INT) DEFAULT CHARSET latin1;
INSERT INTO bup_xpfm_compat_db1.TaBle3 VALUES (1), (2), (3);

CREATE TABLE BUP_XPFM_COMPAT_DB2.TABLE2 (c13 INT) DEFAULT CHARSET latin1;
INSERT INTO BUP_XPFM_COMPAT_DB2.TABLE2 VALUES (1), (2), (3);

CREATE TABLE BUP_XPFM_COMPAT_DB2.table1 (c13 INT) DEFAULT CHARSET latin1;
INSERT INTO BUP_XPFM_COMPAT_DB2.table1 VALUES (1), (2), (3);

CREATE TABLE bup_xpfm_compat_db2.table3 (c13 INT) DEFAULT CHARSET latin1;
INSERT INTO bup_xpfm_compat_db2.table3 VALUES (1), (2), (3);

CREATE TABLE bupxpfmcompat_db3.table1 (c13 INT) DEFAULT CHARSET latin1;
INSERT INTO bupxpfmcompat_db3.table1 VALUES (1), (2), (3);

CREATE TABLE bupxpfmcompat_db3.table2 (c13 INT) DEFAULT CHARSET latin1;
INSERT INTO bupxpfmcompat_db3.table2 VALUES (1), (2), (3);

CREATE TABLE bupxpfmcompat_db3.table3 (c13 INT) DEFAULT CHARSET latin1;
INSERT INTO bupxpfmcompat_db3.table3 VALUES (1), (2), (3);

delimiter |;
#
CREATE TRIGGER bup_xpfm_compat_db1.trigger1 AFTER INSERT
  ON bup_xpfm_compat_db1.table1 FOR EACH ROW
BEGIN
  DELETE FROM bup_xpfm_compat_db1.TABLE2
    WHERE bup_xpfm_compat_db1.TABLE2.c13 = NEW.c13;
END;
|
CREATE TRIGGER bup_xpfm_compat_db1.TRIGGER2 AFTER INSERT
  ON bup_xpfm_compat_db1.TABLE2 FOR EACH ROW
BEGIN
  DELETE FROM bup_xpfm_compat_db1.table1
    WHERE bup_xpfm_compat_db1.table1.c13 = NEW.c13;
END;
|
CREATE TRIGGER bup_xpfm_compat_db1.TrigGer3 AFTER INSERT
  ON bup_xpfm_compat_db1.TaBle3 FOR EACH ROW
BEGIN
  INSERT INTO BUP_XPFM_COMPAT_DB2.TABLE2 SET c13 = 51;
END;
|
CREATE TRIGGER BUP_XPFM_COMPAT_DB2.trigger1 AFTER INSERT
  ON BUP_XPFM_COMPAT_DB2.table1 FOR EACH ROW
BEGIN
  DELETE FROM BUP_XPFM_COMPAT_DB2.TABLE2
    WHERE BUP_XPFM_COMPAT_DB2.TABLE2.c13 = NEW.c13;
END;
|
CREATE TRIGGER BUP_XPFM_COMPAT_DB2.TRIGGER2 AFTER INSERT
  ON BUP_XPFM_COMPAT_DB2.TABLE2 FOR EACH ROW
BEGIN
  DELETE FROM BUP_XPFM_COMPAT_DB2.table1
    WHERE BUP_XPFM_COMPAT_DB2.table1.c13 = NEW.c13;
END;
|
CREATE TRIGGER BUP_XPFM_COMPAT_DB2.TrigGer3 AFTER INSERT
  ON BUP_XPFM_COMPAT_DB2.TaBle3 FOR EACH ROW
BEGIN
  INSERT INTO BupXPfmCompat_db3.TaBle3 SET c13 = 52;
END;
|
CREATE TRIGGER BupXPfmCompat_db3.trigger1 AFTER INSERT
  ON BupXPfmCompat_db3.table1 FOR EACH ROW
BEGIN
  DELETE FROM BupXPfmCompat_db3.TABLE2
    WHERE BupXPfmCompat_db3.TABLE2.c13 = NEW.c13;
END;
|
CREATE TRIGGER BupXPfmCompat_db3.TRIGGER2 AFTER INSERT
  ON BupXPfmCompat_db3.TABLE2 FOR EACH ROW
BEGIN
  DELETE FROM BupXPfmCompat_db3.table1
    WHERE BupXPfmCompat_db3.table1.c13 = NEW.c13;
END;
|
CREATE TRIGGER BupXPfmCompat_db3.TrigGer3 AFTER INSERT
  ON BupXPfmCompat_db3.TaBle3 FOR EACH ROW
BEGIN
  INSERT INTO bup_xpfm_compat_db1.table1 SET c13 = 53;
END;
|
delimiter ;|

SELECT trigger_schema, trigger_name, event_object_table FROM INFORMATION_SCHEMA.TRIGGERS 
  WHERE trigger_schema COLLATE utf8_bin = 'bup_xpfm_compat_db1';
SELECT trigger_schema, trigger_name, event_object_table FROM INFORMATION_SCHEMA.TRIGGERS 
  WHERE trigger_schema COLLATE utf8_bin = 'BUP_XPFM_COMPAT_DB2';
SELECT trigger_schema, trigger_name, event_object_table FROM INFORMATION_SCHEMA.TRIGGERS 
  WHERE trigger_schema COLLATE utf8_bin = 'BupXPfmCompat_db3';
  
DROP DATABASE IF EXISTS bup_xpfm_compat_db1;
DROP DATABASE IF EXISTS BUP_XPFM_COMPAT_DB2;
DROP DATABASE IF EXISTS BupXPfmCompat_db3;

On 5.X code (e.g. mysql-next-mr-backup) we get this result which is wrong:

SELECT trigger_schema, trigger_name, event_object_table FROM INFORMATION_SCHEMA.TRIGGERS 
WHERE trigger_schema COLLATE utf8_bin = 'bup_xpfm_compat_db1';

trigger_schema	trigger_name	event_object_table
bup_xpfm_compat_db1	TRIGGER2	TABLE2
bup_xpfm_compat_db1	TrigGer3	TaBle3
bup_xpfm_compat_db1	trigger1	table1

Note: this one is ok.

SELECT trigger_schema, trigger_name, event_object_table FROM INFORMATION_SCHEMA.TRIGGERS 
WHERE trigger_schema COLLATE utf8_bin = 'BUP_XPFM_COMPAT_DB2';

trigger_schema	trigger_name	event_object_table
BUP_XPFM_COMPAT_DB2	TRIGGER2	TABLE2

Note: this one is missing 2 triggers!

SELECT trigger_schema, trigger_name, event_object_table FROM INFORMATION_SCHEMA.TRIGGERS 
WHERE trigger_schema COLLATE utf8_bin = 'BupXPfmCompat_db3';

trigger_schema	trigger_name	event_object_table
BupXPfmCompat_db3	trigger1	table1

Note: this one is missing 2 triggers!

On 6.0 code (e.g. mysql-6.0-backup) we get this which is correct:

SELECT trigger_schema, trigger_name, event_object_table FROM INFORMATION_SCHEMA.TRIGGERS 
WHERE trigger_schema COLLATE utf8_bin = 'bup_xpfm_compat_db1';

trigger_schema	trigger_name	event_object_table
bup_xpfm_compat_db1	TRIGGER2	TABLE2
bup_xpfm_compat_db1	TrigGer3	TaBle3
bup_xpfm_compat_db1	trigger1	table1

SELECT trigger_schema, trigger_name, event_object_table FROM INFORMATION_SCHEMA.TRIGGERS 
WHERE trigger_schema COLLATE utf8_bin = 'BUP_XPFM_COMPAT_DB2';

trigger_schema	trigger_name	event_object_table
BUP_XPFM_COMPAT_DB2	TRIGGER2	TABLE2
BUP_XPFM_COMPAT_DB2	trigger1	table1
BUP_XPFM_COMPAT_DB2	TrigGer3	table3

SELECT trigger_schema, trigger_name, event_object_table FROM INFORMATION_SCHEMA.TRIGGERS 
WHERE trigger_schema COLLATE utf8_bin = 'BupXPfmCompat_db3';

trigger_schema	trigger_name	event_object_table
BupXPfmCompat_db3	trigger1	table1
BupXPfmCompat_db3	TRIGGER2	table2
BupXPfmCompat_db3	TrigGer3	table3

Thank you for the report.

Verified as described using:

mysql-next-mr-backup
mysql-5.1

Not repeatable with:

mysql-next-mr
mysql-5.0

Sveta, could you please try again against 5.5.7?
Is there a shorter test case?

Thanks, 
-- 
kostja

Bug is not repeatable with mysql-trunk, but still is repeatable in 5.1

Regarding to test case you still need to create all these triggers. Probably insert statements can be removed.

Noted in 5.1.57, 5.5.12, 5.6.3.

With lower_case_table_names=2, resolution of objects qualified by
database names could fail. 

CHANGESET - http://lists.mysql.com/commits/134838