error log "Error in user maintenance"

[ksolito]

We're getting about a dozen of these a day in the error log:

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 'shoote1_abc01', table 'ac_Orders', column 'UserId'. The statement has been terminated.

Anybody have any idea what the problem is and what I need to do to fix it?

[Logan Rhodehamel]

I believe this was a situation corrected for AC 7.0.2. What version is the error occuring in?

[ksolito]

v7.0.3, just upgraded from 7.0.2.

[Logan Rhodehamel]

Is the database SQL 2000, SQL 2005, or SQL 2008?

[ksolito]

Showing as SQL Server 8.0.2055 so SQL Server 2000.

[bhill]

We are getting this error as well but using VERSION: 7.0.2
SQL 2005

[mfreeze]

I am also getting these messages. They seem to occur once a day. The SQL server is 2005 and AC is at 7.0.2.

I am getting the messages for both user and checkout maintenance. The full messages are:

Error in anonymous checkout maintenance The DELETE statement conflicted with the REFERENCE constraint "ac_Users_ac_Orders_FK1". The conflict occurred in database "ac7_diabetesandmore", table "diabetes.ac_Orders", column 'UserId'. The statement has been terminated.

Error in user maintenance The DELETE statement conflicted with the REFERENCE constraint "ac_Users_ac_Orders_FK1". The conflict occurred in database "ac7_diabetesandmore", table "diabetes.ac_Orders", column 'UserId'. The statement has been terminated.

Has there been any resolution or explanation for these messages?

Could these errors have anything to do with an inordinate number of abandoned baskets that we have seen since the upgrade to 7.0.2?

[Logan Rhodehamel]

Has there been any resolution or explanation for these messages?

Could these errors have anything to do with an inordinate number of abandoned baskets that we have seen since the upgrade to 7.0.2?

Your messages seem to be different. Were there any problems on database upgrade? Back up your database, then try this script:

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

We had to allow the ac_Orders.UserId field to accept nulls so that a user could be removed without losing associated order history. In your database, the constraint prevents a user with orders from being deleted. This is especially problematic for "anonymous" users who have a dummy account created during the order process. Maintenance is supposed to remove these accounts as they contain no useful (in the long term) information.

[mfreeze]

I received some errors when trying to run the script. I changed ac_orders to xxxxx.ac_orders where xxxxx = the table owner name and got rid of the first set. Then I got:

Msg 2714, Level 16, State 4, Line 3
There is already an object named 'ac_Users_ac_Orders_FK1' in the database.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.

[mfreeze]

BTW. I just upgraded to 7.03. Does the script still need to be run? Should I wait to see if the errors appear again?

I got no errors with the DB upgrade to 7.02 or 7.03.

Also, could these errors explain a HUGE increase in abandoned baskets after the 7.02 upgrade? We went from 2-4 per day before the upgrade to 20-30 after. Most of these abandoned baskets are anonymous users.

[ksolito]

Logan,

I ran your script from two posts above (Tue Sep 22, 2009 5:56 pm) and got the following error:

Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'SET'.

System is:
PLATFORM: ASP.NET
VERSION: 7.0.3
BUILD: 12458
MSSQL v2000
AC SCHEMA v2000

This problem has been remained unresolved for a long time and we continue to get anywhere from 6 to a dozen or more of these errors daily. I am open to any suggestions on how to resolve it.

[Logan Rhodehamel]

Logan,

I ran your script from two posts above (Tue Sep 22, 2009 5:56 pm) and got the following error:

My script above was specific to SQL 2005. I can help you get your database back to good in the following steps, specific to SQL 2000 servers.

1. Backup your database
2. Remove existing constraint
3. Add the correct constraint
4. Drop existing trigger for user deletes
5. Add correct trigger for user deletes

Step 1: Back up your database. If you do not, and something is wrong with this script, you won't be able to recover. So just make a backup and be safe. If something goes wrong you can restore and report back.

For the remainder of the steps, run this script through query analyzer:

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

[sfeher]

Logan --
Getting this same error on SQL 2008. My specs:

PLATFORM: ASP.NET
VERSION: 7.0.3
BUILD: 12458
MSSQL v2008
AC SCHEMA v2000

[Logan Rhodehamel]

PLATFORM: ASP.NET
VERSION: 7.0.3
BUILD: 12458
MSSQL v2008
AC SCHEMA v2000

I should have qualified this better. I believe you will need to follow the instructions for the SQL 2000 from the comment immediately prior to yours. Please back up your database first.

Your AC schema is still using the 2000 model, even though you have the newer database. I am pretty certain we published a schema upgrade script with 7.0.3 if you are interested in upgrading that.

[Kalamazoo]

I have a client with AC 7.03 SP1 and we just ran the above 5 step script with no known errors on SQL 2008. I will post back if this has worked.

Thank you.

Phil Chrisman

[Kalamazoo]

Just to keep you updated. This ran in SQL 2008 no problem. I have one errant record now that gives off one entry in the error log each day so hopefully I can go in and just "root-it-out" of the database.

But all in all, I am very pleased with how well this ran in SQL 2008 and IIS7.

All the best,

Phil Chrisman

[dnoell]

I am receiving the error "Error in user maintenance" as reported by everyone else but I have the following in Debug Data message:
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." I am also receiving the following error messages with the debug data above:

1. Error in anonymous checkout maintenance
2. Error in payment account data maintenance
3. Error in anonymous checkout maintenance

I am running an earlier version of AbleCommerce that has been **very stable** for two years. Why would these errors appear now? The error has been appearing regularly for the past week. Any help would be appreciated.

My version of AbleCommerce is as follows:
PLATFORM: ASP.NET
VERSION: 7.0.0
BUILD: 11659

Thanks for any help understaning this issue,

David Noell

[sfeher]

PLATFORM: ASP.NET
VERSION: 7.0.3
BUILD: 12458
MSSQL v2008
AC SCHEMA v2000

I should have qualified this better. I believe you will need to follow the instructions for the SQL 2000 from the comment immediately prior to yours. Please back up your database first.

Your AC schema is still using the 2000 model, even though you have the newer database. I am pretty certain we published a schema upgrade script with 7.0.3 if you are interested in upgrading that.

Logan --
Coming back to this issue (after quite some time)..... still seeing a large number of abandoned carts from anonymous users. You mentioned an schema upgrade script for 7.0.3. --- can I give that a shot?

[meer2005]

Ran the schema update that was part of the 7.0.3 bundle and it said it was successful, but the admin still says AC SCHEMA v2000?? Any ideas?