search

dotmailer / dotmailer-magento2-extension

The official Dotdigital for Magento2 extension
https://dotdigital.com/integrations/magento
MIT License
48 stars 63 forks source link

Cannot drop column coupon_id #584

Closed asuchalkin-space48 closed 2 years ago

asuchalkin-space48 commented 2 years ago

After upgrading Magento EE from version 2.3.4 to 2.4.4 and Dotmailer extension from version 4.3.3 to 4.17.0 on setup:upgrade Magento fails with error

SQLSTATE[HY000]: General error: 1828 Cannot drop column 'coupon_id': needed in a foreign key constraint 'magento/EMAIL_COUPON_ATTRIBUTE_COUPON_ID_SALESRULE_COUPON_COUPON_ID', query was: ALTER TABLEemail_coupon_attributeADD COLUMNexpires_attimestamp NULL COMMENT "Coupon expiration date", DROP COLUMNcoupon_id, ADD CONSTRAINTEMAIL_COUPON_ATTR_SALESRULE_COUPON_ID_SALESRULE_COUPON_COUPON_IDFOREIGN KEY (salesrule_coupon_id) REFERENCESsalesrule_coupon(coupon_id) ON DELETE CASCADE, ADD INDEXEMAIL_COUPON_ATTRIBUTE_SALESRULE_COUPON_ID(salesrule_coupon_id)

image

Looking at the db_schema.xml Magento tryies to remove coupon_id field (because it was removed from table declaration), but fails becaues of foreign key constaints

Currently workaround is to patch db_schema.xml file and keep coupon_id field.

sta1r commented 2 years ago

@asuchalkin-space48 Thanks for raising this. We'll investigate and report back.

sta1r commented 2 years ago

Hi again, I investigated the history here:

4.3.6 coupon_id column changed (in UpgradeSchema) to salesrule_coupon_id in email_coupon_attribute table [https://github.com/dotmailer/dotmailer-magento2-extension/commit/fa88f72d84bc5046d2e800e6c93a36f75c93339a]

4.14.0 schema converted to declarative schema (coupon_id not in db_schema.xml but salesrule_coupon_id has a migrateDataFrom(coupon_id) AND coupon_id is listed in db_schema_whitelist.json per best practice for previously-removed columns.

When merchants make this big jump (4.3.3 > 4.17.0), the conversion wizard apparently tries to drop the coupon_id column before removing the (legacy) constraint between coupon_id and salesrule_coupon.coupon_id. We can see from the error message that the updated constraint is going to be added, but the column drop happens first, and this triggers the error.

i.e. 

query was: ALTER TABLE email_coupon_attribute ADD COLUMN expires_at timestamp NULL COMMENT "Coupon expiration date", DROP COLUMN coupon_id, ADD CONSTRAINT EMAIL_COUPON_ATTR_SALESRULE_COUPON_ID_SALESRULE_COUPON_COUPON_ID

The ADD CONSTRAINT happens after the drop column, and it can't drop the column because of the pre-existing constraint.

I'm not clear what we can do to improve this for merchants like yourself. One could argue that we should have retained the old column (marking it as deprecated) BUT we couldn't do this because of the problem we were having with the collision between our coupon_id column and the core sales_rule table's coupon_id column (see commit message description linked above).

I think if you were to remove your db_schema.xml patch and re-run setup:upgrade, the process should complete without error - however do try this on a staging env first.

I appreciate this doesn't exactly 'solve' your issue, and I'm sorry you've had this headache! The advice to any other merchants experiencing this same issue would be to upgrade to 4.3.6 first, then fast forward to the latest version.