Although the setting set to dump triggers the output is rather useless and sql file can't be restored.
My settings - see in attachment.
Let's say I have a trigger in "##_book_comments_replays" table that defined in database like this:
(Using PHPAdmin export)
After creating SQL backup with such table and trigger I get a .sql file containing:
And that's all , not only it does not contain the trigger content , the 2nd line also create an error preventing SQL file import using :
when restore.sql:
The error is like this:
But this syntax is a least problem as it can be fixed manually, the bigger problem is as I said that the backup does not include trigger at all.
My settings - see in attachment.
Let's say I have a trigger in "##_book_comments_replays" table that defined in database like this:
CREATE TRIGGER `DecrementParentCommentWhenReplayDeleted` AFTER DELETE ON `ml_maxlib_book_comments_replays`
FOR EACH ROW BEGIN
DECLARE currentCount INT(11);
DECLARE recordExists INT(11);
-- check current value of replay count and if parent record exist at all
SELECT ml_maxlib_book_comments.replay_count, COUNT(*) INTO currentCount, recordExists FROM ml_maxlib_book_comments WHERE ml_maxlib_book_comments.comment_id = OLD.comment_id LOCK IN SHARE MODE;
IF (recordExists > 0)
THEN
-- decrement replay count
SET currentCount = currentCount -1;
-- if we get negative value it means something wrong with consistancy
IF (currentCount < 0)
THEN
-- so need to redetect real number of reply counts, this is longer so preffer to do it only here
SELECT COUNT(1) INTO currentCount FROM ml_maxlib_book_comments_replays WHERE ml_maxlib_book_comments_replays.comment_id = OLD.comment_id;
END IF;
-- put new count in
UPDATE ml_maxlib_book_comments SET ml_maxlib_book_comments.replay_count = currentCount WHERE ml_maxlib_book_comments.comment_id = OLD.comment_id;
END IF;
END
(Using PHPAdmin export)
After creating SQL backup with such table and trigger I get a .sql file containing:
/**ABDB**/DROP TRIGGER IF EXISTS `IncrementParentCommentWithNewReplay`;
/**ABDB**/CREATE TRIGGER `IncrementParentCommentWithNewReplay` AFTER INSERT ON `ml_maxlib_book_comments_replays`;;
And that's all , not only it does not contain the trigger content , the 2nd line also create an error preventing SQL file import using :
mysql.exe -uroot -hlocalhost < restore.sql
when restore.sql:
USE maxima
SET names 'utf8';
SOURCE mydbfile.sql;
The error is like this:
ERROR 1064 (42000) at line 359434 in file: 'mydbfile.sql': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
But this syntax is a least problem as it can be fixed manually, the bigger problem is as I said that the backup does not include trigger at all.
