Migration payment_intent_unique_on_transaction takes too long if indexes are deleted

[jep-a]

Description

Related #185

When running the update from 2.3.2.1 to 2.4.2 on our production database which has:

• 23,000 rows in craft_stripe_paymentintents
• 950,000 rows in craft_commerce_transactions

m210903_040320_payment_intent_unique_on_transaction takes more than 3 hours on my local when running its running this SQL command #L34-L38

I noticed before this SQL update in the migration, it deletes all the indexes on the stripe_paymentintents table, I tried running the same SQL update without deleting the indexes and it takes 13 seconds.

Is there any reason those indexes are deleted before the update? It's blocking our team from updating commerce-stripe since the migration would theoretically block our prod db for hours.

Steps to reproduce

Can send my database privately if needed

1. Populate craft_stripe_paymentintents with 23,000 rows
2. Populate craft_commerce_transactions with 950,000 rows
3. Run m210903_040320_payment_intent_unique_on_transaction and it will take super long (~3+ hours)

Additional info

• Craft CMS version: 3.7.27.2
• Stripe for Craft Commerce version: 2.3.2.1 (attempting update to 2.4.2)
• PHP version: 7.3.33
• Database driver & version: MySQL 5.7.34
• Plugins & versions:

Amazon S3 1.3.0
Cookies 1.1.15
Craft Commerce 3.4.10.1
Doxter 3.5.1
Instant Analytics 1.1.14
Linkit 1.1.12.1
oEmbed 1.3.13
Redactor 2.8.8
Retour 3.1.70
SEOmatic 3.4.22
Similar 1.1.5
Stripe for Craft Commerce 2.3.2.1

[paul-blundell]

We just came across the exact same issue.
Upgrading from 2.3.2.1 to 2.4.2 is taking way too long and in some cases timing out. Our craft_stripe_paymentintents table has about 9,000 records.
This will be impossible to deploy to production like this, do you have any solution?

If I move these lines in commerce-stripe/src/migrations/m210903_040320_payment_intent_unique_on_transaction.php to after the UPDATE execute and before it adds them back, then it runs in just seconds.

MigrationHelper::dropAllForeignKeysOnTable('{{%stripe_paymentintents}}', $this);
MigrationHelper::dropAllIndexesOnTable('{{%stripe_paymentintents}}', $this);

Is there a particular reason it needs to happen in this order?
What are the risks of doing this?

[emarthinsen]

Oof. We're running into this as well. About 30k payment intents and 56k transactions. It's been running for over 30 minutes. The query that's taking so long is this one:

UPDATE `craft_stripe_paymentintents` `pi`
INNER JOIN `craft_commerce_transactions` `t` ON `t`.`reference` = `pi`.`reference`
SET `pi`.`transactionHash` = `t`.`hash`
WHERE `pi`.`transactionHash` IS NULL;

The rub is that we don't have any records where pi.transactionHash IS NULL.

[jep-a]

Our team ended up running this migration manually with this script that marks this migration as done to skip it and mimics the migration with database commands. This ran pretty fast, took a few minutes.

No problems ever since running this a few months ago.

USE database;


## Add migration row for skipping

INSERT INTO craft_migrations (track, name, applyTime, dateCreated, dateUpdated, uid) VALUES ('plugin:commerce-stripe', 'm210903_040320_payment_intent_unique_on_transaction', NOW(), NOW(), NOW(), UUID())


## Add transactionHash column

ALTER TABLE craft_stripe_paymentintents
ADD transactionHash varchar(255) null
AFTER orderId;


## Join transactionHashes

UPDATE craft_stripe_paymentintents pi
INNER JOIN craft_commerce_transactions t ON t.reference = pi.reference
SET pi.transactionHash = t.hash
WHERE pi.transactionHash IS NULL;


## Drop foreign keys

alter table craft_stripe_paymentintents drop foreign key craft_stripe_paymentintents_customerId_fk;
alter table craft_stripe_paymentintents drop foreign key craft_stripe_paymentintents_gatewayId_fk;
alter table craft_stripe_paymentintents drop foreign key craft_stripe_paymentintents_orderId_fk;


## Drop indexes

drop index craft_stripe_paymentintents_customerId_fk on craft_stripe_paymentintents;
drop index craft_stripe_paymentintents_gatewayId_fk on craft_stripe_paymentintents;
drop index craft_stripe_paymentintents_orderId_gatewayId_customerId_unq_idx on craft_stripe_paymentintents;
drop index craft_stripe_paymentintents_reference_unq_idx on craft_stripe_paymentintents;


## Add foreign keys

alter table craft_stripe_paymentintents
	add constraint craft_stripe_paymentintents_gatewayId_fk
		foreign key (gatewayId) references craft_commerce_gateways (id)
			on delete cascade;

alter table craft_stripe_paymentintents
	add constraint craft_stripe_paymentintents_customerId_fk
	    foreign key (customerId) references craft_stripe_customers (id)
		    on delete cascade;

alter table craft_stripe_paymentintents
	add constraint craft_stripe_paymentintents_orderId_fk
		foreign key (orderId) references craft_commerce_orders (id)
			on delete cascade;


## Add indexes

CREATE UNIQUE INDEX craft_stripe_paymentintents_reference_unq_idx
ON craft_stripe_paymentintents (reference);

## Add back old index but include transactionHash

CREATE UNIQUE INDEX craft_idx_pi_orderId_gtId_custId_transHash
ON craft_stripe_paymentintents (orderId ASC, gatewayId ASC, customerId ASC, transactionHash ASC);