Bug #95361 Mysqldump includes database name on create trigger statements.
Submitted: 13 May 2019 19:57 Modified: 14 May 2019 11:09
Reporter: Leonardo Fernandes Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.7, 5.7.26, 5.6.44 OS:Any
Assigned to: CPU Architecture:Any

[13 May 2019 19:57] Leonardo Fernandes
Description:
If a trigger was created specifying "CREATE TRIGGER <database_name>.<trigger_name>", on mysqldump the create trigger statement will include <database_name>.<trigger_name>. 

The "SHOW CREATE TRIGGER <trigger_name>" will also display the CREATE TRIGGER with <database_name<.<trigger_name>.

This is different if you create the trigger using only "CREATE TRIGGER <trigger_name>.

This behavior is also different on MySQL 8.0, in which no matter how you create the trigger, the mysqldump file only uses <trigger_name> on the create trigger statement.

How to repeat:
On MySQL 5.7: 

mysql> create database if not exists test; 
mysql> use test ;
mysql> create table test (id int(1));
mysql> delimiter ;;
mysql> CREATE TRIGGER `test`.`test_name` AFTER UPDATE ON `test` FOR EACH ROW BEGIN insert into test values (); END ;;
mysql> delimiter ; 

mysql> delimiter ;;
mysql> CREATE TRIGGER `test_name2` AFTER UPDATE ON `test` FOR EACH ROW BEGIN insert into test values (); END ;;
mysql> delimiter ; 

mysql> show create trigger test_name\G
mysql> show create trigger test_name2\G

shell> mysqldump --no-data --no-create-db --skip-opt --triggers --no-create-info test > test_trigger.sql

shell> cat test_trigger.sql | grep "TRIGGER"
/*!50003 CREATE*/ /*!50017 DEFINER=`msandbox`@`localhost`*/ /*!50003 TRIGGER `test`.`test_name` AFTER UPDATE ON `test` FOR EACH ROW BEGIN insert into test values (); END */;;
/*!50003 CREATE*/ /*!50017 DEFINER=`msandbox`@`localhost`*/ /*!50003 TRIGGER `test_name2` AFTER UPDATE ON `test` FOR EACH ROW BEGIN insert into test values (); END */;;

Suggested fix:
Remove the database_name from the create trigger statement on dumps generated with mysqldump, equalizing behavior with 8.0.
[14 May 2019 11:09] Umesh Shastry
Hello Leonardo,

Thank you for the report.

regards,
Umesh