Recurring Date December 31, 2012

The date reserved to internally represent end of recurring subscriptions on my system was December 31, 2012. The problem is obvious in that this was yesterday. Is there a way I can change this?

I edited the date in amemer/plugins/payments/paypal_r/paypal_r.inc.php but that still hasn't resolved the issue. Are there others or do I need to rebuild database?

This made a huge mess for me and I'm still trying to recover. I did find mention of it here: http://www.amember.com/forum/threads/all-payments-automatically-switched-to-recurring.13959/

I did do as suggested and updated the RECURRING_SQL_DATE in product.inc.php but new subscriptions are getting expired right away still. I have a feeling we'll have to go through the database and change all the 2012-12-31's manually. Would like to hear confirmation about the proper way to recover from this.

Ok, I think the change has to be made in both places. paypal_r.inc.php had it hard coded when it should have referenced RECURRING_SQL_DATE. I made this change and then updated the database and I think I'm under control now. I used this query to fix the db:

update amember_payments set expire_date="2036-12-31" where expire_date="2012-12-31" and paysys_id="paypal_r";

I am having the same problem as you and have done the following:

1. Ran the SQL update code: select * from amember_payments where expire_date = '2036-12-31'; (I am using 2036 as that changes the subscription to RECURRING - 2037 changes it to LIFETIME.
2. Changed 2012-12-31 to 2036-12-31 in product_inc.php.
3. Changed all occurrences of 2012-12-31 to 2036-12-31 in paypal_r.inc.php

HOWEVER, this does not fix one huge problem, which is that all members that were mistakenly expired on 2012-12-31 are still shown to be expired! Which means nobody can log into my posts now. I have no idea how to fix this.

UPDATE - some friendly soul advised me that it is necessary to click on 'Rebuild DB' in the aMember CP. This supposedly fixes all dirty references after a manual update or a db recovery. Anyway, I did it and thus far it 'seems' that it is working again.

However, if there are any other places where I need to make date changes to 2036 please let me know.

I did not have to rebuild the db after doing the query to update expiration dates to 2036-12-31. That option scares me.

Is that the exact query you ran? The select will not modify the database. You have to do this:

update amember_payments set expire_date="2036-12-31" where expire_date="2012-12-31" and paysys_id="paypal_r";

Assuming the problem is only for paypal users like it was for me. I don't know about the other billing methods but I know credit card processing doesn't have this issue.

No, I only ran this: select * from amember_payments where expire_date = '2036-12-31;

I am not certain that the payap_r qualifier is necessary but it may be safer as it makes sure that no 'non-recurring' members who just happen to end that date will get a freebie for life

I ran the query against the database and rebuilt it (after editing the aforementioned files) and it didn't do the trick. Still in the process of manually editing all the user records. Complete epic New Year's disaster.

Just for reference (because I'm sure Alexander and company need to see this)

FILES EDITED:

amember/plugins/payment/paypal_r/paypal_r.inc.php


amember/product.inc.php


From December 31, 2012

To December 31, 2030

I ran the query against the database in attempt to automatically update the recurring expiration date. The records still showed as expired so I spent the day MANUALLY editing the dates to December 31, 2030. Once manually edited, everything works fine. I can also confirm new signups are set correctly as well.

Well, other payment processing scripts are going to have the same problem. If you know Unix command shell then you can log in via SSH and navigate to your amember folder. Then run the following code:

find . -type f -name "*.php" | xargs grep 2012-12-31

This will show you all PHP files that have the 2012 date reference.

Now regarding your SQL question - here's how you can find members who expired on 2012-12-31 but are NOT recurring subs:

select DISTINCT (m.email), m.login, m.status from amember_members m LEFT JOIN amember_payments p ON m.member_id = p.member_id where p.expire_date="2036-12-31" and p.paysys_id!="paypal_r";

If you change p.paysys_id!="paypal_r to p.paysys_id="paypal_r then you will get only the recurring ones. Hope this makes sense.

I also edited admin/mass_subscribe.php

I checked mass_subscribe and only see the lifetime date set at 31/12/2037. Nothing to do with December 31, 2012 but I appreciate the heads up.

One thing I have noticed in manually going through my records are some errors in people that haven't paid yet still had access up until today. That's been the one good thing to come out of this is catching people that somehow got on perhaps by script error.

You must be running a different version of amember then as I had several references to 2012-12-31. Anyway, if you want to see all files that reference this hare-brained date then run this from the command line after navigating to your amember folder:

find . -type f -name "*.php" | xargs grep 2012-12-31

This command scans the entire file tree and prints the filenames and the references if there are any.

OK the nightmare is over. When I got down to my last couple of records I ran the command again and this time it worked, saving me a half hour or so. I ran the aforementioned command to see all fires and edited that date out of amember/templates_c/%%592^%%592145302^member_payment_history.html.php.

The one thing I'll caution is I was surprised at the amount of accounts that had access despite non-payment. It wasn't a tremendous amount but more than I wanted to see.

I'll certainly be updating soon. Fought it long enough but this has made for one interesting day.

Sounds like the reason you're having to manually edit is because you used a different date than the instructions said. The sql query quoted in this thread was:

update amember_payments set expire_date="2036-12-31" where expire_date="2012-12-31" and paysys_id="paypal_r";

The date you use can be 2030 or 2036 or whatever, but you need to use the same date in your sql query and update the paypal_r.inc.php and product.inc.php files with the same date. The dates can't be different.

Exactly right. That's what I did and it seems to be working again now. Of course I am eagerly anticipating a word from amember support to make sure we didn't leave anything out.

Hi guys,

Thanks for sharing your thoughts, it's great that while amember support ignores the problem - there is source of information to deal with such an urgent problem.

I'm in process of fixing this paypal expire bug on our side and wondering about all those "expired/cancelled" users. I see that for all of them 31-12-2012 is used in amember table. So does that mean - when we perform mass update in db - it will have any side effect - ie all these users will get access to amember again until next paypal rebilling cycle? Would it renew cancelled ones and charge them once again?

Side effect for me was:

1) All paypal subscribers received an email saying their subscriptions expired
2) A bunch of people came in and bought new subscriptions when they didn't have to.

If you have notifications turned on, you'll get people thinking their subscription expired. I sent a mass email to everyone explaining the situation to stop them from signing up again until I could get the database cleaned up.

To answer your question, they will not be charged again when you change the expiration date. The subscription will become "Active" again and follow the original subscription period based on their PayPal subscription settings. Cancelled subscriptions will remain cancelled.