Errors in Anonymous User Maintenance

euroluxantiques · Post by **euroluxantiques** » Sat Sep 21, 2013 9:16 am

Our site is generating 1000s of errors in our Error Log daily for anonymous user maintenance. The types of errors we keep getting are:

Error in user maintenance; could not delete user id 2711244
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.DeleteUsers(IList`1 idList) Inner Exception: The DELETE statement conflicted with the REFERENCE constraint "ac_Users_ac_PageViews_FK1". The conflict occurred in database "AbleCommerceGoldR5Live", table "dbo.ac_PageViews", 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.SqlInternalConnection.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)

I have run various scripts to clean up anonymous user data, as suggested in the forums and in this document http://help.ablecommerce.com/index.htm# ... enance.htm, but it seems the errors keep getting generated. Does anyone know how to stop this from happening. If I do not clean these out manually every couple of days, it doesn't take long before the errors are in the 100s of thousands and bogging down the error table.

rlopez · Post by **rlopez** » Sun Sep 22, 2013 10:04 pm

We were getting similar errors in our logs since upgrading to R6. Ive set all the parameters per Able instructions for Users and Group maintanence and the errors are only handful during a 24hr period now. Once again our build is R6 Dev site. Any feedback would be great.

Post by **mazhar** » Mon Sep 23, 2013 9:05 am

Can you run following query on your database and post the results here. The query will simply list what type of actions are set on delete and update rules on this constraint.

Code: Select all

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

euroluxantiques · Post by **euroluxantiques** » Tue Sep 24, 2013 2:15 pm

name delete_referential_action_desc update_referential_action_desc
ac_Users_ac_PageViews_FK1 NO_ACTION NO_ACTION

rlopez · Post by **rlopez** » Thu Sep 26, 2013 1:46 pm

When we ran the SQL query we received the same response as listed above. Is there any follow-up regarding this from Able? I'm now receiving this error once every hour. I have no groups set-up this is a Dev site. The store is not live so there is no daily anonymous users that would need to be deleted and the parameters are blank in the admin side set up for anonymous user management. We are using R6 build sql 2005 express if that means anything.

Post by **mazhar** » Mon Sep 30, 2013 3:36 am

Well I tried to reproduce it but anonymous users are cleanedup without any issues for me. I have confirmed that ac_Users_ac_PageViews_FK1 should have its both delte and udpate action set to none. I have SQL Express 2K8. Maybe try editing the ac_Users_ac_PageViews_FK1 constraint and make sure to save it with its both delte and update actions set to none.

euroluxantiques · Post by **euroluxantiques** » Mon Sep 30, 2013 2:07 pm

I checked the update/delete for ac_Users_ac_PageViews_FK1 and they are both set to No Action. Meanwhile, I have over 1 million error messages in the website that have to be cleared manually ever few days.

euroluxantiques · Post by **euroluxantiques** » Tue Oct 15, 2013 8:59 pm

Still generating these types of errors very frequently and having to clear them manually. Can someone please suggest a solution? Thanks!

AbleOne · Post by **AbleOne** » Tue Dec 03, 2013 12:38 pm

We are still experiencing this issue. We have 3 stores that are live that are getting thousands of errors per day. Please let us know when/if this is resolved!

jmestep · Post by **jmestep** » Tue Dec 03, 2013 2:11 pm

I'm not seeing it with page views, but I did see it quite heavily on a site not on our servers who went from 7 to Gold. They were actually having checkout errors on Friday and once I manually did the steps in this following page, I am not seeing the errors now. I'm guessing that site had not had the routines run when it was Able 7.
http://help.ablecommerce.com/upgrades/a ... enance.htm

Post by **mazhar** » Thu Dec 05, 2013 2:43 am

euroluxantiques wrote:Still generating these types of errors very frequently and having to clear them manually. Can someone please suggest a solution? Thanks!

AbleOne wrote:We are still experiencing this issue. We have 3 stores that are live that are getting thousands of errors per day. Please let us know when/if this is resolved!

Can you please run following query and post the results here.

Code: Select all

SELECT name, is_disabled, delete_referential_action_desc, update_referential_action_desc
FROM sys.foreign_keys
WHERE name IN('ac_Users_ac_PageViews_FK1','ac_Users_ac_Orders_FK1')

Also please provide information about which version of Gold you are using? This was a fresh install or upgrade previous version of AC 7/Gold?

rlopez · Post by **rlopez** » Sat Dec 07, 2013 9:47 am

Here are my results. We are running Gold 6 Build 6169 which upgraded DEV site. Database is SQl 2005 Express.

ac_Users_ac_Orders_FK1 0 SET_NULL NO_ACTION
ac_Users_ac_PageViews_FK1 1 NO_ACTION NO_ACTION

Results:
2 row(s) affected

Post by **Katie** » Tue Dec 10, 2013 4:01 pm

We are running Gold 6 Build 6169 which upgraded DEV site.

Did you upgrade from version 7? If so, did you checkout the post from Judy above?

rlopez · Post by **rlopez** » Wed Dec 11, 2013 7:27 am

Yes we upgraded from Able 5.5 to 7.0.7 then onto Gold. However this problem started after upgrading from Gold R4 to R6. Yes, we did try the settings in the help files that Judy posted above as well as other settings that have been recommended in earlier post above. Still getting errors.

Post by **Katie** » Wed Dec 11, 2013 7:57 am

Ok. That's a big jump. Did you spend some time testing to make sure the upgrade from 7.0.7 was successful? This would be important to narrow down the problem to one of the two upgrades. (both major)
You might want to post your exact error messages.

Also, how big is this database?
Try running a database performance wizard. The steps are described here:

viewtopic.php?f=42&t=17625&p=75890&hili ... ard#p75890

Lastly, did you upgrade directly from R4 to R6 using the upgrade files? I'm asking because there is no direct upgrade from R4 to R6. You need to apply the files from the R5 upgrade as well. What steps did you follow to upgrade from R4 to R6?

rlopez · Post by **rlopez** » Wed Dec 11, 2013 8:35 am

Yes we did test after upgrading from 5.5 to 7.0.7 which was successful and was required in order to get to Gold. Note: Database was cleaned up prior with Anonymous users before the 5.5 to 7.0.7 upgrade and site was in Dev mode at that time as it is now. We have around 20M users. Then off to gold we went using incremental upgrades as they have come down the pipeline and yes we upgraded from R4 to R5 and then R6. We have been at this for over a year because of the jump we have made. Once again we were not seeing any errors until we upgraded from R4, R5 and then to R6. However we did not do any testing between R4 to R5. So I cannot tell if the problem started at R5 or R6. But the errors are still being generated now. Here is a copy.

Date: 12/7/2013 3:22:56 PM
Severity: Error
Message: Error in user maintenance
Exception: could not execute native bulk manipulation query: INSERT INTO ac_UserGroups (UserId, GroupId) SELECT 328047 ,9 UNION ALL SELECT 438191 ,9 UNION ALL SELECT 467716 ,9 UNION ALL SELECT 507013 ,9 UNION ALL SELECT 509491 ,9 UNION ALL SELECT 509585 ,9 UNION ALL SELECT 509720 ,9 UNION ALL SELECT 511275 ,9 UNION ALL SELECT 511503 ,9 UNION ALL SELECT 515320 ,9 UNION ALL SELECT 515339 ,9 UNION ALL SELECT 516150 ,9 UNION ALL SELECT 516189 ,9 UNION ALL SELECT 516361 ,9 UNION ALL SELECT 516520 ,9 UNION ALL SELECT 518176 ,9 UNION ALL SELECT 518334 ,9 UNION ALL SELECT 518392 ,9 UNION ALL SELECT 518419 ,9 UNION ALL SELECT 518591 ,9 UNION ALL SELECT 520153 ,9 UNION ALL SELECT 520222 ,9 UNION ALL SELECT 520230 ,9 UNION ALL SELECT 523547 ,9 UNION ALL SELECT 523931 ,9 UNION ALL SELECT 526602 ,9 UNION ALL SELECT 526809 ,9 UNION ALL SELECT 527364 ,9 UNION ALL SELECT 528376 ,9 UNION ALL SELECT 531180 ,9 UNION ALL SELECT 532606 ,9 UNION ALL SELECT 532652 ,9 UNION ALL SELECT 534452 ,9 UNION ALL SELECT 534903 ,9 UNION ALL SELECT 534936 ,T 19694979 ,9 UNION ALL SELECT 19695201 ,9 UNION ALL SELECT 19706034 ,9 UNION ALL SELECT 19713553 ,9 UNION ALL SELECT 19720614 ,9 UNION ALL SELECT 19727717 ,9 UNION ALL SELECT 19735802 ,9 UNION ALL SELECT 19739188 ,9 UNION ALL SELECT 19749171 ,9 UNION ALL SELECT 19749955 ,9 UNION ALL SELECT 19749956 ,9 UNION ALL SELECT 19749958 ,9 UNION ALL SELECT 19752090 ,9 UNION ALL SELECT 19753056 ,9 UNION ALL SELECT 19759656 ,9 UNION ALL SELECT 19764806 ,9 UNION ALL SELECT 19768673 ,9 UNION ALL SELECT 19768789 ,9 UNION ALL SELECT 19770643 ,9 UNION ALL SELECT 19770665 ,9 UNION ALL SELECT 19770924 ,9 UNION ALL SELECT 19789120 ,9 UNION ALL SELECT 19795268 ,9 UNION ALL SELECT 19801205 ,9 UNION ALL SELECT 19813519 ,9 UNION ALL SELECT 19813520 ,9 UNION ALL SELECT 19823278 ,9 UNION ALL SELECT 19826805 ,9 UNION ALL SELECT 19830356 ,9 UNION ALL SELECT 19830753 ,9 UNION ALL SELECT 19830801 ,9 UNION ALL SELECT 19831066 ,9 UNION ALL SELECT 19831386 ,9 UNION ALL SELECT 19834394 ,9 UNION ALL SELECT 19834397 ,9 UNION ALL SELECT 19834897 ,,9 UNION ALL SELECT 20262962 ,9 UNION ALL SELECT 20264623 ,9 UNION ALL SELECT 20265836 ,9 UNION ALL SELECT 20265852 ,9 UNION ALL SELECT 20266744 ,9 UNION ALL SELECT 20273566 ,9 UNION ALL SELECT 20282688 ,9 UNION ALL SELECT 20312303 ,9 UNION ALL SELECT 20325020 ,9 UNION ALL SELECT 20353105 ,9 UNION ALL SELECT 20355543 ,9 UNION ALL SELECT 20355913 ,9 UNION ALL SELECT 20356723 ,9 UNION ALL SELECT 20358204 ,9 UNION ALL SELECT 20359037 ,9 UNION ALL SELECT 20373581 ,9 UNION ALL SELECT 20376822 ,9 UNION ALL SELECT 20377319 ,9 UNION ALL SELECT 20378181 ,9 UNION ALL SELECT 20379498 ,9 UNION ALL SELECT 20379683 ,9 UNION ALL SELECT 20391361 ,9 UNION ALL SELECT 20392591 ,9 UNION ALL SELECT 20395966 ,9 UNION ALL SELECT 20396612 ,9 UNION ALL SELECT 20404517 ,9 UNION ALL SELECT 20404619 ,9 UNION ALL SELECT 20409288 ,9 UNION ALL SELECT 20409870 ,9 UNION ALL SELECT 20411190 ,9 UNION ALL SELECT 20411356 ,9 UNION ALL SELECT 20411411 ,9 UNION ALL SELECT 20412772 ,9 UNION ALL SELECT 20414029 ,9 UNION ALL SELECT 20414380 ,9 UNION ALL SELECT 20414623 ,9 UNION ALL SELECT 20414875 ,9 UNION ALL SELECT 20418936 ,9 UNION ALL SELECT 20422899 ,9 UNION ALL SELECT 20423277 ,9 UNION ALL SELECT 20423905 ,9 UNION ALL SELECT 20423932 ,9 UNION ALL SELECT 20425602 ,9 UNION ALL SELECT 20425692 ,9 UNION ALL SELECT 20426485 ,9 UNION ALL SELECT 20435190 ,9 UNION ALL SELECT 20435465 ,9 UNION ALL SELECT 20440055 ,9 UNION ALL SELECT 20442487 ,9]
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.MaintainUsers(Int32 anonymousUserLifespan, Int32 anonymousAffiliateUserLifespan, Boolean includeInactive)
at CommerceBuilder.Services.MaintenanceWorker.RunMaintenance()
Inner Exception: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
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.RunExecuteNonQueryTds(String methodName, Boolean async)
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)

Post by **Katie** » Wed Dec 11, 2013 9:09 am

A developer will need to look at the error message and see if there is some code problem to be solved. But, I am a little shocked that you have 20 million user records in an SQL Express 2005 database. You do know that the physical size of the database is limited to 4GB, and can only use 1GB of RAM? We only recommend use of Express database for development use, so make sure that you're not running into performance issues.

I'll see if I can get one of our developers to respond to the error message. It might take a day though, so please be patient with us.

Katie

rlopez · Post by **rlopez** » Wed Dec 11, 2013 10:11 am

Katie,
We have around 20 thousand (20M). Sorry for the confusion.

ForumsAdmin · Post by **ForumsAdmin** » Wed Dec 11, 2013 10:12 pm

Inner Exception: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Looks like DB server fails to complete the operation for some reason. Most likely because the query being run is too big an complicated. I think there should a a way to improve this query. We will look into this and let you know.

jmestep · Post by **jmestep** » Thu Dec 12, 2013 6:57 am

We upgraded a site from 706 to R6 yesterday and I had run all the scripts to clean out users the night before- it had got rid of 200,000+ users. I can't past the error in here- it was too many characters, so I have uploaded it in a file. It was caused by running maintenance. I'm not sure what triggered the error at the top of the same file- it was inserting into users.

Post by **mazhar** » Fri Dec 13, 2013 4:35 am

rlopez wrote:Yes we did test after upgrading from 5.5 to 7.0.7 which was successful and was required in order to get to Gold. Note: Database was cleaned up prior with Anonymous users before the 5.5 to 7.0.7 upgrade and site was in Dev mode at that time as it is now. We have around 20M users. Then off to gold we went using incremental upgrades as they have come down the pipeline and yes we upgraded from R4 to R5 and then R6. We have been at this for over a year because of the jump we have made. Once again we were not seeing any errors until we upgraded from R4, R5 and then to R6. However we did not do any testing between R4 to R5. So I cannot tell if the problem started at R5 or R6. But the errors are still being generated now. Here is a copy.

Date: 12/7/2013 3:22:56 PM
Severity: Error
Message: Error in user maintenance
Exception: could not execute native bulk manipulation query: INSERT INTO ac_UserGroups (UserId, GroupId) SELECT 328047 ,9 UNION ALL SELECT 438191 ,9 UNION ALL SELECT 467716 ,9 UNION ALL SELECT 507013 ,9 UNION ALL SELECT 509491 ,9 UNION ALL SELECT 509585 ,9 UNION ALL SELECT 509720 ,9 UNION ALL SELECT 511275 ,9 UNION ALL SELECT 511503 ,9 UNION ALL SELECT 515320 ,9 UNION ALL SELECT 515339 ,9 UNION ALL SELECT 516150 ,9 UNION ALL SELECT 516189 ,9 UNION ALL SELECT 516361 ,9 UNION ALL SELECT 516520 ,9 UNION ALL SELECT 518176 ,9 UNION ALL SELECT 518334 ,9 UNION ALL SELECT 518392 ,9 UNION ALL SELECT 518419 ,9 UNION ALL SELECT 518591 ,9 UNION ALL SELECT 520153 ,9 UNION ALL SELECT 520222 ,9 UNION ALL SELECT 520230 ,9 UNION ALL SELECT 523547 ,9 UNION ALL SELECT 523931 ,9 UNION ALL SELECT 526602 ,9 UNION ALL SELECT 526809 ,9 UNION ALL SELECT 527364 ,9 UNION ALL SELECT 528376 ,9 UNION ALL SELECT 531180 ,9 UNION ALL SELECT 532606 ,9 UNION ALL SELECT 532652 ,9 UNION ALL SELECT 534452 ,9 UNION ALL SELECT 534903 ,9 UNION ALL SELECT 534936 ,T 19694979 ,9 UNION ALL SELECT 19695201 ,9 UNION ALL SELECT 19706034 ,9 UNION ALL SELECT 19713553 ,9 UNION ALL SELECT 19720614 ,9 UNION ALL SELECT 19727717 ,9 UNION ALL SELECT 19735802 ,9 UNION ALL SELECT 19739188 ,9 UNION ALL SELECT 19749171 ,9 UNION ALL SELECT 19749955 ,9 UNION ALL SELECT 19749956 ,9 UNION ALL SELECT 19749958 ,9 UNION ALL SELECT 19752090 ,9 UNION ALL SELECT 19753056 ,9 UNION ALL SELECT 19759656 ,9 UNION ALL SELECT 19764806 ,9 UNION ALL SELECT 19768673 ,9 UNION ALL SELECT 19768789 ,9 UNION ALL SELECT 19770643 ,9 UNION ALL SELECT 19770665 ,9 UNION ALL SELECT 19770924 ,9 UNION ALL SELECT 19789120 ,9 UNION ALL SELECT 19795268 ,9 UNION ALL SELECT 19801205 ,9 UNION ALL SELECT 19813519 ,9 UNION ALL SELECT 19813520 ,9 UNION ALL SELECT 19823278 ,9 UNION ALL SELECT 19826805 ,9 UNION ALL SELECT 19830356 ,9 UNION ALL SELECT 19830753 ,9 UNION ALL SELECT 19830801 ,9 UNION ALL SELECT 19831066 ,9 UNION ALL SELECT 19831386 ,9 UNION ALL SELECT 19834394 ,9 UNION ALL SELECT 19834397 ,9 UNION ALL SELECT 19834897 ,,9 UNION ALL SELECT 20262962 ,9 UNION ALL SELECT 20264623 ,9 UNION ALL SELECT 20265836 ,9 UNION ALL SELECT 20265852 ,9 UNION ALL SELECT 20266744 ,9 UNION ALL SELECT 20273566 ,9 UNION ALL SELECT 20282688 ,9 UNION ALL SELECT 20312303 ,9 UNION ALL SELECT 20325020 ,9 UNION ALL SELECT 20353105 ,9 UNION ALL SELECT 20355543 ,9 UNION ALL SELECT 20355913 ,9 UNION ALL SELECT 20356723 ,9 UNION ALL SELECT 20358204 ,9 UNION ALL SELECT 20359037 ,9 UNION ALL SELECT 20373581 ,9 UNION ALL SELECT 20376822 ,9 UNION ALL SELECT 20377319 ,9 UNION ALL SELECT 20378181 ,9 UNION ALL SELECT 20379498 ,9 UNION ALL SELECT 20379683 ,9 UNION ALL SELECT 20391361 ,9 UNION ALL SELECT 20392591 ,9 UNION ALL SELECT 20395966 ,9 UNION ALL SELECT 20396612 ,9 UNION ALL SELECT 20404517 ,9 UNION ALL SELECT 20404619 ,9 UNION ALL SELECT 20409288 ,9 UNION ALL SELECT 20409870 ,9 UNION ALL SELECT 20411190 ,9 UNION ALL SELECT 20411356 ,9 UNION ALL SELECT 20411411 ,9 UNION ALL SELECT 20412772 ,9 UNION ALL SELECT 20414029 ,9 UNION ALL SELECT 20414380 ,9 UNION ALL SELECT 20414623 ,9 UNION ALL SELECT 20414875 ,9 UNION ALL SELECT 20418936 ,9 UNION ALL SELECT 20422899 ,9 UNION ALL SELECT 20423277 ,9 UNION ALL SELECT 20423905 ,9 UNION ALL SELECT 20423932 ,9 UNION ALL SELECT 20425602 ,9 UNION ALL SELECT 20425692 ,9 UNION ALL SELECT 20426485 ,9 UNION ALL SELECT 20435190 ,9 UNION ALL SELECT 20435465 ,9 UNION ALL SELECT 20440055 ,9 UNION ALL SELECT 20442487 ,9]
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.MaintainUsers(Int32 anonymousUserLifespan, Int32 anonymousAffiliateUserLifespan, Boolean includeInactive)
at CommerceBuilder.Services.MaintenanceWorker.RunMaintenance()
Inner Exception: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
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.RunExecuteNonQueryTds(String methodName, Boolean async)
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)

Please make sure you have database backup and its up to date

You need to execute following query on your database

Code: Select all

declare @groupId as INT
set @groupId = (SELECT GroupId FROM ac_Groups WHERE Name = '_Default_')
INSERT INTO ac_UserGroups(UserId, GroupId)
SELECT UserId, @groupId FROM ac_Users WHERE 
UserId NOT IN (SELECT DISTINCT U.UserId FROM ac_Users U LEFT JOIN ac_UserGroups UG ON U.UserId = UG.UserId WHERE 
(UG.GroupId = @groupId OR (UG.UserId IS NOT NULL AND UG.SubscriptionId IS NULL))) AND IsAnonymous = 0

After execution if it went well it show you the number of affected rows so please share the number here. This query will attempt to fix what the other failing query is trying to do. Hopefully this will get you pass the problem.

rlopez · Post by **rlopez** » Fri Dec 13, 2013 10:05 am

Ok I ran the query from above and this is how many rows were affected: (5539 row(s) affected). So I will clear out errors and see if this may solve the problem. Thanks again for your help.
Rick

rlopez · Post by **rlopez** » Fri Dec 13, 2013 9:47 pm

Mazhar, After running the query above that you have provided we are no longer receiving anymore ( Error's in user maintenance) as listed above. We will continue to monitor and will post back if the error reappears.
Thank you again for your help.
Rick

euroluxantiques · Post by **euroluxantiques** » Tue Jan 14, 2014 9:06 pm

I just saw the latest posts in this topic, so I ran the SQL code posted by Mazhar. I returned 0 records, so it seems the code didn't do anything for me. I'm still generating tons of errors every day. I'm around 26,000 today, and I clear this out daily to keep the error log from getting into the millions.

euroluxantiques · Post by **euroluxantiques** » Tue Jan 14, 2014 9:14 pm

I ran the code from Mazhar's post December 5, 2013, and got:

ac_Users_ac_Orders_FK1 0 SET_NULL NO_ACTION
ac_Users_ac_PageViews_FK1 0 NO_ACTION NO_ACTION

Running AbleCommerce for ASP.NET
VERSION: 7.0.86.6169
Running on SQL Server 2008 R2

AbleCommerce Shopping Cart Software

Errors in Anonymous User Maintenance

Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance

Re: Errors in Anonymous User Maintenance