Retrieving export/newsletter lists hangs on database query

[signs]

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 ;

[signs]

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.