Deleting Anonymous Users

[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.

[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.

[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

[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.

[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.

[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.

[mazhar]

Have a look at this thread
viewtopic.php?f=42&t=12027&p=53189

[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?

[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.

[Brewhaus]

Thank you, Mazhar. I will try this tomorrow.

[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.

[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.

[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.

[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.

[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.

[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.

[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!

[Brewhaus]

Will do. Thanks, Katie.

[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