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
