Anonymous User and Activity Logging Maintenance

Applies to: AbleCommerce 7.0.0 through 7.0.7

Date:  11/18/2011, Amended 10/22/12 and 07/17/13

Download: Anonymous User Maintenance patch (needed for versions 7.0.0 through 7.0.6)

Note: Updated files are included with AbleCommerce 7.0.7 SR1a (build 14600)

On this page you will find information on solving maintenance problems and errors.  Please review the entire page.

 

 

Description of Anonymous User Maintenance Issue

Turning on database maintenance for the anonymous user has been an available feature since AbleCommerce 7.0.1.  However, it was not enabled in the default installation settings.  This issue has the potential for accumulating large numbers of anonymous user records in the database.

To see if your installation is impacted -

  1. Login to the Merchant Menu

  2. Go to the Configure > Maintenance page.  

  3. In the Anonymous User Maintenance section, check to see if there are values entered in the Days to Save fields.  


     

  4. If the fields above are blank, then anonymous user maintenance is disabled, and records are accumulating in the affected database.  

  5. Read and complete the instructions below to enable anonymous user maintenance and clean-up the database.

 

Download

  • The Anonymous User Maintenance patch for all versions 7.0.6 and below only.

  • If you are running AbleCommerce 7.0.7 SR1a (build 14600), do not install the files from the download because your installation already includes all the latest updates.

 

Instructions:

  1. Extract the files to a temporary location.

  2. Copy both files to the \Admin\Store\ folder.

    \Admin\Store\Maintenance.aspx
    \Admin\Store\Maintenance.aspx.cs

  3. Refresh the page to view new features.

 

Enable Automatic Maintenance

  1. Make sure to enter a value in the Days to Save fields as indicated in the screenshot above.  

    A value of 30 days is recommended.


If you have upgraded your AbleCommerce installation from version 7.0.0 (released June 24, 2008), then your database may be missing an important update -
 

  1. Click here for instructions to fix User Constraints in the database.

Installations of 7.0.1 and later are NOT affected by this issue.

 

New Maintenance feature to cleanup database

After applying the patch, you should be able to check number of records in your database, as well as trigger the maintenance manually.  

Note:  This step is highly recommended, even if you have already enabled automatic maintenance.

 

  1. From the Configure > Maintenance page, click the CHECK DATABASE button to view the number of anonymous records in your database.


This is an example installation with maintenance running successfully -


If you have a large number of records, you may want to manually cleanup the database by following the steps below.  

 

TIP:  This is a resource intensive process so make sure to perform this task during an off-peak time.

 

  1. If needed, manually Trigger Anonymous User Maintenance from the same page.  

  2. Find the section below your database results and enter a value (in days) into the form provided.

  3. Click the DELETE ANONYMOUS USERS button to begin.

 

TIP:  If you are experiencing extremely long delays, then you may need to begin with a number larger than 30 days.  Repeat the process while gradually reducing the number of days as needed.

 

Manually trigger maintenance routine

  1. Open the /App_Data/AbleCommerce.config file.

  2. Change maintenanceInterval="360" (every 6 hours) to something smaller, like maintenanceInterval="5"

  3. Save the file and visit the website in 5 minutes.

  4. This will trigger the maintenance to clear anonymous users.

  5. Check the number of records by going to the Configure > Maintenance page.

  6. Click the CHECK DATABASE button.  

    - If the number or records is still not reducing, then check the Help > Error Log for clues.

 

Description of Activity Logging Issue

Turning on activity logging has been an available feature since AbleCommerce 7.0.0.  However, it may be possible that large numbers of records build up in the AC_Activity table with high traffic sites.  If this should happen, then we recommend that you either manually remove the records from the database, clear the log file from merchant admin, and/or change the number of days to maintain history.

To see if your installation is impacted -

  1. Login to the Merchant Menu

  2. Go to the Configure > Page Tracking page.  

  3. In the Configure Page Tracking section, check to see what value is entered in the History Length field.

  4. Next, check the Current Records value in the Current Log section.  


     

  5. If the number of records is high, then you can click the CLEAR button to remove them.

Manually Remove Anonymous Records from Database

The following SQL script can be used to manually remove anonymous users, baskets, and wishlists from the database. Before doing this, you should already have a basic understanding of SQL server scripts.

Some important notes:

The first block is a SELECT statement, so you can see how many users you would actually be affecting by running the next three statements. You can change the dates as you needed to remove more or less data.  You can also modify the AffiliateId IS NULL part to IS NOT NULL if you want to remove anonymous users who also have an affiliate assigned to them (if you even use that functionality).

We suggest running the SELECT first, and even try doing a SELECT on all three tables to make sure there is no important data in the results that you don't want to delete.

As always, make a backup of the database before continuing.

 

SELECT statement for Anonymous Users:

SELECT UserId FROM ac_Users
WHERE StoreId = 1 AND IsAnonymous = 1
AND AffiliateId IS NULL
AND (LastActivityDate IS NULL OR LastActivityDate < '2011-01-01')

 

Statements to remove Anonymous Users Baskets, Wishlists and Users.

Run in order.

DELETE FROM ac_Baskets
WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1
AND (AffiliateId IS NULL)
AND (LastActivityDate IS NULL OR LastActivityDate < '2013-01-01'))

DELETE FROM ac_Wishlists
WHERE UserId IN (SELECT UserId FROM ac_Users WHERE StoreId = 1 AND IsAnonymous = 1
AND (AffiliateId IS NULL)
AND (LastActivityDate IS NULL OR LastActivityDate < '2013-01-01'))

DELETE FROM ac_Users
WHERE StoreId = 1 AND IsAnonymous = 1
AND (AffiliateId IS NULL)
AND (LastActivityDate IS NULL OR LastActivityDate < '2013-01-01')

 

Fix User Constraints

For installations upgraded from version 7.0.0, the User Maintenance scripts may fail and result in errors like the following:

Severity: Error

Message: Error in user maintenance

Debug Data: DELETE statement conflicted with COLUMN REFERENCE constraint 'ac_Users_ac_Orders_FK1'. The conflict occurred in database 'test_01', table 'ac_Orders', column 'UserId'. The statement has been terminated.


If you should find errors like this in your \app_data\app.log file, then follow the steps below to correct the issue.

 

To Fix User Constraint error:

  1. Download the patch Fixuserconstraints.zip, extract the file, and place in a folder named \install\

  2. Open a browser, and access the page -  

e.g. http://yourdomain/install/Fixuserconstraints.aspx

  1. Click the Fix Constraints button to complete the process.

Database Scripts to fix user constraints

In the event you have access, and the ability to run an SQL query directly on the database, this is also an option to you.  Make sure to backup your database before continuing.

For SQL 2005 and 2008 Server, execute the following query:

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

 

For SQL 2000 Server, execute this query instead:

/* 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

 

 

Manual database cleanup and fixing maintenance errors

Using the Dataport, or changing the database outside of the AbleCommerce, can sometimes lead to errors such as this -

System.ArgumentException: An item with the same key has already been added.

If this should occur, you will need to open the Products table and remove any columns that shouldn't be there.

 

 

If you are getting errors similar to the one below, then your store maintenance are not working.

The DELETE statement conflicted with the REFERENCE constraint "ac_Addresses_ac_BasketShipments_FK1". The conflict occurred in database "sample_AC", table "dbo.ac_BasketShipments", column 'AddressId'.

 

It is a bad data condition that can't be fixed by running any of the standard updates we provide.

The SQL query below can be run to fix the problem.  There is a toggle so you first preview the records before deleting them.

If you notice in the script below, there is a variable called @preview.  You can change this between 0 (update) and 1 (preview).  Start with a preview and run it against the database to see if there are records it thinks need to be altered.  If so, then change preview to 0 and rerun it to reconcile the addresses.

Once this is done, the maintenance scripts should be running again.

Then you will need to run this script on your MS SQL 2005+ database -

DECLARE @storeId int;

DECLARE @daysToKeep int;

DECLARE @expireDate DateTime;

DECLARE @preview bit;

SET @storeId = 1;

/* CHANGE @daysToKeep TO NUMBER OF DAYS TO KEEP NONAFFILIATE ANONYMOUS USERS */

SET @daysToKeep = 5;

SET @expireDate = DATEADD(Day, -1 * @daysToKeep, GETDATE());

/* CHANGE @preview TO 0 TO EXECUTE UPDATE */

SET @preview = 1;

IF (@preview <> 0)

BEGIN

SELECT * FROM ac_BasketShipments WHERE AddressId IN (

SELECT AddressId FROM ac_Addresses WHERE UserId IN (

SELECT UserId FROM ac_Users WHERE StoreId = @storeId AND IsAnonymous = 1 AND AffiliateId IS NULL AND (LastActivityDate IS NULL OR LastActivityDate < @expireDate)

))

END

ELSE

BEGIN

UPDATE ac_BasketShipments SET AddressId = 0 WHERE AddressId IN (

SELECT AddressId FROM ac_Addresses WHERE UserId IN (

SELECT UserId FROM ac_Users WHERE StoreId = @storeId AND IsAnonymous = 1 AND AffiliateId IS NULL AND (LastActivityDate IS NULL OR LastActivityDate < @expireDate)

))

END

 

Copyright © 1994 - 2023 AbleCommerce.com, All rights Reserved | Privacy Policy

A division of Able Solutions Corporation, headquarters located in Vancouver, WA