Bug#55222 - RB://517 - Approved by Sunny

InnoDB does not attempt to handle lower_case_table_names == 2 when looking
up foreign table names and referenced table name.  It turned that server
variable into a boolean and ignored the possibility of it being '2'.  

The setting lower_case_table_names == 2 means that it should be stored and
displayed in mixed case as given, but compared internally in lower case.
Normally the server deals with this since it stores table names.  But
InnoDB stores referential constraints for the server, so it needs to keep
track of both lower case and given names.

This solution creates two table name pointers for each foreign and referenced
table name.  One to display the name, and one to look it up.  Both pointers
point to the same allocated string unless this setting is 2.  So the overhead
added is not too much.

Two functions are created in dict0mem.c to populate the ..._lookup versions
of these pointers.  Both dict_mem_foreign_table_name_lookup_set() and
dict_mem_referenced_table_name_lookup_set() are called 5 times each.

14 files changed, 331 insertions, 75 deletions

diff --git a/mysql-test/r/lowercase_table4.result b/mysql-test/r/lowercase_table4.result
index e3f861f8884..f896b9008e3 100755
--- a/mysql-test/r/lowercase_table4.result
+++ b/mysql-test/r/lowercase_table4.result
@@ -5,3 +5,111 @@
 CREATE DATABASE XY;
 USE XY;
 DROP DATABASE XY;
+USE TEST;
+#
+# Bug55222 Mysqldump table names case bug in REFERENCES clause
+# InnoDB did not handle lower_case_table_names=2 for
+# foreign_table_names and referenced_table_names.
+#
+SHOW VARIABLES LIKE 'lower_case_table_names';
+Variable_name	Value
+lower_case_table_names	2
+DROP TABLE IF EXISTS `Table2`;
+DROP TABLE IF EXISTS `Table1`;
+CREATE TABLE `Table1`(c1 INT PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE `Table2`(c1 INT PRIMARY KEY, c2 INT) ENGINE=InnoDB;
+ALTER TABLE `Table2` ADD CONSTRAINT fk1 FOREIGN KEY(c2) REFERENCES `Table1`(c1);
+SHOW CREATE TABLE `Table2`;
+Table	Table2
+Create Table	CREATE TABLE `Table2` (
+  `c1` int(11) NOT NULL,
+  `c2` int(11) DEFAULT NULL,
+  PRIMARY KEY (`c1`),
+  KEY `fk1` (`c2`),
+  CONSTRAINT `fk1` FOREIGN KEY (`c2`) REFERENCES `Table1` (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;
+CONSTRAINT_CATALOG	def
+CONSTRAINT_SCHEMA	test
+CONSTRAINT_NAME	fk1
+UNIQUE_CONSTRAINT_CATALOG	def
+UNIQUE_CONSTRAINT_SCHEMA	test
+UNIQUE_CONSTRAINT_NAME	PRIMARY
+MATCH_OPTION	NONE
+UPDATE_RULE	RESTRICT
+DELETE_RULE	RESTRICT
+TABLE_NAME	Table2
+REFERENCED_TABLE_NAME	Table1
+DROP TABLE `Table2`;
+DROP TABLE `Table1`;
+DROP TABLE IF EXISTS Product_Order;
+DROP TABLE IF EXISTS Product;
+DROP TABLE IF EXISTS Customer;
+CREATE TABLE Product (Category INT NOT NULL, Id INT NOT NULL,
+Price DECIMAL, PRIMARY KEY(Category, Id)) ENGINE=InnoDB;
+CREATE TABLE Customer (Id INT NOT NULL, PRIMARY KEY (Id)) ENGINE=InnoDB;
+CREATE TABLE Product_Order (No INT NOT NULL AUTO_INCREMENT,
+Product_Category INT NOT NULL,
+Product_Id INT NOT NULL,
+Customer_Id INT NOT NULL,
+PRIMARY KEY(No),
+INDEX (Product_Category, Product_Id),
+FOREIGN KEY (Product_Category, Product_Id)
+REFERENCES Product(Category, Id) ON UPDATE CASCADE ON DELETE RESTRICT,
+INDEX (Customer_Id),
+FOREIGN KEY (Customer_Id)
+REFERENCES Customer(Id)
+) ENGINE=INNODB;
+SHOW CREATE TABLE Product_Order;
+Table	Product_Order
+Create Table	CREATE TABLE `Product_Order` (
+  `No` int(11) NOT NULL AUTO_INCREMENT,
+  `Product_Category` int(11) NOT NULL,
+  `Product_Id` int(11) NOT NULL,
+  `Customer_Id` int(11) NOT NULL,
+  PRIMARY KEY (`No`),
+  KEY `Product_Category` (`Product_Category`,`Product_Id`),
+  KEY `Customer_Id` (`Customer_Id`),
+  CONSTRAINT `product_order_ibfk_1` FOREIGN KEY (`Product_Category`, `Product_Id`) REFERENCES `Product` (`Category`, `Id`) ON UPDATE CASCADE,
+  CONSTRAINT `product_order_ibfk_2` FOREIGN KEY (`Customer_Id`) REFERENCES `Customer` (`Id`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SHOW CREATE TABLE Product;
+Table	Product
+Create Table	CREATE TABLE `Product` (
+  `Category` int(11) NOT NULL,
+  `Id` int(11) NOT NULL,
+  `Price` decimal(10,0) DEFAULT NULL,
+  PRIMARY KEY (`Category`,`Id`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SHOW CREATE TABLE Customer;
+Table	Customer
+Create Table	CREATE TABLE `Customer` (
+  `Id` int(11) NOT NULL,
+  PRIMARY KEY (`Id`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;
+CONSTRAINT_CATALOG	def
+CONSTRAINT_SCHEMA	test
+CONSTRAINT_NAME	product_order_ibfk_1
+UNIQUE_CONSTRAINT_CATALOG	def
+UNIQUE_CONSTRAINT_SCHEMA	test
+UNIQUE_CONSTRAINT_NAME	PRIMARY
+MATCH_OPTION	NONE
+UPDATE_RULE	CASCADE
+DELETE_RULE	RESTRICT
+TABLE_NAME	Product_Order
+REFERENCED_TABLE_NAME	Product
+CONSTRAINT_CATALOG	def
+CONSTRAINT_SCHEMA	test
+CONSTRAINT_NAME	product_order_ibfk_2
+UNIQUE_CONSTRAINT_CATALOG	def
+UNIQUE_CONSTRAINT_SCHEMA	test
+UNIQUE_CONSTRAINT_NAME	PRIMARY
+MATCH_OPTION	NONE
+UPDATE_RULE	RESTRICT
+DELETE_RULE	RESTRICT
+TABLE_NAME	Product_Order
+REFERENCED_TABLE_NAME	Customer
+DROP TABLE Product_Order;
+DROP TABLE Product;
+DROP TABLE Customer;
diff --git a/mysql-test/suite/innodb/r/innodb_bug57904.result b/mysql-test/suite/innodb/r/innodb_bug57904.result
index 84868dcf46b..d265adc385f 100755
--- a/mysql-test/suite/innodb/r/innodb_bug57904.result
+++ b/mysql-test/suite/innodb/r/innodb_bug57904.result
@@ -1,16 +1,16 @@
-CREATE TABLE product (category INT NOT NULL, id INT NOT NULL, 
+CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
 price DECIMAL, PRIMARY KEY(category, id)) ENGINE=INNODB;
 CREATE TABLE customer (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
-CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, 
-product_category INT NOT NULL, 
-product_id INT NOT NULL, 
-customer_id INT NOT NULL, 
-PRIMARY KEY(no), 
-INDEX (product_category, product_id), 
-FOREIGN KEY (product_category, product_id) 
-REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, 
-INDEX (customer_id), 
-FOREIGN KEY (customer_id) 
+CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
+product_category INT NOT NULL,
+product_id INT NOT NULL,
+customer_id INT NOT NULL,
+PRIMARY KEY(no),
+INDEX (product_category, product_id),
+FOREIGN KEY (product_category, product_id)
+REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT,
+INDEX (customer_id),
+FOREIGN KEY (customer_id)
 REFERENCES customer(id)
 ) ENGINE=INNODB;
 SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;
diff --git a/mysql-test/suite/innodb/t/innodb_bug57904.test b/mysql-test/suite/innodb/t/innodb_bug57904.test
index d283ad11c3a..1131e24844d 100755
--- a/mysql-test/suite/innodb/t/innodb_bug57904.test
+++ b/mysql-test/suite/innodb/t/innodb_bug57904.test
@@ -3,19 +3,19 @@
 #
 -- source include/have_innodb.inc
 
-CREATE TABLE product (category INT NOT NULL, id INT NOT NULL, 
+CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
 	price DECIMAL, PRIMARY KEY(category, id)) ENGINE=INNODB;
 CREATE TABLE customer (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
-CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, 
-	product_category INT NOT NULL, 
-	product_id INT NOT NULL, 
-	customer_id INT NOT NULL, 
-	PRIMARY KEY(no), 
-	INDEX (product_category, product_id), 
-	FOREIGN KEY (product_category, product_id) 
-		REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, 
-	INDEX (customer_id), 
-	FOREIGN KEY (customer_id) 
+CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
+	product_category INT NOT NULL,
+	product_id INT NOT NULL,
+	customer_id INT NOT NULL,
+	PRIMARY KEY(no),
+	INDEX (product_category, product_id),
+	FOREIGN KEY (product_category, product_id)
+		REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT,
+	INDEX (customer_id),
+	FOREIGN KEY (customer_id)
 		REFERENCES customer(id)
 	) ENGINE=INNODB;
 
diff --git a/mysql-test/t/lowercase_table4.test b/mysql-test/t/lowercase_table4.test
index 93956047145..783a4fcae51 100755
--- a/mysql-test/t/lowercase_table4.test
+++ b/mysql-test/t/lowercase_table4.test
@@ -53,4 +53,56 @@ eval SELECT * FROM XY.T_$tcs LIMIT 1;
 --enable_query_log

 --enable_result_log

 DROP DATABASE XY;

+USE TEST;

+

+--echo #

+--echo # Bug55222 Mysqldump table names case bug in REFERENCES clause

+--echo # InnoDB did not handle lower_case_table_names=2 for

+--echo # foreign_table_names and referenced_table_names.

+--echo #

+

+SHOW VARIABLES LIKE 'lower_case_table_names';

+

+--disable_warnings

+DROP TABLE IF EXISTS `Table2`;

+DROP TABLE IF EXISTS `Table1`;

+--disable_warnings

+

+CREATE TABLE `Table1`(c1 INT PRIMARY KEY) ENGINE=InnoDB;

+CREATE TABLE `Table2`(c1 INT PRIMARY KEY, c2 INT) ENGINE=InnoDB;

+ALTER TABLE `Table2` ADD CONSTRAINT fk1 FOREIGN KEY(c2) REFERENCES `Table1`(c1);

+query_vertical SHOW CREATE TABLE `Table2`;

+query_vertical SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;

+DROP TABLE `Table2`;

+DROP TABLE `Table1`;

+

+--disable_warnings

+DROP TABLE IF EXISTS Product_Order;

+DROP TABLE IF EXISTS Product;

+DROP TABLE IF EXISTS Customer;

+--enable_warnings

+

+CREATE TABLE Product (Category INT NOT NULL, Id INT NOT NULL,

+	Price DECIMAL, PRIMARY KEY(Category, Id)) ENGINE=InnoDB;

+CREATE TABLE Customer (Id INT NOT NULL, PRIMARY KEY (Id)) ENGINE=InnoDB;

+CREATE TABLE Product_Order (No INT NOT NULL AUTO_INCREMENT,

+	Product_Category INT NOT NULL,

+	Product_Id INT NOT NULL,

+	Customer_Id INT NOT NULL,

+	PRIMARY KEY(No),

+	INDEX (Product_Category, Product_Id),

+	FOREIGN KEY (Product_Category, Product_Id)

+		REFERENCES Product(Category, Id) ON UPDATE CASCADE ON DELETE RESTRICT,

+	INDEX (Customer_Id),

+	FOREIGN KEY (Customer_Id)

+		REFERENCES Customer(Id)

+	) ENGINE=INNODB;

+

+query_vertical SHOW CREATE TABLE Product_Order;

+query_vertical SHOW CREATE TABLE Product;

+query_vertical SHOW CREATE TABLE Customer;

+query_vertical SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;

+DROP TABLE Product_Order;

+DROP TABLE Product;

+DROP TABLE Customer;

 

diff --git a/storage/innobase/dict/dict0dict.c b/storage/innobase/dict/dict0dict.c
index 0d15ad8b716..f3660cef611 100644
--- a/storage/innobase/dict/dict0dict.c
+++ b/storage/innobase/dict/dict0dict.c
@@ -52,8 +52,9 @@ UNIV_INTERN dict_index_t*	dict_ind_compact;
 #include "que0que.h"
 #include "rem0cmp.h"
 #include "row0merge.h"
+#include "srv0srv.h" /* srv_lower_case_table_names */
 #include "m_ctype.h" /* my_isspace() */
-#include "ha_prototypes.h" /* innobase_strcasecmp() */
+#include "ha_prototypes.h" /* innobase_strcasecmp(), innobase_casedn_str()*/
 
 #include <ctype.h>
 
@@ -1080,13 +1081,13 @@ dict_table_rename_in_cache(
 			/* Allocate a longer name buffer;
 			TODO: store buf len to save memory */
 
-			foreign->foreign_table_name
-				= mem_heap_alloc(foreign->heap,
-						 ut_strlen(table->name) + 1);
+			foreign->foreign_table_name = mem_heap_strdup(
+				foreign->heap, table->name);
+			dict_mem_foreign_table_name_lookup_set(foreign, TRUE);
+		} else {
+			strcpy(foreign->foreign_table_name, table->name);
+			dict_mem_foreign_table_name_lookup_set(foreign, FALSE);
 		}
-
-		strcpy(foreign->foreign_table_name, table->name);
-
 		if (strchr(foreign->id, '/')) {
 			ulint	db_len;
 			char*	old_id;
@@ -1152,12 +1153,14 @@ dict_table_rename_in_cache(
 			/* Allocate a longer name buffer;
 			TODO: store buf len to save memory */
 
-			foreign->referenced_table_name = mem_heap_alloc(
-				foreign->heap, strlen(table->name) + 1);
+			foreign->referenced_table_name = mem_heap_strdup(
+				foreign->heap, table->name);
+			dict_mem_referenced_table_name_lookup_set(foreign, TRUE);
+		} else {
+			/* Use the same buffer */
+			strcpy(foreign->referenced_table_name, table->name);
+			dict_mem_referenced_table_name_lookup_set(foreign, FALSE);
 		}
-
-		strcpy(foreign->referenced_table_name, table->name);
-
 		foreign = UT_LIST_GET_NEXT(referenced_list, foreign);
 	}
 
@@ -2583,10 +2586,10 @@ dict_foreign_add_to_cache(
 	ut_ad(mutex_own(&(dict_sys->mutex)));
 
 	for_table = dict_table_check_if_in_cache_low(
-		foreign->foreign_table_name);
+		foreign->foreign_table_name_lookup);
 
 	ref_table = dict_table_check_if_in_cache_low(
-		foreign->referenced_table_name);
+		foreign->referenced_table_name_lookup);
 	ut_a(for_table || ref_table);
 
 	if (for_table) {
@@ -3015,19 +3018,25 @@ dict_scan_table_name(
 	memcpy(ref, database_name, database_name_len);
 	ref[database_name_len] = '/';
 	memcpy(ref + database_name_len + 1, table_name, table_name_len + 1);
-#ifndef __WIN__
-	if (srv_lower_case_table_names) {
-#endif /* !__WIN__ */
-		/* The table name is always put to lower case on Windows. */
+
+	/* Values;  0 = Store and compare as given; case sensitive
+	            1 = Store and compare in lower; case insensitive
+	            2 = Store as given, compare in lower; case semi-sensitive */
+	if (srv_lower_case_table_names == 2) {
 		innobase_casedn_str(ref);
-#ifndef __WIN__
+		*table = dict_table_get_low(ref);
+		memcpy(ref, database_name, database_name_len);
+		ref[database_name_len] = '/';
+		memcpy(ref + database_name_len + 1, table_name, table_name_len + 1);
+	} else {
+		if (srv_lower_case_table_names == 1) {
+			innobase_casedn_str(ref);
+		}
+		*table = dict_table_get_low(ref);
 	}
-#endif /* !__WIN__ */
 
 	*success = TRUE;
 	*ref_name = ref;
-	*table = dict_table_get_low(ref);
-
 	return(ptr);
 }
 
@@ -3516,8 +3525,10 @@ col_loop1:
 	}
 
 	foreign->foreign_table = table;
-	foreign->foreign_table_name = mem_heap_strdup(foreign->heap,
-						      table->name);
+	foreign->foreign_table_name = mem_heap_strdup(
+		foreign->heap, table->name);
+	dict_mem_foreign_table_name_lookup_set(foreign, TRUE);
+
 	foreign->foreign_index = index;
 	foreign->n_fields = (unsigned int) i;
 	foreign->foreign_col_names = mem_heap_alloc(foreign->heap,
@@ -3774,8 +3785,9 @@ try_find_index:
 	foreign->referenced_index = index;
 	foreign->referenced_table = referenced_table;
 
-	foreign->referenced_table_name
-		= mem_heap_strdup(foreign->heap, referenced_table_name);
+	foreign->referenced_table_name = mem_heap_strdup(
+		foreign->heap, referenced_table_name);
+	dict_mem_referenced_table_name_lookup_set(foreign, TRUE);
 
 	foreign->referenced_col_names = mem_heap_alloc(foreign->heap,
 						       i * sizeof(void*));
@@ -4586,8 +4598,8 @@ dict_print_info_on_foreign_key_in_create_format(
 
 	fputs(") REFERENCES ", file);
 
-	if (dict_tables_have_same_db(foreign->foreign_table_name,
-				     foreign->referenced_table_name)) {
+	if (dict_tables_have_same_db(foreign->foreign_table_name_lookup,
+				     foreign->referenced_table_name_lookup)) {
 		/* Do not print the database name of the referenced table */
 		ut_print_name(file, trx, TRUE,
 			      dict_remove_db_name(
diff --git a/storage/innobase/dict/dict0load.c b/storage/innobase/dict/dict0load.c
index 5b01669af29..a5f60d8a2f7 100644
--- a/storage/innobase/dict/dict0load.c
+++ b/storage/innobase/dict/dict0load.c
@@ -40,6 +40,7 @@ Created 4/24/1996 Heikki Tuuri
 #include "rem0cmp.h"
 #include "srv0start.h"
 #include "srv0srv.h"
+#include "ha_prototypes.h" /* innobase_casedn_str() */
 
 
 /** Following are six InnoDB system tables */
@@ -435,6 +436,8 @@ dict_process_sys_fields_rec(
 	return(err_msg);
 
 }
+
+#ifdef FOREIGN_NOT_USED
 /********************************************************************//**
 This function parses a SYS_FOREIGN record and populate a dict_foreign_t
 structure with the information from the record. For detail information
@@ -483,13 +486,16 @@ err_len:
 	}
 	foreign->foreign_table_name = mem_heap_strdupl(
 		heap, (const char*) field, len);
+	dict_mem_foreign_table_name_lookup_set(foreign, TRUE);
 
 	field = rec_get_nth_field_old(rec, 4/*REF_NAME*/, &len);
 	if (UNIV_UNLIKELY(len < 1 || len == UNIV_SQL_NULL)) {
 		goto err_len;
 	}
+
 	foreign->referenced_table_name = mem_heap_strdupl(
 		heap, (const char*) field, len);
+	dict_mem_referenced_table_name_lookup_set(foreign, TRUE);
 
 	field = rec_get_nth_field_old(rec, 5/*N_COLS*/, &len);
 	if (UNIV_UNLIKELY(len != 4)) {
@@ -502,6 +508,9 @@ err_len:
 
 	return(NULL);
 }
+#endif  /* FOREIGN_NOT_USED */
+
+#ifdef FOREIGN_NOT_USED
 /********************************************************************//**
 This function parses a SYS_FOREIGN_COLS record and extract necessary
 information from the record and return to caller.
@@ -565,6 +574,8 @@ err_len:
 
 	return(NULL);
 }
+#endif  /* FOREIGN_NOT_USED */
+
 /********************************************************************//**
 Determine the flags of a table described in SYS_TABLES.
 @return compressed page size in kilobytes; or 0 if the tablespace is
@@ -2057,12 +2068,15 @@ dict_load_foreign(
 	foreign->id = mem_heap_strdup(foreign->heap, id);
 
 	field = rec_get_nth_field_old(rec, 3, &len);
+
 	foreign->foreign_table_name = mem_heap_strdupl(
 		foreign->heap, (char*) field, len);
+	dict_mem_foreign_table_name_lookup_set(foreign, TRUE);
 
 	field = rec_get_nth_field_old(rec, 4, &len);
 	foreign->referenced_table_name = mem_heap_strdupl(
 		foreign->heap, (char*) field, len);
+	dict_mem_referenced_table_name_lookup_set(foreign, TRUE);
 
 	btr_pcur_close(&pcur);
 	mtr_commit(&mtr);
@@ -2070,7 +2084,7 @@ dict_load_foreign(
 	dict_load_foreign_cols(id, foreign);
 
 	ref_table = dict_table_check_if_in_cache_low(
-			foreign->referenced_table_name);
+			foreign->referenced_table_name_lookup);
 
 	/* We could possibly wind up in a deep recursive calls if
 	we call dict_table_get_low() again here if there
@@ -2103,7 +2117,7 @@ dict_load_foreign(
 		have to load it so that we are able to make type comparisons
 		in the next function call. */
 
-		for_table = dict_table_get_low(foreign->foreign_table_name);
+		for_table = dict_table_get_low(foreign->foreign_table_name_lookup);
 
 		if (for_table && ref_table && check_recursive) {
 			/* This is to record the longest chain of ancesters
diff --git a/storage/innobase/dict/dict0mem.c b/storage/innobase/dict/dict0mem.c
index bbb8f810f44..32b02e3180f 100644
--- a/storage/innobase/dict/dict0mem.c
+++ b/storage/innobase/dict/dict0mem.c
@@ -33,6 +33,7 @@ Created 1/8/1996 Heikki Tuuri
 #include "data0type.h"
 #include "mach0data.h"
 #include "dict0dict.h"
+#include "srv0srv.h" /* srv_lower_case_table_names */
 #ifndef UNIV_HOTBACKUP
 # include "lock0lock.h"
 #endif /* !UNIV_HOTBACKUP */
@@ -288,6 +289,60 @@ dict_mem_foreign_create(void)
 }
 
 /**********************************************************************//**
+Sets the foreign_table_name_lookup pointer based on the value of
+srv_lower_case_table_names.  If that is 0 or 1, foreign_table_name_lookup
+will point to foreign_table_name.  If 2, then another string is allocated
+of the heap and set to lower case. */
+UNIV_INLINE
+void
+dict_mem_foreign_table_name_lookup_set(
+/*===================================*/
+	dict_foreign_t*	foreign,	/*!< in/out: foreign struct */
+	ibool		do_alloc)	/*!< in: is an alloc needed */
+{
+	if (srv_lower_case_table_names == 2) {
+		if (do_alloc) {
+			foreign->foreign_table_name_lookup = mem_heap_alloc(
+				foreign->heap,
+				strlen(foreign->foreign_table_name) + 1);
+		}
+		strcpy(foreign->foreign_table_name_lookup,
+		       foreign->foreign_table_name);
+		innobase_casedn_str(foreign->foreign_table_name_lookup);
+	} else {
+		foreign->foreign_table_name_lookup
+			= foreign->foreign_table_name;
+	}
+}
+
+/**********************************************************************//**
+Sets the referenced_table_name_lookup pointer based on the value of
+srv_lower_case_table_names.  If that is 0 or 1,
+referenced_table_name_lookup will point to referenced_table_name.  If 2,
+then another string is allocated of the heap and set to lower case. */
+UNIV_INLINE
+void
+dict_mem_referenced_table_name_lookup_set(
+/*======================================*/
+	dict_foreign_t*	foreign,	/*!< in/out: foreign struct */
+	ibool		do_alloc)	/*!< in: is an alloc needed */
+{
+	if (srv_lower_case_table_names == 2) {
+		if (do_alloc) {
+			foreign->referenced_table_name_lookup = mem_heap_alloc(
+				foreign->heap,
+				strlen(foreign->referenced_table_name) + 1);
+		}
+		strcpy(foreign->referenced_table_name_lookup,
+		       foreign->referenced_table_name);
+		innobase_casedn_str(foreign->referenced_table_name_lookup);
+	} else {
+		foreign->referenced_table_name_lookup
+			= foreign->referenced_table_name;
+	}
+}
+
+/**********************************************************************//**
 Adds a field definition to an index. NOTE: does not take a copy
 of the column name if the field is a column. The memory occupied
 by the column name may be released only after publishing the index. */
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 705ab6c8df3..5c7301d453c 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -3639,6 +3639,7 @@ ha_innobase::open(
 	UT_NOT_USED(test_if_locked);
 
 	thd = ha_thd();
+	srv_lower_case_table_names = lower_case_table_names;
 
 	/* Under some cases MySQL seems to call this function while
 	holding btr_search_latch. This breaks the latching order as
@@ -6750,11 +6751,7 @@ ha_innobase::create(
 
 	trx = innobase_trx_allocate(thd);
 
-	if (lower_case_table_names) {
-		srv_lower_case_table_names = TRUE;
-	} else {
-		srv_lower_case_table_names = FALSE;
-	}
+	srv_lower_case_table_names = lower_case_table_names;
 
 	strcpy(name2, name);
 
@@ -7179,11 +7176,7 @@ ha_innobase::delete_table(
 
 	trx = innobase_trx_allocate(thd);
 
-	if (lower_case_table_names) {
-		srv_lower_case_table_names = TRUE;
-	} else {
-		srv_lower_case_table_names = FALSE;
-	}
+	srv_lower_case_table_names = lower_case_table_names;
 
 	name_len = strlen(name);
 
@@ -7306,11 +7299,7 @@ innobase_rename_table(
 	char*	norm_to;
 	char*	norm_from;
 
-	if (lower_case_table_names) {
-		srv_lower_case_table_names = TRUE;
-	} else {
-		srv_lower_case_table_names = FALSE;
-	}
+	srv_lower_case_table_names = lower_case_table_names;
 
 	// Magic number 64 arbitrary
 	norm_to = (char*) my_malloc(strlen(to) + 64, MYF(0));
diff --git a/storage/innobase/include/dict0mem.h b/storage/innobase/include/dict0mem.h
index d448e57e395..7526e4dc8dc 100644
--- a/storage/innobase/include/dict0mem.h
+++ b/storage/innobase/include/dict0mem.h
@@ -238,6 +238,26 @@ dict_foreign_t*
 dict_mem_foreign_create(void);
 /*=========================*/
 
+/**********************************************************************//**
+Sets the foreign_table_name_lookup pointer based on the value of
+srv_lower_case_table_names. */
+UNIV_INTERN
+void
+dict_mem_foreign_table_name_lookup_set(
+/*===================================*/
+	dict_foreign_t*	foreign,	/*!< in/out: foreign struct */
+	ibool		do_alloc);	/*!< in: is an alloc needed */
+
+/**********************************************************************//**
+Sets the reference_table_name_lookup pointer based on the value of
+srv_lower_case_table_names. */
+UNIV_INTERN
+void
+dict_mem_referenced_table_name_lookup_set(
+/*======================================*/
+	dict_foreign_t*	foreign,	/*!< in/out: foreign struct */
+	ibool		do_alloc);	/*!< in: is an alloc needed */
+
 /** Data structure for a column in a table */
 struct dict_col_struct{
 	/*----------------------*/
@@ -393,10 +413,14 @@ struct dict_foreign_struct{
 	unsigned	type:6;		/*!< 0 or DICT_FOREIGN_ON_DELETE_CASCADE
 					or DICT_FOREIGN_ON_DELETE_SET_NULL */
 	char*		foreign_table_name;/*!< foreign table name */
+	char*		foreign_table_name_lookup;
+				/*!< foreign table name used for dict lookup */
 	dict_table_t*	foreign_table;	/*!< table where the foreign key is */
 	const char**	foreign_col_names;/*!< names of the columns in the
 					foreign key */
 	char*		referenced_table_name;/*!< referenced table name */
+	char*		referenced_table_name_lookup;
+				/*!< referenced table name for dict lookup*/
 	dict_table_t*	referenced_table;/*!< table where the referenced key
 					is */
 	const char**	referenced_col_names;/*!< names of the referenced
diff --git a/storage/innobase/include/srv0srv.h b/storage/innobase/include/srv0srv.h
index 98b07f5e893..30e3648576a 100644
--- a/storage/innobase/include/srv0srv.h
+++ b/storage/innobase/include/srv0srv.h
@@ -71,8 +71,8 @@ at a time */
 #define SRV_AUTO_EXTEND_INCREMENT	\
 	(srv_auto_extend_increment * ((1024 * 1024) / UNIV_PAGE_SIZE))
 
-/* This is set to TRUE if the MySQL user has set it in MySQL */
-extern ibool	srv_lower_case_table_names;
+/* This is set to the MySQL server value for this variable. */
+extern uint	srv_lower_case_table_names;
 
 /* Mutex for locking srv_monitor_file */
 extern mutex_t	srv_monitor_file_mutex;
diff --git a/storage/innobase/row/row0ins.c b/storage/innobase/row/row0ins.c
index 09ae80adff4..dbfba358f9f 100644
--- a/storage/innobase/row/row0ins.c
+++ b/storage/innobase/row/row0ins.c
@@ -1525,7 +1525,7 @@ row_ins_check_foreign_constraints(
 		if (foreign->foreign_index == index) {
 
 			if (foreign->referenced_table == NULL) {
-				dict_table_get(foreign->referenced_table_name,
+				dict_table_get(foreign->referenced_table_name_lookup,
 					       FALSE);
 			}
 
diff --git a/storage/innobase/row/row0mysql.c b/storage/innobase/row/row0mysql.c
index 709750fc4e7..69d93367297 100644
--- a/storage/innobase/row/row0mysql.c
+++ b/storage/innobase/row/row0mysql.c
@@ -3163,7 +3163,7 @@ check_next_foreign:
 
 	if (foreign && trx->check_foreigns
 	    && !(drop_db && dict_tables_have_same_db(
-			 name, foreign->foreign_table_name))) {
+			 name, foreign->foreign_table_name_lookup))) {
 		FILE*	ef	= dict_foreign_err_file;
 
 		/* We only allow dropping a referenced table if
diff --git a/storage/innobase/row/row0upd.c b/storage/innobase/row/row0upd.c
index 81750938f58..45fa8538a87 100644
--- a/storage/innobase/row/row0upd.c
+++ b/storage/innobase/row/row0upd.c
@@ -238,7 +238,7 @@ row_upd_check_references_constraints(
 				foreign->n_fields))) {
 
 			if (foreign->foreign_table == NULL) {
-				dict_table_get(foreign->foreign_table_name,
+				dict_table_get(foreign->foreign_table_name_lookup,
 					       FALSE);
 			}
 
diff --git a/storage/innobase/srv/srv0srv.c b/storage/innobase/srv/srv0srv.c
index 0f6d6c95739..6e9fc6ab1c7 100644
--- a/storage/innobase/srv/srv0srv.c
+++ b/storage/innobase/srv/srv0srv.c
@@ -87,9 +87,11 @@ Created 10/8/1995 Heikki Tuuri
 #include "mysql/plugin.h"
 #include "mysql/service_thd_wait.h"
 
-/* This is set to TRUE if the MySQL user has set it in MySQL; currently
-affects only FOREIGN KEY definition parsing */
-UNIV_INTERN ibool	srv_lower_case_table_names	= FALSE;
+/* This is set to the MySQL server value for this variable.  It is only
+needed for FOREIGN KEY definition parsing since FOREIGN KEY names are not
+stored in the server metadata. The server stores and enforces it for
+regular database and table names.*/
+UNIV_INTERN uint	srv_lower_case_table_names	= 0;
 
 /* The following counter is incremented whenever there is some user activity
 in the server */