Magento2: 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 magento/magento2#11369 by @denisristic in 2.2-develop branch
Related commit(s):

• 32558577099d2cdc3dd56cf5d84f814ab3184987
• f1bcf38e7ce0202bde7be843ba1c46d20687ab43

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):

• 716f9afd694b6d7e5423bd2dbdabeeb45797886b
• 595527a919eafd309f8b2a1d1339b312f6546f7b
• 8c05a29b711ca03ecb866fc384122b3e46c08ccd
• 63958cdc14bf3b44ad5fd95aafc626d8e25ca3e3
• 78c2c2159b6c5aacc27e4fc918a1ed56a9df9b5f
• 7f2dc2965a2d46e935770a7acdef8f260bd5fdc6
• aee74586a9d28c1f47fd2ba3bae5e5871b779be9
• 92f85860ce9725e34922a523e5137e656322c394
• bf178ca5c3c10a50e3eba6e64fdf905e75d0b4c5
• ecafa804c26054499b42c6820f0996035eb39dbc
• a15208e96c06248d740ee65d154eda30226c848a
• 76716b84a92f009253039cd06b9e47367384655f
• 490785fffd885f85a7292c9144783cf2576b6b66
• 6fe62802f5a529cd113697df79021bdb1dda9f53
• 33e40ece8246363429be614440622600394a4516
• 7b1bec8edb2eedea399eb78f891f1d6873cb1955
• dbe5fc792279802f83d7110285c15fb2eede8d95
• 22c6937f6db6a2ed17188f24097e0f9a194dc295
• ca7da3fd58edca05b70a2af1768743ffc0294b72
• b55a5d1a497279f72735e577ba60327dba178b6e
• 65b745f7a94b2ba699a68f2825a63c9d78615f1c
• b0a5a52df60a765748f15a7e0391f6b50535de21
• 97dd9ad59c7d6bf9d4cf673a8e22708c480b1967
• aa28366cc7378a972497e186fa8fb955684f74aa
• 0fc86d89ab491fe7fc246d025662658844cd15a4
• f11994acc1e220eb620d3baa63da8427d53e5797
• 04445d07887870ba47dd2e97006c94d656f85942

The fix will be available with the upcoming 2.3.0 release.