Problem with Tools -> Send Email

[kwright]

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

[kobra]

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

[kwright]

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?

[kobra]

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

[kwright]

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?

[kobra]

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

[DrByte]

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.