Issue with site and MySQL db

I am getting very annoyed with MYSQL and now thinking I made a big mistake using its should have used SQL SERVER as never experienced these issue with SQL SERVER.

Basically MYSQL IS shooting to 100%  And below is the process list:

.

The site is open on 2-3 places maybe and on the server where its HOSTED on IIS.

MYSQL works fine and suddenly when the site is idle for some time and loaded shoots to max. And after some time it will come back on its own so now idea why! Also mostly happens when i browse the site on the same PC as the IIS. Why is all that happening?

Also made a bat file that keeps pinging the site:

@echo off
:loop
echo Hitting URL: https://site.com/keepalive/index
curl https://site.com/keepalive/index
timeout /t 300 /nobreak >nul
goto loop

So why would it still go inactive. What am I missing here?

And I keep seeing this error in log:

The "Delete guests" scheduled task failed with the "One or more errors occurred. (Table 'tmp_gueststodelete' already exists)" error (Task type: "Nop.Services.Customers.DeleteGuestsTask, Nop.Services". Store name: "Carparts247". Task run address: "http://site.com/scheduletask/runtask").

I get that error often as well on my offline site (it is not online at all as of yet ... just testing it offline).

I get this error.

Log Level: Error
Message: The "Delete guests" scheduled task failed with the "Table 'tmp_guestsToDelete' already exists" error (Task type: "Nop.Services.Customers.DeleteGuestsTask, Nop.Services". Store name: "Punky Dog Gear". Task run address: "http://localhost/scheduletask/runtask").
Created On: 03/11/2023 6:13:03 PM

So far, tonight, it has done it this often:

03/11/2023 6:13:03 PM  
03/11/2023 5:36:27 PM  
03/11/2023 5:26:27 PM  
03/11/2023 5:16:27 PM  
03/11/2023 5:06:27 PM  
03/11/2023 4:56:26 PM

My System Information:

nopCommerce Version 4.60.0
Operating System Unix 5.10.0.21
ASP.NET Info .NET 7.0.1
Is Full Trust Level Yes
Server Time Zone Central Standard Time
Server Local Time Saturday, March 11, 2023 6:18:01 PM
Coordinated Universal Time (UTC) Sunday, March 12, 2023 12:18:01 AM
Current User Time Saturday, March 11, 2023 6:18:01 PM
HTTP_HOST localhost
Headers Show
Loaded Assemblies Show
Static Cache Manager Name MemoryCacheManager
Azure Blob Storage Enabled

RE: "...mostly happens when i browse the site on the same PC as the IIS."
Is your MySql also on the same server as IIS?  (In general, it's better to have them on different servers).

RE: "...the "Table 'tmp_guestsToDelete' already exists" error..."
Supposedly it was fixed.  What version of nopCommerce are you on?  (I see that PunkyDogGear indicated his is 4.60)
https://www.nopcommerce.com/en/boards/topic/91182/error-trying-to-delete-guest-customers
Also mentioned here
https://www.nopcommerce.com/en/boards/topic/93517/scheduled-task-and-manual-execution-of-task-to-delete-guest-accounts-fail
So, in SQL tool, manually drop/delete the Table 'tmp_guestsToDelete', and then see if it still happens.

Its a big server 60GB ram and a strong specs.

Also using nopcommerce 4.5xxx

Juts keeps getting worse! Nothing running I am just testing and I see it breaking :


MySql.Data.MySqlClient.MySqlException (0x80004005): Too many connections

An exception was thrown attempting to execute the error handler.

Exception: 
MySql.Data.MySqlClient.MySqlException (0x80004005): Too many connections
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.Open()
   at MySql.Data.MySqlClient.Driver.Open()
   at MySql.Data.MySqlClient.Driver.Create(MySqlConnectionStringBuilder settings)

How can I resolve this?

I dropped/deleted the table New York instructed to eliminate, then the following error arose:

The "Delete Guests" scheduled task failed with the "Table 'tmp_guestsAddressesToDelete' already exists" error (Task type: "Nop.Services.Customers.DeleteGuestsTask, Nop.Services". Store name: "Punky Dog Gear". Task run address: "http://localhost/scheduletask/runtask").

After also dropping/deleting the tmp_guestsAddressesToDelete table (just curious what would happen), the following error arose:

The "Delete Guests" scheduled task failed with the "Tablespace for table '`nopcommerce_4.60.0`.`tmp_guestsAddressesToDelete`' exists. Please DISCARD the tablespace before IMPORT" error (Task type: "Nop.Services.Customers.DeleteGuestsTask, Nop.Services". Store name: "Punky Dog Gear". Task run address: "http://localhost/scheduletask/runtask").

Thoughts?

Was forced to switch to SQL SERVER cause of such bad performance of MYSQL.

used this for data migration worked flawlessly.

And 99% of the problems went away.

This still exists:

The "Delete Guests" scheduled task failed with the "Table 'tmp_guestsAddressesToDelete' already exists" error (Task type: "Nop.Services.Customers.DeleteGuestsTask, Nop.Services". Store name: "Punky Dog Gear". Task run address: "http://site.com/scheduletask/runtask").

Delete both of these tables
tmp_gueststodelete
tmp_guestsAddressesToDelete

(if they exist)