Error in user maintenance ac_Addresses_ac_BasketShipments_FK

[bjnoffsi]

This is in our error log:

The DELETE statement conflicted with the REFERENCE constraint "ac_Addresses_ac_BasketShipments_FK1". The conflict occurred in database "Roku.AbleCommerce", table "dbo.ac_BasketShipments", column 'AddressId'. The statement has been terminated.

Is anybody else seeing this?

I looked at the constraint specifications in the db and noticed that the Delete rule is set to No Action which means that the deletion will fail if there is an instance of the addressid in the ac_BasketShipments table. Why isn't the delete rule set to cascade? If the address record is removed from the ac_addresses table, shouldn't the child record also be removed from the ac_BasketShipments table. I updated the rule in our dev environment and noticed that the user maintenance thread successfully deleted all stale, anonymous users. Can somebody please advise on this before I change the Delete specification in our production database? Will changing this rule cause anything else to break?

THANKS!

[mazhar]

Seems like you are using an old build. I think your problem relates to following bug
http://bugs.ablecommerce.com/show_bug.cgi?id=5598

I think upgrading to at least 7.0.0 final will fix this problem.

[bjnoffsi]

Thanks for your reply Mazhar. I think I actually found the issue. The ac_Baskets_ac_BasketShipments_FK1 foreign key was missing which as you probably know causes a cascade delete in the ac_BasketShipments table. By profiling the database, I found that the user maintenance routine deletes expired records first from ac_Baskets and then subsequently from ac_Users. Because of the ac_Addresses_ac_BasketShipments_FK constraint, the orphaned records in ac_BasketShipments caused by the missing constraint were preventing the anonymous users from being removed. I added back the constraint and the maintenance routine seemed to perform as normal.

Since this has been broken for a while, we have a lot of anonymous users in the system. The background thread that does the maintenance routine performs the deletions in an iterative fashion that takes some time and uses up a considerable amount of system resources. I guess this is okay when the amount of anonymous users is small but in our case, this could significantly impact our customer experience. Therefore, I've thrown together some T-SQL which deletes the orphaned records, adds back the constraint, and removes the expired users accounts that were added to the system before 10/07/2009. Do you see any reason why I shouldn't run the below script against our database? After running this, the user maintenance routine should be able to continue as normal without error.

THANKS AGAIN FOR YOUR HELP!

Code: Select all

begin try
	begin transaction	
		delete from ac_basketshipments where basketid not in (select basketid from ac_baskets)				
		alter table ac_BasketShipments add constraint ac_Baskets_ac_BasketShipments_FK1 foreign key (BasketId) references ac_Baskets (BasketId) on update no action on delete cascade		
	
		create table #MaintenanceTable
		(
			UserId int Primary key 
		)

		insert into #MaintenanceTable (UserId)
		select UserId from ac_Users where StoreId = 1 and IsAnonymous = 1 and AffiliateId is null and ReferringAffiliateId is null and (LastActivityDate is null or LastActivityDate < '10/7/2009') 

		delete b from ac_Baskets as b join #MaintenanceTable m on b.UserId = m.UserId   
		delete w from ac_Wishlists as w join #MaintenanceTable m on w.UserId = m.UserId   
		delete u from ac_Users as u join #MaintenanceTable m on u.UserId = m.UserId where StoreId = 1

		drop table #MaintenanceTable		
		
	commit transaction
end try
begin catch
	if (@@TRANCOUNT > 0)
	begin
		SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

		rollback transaction
	end
end catch

[Logan Rhodehamel]

That's a nice script. I made two minor adjustments... I don't create the constraint if it already exists, and I set the oldest user date into a variable to make it easy to see the setting. Now the script can be run whenever you think there might be a problem with user maintenance.

Code: Select all

    begin try
       begin transaction
          DECLARE @oldestUser varchar(30);   
          SET @oldestUser = '11/1/2009'
	
          delete from ac_basketshipments where basketid not in (select basketid from ac_baskets)

          IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'ac_BasketShipments'
          AND CONSTRAINT_NAME = 'ac_Baskets_ac_BasketShipments_FK1')
          BEGIN
             alter table ac_BasketShipments add constraint ac_Baskets_ac_BasketShipments_FK1 foreign key (BasketId) references ac_Baskets (BasketId) on update no action on delete cascade
          END

          create table #MaintenanceTable
          (
             UserId int Primary key
          )

          insert into #MaintenanceTable (UserId)
          select UserId from ac_Users where StoreId = 1 and IsAnonymous = 1 and AffiliateId is null and ReferringAffiliateId is null and (LastActivityDate is null or LastActivityDate < @oldestUser)

          delete b from ac_Baskets as b join #MaintenanceTable m on b.UserId = m.UserId   
          delete w from ac_Wishlists as w join #MaintenanceTable m on w.UserId = m.UserId   
          delete u from ac_Users as u join #MaintenanceTable m on u.UserId = m.UserId where StoreId = 1

          drop table #MaintenanceTable      
          
       commit transaction
    end try
    begin catch
       if (@@TRANCOUNT > 0)
       begin
          SELECT
            ERROR_NUMBER() AS ErrorNumber
            ,ERROR_SEVERITY() AS ErrorSeverity
            ,ERROR_STATE() AS ErrorState
            ,ERROR_PROCEDURE() AS ErrorProcedure
            ,ERROR_LINE() AS ErrorLine
            ,ERROR_MESSAGE() AS ErrorMessage;

          rollback transaction
       end
    end catch

[Kalamazoo]

Thank you for these scripts. I will report back as to which ones are working but both were accepted.

Phil Chrisman