Paypal_Session Table is Huge

[ksoup]

Zen 1.3.8

Can this table zen_paypal_session be purged to a certain point? It is over 100MB. Is that typical?

[CJPinder]

Hmm...well I've had a look at that table on my own site and been through the ZC code. As far as I can tell stuff goes into that table but is never removed from it. The only exception to this is if you are using PDT in which case the session record is removed when a successful PDT is recorded. Without PDT the session information stays there forever.

So if you are using straight PayPal IPN (without PDT) the table will just keep growing and growing.

As far as I can see, once the order has come through into the ZC Admin, the record in the paypal_session table is no longer required. It may be a bug, I'm not sure.

Maybe DrByte could give his opinion?

Regards,
Christian.

[CJPinder]

I just found this thread...
http://www.zen-cart.com/forum/showthread.php?t=103166
...where DrByte says that the table can be purged once all the orders have come through.

The fact that records are never removed from the table looks like a bug to me though. Especially as they have an expiry date on them, you would expect them to be removed after that date.

Regards,
Christian.

[CJPinder]

To prune the paypal_session table, first take a backup of your Zen Cart database, then go to Tools > Install SQL Patches, paste in the following...

delete from `paypal_session` where expiry < unix_timestamp();

...and hit the Send button.

That will remove any PayPal IPN session records that have expired.

Regards,
Christian.

[swguy]

Make a suggestion that this be added to Tools->Store Manager. There are other things like this such as email cleanup that might also be good candidates for a page like this.

[CJPinder]

Make a suggestion that this be added to Tools->Store Manager.

Possibly, but with just one additional line of code the PayPal module can be made to do the clean up automatically in the background without the user ever having to worry about it.

I can see no logical reason for the paypal session information to stay in the table after the expiry date. I think it just got missed.

Regards,
Christian.

[paulm]

I can see no logical reason for the paypal session information to stay in the table after the expiry date.

I think the data should be kept until a final status has been received form Paypal.

Maybe Paypal works different, but in general it's possible that a transaction status is "Payed", but not yet received by the shop. Also after the expiry date.

[CJPinder]

I think the data should be kept until a final status has been received form Paypal.

Maybe Paypal works different, but in general it's possible that a transaction status is "Payed", but not yet received by the shop. Also after the expiry date.

The paypal_session records are used solely with PayPal IPN and only need to be kept until the IPN call-back has occurred and the order has been created. After that the information is never used. The expiry time is set for 2 days after the PayPal transaction starts. If the IPN call-back hasn't happened within 2 days it probably isn't going to and you should have taken action about it before then anyway.

Regards,
Christian.

[ksoup]

Thank you so much, Christian :) Did you make a suggestion for the future? (it would probably sound better coming from you because you obviously know your stuff :) )

All my orders were processed and complete, I ran the query through navicat and the table is so nice and shiny!!! :)

To prune the paypal_session table, first take a backup of your Zen Cart database, then go to Tools > Install SQL Patches, paste in the following...

delete from `paypal_session` where expiry < unix_timestamp();

...and hit the Send button.

That will remove any PayPal IPN session records that have expired.

Regards,
Christian.

[DrByte]

The suggestion is noted for future consideration.

In fact, a Store Manager option for this is already built-in to v2.0.0 development code.