Bug #50924 Object names not resolved correctly on lctn2 systems
Submitted: 4 Feb 2010 17:17 Modified: 25 Apr 2011 18:46
Reporter: Chuck Bell Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1.55-bzr OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[4 Feb 2010 17:17] Chuck Bell
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
[4 Feb 2010 21:57] Sveta Smirnova
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
[12 Oct 2010 16:21] Konstantin Osipov
Sveta, could you please try again against 5.5.7?
Is there a shorter test case?

Thanks, 
-- 
kostja
[12 Oct 2010 19:01] Sveta Smirnova
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.
[25 Apr 2011 18:46] Paul Dubois
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