Retrieving export/newsletter lists hangs on database query

I have this mod installed on a 1.3.8a cart and has been working fine up until recently.
But, now, when I select Email Address Exporter from the Tools menu, the page will not completely load. It loads the choices for file type, but the drop down for Desired Recipient List does not load.

It never completes and the page load times out.

Any ideas?

This is a client's heavily modded cart with the most recent mods installed being (the only 2 things installed right before it quit working):

XSS Protection Patch
Master Password

Still no success in figuring out the issues, however the client has asked their host if any recent changes have been made to update/upgrade things on their end. This is the email they sent her:

MySQL databases / tables have been optimized, however, the following query appears to be the cause of the issue :
Mysql> SELECT DISTINCT c.customers_email_address as customers_email_address, c.customers_lastname as customers_lastname, c.customers_firstname as customers_firstname FROM Zen_customers c LEFT JOIN Zen_orders o ON c.customers_id=o.customers_id WHERE o.date_purchased IS NULL;

And it is still hanging there...

You might want to provide the mysql query provided above to zencart so they can take a further look at it.

On a hunch, I went into her admin to attempt to send a newsletter and the same thing happens. The page never finishes loading and it's blank from the dropdown menu down.

Any help would be greatly appreciated.

Other mods that have been added recently include double email address entry, but that has no sql with it and upon removing it, nothing changed.

Honestly, doesn't this sound like a conflict with the optimization her host recently made?

That query is simply comparing the customers table with the orders table and showing any customers who don't have corresponding orders. It's not all that complex, and shouldn't put much strain on the server. If the problem only "suddenly started" then it's likely caused by one of:
a) hosting company mucking with database operation
b) you've imported new customer records from some external source but the data isn't complete or isn't normalized across appropriate tables
c) one or both of the tables has some damage and needs repair
d) someone's been mucking in the raw database data and broken something
e) or, hopefully not but ... maybe your site's been hacked?

What happens if you attempt to run that query manually via phpMyAdmin?

What happens if you add the word "EXPLAIN" before the query and run it again in phpMyAdmin? Post the results.

What are the results of a file-by-file comparison of your server files vs your known good backups? Anything suspicious which might suggest someone unauthorized has been visiting?

Thanks for your quick response.

I just tried to run it directly on phpMyAdmin and it does the same thing. Hangs up and never completes.

Running the same query with the word "EXPLAIN" in front of it took a very long time, but resulted in the attached screenshot.

The host did admit that the DB and tables had been recently "optimized" but claim that has nothing to do with it.

Nobody has been adding any customers other than by the front end of the cart.

They have all the most up to date security patches installed so I'm pretty sure they haven't been hacked.

I don't know how to tell if the if the tables are damaged and need repair.

Thanks for your help. :D

Oh, I also thought maybe I should install a fresh zen-cart on her server to use diagnostically. See if it works out of the box. Without all the mods since it's a heavily modded cart and my partner and I have only recently started adding more mods for her.

Anyhow, on the new test cart the newsletter function works fine and loads the 2 customers that were created.

I did note on installing the test cart that it was throwing a caution that PHP API mode = cgi-fcgi and it is a linux server. Could that create a problem?

We didn't install her problematic cart so I have no idea if that is a warning that existed when the cart was originally installed or or something that has changed since the host has done their *optimizing".

I have spent the day adding the mods and performing the same actions which we had done to her live cart (immediately prior to her email export mod malfunctioning) to her test cart and still the test cart is working correctly.

Until she can provide me with a list of mods that were installed before we were asked to work on her cart, that's as far as my diagnostics can take me.

Here are my notes about what I did on the new test cart and the results:

Added Email Address Exporter
Everything works correctly.

Added the following mods to test cart and still everything functions correctly:
Master Password
Double Email Address Entry
Backup MySQL

Added the fix for coupon restrictions:
http://www.zen-cart.com/forum/showthread.php?t=138711
Still works fine.

Added the security patch:
http://www.zen-cart.com/forum/showthread.php?t=142927
Still works fine.

Thinking perhaps the patch was incorrectly performed originally on your live site, I redid the patch exactly how I had JUST done it to the test site and still the page for email address export will not load. (it loads fine on test site).

Should I perform a "repair table" on those 2 tables in question? I have never done that before and don't want to attempt something that could make the problem even worse.

She's got a customer base of nearly 4000 customers. Could the amount of stored data be any issue?

signs:

I did note on installing the test cart that it was throwing a caution that PHP API mode = cgi-fcgi and it is a linux server. Could that create a problem?
That's not as common a problem as it was 2-3 years ago when many hosts were still running archaic versions of PHP.
I consider that warning irrelevant nowadays.

As far as installing patches and addons, I wouldn't be too concerned about PHP files unless they have built-in SQL scripts that are altering the database structure.

It seems the problem is with your database.

Go to phpMyAdmin, select your database from the pulldown. Then click on the "Export" tab. Then check ONLY the "Zen_orders" and "Zen_customers" tables. Then check the box that says "Structure Only". Uncheck the "download" option, since I just want you to output the structure to the screen. Don't worry about all the other checkboxes. Click Go.
Copy and paste the DROP TABLE / CREATE TABLE statements that it shows you ... into a reply here.

I'd be interested in knowing if it's the same as defaults.

signs:

She's got a customer base of nearly 4000 customers. Could the amount of stored data be any issue?Well, the 10,000 orders and 4000 customers isn't the same as 5 and 5, so it's clearly more than just a simple couple of records to sort through. And, yes, the size is likely a minor contributing problem. But I've seen many stores with larger amounts of data than that which aren't reporting the same problem.
signs:

Should I perform a "repair table" on those 2 tables in question? I have never done that before and don't want to attempt something that could make the problem even worse.Well ... if in doubt, do a backup of the data, and restore it to another database to be sure the backup is good.
Then do the repair and see what happens. It's unlikely to cause a problem. But one should always do a backup when in doubt.

Another thing you could try is this:
You said you have another store installed which is working fine.
The following instructions WILL BREAK that store, but will let you test JUST this query in a "cleaner" setup.

• do the same export as I mentioned earlier, BUT choose "structure and data" instead of structure-only. Also be sure the "drop table" option IS checked.
• assuming the new database also uses the "Zen_" prefix that your live store is using, just import this new backup into your new store. (You could do a backup of the whole new/test store database first.)
  (If the prefix is different, import it anyway, and then find the correct table names, delete the existing names, and rename the ones you just imported from "Zen_xxxxx" to whatever the right new name is.
• Then test ONLY the Newsletter or Email-address-exporter page.
  I doubt you'll see it work properly. But it rules out a few things.
• Then test it in phpMyAdmin just like you did earlier.
• Now, remember, doing it this way will have just busted all the customer and order data in the new/test site, so I suggest running a new install on it again, or restore it from a backup.

BTW, I've moved this thread to this new location since it's not related specifically to the Email Address Exporter where you first started the discussion.

DrByte:

As far as installing patches and addons, I wouldn't be too concerned about PHP files unless they have built-in SQL scripts that are altering the database structure.

It seems the problem is with your database.

Go to phpMyAdmin, select your database from the pulldown. Then click on the "Export" tab. Then check ONLY the "Zen_orders" and "Zen_customers" tables. Then check the box that says "Structure Only". Uncheck the "download" option, since I just want you to output the structure to the screen. Don't worry about all the other checkboxes. Click Go.
Copy and paste the DROP TABLE / CREATE TABLE statements that it shows you ... into a reply here.

I'd be interested in knowing if it's the same as defaults.

I think I did it correctly:

-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 14, 2010 at 08:33 AM
-- Server version: 5.0.89
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `xxxxxx`
--

-- --------------------------------------------------------

--
-- Table structure for table `zen_customers`
--

CREATE TABLE IF NOT EXISTS `zen_customers` (
  `customers_id` int(11) NOT NULL auto_increment,
  `customers_gender` char(1) NOT NULL default '',
  `customers_firstname` varchar(32) NOT NULL default '',
  `customers_lastname` varchar(32) NOT NULL default '',
  `customers_dob` datetime NOT NULL default '0001-01-01 00:00:00',
  `customers_email_address` varchar(96) NOT NULL default '',
  `customers_nick` varchar(96) NOT NULL default '',
  `customers_default_address_id` int(11) NOT NULL default '0',
  `customers_telephone` varchar(32) NOT NULL default '',
  `customers_fax` varchar(32) default NULL,
  `customers_password` varchar(40) NOT NULL default '',
  `customers_newsletter` char(1) default NULL,
  `customers_group_pricing` int(11) NOT NULL default '0',
  `customers_email_format` varchar(4) NOT NULL default 'TEXT',
  `customers_authorization` int(1) NOT NULL default '0',
  `customers_referral` varchar(32) NOT NULL default '',
  `customers_paypal_payerid` varchar(20) NOT NULL default '',
  `customers_paypal_ec` tinyint(1) unsigned NOT NULL default '0',
  `customers_see_hidden_products` char(3) NOT NULL,
  PRIMARY KEY  (`customers_id`),
  KEY `idx_email_address_zen` (`customers_email_address`),
  KEY `idx_referral_zen` (`customers_referral`(10)),
  KEY `idx_grp_pricing_zen` (`customers_group_pricing`),
  KEY `idx_nick_zen` (`customers_nick`),
  KEY `idx_newsletter_zen` (`customers_newsletter`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3492 ;

-- --------------------------------------------------------

--
-- Table structure for table `zen_orders`
--

CREATE TABLE IF NOT EXISTS `zen_orders` (
  `orders_id` int(11) NOT NULL auto_increment,
  `customers_id` int(11) NOT NULL default '0',
  `customers_name` varchar(64) NOT NULL default '',
  `customers_company` varchar(64) default NULL,
  `customers_street_address` varchar(64) NOT NULL default '',
  `customers_suburb` varchar(32) default NULL,
  `customers_city` varchar(32) NOT NULL default '',
  `customers_postcode` varchar(10) NOT NULL default '',
  `customers_state` varchar(32) default NULL,
  `customers_country` varchar(32) NOT NULL default '',
  `customers_telephone` varchar(32) NOT NULL default '',
  `customers_email_address` varchar(96) NOT NULL default '',
  `customers_address_format_id` int(5) NOT NULL default '0',
  `delivery_name` varchar(64) NOT NULL default '',
  `delivery_company` varchar(64) default NULL,
  `delivery_street_address` varchar(64) NOT NULL default '',
  `delivery_suburb` varchar(32) default NULL,
  `delivery_city` varchar(32) NOT NULL default '',
  `delivery_postcode` varchar(10) NOT NULL default '',
  `delivery_state` varchar(32) default NULL,
  `delivery_country` varchar(32) NOT NULL default '',
  `delivery_address_format_id` int(5) NOT NULL default '0',
  `billing_name` varchar(64) NOT NULL default '',
  `billing_company` varchar(64) default NULL,
  `billing_street_address` varchar(64) NOT NULL default '',
  `billing_suburb` varchar(32) default NULL,
  `billing_city` varchar(32) NOT NULL default '',
  `billing_postcode` varchar(10) NOT NULL default '',
  `billing_state` varchar(32) default NULL,
  `billing_country` varchar(32) NOT NULL default '',
  `billing_address_format_id` int(5) NOT NULL default '0',
  `payment_method` varchar(128) NOT NULL default '',
  `payment_module_code` varchar(32) NOT NULL default '',
  `shipping_method` varchar(128) NOT NULL default '',
  `shipping_module_code` varchar(32) NOT NULL default '',
  `coupon_code` varchar(32) NOT NULL default '',
  `cc_type` varchar(20) default NULL,
  `cc_owner` varchar(64) default NULL,
  `cc_number` varchar(32) default NULL,
  `cc_expires` varchar(4) default NULL,
  `cc_cvv` blob,
  `last_modified` datetime default NULL,
  `date_purchased` datetime default NULL,
  `orders_status` int(5) NOT NULL default '0',
  `orders_date_finished` datetime default NULL,
  `currency` char(3) default NULL,
  `currency_value` decimal(14,6) default NULL,
  `order_total` decimal(14,2) default NULL,
  `order_tax` decimal(14,2) default NULL,
  `paypal_ipn_id` int(11) NOT NULL default '0',
  `ip_address` varchar(96) NOT NULL default '',
  PRIMARY KEY  (`orders_id`),
  KEY `idx_status_orders_cust_zen` (`orders_status`,`orders_id`,`customers_id`),
  KEY `idx_date_purchased_zen` (`date_purchased`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12109 ;

DrByte:

Well, the 10,000 orders and 4000 customers isn't the same as 5 and 5, so it's clearly more than just a simple couple of records to sort through. And, yes, the size is likely a minor contributing problem. But I've seen many stores with larger amounts of data than that which aren't reporting the same problem.
Well ... if in doubt, do a backup of the data, and restore it to another database to be sure the backup is good.
Then do the repair and see what happens. It's unlikely to cause a problem. But one should always do a backup when in doubt.

Another thing you could try is this:
You said you have another store installed which is working fine.
The following instructions WILL BREAK that store, but will let you test JUST this query in a "cleaner" setup.

• do the same export as I mentioned earlier, BUT choose "structure and data" instead of structure-only. Also be sure the "drop table" option IS checked.
• assuming the new database also uses the "Zen_" prefix that your live store is using, just import this new backup into your new store. (You could do a backup of the whole new/test store database first.)
  (If the prefix is different, import it anyway, and then find the correct table names, delete the existing names, and rename the ones you just imported from "Zen_xxxxx" to whatever the right new name is.
• Then test ONLY the Newsletter or Email-address-exporter page.
  I doubt you'll see it work properly. But it rules out a few things.
• Then test it in phpMyAdmin just like you did earlier.
• Now, remember, doing it this way will have just busted all the customer and order data in the new/test site, so I suggest running a new install on it again, or restore it from a backup.

I'm going to get with my partner who is more adept at database manipulation that I am to do this test. She's a night owl and has just signed off until tonight, so I'll touch base with her and post back the results after she's had a chance to follow your directions.

She did do the repair table operation yesterday on the live cart's DB but it had no effect.

Oh, and thanks so much for moving this to it's own thread. I felt bad for the hijack after I realized it had nothing to do with that mod.