Forums / General Questions / Problem with Tools -> Send Email

Problem with Tools -> Send Email

Started by kwright on 3 Apr 2013, 5:14 PM · Views: 761

Results 1 to 7 of 7
3 Apr 2013, 5:14 PM
#1
kwright avatar

kwright

Zen Follower

Join Date:
Nov 2004
Posts:
355
Plugin Contributions:
0

Problem with Tools -> Send Email

Greetings All,

Having an interesting problem that started a day ago. When I go to Tools -> Send Mail, our server load goes through the roof (100%) and shuts down services. The page never finishes loading and our site looses db connection and defaults to the nddbc.html page...

I checked the customers table via phpMyAdmin and it shows 61K rows. I am on a dedicated server, but not sure why in the past day it would be come a problem???

The support folks (hostgator) said I needed to optimize my queries because something was "Stacking" queries to MySQL???

I have another install (same Zen Cart code base, different template), but with only 2K customers and the Send Email takes about 45 sec to load. However, it does complete.

Using Zen Cart 1.3.8x (can't upgrade at this time), MySQL 5.0.96-community, PHP 5.2.14, Apache 2.2.16

Any suggestions as where to look? Do you think this is a MySQL config issues that may have changed?

Thank you :D

Experience is what you get when you don’t get what you want…

3 Apr 2013, 5:58 PM
#2
kobra avatar

kobra

Black Belt

Join Date:
Aug 2005
Location:
Arizona
Posts:
31,500
Plugin Contributions:
4

Re: Problem with Tools -> Send Email

Using Zen Cart 1.3.8x (can't upgrade at this time)
You need to upgrade on your schedule versus getting hacked and having to perform a clean in addition to an upgrade

Being on a dedicated you should check your settings in your "my.cnf" file

Zen-Venom Get Bitten

3 Apr 2013, 6:07 PM
#3
kwright avatar

kwright

Zen Follower

Join Date:
Nov 2004
Posts:
355
Plugin Contributions:
0

Re: Problem with Tools -> Send Email

Thanks for the replay kobra, Yup I know...ASAP on the upgrade. I do have all of the current security patches for that ver.

Any suggestions as to a good Zen Cart my.cnf setup?

Experience is what you get when you don’t get what you want…

3 Apr 2013, 8:07 PM
#4
kobra avatar

kobra

Black Belt

Join Date:
Aug 2005
Location:
Arizona
Posts:
31,500
Plugin Contributions:
4

Re: Problem with Tools -> Send Email

Any suggestions as to a good Zen Cart my.cnf setup?
This is server specific
Depends upon how many CPU's, memory and several other things

Zen-Venom Get Bitten

3 Apr 2013, 10:00 PM
#5
kwright avatar

kwright

Zen Follower

Join Date:
Nov 2004
Posts:
355
Plugin Contributions:
0

Re: Problem with Tools -> Send Email

kobra,

I was able to fine the query that was causing the load when you first select the Send Email page.

SELECT DISTINCT c.customers_email_address as customers_email_address, c.customers_lastname as customers_lastname, c.customers_firstname as customers_firstname FROM customers c LEFT JOIN orders o ON c.customers_id=o.customers_id WHERE o.date_purchased IS NULL

If I remove the wildcard % from the select query in get_audiences_list() function in audience.php that creates the above query,

From:

$queries_list = $db->Execute("select query_name, query_string from " . TABLE_QUERY_BUILDER . " " .
                  "where query_category like '%" . $query_category . "%'");

To:

$queries_list = $db->Execute("select query_name, query_string from " . TABLE_QUERY_BUILDER . " " .
                  "where query_category like '" . $query_category . "'");

The page loads in a reasonable amount of time with minimum server load. Grated, I don't get all the results in the drop down, but I do get the "All" emails list. Maybe it is something with the "LEFT JOIN" and the ver of MySQL???

Any thoughts?

Experience is what you get when you don’t get what you want…

3 Apr 2013, 11:00 PM
#6
kobra avatar

kobra

Black Belt

Join Date:
Aug 2005
Location:
Arizona
Posts:
31,500
Plugin Contributions:
4

Re: Problem with Tools -> Send Email

Any thoughts?
It has been years since I and for that matter most of us here have had 1.3.8a loaded
There are many refinements - security and otherwise in the current code

Zen-Venom Get Bitten

5 Apr 2013, 3:25 AM
#7
drbyte avatar

drbyte

Sensei

Join Date:
Jan 2004
Posts:
63,512
Plugin Contributions:
176

Re: Problem with Tools -> Send Email

kwright:

If I remove the wildcard % from the select query in get_audiences_list() function in audience.php that creates the above query,
the page loads in a reasonable amount of time with minimum server load.
That's because you're no longer building the output based on the queries.

It's been so long since I used or supported 1.3.8 that I don't remember whether there were any specific changes to which queries in that table. And without digging up all the current ones I can't tell whether yours is something extra you've added. You could compare against a current ZC version to see.
And there's also a setting in your admin to have it skip calculating counts for those queries ... ie: to prevent it from running each of those queries in order to present the number of records affected by each, when displaying the pulldowns.

.
Zen Cart - putting the dream of business ownership within reach of anyone!
Donate to: DrByte directly or to the Zen Cart team as a whole

Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.