Database backup doesn't include triggers

[domeglic]

Preconditions

Magento CE 2.1.5, PHP 7.0.15, MariaDB 10.0.29.
Have Index in scheduled mode.

Steps to reproduce

1. Do a database backup with setup:backup --db.
2. Do a database restore with setup:rollback -d.

Expected result

Database is restored and includes triggers.

Actual result

Database is missing triggers, so indexing doesn't work correctly.

I noticed this problem after several product changes failed to show in the front. I realized the index was not updated and after some debugging found that there were no triggers.
I tried switching index mode to realtime and saving the product so the changes would appear. When switching back to scheduled the triggers were recreated.
I could reliably reproduce the problem afterwards which leads me to assume the problem is in the backup CLI command.

Short way to reproduce:

1. Magento CE 2.1.8
2. Create empty test trigger:
   mysql> CREATE TRIGGER test_trigger AFTER INSERT ON catalog_product_entity FOR EACH ROW BEGIN END;
3. To make sure that trigger was created:
   mysql> SHOW TRIGGERS;
4. create backup
   php bin/magento setup:backup --db.
5. Create New DB:
   CREATE DATABASE magento_backup;
6. import data form backup
   mysql -u root -p magento_backup < "/var/backups/1506349239_db.sql"
7. Show trigers from buckup:
   mysql> SHOW TRIGGERS from magento_backup;

Expected result

magento_backup have trigger "test_trigger"

Actual result

magento_backup do not have trigger "test_trigger"

[Stas94]

@domeglic Please add more details to your description of the steps you followed when identifying this issue. Screenshots or logs would be helpful, too.

[domeglic]

I have added some description but I don't really understand why, the problem seems easy enough to understand and reproduce.

[magento-engcom-team]

We updated description:

Short way to reproduce:

1. Magento CE 2.1.8
2. Create empty test trigger:
   mysql> CREATE TRIGGER test_trigger AFTER INSERT ON catalog_product_entity FOR EACH ROW BEGIN END;
3. To make sure that trigger was created:
   mysql> SHOW TRIGGERS;
4. create backup
   php bin/magento setup:backup --db.
5. Create New DB:
   CREATE DATABASE magento_backup;
6. import data form backup
   mysql -u root -p magento_backup < "/var/backups/1506349239_db.sql"
7. Show trigers from buckup:
   mysql> SHOW TRIGGERS from magento_backup;

Expected result

magento_backup have trigger "test_trigger"

Actual result

magento_backup do not have trigger "test_trigger"

[magento-engcom-team]

@domeglic, thank you for your report.
We've created internal ticket(s) MAGETWO-78619 to track progress on the issue.

[denisristic]

I'm working on it #SQUASHTOBERFEST

[denisristic]

@ishakhsuvarov this ticket is resolved :)

[magento-team]

Hi @domeglic. Thank you for your report.
The issue has been fixed in #11369 by @denisristic in 2.2-develop branch
Related commit(s):

• 3255857
• f1bcf38

The fix will be available with the upcoming patch release.

[magento-engcom-team]

Hi @domeglic. Thank you for your report.
The issue has been fixed in magento-engcom/magento2ce#1361 by @magento-engcom-team in 2.3-develop branch
Related commit(s):

• 716f9af
• 595527a
• 8c05a29
• 63958cd
• 78c2c21
• 7f2dc29
• aee7458
• 92f8586
• bf178ca
• ecafa80
• a15208e
• 76716b8
• 490785f
• 6fe6280
• 33e40ec
• 7b1bec8
• dbe5fc7
• 22c6937
• ca7da3f
• b55a5d1
• 65b745f
• b0a5a52
• 97dd9ad
• aa28366
• 0fc86d8
• f11994a
• 04445d0

The fix will be available with the upcoming 2.3.0 release.