Thousands of empty customer records created every day

Since my migration to NopCommerce completed I find myself deleting literally thousands of empty customer records every day. These records only have a an id and LastIpAddress  and nothing more.

I would like to suggest an extra before creating a customer record. at least an email adress should be available

Nop support guest checkout features. These all are guest customers. To keep shopping cart and wishlist saved for anonymous  users, nop automatically create guest user for each new browser instance ( cookie based ). You may want to enable Delete Guest scheduler task to delete guest account periodically, or you can delete guest account from admin manually ( admin -> system -> maintenance )

thx!

Hi guys! I'm new on nopCommerce... just one thing..

I'm configuring my new store, so it's closed right now... I have it on a subdomain for now (the main www it's been redirected to our current store) so in theory no one but I should be accessing the store.. but I see the same IP (fron USA) has a Guest over and over again every day in periods of 60 to 90 seconds!... Then could be hours without another Guest account.. and then 10 or 20 new Guest accounts with 60 to 90 secs.. All of them from the same IP address

Mine is a Spanish IP, and the system recognize it.. so it's not me.

is it a hack attempt or something? should I be worry?

Thanks!

shahdat45ict wrote:

Today, I noticed that since September (after i upgraded to 3.40) I have also had 3,000-5,000 new customer accounts created every day! This was not a problem before I upgraded to 3.40. Even with your above explanation, I do not believe that we had enough genuine traffic to explain so many cookies being issued/records inserted, and I am sure that Google analytics reports will confirm. I assumed the delete guests scheduled task was working, because it runs daily with no reported errors...

So I guess this means that whatever bot is slamming my site with bogus customer registrations is also adding an item to it's shopping cart each time, or else the scheduled task would have deleted them, right?? So there are probably also hundreds of thousands of bogus CartItem records inserted since September???

OK, so this is a problem that I need to solve as soon as possible, but what is frustrating me right now, is the huge number of guest accounts per day that have accumulated since September. Hundreds of thousands.

If I try to use the delete guests tool in admin >> system >> maintenance, and specify more than one day in the date range, it errors almost immediately with a timeout. I can only clean-up one day at a time, and that takes a little more than an hour to complete... with the CPU pegged out for the entire time! SQL server is never using more than about 8% of the CPU, whereas the w3wp is using 85-95% constantly...

What I am hoping is that someone can suggest a way that I can 'clean-up' my customers table via SQL script that I can execute in SSMS so that the w3wp is not involved in the operation.

Best case scenario for me right now is to perform the operation nightly (very late at night) on one day, because i cannot afford my site to perform so poorly for too long of a time and risk google indexing problems...and just a manual clean up of one day, per day, I will never make enough progress...

HELP, Please!

What version of nopC are you using?
3.50 introduced "Performance optimization. Use stored procedure to delete guest customer records."
I've not tried it on prior versions, but it looks like it should work fine - it only references Customer & GenericAttribute tables.


CREATE PROCEDURE [dbo].[DeleteGuests]
(
  @OnlyWithoutShoppingCart bit = 1,
  @CreatedFromUtc datetime,
  @CreatedToUtc datetime,
  @TotalRecordsDeleted int = null OUTPUT
)
AS
BEGIN
  CREATE TABLE #tmp_guests (CustomerId int)
    
  INSERT #tmp_guests (CustomerId)
  SELECT [Id] FROM [Customer] c
  WHERE
  --created from
  ((@CreatedFromUtc is null) OR (c.[CreatedOnUtc] > @CreatedFromUtc))
  AND
  --created to
  ((@CreatedToUtc is null) OR (c.[CreatedOnUtc] < @CreatedToUtc))
  AND
  --shopping cart items
  ((@OnlyWithoutShoppingCart=0) OR (NOT EXISTS(SELECT 1 FROM [ShoppingCartItem] sci inner join [Customer] on sci.[CustomerId]=c.[Id])))
  AND
  --guests only
  (EXISTS(SELECT 1 FROM [Customer_CustomerRole_Mapping] ccrm inner join [Customer] on ccrm.[Customer_Id]=c.[Id] inner join [CustomerRole] cr on cr.[Id]=ccrm.[CustomerRole_Id] WHERE cr.[SystemName] = N'Guests'))
  AND
  --no orders
  (NOT EXISTS(SELECT 1 FROM [Order] o inner join [Customer] on o.[CustomerId]=c.[Id]))
  AND
  --no blog comments
  (NOT EXISTS(SELECT 1 FROM [BlogComment] bc inner join [Customer] on bc.[CustomerId]=c.[Id]))
  AND
  --no news comments
  (NOT EXISTS(SELECT 1 FROM [NewsComment] nc inner join [Customer] on nc.[CustomerId]=c.[Id]))
  AND
  --no product reviews
  (NOT EXISTS(SELECT 1 FROM [ProductReview] pr inner join [Customer] on pr.[CustomerId]=c.[Id]))
  AND
  --no product reviews helpfulness
  (NOT EXISTS(SELECT 1 FROM [ProductReviewHelpfulness] prh inner join [Customer] on prh.[CustomerId]=c.[Id]))
  AND
  --no poll voting
  (NOT EXISTS(SELECT 1 FROM [PollVotingRecord] pvr inner join [Customer] on pvr.[CustomerId]=c.[Id]))
  AND
  --no forum topics
  (NOT EXISTS(SELECT 1 FROM [Forums_Topic] ft inner join [Customer] on ft.[CustomerId]=c.[Id]))
  AND
  --no forum posts
  (NOT EXISTS(SELECT 1 FROM [Forums_Post] fp inner join [Customer] on fp.[CustomerId]=c.[Id]))
  AND
  --no system accounts
  (c.IsSystemAccount = 0)
  
  --delete guests
  DELETE [Customer]
  WHERE [Id] IN (SELECT [CustomerID] FROM #tmp_guests)
  
  --delete attributes
  DELETE [GenericAttribute]
  WHERE ([EntityID] IN (SELECT [CustomerID] FROM #tmp_guests))
  AND
  ([KeyGroup] = N'Customer')
  
  --total records
  SELECT @TotalRecordsDeleted = COUNT(1) FROM #tmp_guests
  
  DROP TABLE #tmp_guests
END

I'm still running 3.40 for the next week or two.

I'll make a copy of my db and try executing the script on the copy and see how it goes.

I'm thinking it might still be necessary to split it into a few smaller chunks.
If you had to guess, how long do you think it will take to delete 400k customer records (and other related records)?

Thanks  Dennis-

Steve

Hi Steve,

It all depends on your server configuration (where database is installed). But I think this stored procedure (SQL) should take just several seconds to delete 400K records. It's MUCH faster than deleting records using Entity Framework by executing 400K commands (like it was done in prior versions)

a.m. wrote:
Yes, it only took my server (with sql server on same machine) about 30 seconds to delete 381k records!
Thanks for the tip Dennis, and thanks Andrey for the nice improvement!

Now I just need to figure out how to stop them from happening at all, and to get rid of the other bot that keeps creating registered customers (google / 123456)...very annoying!

Steve

embryo wrote:
Hi Steve,

Actually Google crawler does not create new guest records. We have special built-in guest account for search engines (see demo here). A list of search engines is managed in the \App_Data\uas_20140512-01.ini file. So I presume it's done by some new crawler/bot not registered in the \App_Data\uas_20140512-01.ini file file