Clean up Usermeta table (for subscribers)

#1

Over the years my website has gathered loads of usermeta data that I would like to selectively delete - best with an SQL query - however I don’t know how (yet).

Here are the fields I would like to delete (if I find out I will post how)

For all users remove the following meta_key entries - which is easy:
DELETE FROM wp_usermeta WHERE meta_key = ‘aim’;
DELETE FROM wp_usermeta WHERE meta_key = ‘rich_editing’;
DELETE FROM wp_usermeta WHERE meta_key = ‘show_admin_bar_front’;
DELETE FROM wp_usermeta WHERE meta_key = ‘locale’;
DELETE FROM wp_usermeta WHERE meta_key = ‘jabber’;
DELETE FROM wp_usermeta WHERE meta_key = ‘default_password_nag’;
DELETE FROM wp_usermeta WHERE meta_key = ‘wfls-last-login’;
DELETE FROM wp_usermeta WHERE meta_key = ‘wp_s2member_file_download_access_log’;
DELETE FROM wp_usermeta WHERE meta_key = ‘wp_s2member_file_download_access_arc’;
DELETE FROM wp_usermeta WHERE meta_key = ‘yim’;
DELETE FROM wp_usermeta WHERE meta_key = ‘syntax_highlighting’;
DELETE FROM wp_usermeta WHERE meta_key = ‘comment_shortcuts’;
DELETE FROM wp_usermeta WHERE meta_key = ‘admin_color’;
DELETE FROM wp_usermeta WHERE meta_key = ‘use_ssl’;
DELETE FROM wp_usermeta WHERE meta_key = ‘wp_user_level’;
DELETE FROM wp_usermeta WHERE meta_key = ‘_tern_wp_member_list’;
DELETE FROM wp_usermeta WHERE meta_key = ‘wp_s2member_last_auto_eot_time’;
DELETE FROM wp_usermeta WHERE meta_key = ‘crb_reg’;

However For subscribers: wp_capabilities = a:1:{s:10:“subscriber”;b:1;}
I would like to drop the following entries on top:
wp_s2member_paid_registration_times
wp_s2member_first_payment_txn_id
wp_s2member_ipn_signup_vars

wp_s2member_last_login_time

wp_s2member_login_counter
wp_s2member_last_auto_eot_time

My usermeta table is over 200 MB in size, so I really would like to cut it down to make the database faster and especially make backups via email easier.
Also actually all maybe even delete all subscribers who have not logged in for XX years (wp_s2member_last_login_time ) whould be a good idea GDPR wise.

#2

My oldest (10 years or so) s2 site has a usermeta that’s about 100M, but I don’t run a lot of other plugins there. My backups go to s3 or a similar host so email size isn’t a concern. Are you experiencing speed concerns? You said you wanted to make the db faster, but how do you know this is making it slower? With other plugins I’ve had to add my own indexes (stuff made by Automattic, ironically), but I haven’t had to do that with s2 yet.

I’m assuming you already tried Query Monitor, and if I recall correctly, you’ve already enlightened me about s2’s lack of compatibility with Redis or Memcached, which might normally be another solution.

#3

Yeah - I don’t think you will notice any difference - the important data what you want to keep small is the wp_options table with autoload=yes. The rest is more about why keep data that you don’t need. I haven’t seen any actual use for that data - and without it I can backup my database via email as it’s below 25MB gzipped.

I still don’t understand how I can get two conditions run agains the same user_id to match. Filtering subscribers would be like:

DELETE FROM wp_usermeta
WHERE EXISTS
( SELECT *
FROM wp_usermeta.user_id
WHERE wp_usermeta.meta_value LIKE ‘%subscriber%’
AND wp_usermeta.meta_key = ‘wp_s2member_login_counter’);

But something is wrong with this command.

I’ve deleted more stuff and didn’t see any consequences for my use - I don’t use access caps - keep that in mind. E.g. I don’t see why I would need to keep wp_s2member_ipn_signup_vars
it will be never used after actual sign up so could be cleaned up.
Also the download counter stuff can be cleaned up from time to time - no need to keep it for ages. Same for: wp_s2member_first_payment_txn_id

So my longer list is as follows (I would still like to delete more stuff for subscribers - but do not know how to do that)
DELETE FROM wp_usermeta WHERE meta_key = ‘aim’;
DELETE FROM wp_usermeta WHERE meta_key = ‘rich_editing’;
DELETE FROM wp_usermeta WHERE meta_key = ‘comment_shortcuts’;
DELETE FROM wp_usermeta WHERE meta_key = ‘admin_color’;
DELETE FROM wp_usermeta WHERE meta_key = ‘closedpostboxes_dashboard’;
DELETE FROM wp_usermeta WHERE meta_key = ‘metaboxhidden_dashboard’;
DELETE FROM wp_usermeta WHERE meta_key = ‘screen_layout_dashboard’;
DELETE FROM wp_usermeta WHERE meta_key = ‘wp_dashboard_quick_press_last_post_id’;
DELETE FROM wp_usermeta WHERE meta_key = ‘managenav-menuscolumnshidden’;
DELETE FROM wp_usermeta WHERE meta_key = ‘metaboxhidden_nav-menus’;
DELETE FROM wp_usermeta WHERE meta_key = ‘closedpostboxes_page’;
DELETE FROM wp_usermeta WHERE meta_key = ‘metaboxhidden_page’;
DELETE FROM wp_usermeta WHERE meta_key = ‘meta-box-order_page’;
DELETE FROM wp_usermeta WHERE meta_key = ‘screen_layout_page’;
DELETE FROM wp_usermeta WHERE meta_key = ‘use_ssl’;
DELETE FROM wp_usermeta WHERE meta_key = ‘show_admin_bar_front’;
DELETE FROM wp_usermeta WHERE meta_key = ‘yim’;
DELETE FROM wp_usermeta WHERE meta_key = ‘jabber’;
DELETE FROM wp_usermeta WHERE meta_key = ‘locale’;
DELETE FROM wp_usermeta WHERE meta_key = ‘syntax_highlighting’;
DELETE FROM wp_usermeta WHERE meta_key = ‘default_password_nag’;
DELETE FROM wp_usermeta WHERE meta_key = ‘wikipedia’;
DELETE FROM wp_usermeta WHERE meta_key = ‘youtube’;
DELETE FROM wp_usermeta WHERE meta_key = ‘community-events-location’;
DELETE FROM wp_usermeta WHERE meta_key = ‘twitter’;
DELETE FROM wp_usermeta WHERE meta_key = ‘instagram’;
DELETE FROM wp_usermeta WHERE meta_key = ‘linkedin’;
DELETE FROM wp_usermeta WHERE meta_key = ‘myspace’;
DELETE FROM wp_usermeta WHERE meta_key = ‘pinterest’;
DELETE FROM wp_usermeta WHERE meta_key = ‘soundcloud’;
DELETE FROM wp_usermeta WHERE meta_key = ‘tumblr’;
DELETE FROM wp_usermeta WHERE meta_key = ‘facebook’;
DELETE FROM wp_usermeta WHERE meta_key LIKE ‘wpseo%’;
DELETE FROM wp_usermeta WHERE meta_key LIKE ‘_yoast%’;
DELETE FROM wp_usermeta WHERE meta_key = ‘description’;
DELETE FROM wp_usermeta WHERE meta_key = ‘wp_s2member_ipn_signup_vars’;
DELETE FROM wp_usermeta WHERE meta_key = ‘wp_s2member_file_download_access_arc’;
DELETE FROM wp_usermeta WHERE meta_key LIKE ‘wp_s2member_file_download_access_log’;
DELETE FROM wp_usermeta WHERE meta_key LIKE ‘wp_s2member_first_payment_txn_id’;
DELETE FROM wp_usermeta WHERE meta_key = ‘wp_s2member_access_cap_times’;

#4

s2 actually does check against it when it gets a notification from the payment gateway, and could end up ignoring the notification if those vars are missing. This is something I want to look into, hopefully remove the check if I can figure out why Jason included it and understand the consequence of doing it.

#5

I’m sure it’s not used with PayPal. Because I deleted them and had no problems whatsoever. I’m unsure if they are used for stripe however or another gateway.
I’ve written in another topic about s2member dropping ipn notifications and there is another problem though I don’t know what and it usually happens some hours then is fixed again or as last quarter it affected all payments from Paypal Poland where customers had special characters in their name.

#6

It’d be more relevant for subscriptions and their notifications.

Found the other topic.

#7

I checked all three, subscription arriving, cancelled and unpaid. All work for PayPal.
I didn’t yet have a stripe subscription arriving because for years due to the bug in my S2member database (domain in ipn field) stripe cancellation weren’t handled so I stopped subscription creation for stripe.

#8

Actually I really wonder about the signup_vars value. It kinda includes everything but I guess it would be quite a mess finding something in that value. I think the main reason to have it would be for logging/bug finding but not for actual use.
e.g. yeah you could use that info to write invoices or similar - but then having this all in one key would make it a mess to know which position is what.
It also would not be efficient to search in that value for something.

And from EU data protection this value is a big mess if it’s not cleaned up after XX years of paid term is over. I cannot see how it’s used for subscription and notifications.

Maybe it could be good for plugins to hook into to search more about a customer? I keep all s2Member / API Notification
emails because I very often need to find additional information about a customer - especially if they mistype their email or change email and with the content of that email I can get more input to search info about the customer.
However as this value I guess isn’t updated on subsequent payments it’s quite useless. The data would be great starting point to create a better history page for the customer to see his past payments, products bought and so on. But then again the info really should be in subkeys and not all mixed up in one key plus it would need to be ammended on each subscription payment. The way it is right now except for someone to look into that value (but very troublesome as you need to use phymyadmin or the export function) it is not really usable and it makes sense to clear it up - especially if you archive those api emails and then use your email provider and end up with a much better and more comprehensive overview of your customers history.

To me it looks like a key introduced to just log a lot of stuff so one can see what is there but that was never used - and if needed in future export it into their own key/value once a use case is found. But as this is not recreated on future payments it is pretty much a dead end and more useful for a developer but not for an actual s2member admin. Would be enough to have that content somewhere once in documentation so you can see what s2member records and create a custom hook/plugin/addition to s2member for more functionality.

#9

It was created by Jason. It’s a serialized array, that’s why the weird format, it needs to be unserialized.

Some of those values (at least the “custom” value, i.e. the domain name) are also stored in the gateway. s2 checks against that to validate the gateway’s notification, and discards the notification if it can’t.

The reason for it, I think Jason mentioned it as a quick comment to me 12 years ago, is that he wanted to make sure that s2 wouldn’t take action on anything that s2 didn’t start. Also to avoid someone starting a subscription on his own PayPal, for example, and have it notify someone else’s s2, and gain access that way.

I’ve been thinking about it because I want to make it easier/possible to migrate subscriptions that were started by some other means, into s2, tie it with a user, and let the notifications update his access going forward.

If the subscr ID is in the user’s profile, and it matches the notification’s, that should be enough, I think. And even if someone can manage to send a notification to s2, it wouldn’t be possible for him to add that subscr ID to a user profile, so it’s still safe.

So I’m considering modifying the notifications validation to allow more options, while keeping it safe.

1 Like
#10

I currently already do it. But I update everything manually. :grimacing:

1 Like
#11

Right, it’s doable, but takes work. I’d like to make it simpler and more straightforward.

1 Like
#12

I think stripe subscription needs it, while PayPal button payments or PayPal payments work without this additional hidden information. Without it I think currently stripe cancellations don’t work and users aren’t demoted?

1 Like
#13

If the signup vars are missing, I’m pretty sure the stripe webshook will be skipped by s2, because it didn’t match it to a user.

You can check your s2 stripe and core-ipn logs, and see if it’s there and it was skipped. You can also look for one that did get processed, and check if that user does have the signup vars.

If the signup vars are there, but the webhook/notification got skipped anyway, could you show me the log entry and any other detail about the sub/user that could help?

1 Like
#14

I have basically no users on stripe subscription with that data because of the bug with domain in the second IPN field and all signing up once that happened to be missing it. So yeah it would be great if you could change Stripe to also work without that data! With Stripe the only problem for me if it’s missing is that users aree not demoted once suscripbtion is over. Because invoices are plugged right into Stripe instead.But yeah there is also no s2member IPN notification on payment email for those cases (while this email works for paypal with or without signup-vars).

For paypal (button payments) it’s not needed already.

1 Like
#15

If the signup_vars really are needed for Stripe and that cannot be dropped in a future version it would still be good if they could be deleted on demotion to subscriber.
The reason is simply that this data is very problematic for privacy reason. Or better s2member should delete all “invisible” metadata that it created on demotion of a user.

And yeah while it’s only a small part of useless metadata of what can accumulate - for data privacy in EU such data really needs to be deleted after XY years no login/no interaction with user. As I said likely we would even need to delete users alltogether 3-5 years after they demote. So s2member should store a demotion date into a new key. and then offer a way to delete all users after X years being subscriber.- or even better inform the user and ask to login and if that does not happen within 30 days remove that user.
As long as there is no such function we would need a way to keep personal data to an absolute minimum.

1 Like
#16

Yes. PayPal doesn’t have this issue, only Stripe. Since my website is small I do a lot of things manually, in the end, because I am unable to fix those issues.

I also have to downgrade and reupgrade users manually when a payment fails (or happens later) because the plugin just doesn’t process those situations correctly (in my opinion).

Maybe in the future the plugin can demote upon decline and reupgrade upon payment made late and notify us when doing that, in a future version… Until then I have to spend a considerable amount of time, daily, doing those things manually, user by user, for any payment processor. :grimacing:

#17

Yeah this point is really for me the main problem of s2member. It’s unreliable as crazy due to its fishing around in the signup_vars for Stripe.

There are slight problems in my understanding how missed or failed payments with paypal are handled, but the real problem is how unreliable s2member works with Stripe - and yeah large part of that is due to that for long time I had a bug in so far that the signup_vars were not recorded on checkout so all old customers don’t have them.

I really can’t see any security problem in this point - only the opposite frequent problems of s2member with Stripe due to checking that field or failing to write it from time to time.

1 Like
#18

My site is small and I have a tiny list of members to manage. If I had a large community I’d have been forced to migrate to another solution or hire a dev to help me with it, as it would be unfeasible to manage all demotions and reupgrades that are necessary on a daily basis due to declines and late payments etc.

1 Like
#19

Actually with the new patch finally the usermeta can be cleaned up including the signup_vars (well hoping for the payment email regarding stripe to be sorted out too - that one is still missing. But then I only need that email to debug stripe demotions which are fixed now).

1 Like
#20

Oh? How can we do that?