anonymous users maintenance bug

[tripleW]

I upgraded from R5 to R8 a couple months ago. I did a clean install of R8 first then pointed to a copy of the live db to upgrade it. I know there is a bug with the user maintenance this but I thought I followed the correct procedure.

Now it looks like the anoymous users maintenance hasn't been running. I would see an occasional "Error in user maintenance" in the error log but nothing unusual until the last few days.

I noticed some site performance issues so I checked the database via the Admin Store Maintenance page. Their are over 350,000 records when I've never seen anything over 50,000 with the Days to Save set at 8 days on this site. I lowered the Days to Save to 5 days and tried to manually delete anonymous users but it times out and generates hundreds of errors.

Is there a patch for this bug? I don't think this client can afford to pay someone to check the database and delete records from the table on a regular basis. What should be done at this point?

Thanks

[tripleW]

Trying to delete users manually since the dashbopard feature won't work. I got an error when running the last statement:

DELETE FROM ac_Baskets
WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1
AND (LastActivityDate IS NULL OR LastActivityDate < 'November 21, 2014'))

DELETE FROM ac_Wishlists
WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1
AND (LastActivityDate IS NULL OR LastActivityDate < 'November 22, 2014'))

DELETE FROM ac_Users
WHERE StoreId = 1 AND IsAnonymous = 1
AND (LastActivityDate IS NULL OR LastActivityDate < 'November 22, 2014')


(0 row(s) affected)

(2307 row(s) affected)
Msg 547, Level 16, State 0, Line 9
The DELETE statement conflicted with the REFERENCE constraint "ac_Addresses_ac_BasketShipments_FK1". The conflict occurred in database "awesomeevent-ablecommercedb", table "dbo.ac_BasketShipments", column 'AddressId'.
The statement has been terminated.

So, the script should only be run for records older than 1 day!

I really need help with this. Do you have scripts that will delete the old users?

[mazhar]

Give a try to fix mentioned in this help topic.

http://help.ablecommerce.com/upgrades/a ... enance.htm

[tripleW]

Thanks for your reply mazhar. I applied the patch and went through all the steps listed before upgrading to GoldR5 from 7.0.7 a year and a half ago. The problem was fixed but now has reappeared after upgrading from R5 to R8. So I need to do this after every upgrade?

[jguengerich]

The error message is indicating that there are some records in ac_BasketShipments that are using some addresses that it is trying to delete (because it is deleting users). The queries you copied earlier don't all have the same date. If that is the set you actually used, there could still be baskets from November 21 in the table, but then you are trying to delete users that had activity on November 21. If there are baskets for those users on Novemeber 21, it won't be able to delete the user. Make sure you use the same date on all 3 statements.