MySQL Bugs: #80817: mysqldump outputs incorrect table case for triggers

Bug #80817	mysqldump outputs incorrect table case for triggers
Submitted:	22 Mar 2016 6:03	Modified:	12 Jun 2016 7:03
Reporter:	Mike Lewis	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.6	OS:	MacOS (10.11.3)
Assigned to:		CPU Architecture:	Any

Description:
I have a database (using InnoDB as the storage engine) that I am attempting to move in an automated script from my development machine (OS X 10.11.3) to a testing machine (Ubuntu 12.04). Both machines are running MySQL 5.6.29, with the development machine having its lower_case_table_names setting at 2, and the testing machine having it set at 0 (as recommended in the documentation to preserve database and table capitalization).

When dumping the development database, all table names are dumped in the correct case for things like table structure and data insertion, but NOT for creating triggers. For example, a dump would look something like this:

CREATE TABLE `Table Name` ...

INSERT INTO `Table Name` ...

CREATE TRIGGER `Trigger Name` ... ON `table name`

This different case does not affect re-importing the dump on a case-insensitive system, but prevents the dump from being imported as-is on a case-sensitive system like the testing machine running Ubuntu. As a workaround, I am currently searching through the dump file for trigger table names, and replacing them with the result of a "show tables like 'table name'" command, but this is an imperfect solution, especially when trying to script it across multiple platforms (including Windows).

How to repeat:
1. Create a table with upper-case characters on a case-insensitive system (OS X, for instance)

2. Create a trigger on that table

3. Dump the table using mysqldump

4. Attempt to import the dump on a case-sensitive system (Ubuntu, for instance)

Suggested fix:
Use the same process for generating the table names for triggers as is done for table creation and data insertion. Those write the correct case to the dump file, and only triggers get written as lower-case.

Hello Mike,

Thank you for the report.
I'm not seeing this issue at my end(Mac OS X 10.11.3, MySQL 5.6.30) with the exact repeatable steps. Could you please pass on exact test case to see if this is repeatable at our end?

--
umshastr:mysql-5.6.30 umshastr$ bin/mysql -uroot -S data/mysql_ushastry.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.30 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'lower_case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 2     |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql> use test
Database changed
mysql> CREATE TABLE AccountTable (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TRIGGER TriggerName BEFORE INSERT ON AccountTable FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.35 sec)

mysql> \q
Bye
umshastr:mysql-5.6.30 umshastr$ bin/mysqldump -uroot -S data/mysql_ushastry.sock --databases test > dmp.sql 

-- dump file has

--
-- Table structure for table `AccountTable`
--

DROP TABLE IF EXISTS `AccountTable`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `AccountTable` (
  `acct_num` int(11) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `AccountTable`
--

LOCK TABLES `AccountTable` WRITE;
/*!40000 ALTER TABLE `AccountTable` DISABLE KEYS */;
/*!40000 ALTER TABLE `AccountTable` ENABLE KEYS */;
UNLOCK TABLES;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER TriggerName BEFORE INSERT ON AccountTable FOR EACH ROW SET @sum = @sum + NEW.amount */;;
DELIMITER ;

Thanks,
Umesh

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".