Urgent Help Needed **Account Suspended**

[Boatersplanet]

We have recently worked with some developers on uploading 80,000+ products in the inventory and for the most part everything has been OK. We have added custom fields such as "UPC" and "Distributor Part #"

I suggested that they use the EZ Populate tool, but it appears as though they uploaded the products directly into the MySQL database. Due to a time difference, and communication barrier a couple of things seem to have been neglected. Although all the products appear, and everything pulls up fine, the page loads significantly slower on both the store, and the admin side. I have noticed in the upload, the "manufacturer field" in the product table has all the correct manufacturers listed, but the product_manufacturers table does not list all the manufacturers. I also think that the added fields (UPC, and distributor Part #) may be causing a loop.

Last week, our account was suspended and we were directed to call terms of service. They informed me that the MySQL database was being hit hard, and that there were a bunch of php malware scripts in the image directory. I cleaned them up and called back. Everything seemed to be OK. A couple days later they took the site back down. When I called they said the MySQL database was just consuming the server and that if we were suspended again within 90 days we would lose the account.

All of this, and the site has been down for 3-4 days while I try to work with the developers. I sent the error log provided below...

# Tue May 10 07:00:09 2011
# Query_time: 1.239271  Lock_time: 0.000133 Rows_sent: 5196  Rows_examined: 1082311
use boatersp_zc02;
select startdate, counter, session_counter from counter_history where startdate='20110510'

# Tue May 10 07:00:11 2011
# Query_time: 1.314155  Lock_time: 0.000102 Rows_sent: 0  Rows_examined: 1082312
use boatersp_zc02;
update counter_history set counter = '4697', session_counter ='543' where startdate='20110510'

# Tue May 10 07:00:15 2011
# Query_time: 3.776351  Lock_time: 0.000131 Rows_sent: 1  Rows_examined: 235910
use boatersp_zc02;
select count(*) as total  FROM products p                              LEFT JOIN manufacturers m ON (p.manufacturers_id = m.manufacturers_id), products_description pd                              WHERE p.products_status = 1                              AND p.products_id = pd.products_id                              AND pd.language_id = 1

# Tue May 10 07:00:43 2011
# Query_time: 1.344811  Lock_time: 0.000189 Rows_sent: 5198  Rows_examined: 1082313
use boatersp_zc02;
select startdate, counter, session_counter from counter_history where startdate='20110510'

# Tue May 10 07:00:46 2011
# Query_time: 1.881133  Lock_time: 0.000275 Rows_sent: 1  Rows_examined: 235910
use boatersp_zc02;
select count(*) as total  FROM products p                              LEFT JOIN manufacturers m                              ON (p.manufacturers_id = m.manufacturers_id), products_description pd                              WHERE p.products_status = 1                              AND p.products_id = pd.products_id                              AND pd.language_id = 1

# Tue May 10 07:00:49 2011
# Query_time: 2.821344  Lock_time: 0.000103 Rows_sent: 10  Rows_examined: 314806
use boatersp_zc02;
SELECT p.products_id, p.products_type, pd.products_name, p.products_image, p.products_price,                                     p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model,                                     p.products_quantity, p.products_weight, p.product_is_call,                                     p.product_is_always_free_shipping, p.products_qty_box_status,                                     p.master_categories_id                              FROM products p                              LEFT JOIN manufacturers m                              ON (p.manufacturers_id = m.manufacturers_id), products_description pd                              WHERE p.products_status = 1                              AND p.products_id = pd.products_id                              AND pd.language_id = 1  order by p.products_date_added DESC, pd.products_name limit 10

# Tue May 10 07:00:51 2011
# Query_time: 2.094346  Lock_time: 0.000126 Rows_sent: 10  Rows_examined: 314806
use boatersp_zc02;
SELECT p.products_id, p.products_type, pd.products_name, p.products_image, p.products_price,                                     p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model,                                     p.products_quantity, p.products_weight, p.product_is_call,                                     p.product_is_always_free_shipping, p.products_qty_box_status,                                     p.master_categories_id                              FROM products p                              LEFT JOIN manufacturers m                              ON (p.manufacturers_id = m.manufacturers_id), products_description pd                              WHERE p.products_status = 1                              AND p.products_id = pd.products_id                              AND pd.language_id = 1  order by p.products_date_added DESC, pd.products_name limit 10

# Tue May 10 07:01:03 2011
# Query_time: 1.548022  Lock_time: 0.000074 Rows_sent: 5204  Rows_examined: 1082319
use boatersp_zc02;
select startdate, counter, session_counter from counter_history where startdate='20110510'

# Tue May 10 07:01:05 2011
# Query_time: 1.178263  Lock_time: 0.000110 Rows_sent: 0  Rows_examined: 1082320
use boatersp_zc02;
update counter_history set counter = '4705', session_counter ='549' where startdate='20110510'

# Tue May 10 07:01:06 2011
# Query_time: 1.385802  Lock_time: 0.215637 Rows_sent: 5205  Rows_examined: 1082320
use boatersp_zc02;
select startdate, counter, session_counter from counter_history where startdate='20110510'

# Tue May 10 07:01:12 2011
# Query_time: 4.135750  Lock_time: 0.000083 Rows_sent: 1  Rows_examined: 235910
use boatersp_zc02;
select count(*) as total  FROM products p                              LEFT JOIN manufacturers m                              ON (p.manufacturers_id = m.manufacturers_id), products_description pd                              WHERE p.products_status = 1                              AND p.products_id = pd.products_id                              AND pd.language_id = 1

# Tue May 10 07:01:15 2011
# Query_time: 2.925500  Lock_time: 0.000141 Rows_sent: 10  Rows_examined: 314806
use boatersp_zc02;
SELECT p.products_id, p.products_type, pd.products_name, p.products_image, p.products_price,                                     p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model,                                     p.products_quantity, p.products_weight, p.product_is_call,                                     p.product_is_always_free_shipping, p.products_qty_box_status,                                     p.master_categories_id                              FROM products p                              LEFT JOIN manufacturers m                              ON (p.manufacturers_id = m.manufacturers_id), products_description pd                              WHERE p.products_status = 1                              AND p.products_id = pd.products_id                              AND pd.language_id = 1  order by p.products_date_added DESC, pd.products_name limit 10

# Tue May 10 07:01:31 2011
# Query_time: 2.170649  Lock_time: 0.000216 Rows_sent: 1  Rows_examined: 235910
use boatersp_zc02;
select count(*) as total  FROM products p                              LEFT JOIN manufacturers m ON (p.manufacturers_id = m.manufacturers_id), products_description pd                              WHERE p.products_status = 1                              AND p.products_id = pd.products_id                              AND pd.language_id = 1

# Tue May 10 07:01:48 2011
# Query_time: 1.045704  Lock_time: 0.000073 Rows_sent: 5212  Rows_examined: 1082327
use boatersp_zc02;
select startdate, counter, session_counter from counter_history where startdate='20110510'

# Tue May 10 07:01:50 2011
# Query_time: 1.032427  Lock_time: 0.000048 Rows_sent: 0  Rows_examined: 1082328
use boatersp_zc02;
update counter_history set counter = '4712', session_counter ='552' where startdate='20110510'

# Tue May 10 07:01:53 2011
# Query_time: 1.098743  Lock_time: 0.000039 Rows_sent: 10  Rows_examined: 1082338
use boatersp_zc02;
select startdate, counter, session_counter from counter_history order by startdate DESC limit 10

# Tue May 10 07:01:57 2011
# Query_time: 1.043093  Lock_time: 0.000100 Rows_sent: 5214  Rows_examined: 1082329
use boatersp_zc02;
select startdate, counter, session_counter from counter_history where startdate='20110510'

# Tue May 10 07:01:58 2011
# Query_time: 3.205881  Lock_time: 0.000094 Rows_sent: 1  Rows_examined: 235910
use boatersp_zc02;
select count(*) as total  FROM products p                              LEFT JOIN manufacturers m                              ON (p.manufacturers_id = m.manufacturers_id), products_description pd                              WHERE p.products_status = 1                              AND p.products_id = pd.products_id                              AND pd.language_id = 1

# Tue May 10 07:02:00 2011
# Query_time: 2.384709  Lock_time: 0.000187 Rows_sent: 10  Rows_examined: 314806
use boatersp_zc02;
SELECT p.products_id, p.products_type, pd.products_name, p.products_image, p.products_price,                                     p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model,                                     p.products_quantity, p.products_weight, p.product_is_call,                                     p.product_is_always_free_shipping, p.products_qty_box_status,                                     p.master_categories_id                              FROM products p                              LEFT JOIN manufacturers m                              ON (p.manufacturers_id = m.manufacturers_id), products_description pd                              WHERE p.products_status = 1                              AND p.products_id = pd.products_id                              AND pd.language_id = 1  order by p.products_date_added DESC, pd.products_name limit 10

# Tue May 10 07:02:04 2011
# Query_time: 3.653434  Lock_time: 0.000061 Rows_sent: 0  Rows_examined: 78918
use boatersp_zc02;
select p.products_id, pd.products_name, products_date_available as date_expected
  from products p, products_description pd
  where to_days(products_date_available) > to_days(now())
  and p.products_id = pd.products_id
  and pd.language_id = '1'
  order by date_expected desc
  limit 10

# Tue May 10 07:02:05 2011
# Query_time: 4.683100  Lock_time: 0.000108 Rows_sent: 10  Rows_examined: 314806
use boatersp_zc02;
SELECT p.products_id, p.products_type, pd.products_name, p.products_image, p.products_price,                                     p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model,                                     p.products_quantity, p.products_weight, p.product_is_call,                                     p.product_is_always_free_shipping, p.products_qty_box_status,                                     p.master_categories_id                              FROM products p                              LEFT JOIN manufacturers m                              ON (p.manufacturers_id = m.manufacturers_id), products_description pd                              WHERE p.products_status = 1                              AND p.products_id = pd.products_id                              AND pd.language_id = 1  order by p.products_date_added DESC, pd.products_name limit 10

# Tue May 10 07:02:09 2011
# Query_time: 1.150727  Lock_time: 0.000244 Rows_sent: 1  Rows_examined: 235910
use boatersp_zc02;
select count(*) as total  FROM products p                              LEFT JOIN manufacturers m                              ON (p.manufacturers_id = m.manufacturers_id), products_description pd                              WHERE p.products_status = 1                              AND p.products_id = pd.products_id                              AND pd.language_id = 1

# Tue May 10 07:02:30 2011
# Query_time: 1.055514  Lock_time: 0.000036 Rows_sent: 0  Rows_examined: 81
use boatersp_zc02;
delete from sessions where expiry < 1305032549

# Tue May 10 07:02:44 2011
# Query_time: 1.762970  Lock_time: 0.065866 Rows_sent: 1  Rows_examined: 1082335
use boatersp_zc02;
SELECT * FROM counter_history WHERE startdate = '20110510' AND counter = 1 AND session_counter = 1 LIMIT 1

# Tue May 10 07:02:46 2011
# Query_time: 1.113570  Lock_time: 0.000044 Rows_sent: 0  Rows_examined: 1082335
use boatersp_zc02;
update counter_history set counter = '4718', session_counter ='555' where startdate='20110510'

# Tue May 10 07:02:59 2011
# Query_time: 1.059522  Lock_time: 0.000242 Rows_sent: 0  Rows_examined: 78918
use boatersp_zc02;
select p.products_id, pd.products_name, products_date_available as date_expected
  from products p, products_description pd
  where to_days(products_date_available) > to_days(now())
  and p.products_id = pd.products_id
  and pd.language_id = '1'
  order by date_expected desc
  limit 10

# Tue May 10 07:03:08 2011
# Query_time: 1.434077  Lock_time: 0.000107 Rows_sent: 5222  Rows_examined: 1082337
use boatersp_zc02;
select startdate, counter, session_counter from counter_history where startdate='20110510'

# Tue May 10 07:03:10 2011
# Query_time: 1.006655  Lock_time: 0.000096 Rows_sent: 0  Rows_examined: 1082338
use boatersp_zc02;
update counter_history set counter = '4721', session_counter ='557' where startdate='20110510'

# Tue May 10 07:03:11 2011
# Query_time: 9.264773  Lock_time: 0.000087 Rows_sent: 54449  Rows_examined: 272245
use boatersp_zc02;
select p.products_id, p.products_model, p.products_price_sorter, pd.products_name, p.products_sort_order
  from   products p, products_description pd, products_to_categories ptc
  where  p.products_status = '1' and p.products_id = pd.products_id and pd.language_id= '1' and p.products_id = ptc.products_id and ptc.categories_id = '838' order by pd.products_name

# Tue May 10 07:03:16 2011
# Query_time: 6.003214  Lock_time: 0.000143 Rows_sent: 54449  Rows_examined: 272245
use boatersp_zc02;
select p.products_id, p.products_model, p.products_price_sorter, pd.products_name, p.products_sort_order
  from   products p, products_description pd, products_to_categories ptc
  where  p.products_status = '1' and p.products_id = pd.products_id and pd.language_id= '1' and p.products_id = ptc.products_id and ptc.categories_id = '838' order by pd.products_name

# Tue May 10 07:03:40 2011
# Query_time: 1.696803  Lock_time: 0.000082 Rows_sent: 0  Rows_examined: 1082349
use boatersp_zc02;
update counter_history set counter = '4732', session_counter ='558' where startdate='20110510'

# Tue May 10 07:03:42 2011
# Query_time: 1.691528  Lock_time: 0.000080 Rows_sent: 54449  Rows_examined: 272245
use boatersp_zc02;
select p.products_id, p.products_model, p.products_price_sorter, pd.products_name, p.products_sort_order
  from   products p, products_description pd, products_to_categories ptc
  where  p.products_status = '1' and p.products_id = pd.products_id and pd.language_id= '1' and p.products_id = ptc.products_id and ptc.categories_id = '838' order by pd.products_name

# Tue May 10 07:03:43 2011
# Query_time: 1.867374  Lock_time: 0.000082 Rows_sent: 54449  Rows_examined: 272245
use boatersp_zc02;
select p.products_id, p.products_model, p.products_price_sorter, pd.products_name, p.products_sort_order
  from   products p, products_description pd, products_to_categories ptc
  where  p.products_status = '1' and p.products_id = pd.products_id and pd.language_id= '1' and p.products_id = ptc.products_id and ptc.categories_id = '838' order by pd.products_name

# Tue May 10 07:04:13 2011
# Query_time: 1.299153  Lock_time: 0.000101 Rows_sent: 75741  Rows_examined: 227228
use boatersp_zc02;
select distinct p.products_id, p.products_image, p.products_tax_class_id, p.products_date_added,
  p.products_price
  from products p
  left join specials s
  on p.products_id = s.products_id, products_to_categories p2c, categories c
  where p.products_id = p2c.products_id
  and p2c.categories_id = c.categories_id
  and c.parent_id = '730'
  and p.products_status = '1'

# Tue May 10 07:04:14 2011
# Query_time: 1.473560  Lock_time: 0.000210 Rows_sent: 0  Rows_examined: 227228
use boatersp_zc02;
select p.products_id, pd.products_name, products_date_available as date_expected
  from products p, products_description pd, products_to_categories p2c, categories c
  where p.products_id = p2c.products_id
  and p2c.categories_id = c.categories_id
  and c.parent_id = '730'
  and to_days(products_date_available) > to_days(now())
  and p.products_id = pd.products_id
  and pd.language_id = '1'
  order by date_expected desc
  limit 10

# Tue May 10 07:04:18 2011
# Query_time: 1.954087  Lock_time: 0.000195 Rows_sent: 1  Rows_examined: 235910
use boatersp_zc02;
select count(*) as total  FROM products p                              LEFT JOIN manufacturers m ON (p.manufacturers_id = m.manufacturers_id), products_description pd                              WHERE p.products_status = 1                              AND p.products_id = pd.products_id                              AND pd.language_id = 1

# Tue May 10 07:04:30 2011
# Query_time: 2.719382  Lock_time: 0.000080 Rows_sent: 54449  Rows_examined: 272245
use boatersp_zc02;
select p.products_id, p.products_model, p.products_price_sorter, pd.products_name, p.products_sort_order
  from   products p, products_description pd, products_to_categories ptc
  where  p.products_status = '1' and p.products_id = pd.products_id and pd.language_id= '1' and p.products_id = ptc.products_id and ptc.categories_id = '838' order by pd.products_name

# Tue May 10 07:04:30 2011
# Query_time: 1.990561  Lock_time: 0.443128 Rows_sent: 0  Rows_examined: 1
use boatersp_zc02;
update products_description
  set        products_viewed = products_viewed+1
  where      products_id = '1111'
  and        language_id = '1'

# Tue May 10 07:04:46 2011
# Query_time: 1.707324  Lock_time: 0.000173 Rows_sent: 10  Rows_examined: 314806
use boatersp_zc02;
SELECT p.products_id, p.products_type, pd.products_name, p.products_image, p.products_price,                                     p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model,                                     p.products_quantity, p.products_weight, p.product_is_call,                                     p.product_is_always_free_shipping, p.products_qty_box_status,                                     p.master_categories_id                              FROM products p                              LEFT JOIN manufacturers m                              ON (p.manufacturers_id = m.manufacturers_id), products_description pd                              WHERE p.products_status = 1                              AND p.products_id = pd.products_id                              AND pd.language_id = 1  order by p.products_date_added DESC, pd.products_name limit 10

# Tue May 10 07:05:04 2011
# Query_time: 1.031283  Lock_time: 0.000095 Rows_sent: 5269  Rows_examined: 1082384
use boatersp_zc02;
select startdate, counter, session_counter from counter_history where startdate='20110510'

# Tue May 10 07:05:04 2011
# Query_time: 3.922659  Lock_time: 0.000097 Rows_sent: 54449  Rows_examined: 272245
select p.products_id, p.products_model, p.products_price_sorter, pd.products_name, p.products_sort_order
  from   products p, products_description pd, products_to_categories ptc
  where  p.products_status = '1' and p.products_id = pd.products_id and pd.language_id= '1' and p.products_id = ptc.products_id and ptc.categories_id = '838' order by pd.products_name

# Tue May 10 07:05:20 2011
# Query_time: 1.278071  Lock_time: 0.000243 Rows_sent: 0  Rows_examined: 78918
use boatersp_zc02;
select p.products_id, pd.products_name, products_date_available as date_expected
  from products p, products_description pd
  where to_days(products_date_available) > to_days(now())
  and p.products_id = pd.products_id
  and pd.language_id = '1'
  order by date_expected desc
  limit 10

# Tue May 10 07:05:29 2011
# Query_time: 1.305971  Lock_time: 0.000255 Rows_sent: 1  Rows_examined: 235910
use boatersp_zc02;
select count(*) as total  FROM products p                              LEFT JOIN manufacturers m ON (p.manufacturers_id = m.manufacturers_id), products_description pd                              WHERE p.products_status = 1                              AND p.products_id = pd.products_id                              AND pd.language_id = 1

# Tue May 10 07:05:33 2011
# Query_time: 3.785697  Lock_time: 0.000217 Rows_sent: 54449  Rows_examined: 272245
use boatersp_zc02;
select p.products_id, p.products_model, p.products_price_sorter, pd.products_name, p.products_sort_order
  from   products p, products_description pd, products_to_categories ptc
  where  p.products_status = '1' and p.products_id = pd.products_id and pd.language_id= '1' and p.products_id = ptc.products_id and ptc.categories_id = '838' order by pd.products_name

I was told that they looked at the database and everything seemed to look OK and that if they had to work on the application level, it was going to be a significant amount of time and money. Are there any suggestions anyone may be able to provide me to mitigate these issues?

Thanks!!

[stevesh]

You didn't mention what version of Zencart you're using, but it sounds like it's an earlier version than the current 1.3.9h. There are serious security vulnerabilities in the earlier versions which are leaving you open to hacks like this.

You'll need to upgrade to the latest version ASAP, and:

http://www.zen-cart.com/wiki/index.p...ing_From_Hacks

[Boatersplanet]

You didn't mention what version of Zencart you're using, but it sounds like it's an earlier version than the current 1.3.9h. There are serious security vulnerabilities in the earlier versions which are leaving you open to hacks like this.

You'll need to upgrade to the latest version ASAP, and:

http://www.zen-cart.com/wiki/index.p...ing_From_Hacks

I apologize for neglecting to mention this, but we are current. The store is running on 1.3.9h.

[Boatersplanet]

I have cleaned the malware, and I will visit the suggested page to improve security, but it looks to be a configuration issue in the zen cart.

I can say with certainty that not all manufacturers are listed in the manufacturers table with a manufacturer id. Is it possible that a line in products.php for example is trying to load from that table, but there is no manufacturer listed so it just loops? Just a thought..

[CoolCarPartsOnline]

What kind of hosting account do you have? It is very common to get your account suspended by most shared servers when you get to that kind of products. If Google and Yahoo crawl your website at the same time with 100,000 products that is enough to flag your account with most shared host companies... I can say that from experience. Once I started adding products a few years ago my account was suspended, so I moved to a different host who claimed their servers can handle that account but few months later I start getting suspended because of "third party scripts installed on my website", after spending few hundreds on optimization I was still being suspended. To make the story short. 5 different host companies in 4 months. And finally got a good dedicated server running multiple sites with more than 300,000 products and no issues. Over 700,000 page views/month with no server issues.

[Boatersplanet]

Point taken. Unfortunately I don't think this is the case. Our hosting company is Host Monster.

Our MySQL server was getting pounded with like 33,000,000,000 requests. The two affected tables are the products, and tables as shown in the error log provided.

[CoolCarPartsOnline]

That was my guess. Good Luck to you but HostMonster will not be able to handle an account with 100,000 products. Once you start getting any kind of traffic there shared server will not handle your site and they terms and conditions policy clearly states that.

Just giving you heads up as I once was there.