Gift Voucher admin page crashes my DB

My gift card page (gv_mail.php) in the admin will not load. It tries to load, and gets partway, and then just keeps looking like it's trying to finish loading the page, but times out eventually.

It gets to the point where the menu bar is loaded, and the loaded html ends at <li><a href="[path]/gv_admin_redemption_edit.php">Gift Certificate Admin Editor (Redeemed)</a></li>

(that's the last item in the menu bar) and then after little bit (or immediately, I'm not entirely sure, but I'm pretty sure it takes a few moments), it crashes the zc database and I need to get the host to reset/repair it. This has happened a number of times in the past few days, but only just today was I able to attribute it to this page (or at very least, this page among others).

The page has two modifications from the default 1.3.8a code, which were added to accomodate the Gift Voucher Edit mod (Here is a comparisson of the original and new code for the first change, and the 2nd change is a new addition after the existing line shown)

 $gv_query_raw = "select c.coupon_amount, c.coupon_code, c.coupon_id, et.sent_firstname, et.sent_lastname, et.customer_id_sent, et.emailed_to, et.date_sent, crt.redeem_date, c.coupon_id   from.....

  $gv_query_raw = "select c.coupon_amount, c.coupon_code, c.coupon_id, et.sent_firstname, et.sent_lastname, et.customer_id_sent, et.emailed_to, et.date_sent, crt.redeem_date, c.coupon_id, c.date_modified    from.....

$contents[] = array('text' => TEXT_INFO_DATE_SENT . ' ' . zen_date_short($gInfo->date_sent));
//Add modified date (gv edit module):
	 if (($gInfo->date_modified) != '0001-01-01 00:00:00') { $contents[] = array('text' => 'Date Modified:'. ' ' . $gInfo->date_modified); }

Nothing SEEMS to be problematic with that to me, but I can't be 100% sure. The only way to test that would be to delete the lines and open the page, but crashing the Database on purpose with a 3+ hour turnaround for a reset from my host's support department is not something I relish if it isn't those lines.

Has anyone seen this issue before or have any idea what might be causing it?

Anything? This is a somewhat critical problem.

How many customers do you have in the customers table of your database?

If you have a huge number of customers and trying to display a dropdown with all of the customer emails then that is trying to load a page with 1 line per customer ...

This ended up being due to the addition of the "users who have never ordered" group to the database in a recent zencart update. I eventually narrowed it down to that and getting rid of it fixed the issue.

Ajeh:

How many customers do you have in the customers table of your database?

If you have a huge number of customers and trying to display a dropdown with all of the customer emails then that is trying to load a page with 1 line per customer ...

Ahh! I'm having the same problem with a test setup I'm running. There are no modifications to the GV system but I've imported about 6,000 customers and the gv_mail.php page never finishes loading before timing out.

If I force $display_count to false in audience.php, the page comes up right away but if I let it run the count, the page never completes.

I'd sure like to see how those numbers break down with my customers, is there any way I can speed this up so the page completes with the numbers? I'm running on a very fast server, this is the first page I've encountered with any noticeable delay.

thanks,

Ed

Try adding to the customers table indexes on
customers_lastname
customers_firstname
customers_email_address

does that do anything for you?

Ajeh:

Try adding to the customers table indexes on
customers_lastname
customers_firstname
customers_email_address

does that do anything for you?

I added indexes on customers first name and last name. An index already exists on customers_email_address (idx_email_address_zen).

In any case, that does not improve the situation, the page still won't load if counts are on.

Ed

Note.

You can turn off counts on audience queries in admin.

admin->configuration->Email Options->Audience-Select Count Display

wilt:

Note.

You can turn off counts on audience queries in admin.

admin->configuration->Email Options->Audience-Select Count Display

Thanks for pointing that out. I knew there had to be a switch for that in admin but I couldn't find it.

But the switch doesn't seem to work for me unless I change this:

if ($display_count=='true' || $display_count ==true )

to this:

if ($display_count=='true')

in audience.php

Must be something to do with my database conversion, I haven't tested this in an unaltered ZenCart.

Ed