ifsnop / mysqldump-php

PHP version of mysqldump cli that comes with MySQL
https://github.com/ifsnop/mysqldump-php
GNU General Public License v3.0
1.25k stars 300 forks source link

Delete database name on exported triggers #268

Closed freyca closed 2 months ago

freyca commented 1 year ago

Triggers are listed with subsequent command:

SHOW CREATE TRIGGER triggername

I give you the output of a statement creation:

CREATE DEFINER=`user`@`localhost` TRIGGER `dbname`.`after_delete_module` AFTER DELETE ON `dbname`.`fs_module` FOR EACH ROW BEGIN
                    DECLARE module_id_removed INT(11);
                    SELECT id_payment INTO module_id_removed FROM fs_opc_payment WHERE id_module = OLD.id_module;
                    DELETE FROM fs_opc_payment WHERE id_payment = module_id_removed;
                    DELETE FROM fs_opc_payment_lang WHERE id_payment = module_id_removed;
                    DELETE FROM fs_opc_payment_shop WHERE id_payment = module_id_removed;
                END

The part about the definer gets nicely treated by the exporter, and get's rid of it when skip-definers is set to true.

The issue comes with the part calling 'dbname'. When trying to import the file on another database, if the name of the database is not the same, the trigger can't be created.

That's that the exporter has swote in sql file:

DROP TRIGGER IF EXISTS `after_delete_module`;
DELIMITER ;;
/*!50003 CREATE*/ /*!50003 TRIGGER `dbname`.`after_delete_module` AFTER DELETE ON `dbname`.`fs_module` FOR EACH ROW BEGIN
                    DECLARE module_id_removed INT(11);
                    SELECT id_payment INTO module_id_removed FROM fs_opc_payment WHERE id_module = OLD.id_module;
                    DELETE FROM fs_opc_payment WHERE id_payment = module_id_removed;
                    DELETE FROM fs_opc_payment_lang WHERE id_payment = module_id_removed;
                    DELETE FROM fs_opc_payment_shop WHERE id_payment = module_id_removed;
                END */;;
DELIMITER ;

Any chances to improvement? Something like this will import nicely -it has been tested-:

DROP TRIGGER IF EXISTS `after_delete_module`;
DELIMITER ;;
/*!50003 CREATE*/ /*!50003 TRIGGER `after_delete_module` AFTER DELETE ON `fs_module` FOR EACH ROW BEGIN
                    DECLARE module_id_removed INT(11);
                    SELECT id_payment INTO module_id_removed FROM fs_opc_payment WHERE id_module = OLD.id_module;
                    DELETE FROM fs_opc_payment WHERE id_payment = module_id_removed;
                    DELETE FROM fs_opc_payment_lang WHERE id_payment = module_id_removed;
                    DELETE FROM fs_opc_payment_shop WHERE id_payment = module_id_removed;
                END */;;
DELIMITER ;
ifsnop commented 1 year ago

Hi, as far as I tested, omitting database name will lead to the creation of a trigger that will match all tables ignoring in which dB they are. I'm not sure that this will work as you want.