Add migration to remove duplicate Customers & Subscriptions #12100
Assignees
Comments
allouis
added a commit
to allouis/Ghost
that referenced
this issue
Aug 4, 2020
refs TryGhost#12100 We want to add unique constraints to the subscription_id and customer_id fields in the members_stripe_customers_subscriptions and members_stripe_customers tables respectively. In order to do this safely we must first ensure that no duplicate entries exist.
|
As we are adding foreign key constraints as well as unique constraints, we must also ensure the database is in the correct state to accept that, which means no orphaned records in the |
|
Also - as the migrations we are writing to add the new constraints is only being run for MySQL databases, the cleanup migrations will also only be run for MySQL! |
allouis
added a commit
that referenced
this issue
Aug 6, 2020
refs #12100 For performance reasons we want to add foreign key and unique constraints to the members_stripe_* tables so we can utilised cascading deletes and joins across the tables when querying. In order to do this we must first ensure that: - There are no duplicate entries in the `subscription_id` or `customer_id` columns - There are no orphaned rows in the subscription or customers tables If the first is not true, the unique constraint will fail, and if the second is not true, the foreign key constraint will fail. As we are only adding the indexes to existing MySQL databases at this point, the cleanup migrations will also only be done for existing MySQL databases too. The migrations for removing orphaned rows splits the deletion into a `SELECT` followed by a `WHERE IN` to avoid the database "optimising" the query into a `JOIN` which ends up taking much longer due to the lack of indexes.
|
Closed in d338497 🎉 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
We want to add a unique constraint to the stripe id columns, but we have seen instances of duplicate entries, which would cause the constraint to fail - we should first run a migration to clean these up so that we can be certain the unique constraint migration will work.
estimated 1 day of work
The text was updated successfully, but these errors were encountered: