search

pronamic / wp-pronamic-pay

The Pronamic Pay plugin allows you to easily accept payments with payment methods like credit card, iDEAL, Bancontact and Sofort through a variety of payment providers on your WordPress website.
https://pronamicpay.com
34 stars 14 forks source link

Subscriptions follow-up payments problem due to slow database / lots of metadata #212

Closed remcotolsma closed 2 years ago

remcotolsma commented 2 years ago

We have a customer with many payments and subscriptions where the start-up of the follow-up payments is not going well. At first, the slow server/database seemed to be causing problems, but adding a temporary index on the wp_postmeta.meta_value may have played a role as well. However, this issue may be a signal that not all WordPress installations are stable enough to initiate subscription follow-up payments. I don't think it's the first time that this doesn't run as smoothly as we'd like. The WordPress cron doesn't always seem to work well for this. The big question is how are we going to fix/improve this? Is it better to use the Mollie Subscriptions API?

Internal HelpScout ticket: https://secure.helpscout.net/conversation/1637278281/22756?folderId=1425710

remcotolsma commented 2 years ago

This issue is not completely new, we have the following internal Basecamp to-do open for this: "Abonnementen » Dubbele betalingen mogelijk bij dubbele cron". A possible solution is also to make more use of custom tables and to lock rows while starting up a follow-up payment.

remcotolsma commented 2 years ago

We wrote 2 Mollie CLI commands to help cancel payments.

wp pronamic-pay mollie payments list --api_key=live_●●●●●●●●●●●●●●●●●●●●●●●●●●●●●● --is_cancelable --format=ids`
wp pronamic-pay mollie payments cancel $( wp pronamic-pay mollie payments list --api_key=live_●●●●●●●●●●●●●●●●●●●●●●●●●●●●●● --is_cancelable --format=ids ) --api_key=live_●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●
remcotolsma commented 2 years ago

Option 1 - Switch to Mollie Subscriptions API

Advantages Mollie Subscriptions API

Disadvantages Mollie Subscriptions API

Are there any real drawbacks?

Other options?

rvdsteege commented 2 years ago

Disadvantages Mollie Subscriptions API

remcotolsma commented 2 years ago

Option 2 - One global daily schedule event that schedules single events for each follow-up payment

Note that scheduling an event to occur within 10 minutes of an existing event with the same action hook will be ignored unless you pass unique $args values for each scheduled event.

https://developer.wordpress.org/reference/functions/wp_schedule_single_event/

The advantage of this may also be that if a follow-up payment cannot be started at the gateway, we can reschedule the single event. I'm not exactly sure what WordPress does with events that throw an exception. It might not be watertight yet, but this option might solve some issues.

@rvdsteege You mentioned that WooCommerce also schedules events for orders, i couldn't find this: https://github.com/woocommerce/woocommerce/search?q=wp_schedule_single_event.

rvdsteege commented 2 years ago

You mentioned that WooCommerce also schedules events for orders, i couldn't find this.

It's here:

Schermafbeelding 2021-09-29 om 11 18 10
remcotolsma commented 2 years ago

@rvdsteege made a overview of meta usage: https://docs.google.com/spreadsheets/d/1CJHnHcr9s8lkh4Fq9HQmlAThCK69eXiTC3UktyeQKYw/edit?usp=sharing

Pronamic Pay meta

Payments

select p.ID, p.post_title, m.meta_key, m.meta_value from wp_posts as `p` left join wp_postmeta as `m` ON m.post_id = p.ID where p.post_type=‘pronamic_payment’ and m.meta_key LIKE ‘_pronamic%’ group by m.meta_key order by m.meta_key ASC
Key In JSON In post meta
_pronamic_payment_action_url :x: :white_check_mark:
_pronamic_payment_address :white_check_mark: :white_check_mark:
_pronamic_payment_adyen_details_result :x: :white_check_mark:
_pronamic_payment_adyen_payment_response :x: :white_check_mark:
_pronamic_payment_adyen_sdk_version :x: :white_check_mark:
_pronamic_payment_amount :white_check_mark: :white_check_mark:
_pronamic_payment_city :white_check_mark: :white_check_mark:
_pronamic_payment_config_id :x: :white_check_mark:
_pronamic_payment_consumer_account_number :white_check_mark: :white_check_mark:
_pronamic_payment_consumer_bic :white_check_mark: :white_check_mark:
_pronamic_payment_consumer_iban :white_check_mark: :white_check_mark:
_pronamic_payment_consumer_name :white_check_mark: :white_check_mark:
_pronamic_payment_country :white_check_mark: :white_check_mark:
_pronamic_payment_currency :white_check_mark: :white_check_mark:
_pronamic_payment_customer_name :white_check_mark: :white_check_mark:
_pronamic_payment_description :x: :white_check_mark:
_pronamic_payment_email :white_check_mark: :white_check_mark:
_pronamic_payment_end_date :white_check_mark: (also through periods) :white_check_mark:
_pronamic_payment_entrance_code :x: :white_check_mark:
_pronamic_payment_first_name :white_check_mark: :white_check_mark:
_pronamic_payment_issuer :x: :white_check_mark:
_pronamic_payment_key :x: :white_check_mark:
_pronamic_payment_language :white_check_mark: :white_check_mark:
_pronamic_payment_last_name :white_check_mark: :white_check_mark:
_pronamic_payment_locale :white_check_mark: :white_check_mark:
_pronamic_payment_method :x: :white_check_mark:
_pronamic_payment_mollie_change_payment_state_url :x: :white_check_mark:
_pronamic_payment_mollie_customer_id :x: :white_check_mark:
_pronamic_payment_omnikassa_2_merchant_order_id :x: :white_check_mark:
_pronamic_payment_order_id :x: :white_check_mark:
_pronamic_payment_recurring :x: :white_check_mark:
_pronamic_payment_recurring_type :x: :white_check_mark:
_pronamic_payment_source :x: :white_check_mark:
_pronamic_payment_source_id :x: :white_check_mark:
_pronamic_payment_start_date :white_check_mark: (also through periods) :white_check_mark:
_pronamic_payment_status :white_check_mark: :white_check_mark:
_pronamic_payment_subscription_id :white_check_mark: through periods :white_check_mark:
_pronamic_payment_telephone_number :white_check_mark: :white_check_mark:
_pronamic_payment_transaction_id :white_check_mark: :white_check_mark:
_pronamic_payment_user_agent :white_check_mark: :white_check_mark:
_pronamic_payment_user_ip :white_check_mark: :white_check_mark:
_pronamic_payment_version :x: :white_check_mark:
_pronamic_payment_woocommerce_payment_method :x: :white_check_mark:
_pronamic_payment_woocommerce_payment_method_title :x: :white_check_mark:
_pronamic_payment_zip :white_check_mark: :white_check_mark:

Subscriptions

select p.ID, p.post_title, m.meta_key, m.meta_value from wp_posts as `p` left join wp_postmeta as `m` ON m.post_id = p.ID where p.post_type=‘pronamic_pay_subscr’ and m.meta_key LIKE ‘_pronamic%’ group by m.meta_key order by m.meta_key ASC
Key In JSON In post meta
_pronamic_subscription_amount :white_check_mark: through fases :white_check_mark:
_pronamic_subscription_config_id :x: :white_check_mark:
_pronamic_subscription_currency :white_check_mark: through fases :white_check_mark:
_pronamic_subscription_customer_name :white_check_mark: :white_check_mark:
_pronamic_subscription_description :x: :white_check_mark:
_pronamic_subscription_email :white_check_mark: :white_check_mark:
_pronamic_subscription_end_date :white_check_mark: :white_check_mark:
_pronamic_subscription_expiry_date :white_check_mark: :white_check_mark:
_pronamic_subscription_key :x: :white_check_mark:
_pronamic_subscription_mollie_customer_id :x: :white_check_mark:
_pronamic_subscription_mollie_mandate_id :x: :white_check_mark:
_pronamic_subscription_next_payment :white_check_mark: :white_check_mark:
_pronamic_subscription_next_payment_delivery_date :white_check_mark: :white_check_mark:
_pronamic_subscription_payment_method :x: :white_check_mark:
_pronamic_subscription_source :x: :white_check_mark:
_pronamic_subscription_source_id :x: :white_check_mark:
_pronamic_subscription_start_date :white_check_mark: through fases :white_check_mark:
_pronamic_subscription_status :white_check_mark: :white_check_mark:
remcotolsma commented 2 years ago

I think we can remove the following issues from the Subscriptions follow-up payments improvement project:

remcotolsma commented 2 years ago

We still have a few open issues in the Subscriptions follow-up payments improvement project, but we have already implemented the solution for this issue, so I'm closing this ticket.