Page 1 of 1
Deleting Anonymous Users
Posted: Tue Oct 12, 2010 11:15 am
by Brewhaus
We just looked in our Users table (for an unrelated reason) and found that anonymous users (things like fe941c01-9443-43cb-a53d-6300b69d38dc) are filling the table. We appear to have literally hundreds of thousands of these. My understanding was that they would be deleted after X number of days (based on what was listed in your settings for how long to retain anonymous users and their shopping carts). But, for whatever reason, we are just piling on these users in the table. Is there any way to flush the Users table to get rid of these users?
While we are at it, is there any way to 'bulk' delete customers? With our McAfee scans, we are building quite a pile of users with @mcafeesecure.com addresses, and would like to avoid having to delete them one-by-one.
Re: Deleting Anonymous Users
Posted: Wed Oct 13, 2010 5:19 am
by mazhar
Maybe you can try to delete them from DB directly by doing something like
Code: Select all
DELETE FROM ac_Baskets WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1 AND AffiliateId IS NULL )
DELETE FROM ac_Wishlists WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1 AND AffiliateId IS NULL )
DELETE FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1 AND AffiliateId IS NULL
This will try to delete all abandoned baskets associated with anonymous user, then all Wishlists for anonymous users and finally anonymous users from database.
Re: Deleting Anonymous Users
Posted: Wed Oct 13, 2010 5:47 am
by mazhar
Or may be in App_Data/AbleCommerce.config file you can set maintenanceInterval="360" to some lower value where 360 represents 360 mnts(6Hours). method
Re: Deleting Anonymous Users
Posted: Wed Oct 13, 2010 7:19 am
by Brewhaus
Maybe you can try to delete them from DB directly by doing something like
Code: Select all
DELETE FROM ac_Baskets WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1 AND AffiliateId IS NULL )
DELETE FROM ac_Wishlists WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1 AND AffiliateId IS NULL )
DELETE FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1 AND AffiliateId IS NULL
Would we do this in SQL Query Analyzer?
Or may be in App_Data/AbleCommerce.config file you can set maintenanceInterval="360" to some lower value where 360 represents 360 mnts(6Hours). method
This is already set to 360, but the users that I showed go back more than a year.
Re: Deleting Anonymous Users
Posted: Wed Oct 13, 2010 8:16 am
by mazhar
Would we do this in SQL Query Analyzer?
Yes you will use Query Analyzer to execute query, but
don't forget to take backup of your Database first.
This is already set to 360, but the users that I showed go back more than a year.
What version of AbleCommerce you are using? It should clear anonymous users, go to your Help -> Error Log and see if there are any messages that seems related to anonymous users cleanup.
Re: Deleting Anonymous Users
Posted: Wed Oct 13, 2010 10:31 am
by Brewhaus
The first two commands run successfully, but the third (when trying to delete the anonymous users) throws the following error after about two minutes:
Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint 'ac_Users_ac_Orders_FK1'. The conflict occurred in database 'HotSauceDepot', table 'ac_Orders', column 'UserId'.
The statement has been terminated.
What version of AbleCommerce you are using? It should clear anonymous users, go to your Help -> Error Log and see if there are any messages that seems related to anonymous users cleanup.
We are running version 7.0.2, build 11659.
Re: Deleting Anonymous Users
Posted: Wed Oct 13, 2010 11:00 am
by mazhar
Re: Deleting Anonymous Users
Posted: Wed Oct 13, 2010 11:57 am
by Brewhaus
I I understand correctly, I should run the following code (from the thread that you selected):
Code: Select all
/* STEP 2: Remove existing constraint */
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'ac_Orders'
AND CONSTRAINT_NAME = 'ac_Users_ac_Orders_FK1')
BEGIN
ALTER TABLE ac_Orders
DROP CONSTRAINT ac_Users_ac_Orders_FK1
END
GO
/* STEP 3: Add the correct constraint */
alter table "ac_Orders"
with nocheck
add constraint "ac_Users_ac_Orders_FK1" foreign key ("UserId")
references "ac_Users" ("UserId") on update no action
go
/* STEP 4. Drop existing trigger for user deletes */
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ac_Usersdelete' AND type = 'TR')
DROP TRIGGER ac_Usersdelete
GO
/* STEP 5. 5. Add correct trigger for user deletes */
Create trigger "ac_Usersdelete" on "ac_Users"
for delete
as
BEGIN
declare
@errorNumber int,
@errorMsg varchar(255)
/* Clause for ON DELETE to referenced table SET NULL */
update "ac_AuditEvents"
set "ac_AuditEvents"."UserId" = NULL
from deleted,"ac_AuditEvents"
where "ac_AuditEvents"."UserId" = deleted."UserId"
/* Clause for ON DELETE to referenced table SET NULL */
update "ac_OrderNotes"
set "ac_OrderNotes"."UserId" = NULL
from deleted,"ac_OrderNotes"
where "ac_OrderNotes"."UserId" = deleted."UserId"
/* Clause for ON DELETE to referenced table SET NULL */
update "ac_Orders"
set "ac_Orders"."UserId" = NULL
from deleted,"ac_Orders"
where "ac_Orders"."UserId" = deleted."UserId"
return
errorHandler:
raiserror @errorNumber @errorMsg
rollback transaction
END
go
which is for SQL 2000 (this is the version that I am running). I would then run the code that you posted:
Code: Select all
DELETE FROM ac_Baskets WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1 AND AffiliateId IS NULL )
DELETE FROM ac_Wishlists WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1 AND AffiliateId IS NULL )
DELETE FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1 AND AffiliateId IS NULL
Is that correct?
Re: Deleting Anonymous Users
Posted: Thu Oct 14, 2010 5:31 am
by mazhar
The thread that I mentioned contains two type of fix queries one for SQL 2005 and second for SQL2000. So first of all if you are using SQL 2005 and if you go to your Admin -> Help -> About AbleCommerce page and it says AC SCHEMA 2005 then you need to execute following query
Code: Select all
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'ac_Orders'
AND CONSTRAINT_NAME = 'ac_Users_ac_Orders_FK1')
BEGIN
ALTER TABLE ac_Orders
DROP CONSTRAINT ac_Users_ac_Orders_FK1
END
GO
ALTER TABLE ac_Orders ADD CONSTRAINT
ac_Users_ac_Orders_FK1 FOREIGN KEY
(
UserId
) REFERENCES ac_Users
(
UserId
) ON UPDATE NO ACTION
ON DELETE SET NULL
GO
If you are using SQL2000 or your your Admin -> Help -> About AbleCommerce page and it says AC SCHEMA 2000 then execute other query mentioned by Logan
Code: Select all
/* STEP 2: Remove existing constraint */
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'ac_Orders'
AND CONSTRAINT_NAME = 'ac_Users_ac_Orders_FK1')
BEGIN
ALTER TABLE ac_Orders
DROP CONSTRAINT ac_Users_ac_Orders_FK1
END
GO
/* STEP 3: Add the correct constraint */
alter table "ac_Orders"
with nocheck
add constraint "ac_Users_ac_Orders_FK1" foreign key ("UserId")
references "ac_Users" ("UserId") on update no action
go
/* STEP 4. Drop existing trigger for user deletes */
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ac_Usersdelete' AND type = 'TR')
DROP TRIGGER ac_Usersdelete
GO
/* STEP 5. 5. Add correct trigger for user deletes */
Create trigger "ac_Usersdelete" on "ac_Users"
for delete
as
BEGIN
declare
@errorNumber int,
@errorMsg varchar(255)
/* Clause for ON DELETE to referenced table SET NULL */
update "ac_AuditEvents"
set "ac_AuditEvents"."UserId" = NULL
from deleted,"ac_AuditEvents"
where "ac_AuditEvents"."UserId" = deleted."UserId"
/* Clause for ON DELETE to referenced table SET NULL */
update "ac_OrderNotes"
set "ac_OrderNotes"."UserId" = NULL
from deleted,"ac_OrderNotes"
where "ac_OrderNotes"."UserId" = deleted."UserId"
/* Clause for ON DELETE to referenced table SET NULL */
update "ac_Orders"
set "ac_Orders"."UserId" = NULL
from deleted,"ac_Orders"
where "ac_Orders"."UserId" = deleted."UserId"
return
errorHandler:
raiserror @errorNumber @errorMsg
rollback transaction
END
go
So once you are done executing one query from above depending upon you database version. Then you don't need to execute the delete query posted by me. It would be better to ask maintenance worker to do this instead of manual query, it should work now because we have attempted to correct this using query fix posted by Logan. Go to your Website/App_Data/AbleCommerce.config file and change maintenanceInterval="360" to say maintenanceInterval="5" and save it. Now visit website our 5 minutes. This will trigger the maintenance to clear anonymous users, go and check if anonymous users are still in system and secondly see error log for any new error related to users table.
Re: Deleting Anonymous Users
Posted: Thu Oct 14, 2010 5:16 pm
by Brewhaus
Thank you, Mazhar. I will try this tomorrow.
Re: Deleting Anonymous Users
Posted: Fri Oct 15, 2010 3:03 pm
by Brewhaus
I still see the following error in the Error Log:
DELETE statement conflicted with COLUMN REFERENCE constraint 'ac_Users_ac_Orders_FK1'. The conflict occurred in database 'HotSauceDepot', table 'ac_Orders', column 'UserId'. The statement has been terminated.
Oh, and while we are running SQL 2000, in the About AbleCommerce, there is no SCHEMA listing at all.
Re: Deleting Anonymous Users
Posted: Mon Oct 18, 2010 1:52 pm
by Brewhaus
Mazhar- do you have any more thoughts on this? I am concerned about the long term effects of having hundreds of thousands of extra entries in the table, and obviously we cannot manually delete them as it would likely take months.
Re: Deleting Anonymous Users
Posted: Wed Oct 20, 2010 11:04 am
by Logan Rhodehamel
Hello Rick, with SQL 2000 you will want to run the second set of the queries Mazhar provided. This is the set of query code that drops the ac_usersdelete trigger and then adds a new replacement trigger. SQL 2000 did not allow a cascading delete to nullify records and this is what we need to do. The ac_Orders table has references to these anonymous user records and we need to break that link so the anonymous user record can be deleted.
If you are able to run that set of commands in query analyzer, then you should be able to run the three delete statements to clear out the records. Also the automatic maintenance will be able to function properly.
Re: Deleting Anonymous Users
Posted: Wed Oct 20, 2010 2:48 pm
by Brewhaus
Hi Logan,
Just to confirm, is this specifically what you are referring to:
If you are using SQL2000 or your your Admin -> Help -> About AbleCommerce page and it says AC SCHEMA 2000 then execute other query mentioned by Logan
Code: Select all
/* STEP 2: Remove existing constraint */
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'ac_Orders'
AND CONSTRAINT_NAME = 'ac_Users_ac_Orders_FK1')
BEGIN
ALTER TABLE ac_Orders
DROP CONSTRAINT ac_Users_ac_Orders_FK1
END
GO
/* STEP 3: Add the correct constraint */
alter table "ac_Orders"
with nocheck
add constraint "ac_Users_ac_Orders_FK1" foreign key ("UserId")
references "ac_Users" ("UserId") on update no action
go
/* STEP 4. Drop existing trigger for user deletes */
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ac_Usersdelete' AND type = 'TR')
DROP TRIGGER ac_Usersdelete
GO
/* STEP 5. 5. Add correct trigger for user deletes */
Create trigger "ac_Usersdelete" on "ac_Users"
for delete
as
BEGIN
declare
@errorNumber int,
@errorMsg varchar(255)
/* Clause for ON DELETE to referenced table SET NULL */
update "ac_AuditEvents"
set "ac_AuditEvents"."UserId" = NULL
from deleted,"ac_AuditEvents"
where "ac_AuditEvents"."UserId" = deleted."UserId"
/* Clause for ON DELETE to referenced table SET NULL */
update "ac_OrderNotes"
set "ac_OrderNotes"."UserId" = NULL
from deleted,"ac_OrderNotes"
where "ac_OrderNotes"."UserId" = deleted."UserId"
/* Clause for ON DELETE to referenced table SET NULL */
update "ac_Orders"
set "ac_Orders"."UserId" = NULL
from deleted,"ac_Orders"
where "ac_Orders"."UserId" = deleted."UserId"
return
errorHandler:
raiserror @errorNumber @errorMsg
rollback transaction
END
go
I believe that this is what I tried to run, and after about two minutes of working we get the error that I mentioned.
Re: Deleting Anonymous Users
Posted: Wed Oct 20, 2010 2:55 pm
by Logan Rhodehamel
Yes, what you have just posted, this is what I think needs to be run in your case. The code drops the constraint that is causing the error, and then uses a trigger instead to ensure data consistency.
The error message you posted talks about a delete statement, so it seems like the error you would get from the three delete queries. Can you double check that this has been attempted? And if you get an error, please paste the error message that it returns. The error message you posted doesn't line up with an error in this set of queries.
Re: Deleting Anonymous Users
Posted: Wed Oct 20, 2010 3:55 pm
by Brewhaus
My bad- this code does not throw an error. When I run the code mentioned in SQL Query Analyzer it shows to have completed successfully. However, the system is still not deleting the anonymous users, and I still have the error noted above in the Error Log and if I try to run the third line of code from Mazhar.
Re: Deleting Anonymous Users
Posted: Thu Oct 21, 2010 8:04 am
by Katie
Hi Rick,
This is likely something specific to your system and we're going to need to get the login credentials to your server and AbleCommerce. Can you open a support case please?
Thanks!
Re: Deleting Anonymous Users
Posted: Sat Oct 23, 2010 4:04 pm
by Brewhaus
Will do. Thanks, Katie.
Re: Deleting Anonymous Users
Posted: Wed Jan 19, 2011 9:45 am
by sfeher
I'm having trouble getting my anonymous users to be removed too...
PLATFORM: ASP.NET
VERSION: 7.0.3
BUILD: 12458
MSSQL v2008
AC SCHEMA v2000
The SQL commands above might not work with SQL 2008..... .
Any recommendations?
Thanks!
Steve