Error in user maintenance; could not delete user id

[HaggisMan]

Recently upgraded from 7.0.4 to 7.0.7, then to 7.0.8 (Gold). Have started seeing the following in the Ablecommerce Error log regarding one specific user:

Severity: Error
Message: Error in user maintenance; could not delete user id 118030
Exception: could not execute native bulk manipulation query:DELETE FROM ac_Users WHERE UserId = :userId[SQL: DELETE FROM ac_Users WHERE UserId = @p0]
Stack Trace: at NHibernate.Engine.Query.NativeSQLQueryPlan.PerformExecuteUpdate(QueryParameters queryParameters, ISessionImplementor session)
at NHibernate.Impl.SessionImpl.ExecuteNativeUpdate(NativeSQLQuerySpecification nativeQuerySpecification, QueryParameters queryParameters)
at NHibernate.Impl.SqlQueryImpl.ExecuteUpdate()
at CommerceBuilder.Services.MaintenanceWorker.a(IList`1 A_0)
Inner Exception: The DELETE statement conflicted with the REFERENCE constraint "ac_Users_ac_Orders_FK1". The conflict occurred in database "MYDATABASE", table "dbo.ac_Orders", column 'UserId'.
The statement has been terminated.
Inner Exception Stack Trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at NHibernate.AdoNet.AbstractBatcher.ExecuteNonQuery(IDbCommand cmd)
at NHibernate.Engine.Query.NativeSQLQueryPlan.PerformExecuteUpdate(QueryParameters queryParameters, ISessionImplementor session)

That particular userid submitted an order successfully, payment was processed, but the order is in Shipment Pending mode. This isn't an anonymous user, although the order was begun as an anonymous user who then created an account on checkout. I've seen the Anonymous User Naintenance tech bulletin (http://help.ablecommerce.com/upgrades/a ... enance.htm) and looked at the "Fix User Constraints" section, but this doesn't seem to fit the mold - the initial install was 7.0.4, not 7.0.0. Other orders are successful and don't generate error log Maintenance entries every day like this one does. This is the only order that contains a shippable product since the upgrade to Gold, however. Any clues or things I should look at?

System Specs:

AbleCommerce Server:
Windows 2008 Standard, SP2 64 bit
ASP.NET 4.0
IIS 7.0 (7.0.6000.16386)
AbleCommerce Gold (build 5410)

Database Server
Windows Server 2008 R2 Standard, SP1 64 bit
MS SQL Server 2008 R2 Standard Version 10.50.2550.0
Database Compatability level 100 (SQL Server 2008)
Database was restored from a SQL 2000 backup and compatibility level was set from 80 to 100 in August 2012. No AbleCommerce errors occured at the then AC version 7.0.4.

Thanks,

HaggisMan

[ForumsAdmin]

We are going to investigate this. Apparently it looks like the maintenance routine is trying to delete a user that it not anonymous and has an order associated with it.

[mazhar]

Can you please run following query to get ac_Orders table schema information and post the results here. ac_Users_ac_Orders_FK1 constrain should set UserId column to null if associated User is deleted. I want to see if UserId column is nullable or not in case somthing got changed during upgrades.

Code: Select all

SELECT 
   ORDINAL_POSITION
  ,COLUMN_NAME
  ,DATA_TYPE
  ,CHARACTER_MAXIMUM_LENGTH
  ,IS_NULLABLE
  ,COLUMN_DEFAULT
FROM   
  INFORMATION_SCHEMA.COLUMNS 
WHERE   
  TABLE_NAME = 'ac_Orders' 
ORDER BY 
  ORDINAL_POSITION ASC; 

[HaggisMan]

Hi Mazhar,

Here are the query results:

Code: Select all

ORDINAL_POSITION	COLUMN_NAME	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	IS_NULLABLE	COLUMN_DEFAULT
1	OrderId	int	NULL	NO	NULL
2	OrderNumber	int	NULL	NO	NULL
3	OrderDate	datetime	NULL	NO	NULL
4	StoreId	int	NULL	NO	NULL
5	UserId	int	NULL	YES	NULL
6	AffiliateId	int	NULL	YES	NULL
7	BillToFirstName	nvarchar	30	YES	NULL
8	BillToLastName	nvarchar	50	YES	NULL
9	BillToCompany	nvarchar	50	YES	NULL
10	BillToAddress1	nvarchar	255	YES	NULL
11	BillToAddress2	nvarchar	255	YES	NULL
12	BillToCity	nvarchar	50	YES	NULL
13	BillToProvince	nvarchar	50	YES	NULL
14	BillToPostalCode	nvarchar	15	YES	NULL
15	BillToCountryCode	char	2	YES	NULL
16	BillToPhone	nvarchar	50	YES	NULL
17	BillToFax	nvarchar	50	YES	NULL
18	BillToEmail	nvarchar	255	YES	NULL
19	ProductSubtotal	decimal	NULL	NO	NULL
20	TotalCharges	decimal	NULL	NO	NULL
21	TotalPayments	decimal	NULL	NO	NULL
22	OrderStatusId	int	NULL	NO	NULL
23	Exported	bit	NULL	NO	NULL
24	RemoteIP	varchar	39	YES	NULL
25	Referrer	nvarchar	255	YES	NULL
26	GoogleOrderNumber	nvarchar	50	YES	NULL
27	PaymentStatusId	tinyint	NULL	NO	NULL
28	ShipmentStatusId	tinyint	NULL	NO	NULL
29	TaxExemptionType	int	NULL	NO	NULL
30	TaxExemptionReference	nvarchar	200	YES	NULL

Thanks!

HaggisMan

[mazhar]

UserId does seems to be nullable which is correct. I wasn't able to produce the problem in Gold. Please execute following query and post the result back

Code: Select all

SELECT name, delete_referential_action_desc, update_referential_action_desc
FROM sys.foreign_keys
WHERE name = 'ac_Users_ac_Orders_FK1'

This query will get us the delete and update rule information for ac_Users_ac_Orders_FK1. The delelte rule should be SET NULL while update rule should be NO_ACTION.

[HaggisMan]

Query results are:

Code: Select all

name	delete_referential_action_desc	update_referential_action_desc
ac_Users_ac_Orders_FK1	NO_ACTION	NO_ACTION

[ForumsAdmin]

hmmm ... It should have been SET NULL for delete action.

[ForumsAdmin]

Please run this on your database

Code: Select all

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  

[ForumsAdmin]

This was part of the standard upgrade routine. I wonder if upgrae scripts were run, how come your database still has the wrong setting?

[ForumsAdmin]

You will probably have to drop the constraint first

Code: Select all

ALTER TABLE ac_Orders
   DROP CONSTRAINT ac_Users_ac_Orders_FK1;

[HaggisMan]

Yes, the upgrade scripts were definitely run. I'll see about dropping and recreating the constraint, but I'm mighty curious why that constraint wasn't updated. There were no errors reported during the DB upgrade part of the process.

HaggisMan