Newsletter send problem: 2013:Lost connection to MySQL server during query

[maxmarketing]

Our store has been online with Zen-cart for many years. We recently moved our cart from one host to another. Everything seemed to be working fine. We have received orders, the order-related emails have been sending, etc. However, when we click on "Newsletter" under tools the page does not load and we finally receive a notice after several minutes of "WARNING: An Error occurred, please refresh the page and try again."

When we click "product notifications" under tools we can create a newsletter but receive the same error as above when attempting to send.

Here is the log report:

[15-May-2015 17:45:01 America/Chicago] PHP Fatal error: 2013:Lost connection to MySQL server during query :: 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 in /home2/XXXXX/public_html/includes/classes/db/mysql/query_factory.php on line 120

Version 1.5.1
PHP Version 5.4.38
Database: MySQL 5.5.42-37.1

Thank you in advance for any words of wisdom you have!

[barco57]

Yes, it's timing out. It's taking longer than the new host allows, and the process is automatically killed. Time to talk to the host.

[maxmarketing]

Given that we have about 4,000 opt-in subscribers in the database, what is Zencart's recommendation for the timeout variable for PHP (assuming you are referring to max_execution_time)? If there are any other variables that should be increased please let me know.

Thank you!

[DrByte]

Given that we have about 4,000 opt-in subscribers in the database, what is Zencart's recommendation for the timeout variable for PHP (assuming you are referring to max_execution_time)? If there are any other variables that should be increased please let me know.

It's not specifically an issue with PHP max_execution_time, because the error was with MySQL.
It seems PHP is running along happily up until the point where MySQL says "oh, your connection to the database has been open longer than we're configured for, so we're going to disconnect you now", and PHP is simply reporting what happened.

This could be "different" from your "previous host" in multiple ways:
- maybe MySQL is configured differently on both hosts
- maybe sending email is taking longer on the new host
- maybe the new server is indeed slower than the old one, so even though PHP isn't hitting timeouts yet, the total iteration time is longer because it requires PHP plus MySQL plus email ... for each recipient.

As for recommendations, in suggested order:
1. If your list is growing significantly, consider using a proper newsletter-sending service like MailChimp ... because "successful email delivery" is their expertise, and sending massive amounts of email thru them won't risk getting your webstore blacklisted for sending too many emails, and therefore won't put at risk the delivery of order-update emails which are far more critical.
2. Consider segmenting your list into smaller groups (manually editing or adding to the query_builder database table records) so these timeouts don't affect you.
3. Work with your host to understand the current MySQL configuration for open-connection timeouts and tweak if possible (most won't let you unless you have a VPS, else it affects everyone else on the server too, and can slow down the database overall or max-out the server RAM).
4. Tune and tweak the server for optimal overall performance, not just with MySQL: https://www.zen-cart.com/content.php...eed-up-my-site and https://www.zen-cart.com/content.php...er-tuning-tips

A future version of Zen Cart will have queuing in it for sending messages ... which is basically a very similar net effect as #2 above, but will work effectively with #1 too.