Fixing Error 2006 MySQL server has gone away for Newsletters?

I have been asked to look at a Zen Cart installation that has over 6,500 customers and 8,000 orders placed because the site owner was unable to use the Newsletter functionality. When you go to send a newsletter you click the 'Send' button, but after a delay you see the error message "Error 2006 MySQL server has gone away".

In the default installation when you go to send a newsletter or product update the system runs several queries to look at who should receive the notification. Should it be all newsletter subscribers, only those who have ordered in the past three months, those who have never ordered etc. And these are all very useful marketing tools - just that with so many rows to process it can take time. And in a shared hosting environment they don't want you hammering their servers to the detriment of other users so you are only allowed a given amount of time to execute a query in. As soon as you exceed that time the server cuts you off - very frustrating, and a lot of hosting companies won't help you out by changing this option.

In most cases the solution is fairly straight forward. In this installation the owner only really wants to send e-mails to two groups of people.

1. The admins to proof the newsletter formatting
2. All newsletter subscribers

Both of these queries are very simple and very quick to run so you don't have the problem above.

The queries that are used are stored within the Database so you don't need to go poking around in the code. Your hosting company should provide you with access to a tool like phpMyAdmin which gives you direct access to the database.

Before you proceed ... BACKUP YOUR DATABASE.
~~
Login to phpMyAdmin and open the table query_builder and select browse to view all the rows.

Here you will see a list of all the available audiences you can send your newsletter or email to. You need to select all entries excluding the following three:

All customers
All newsletter subscribers
Administrator

All the rows you have selected you are about to delete, so ensure you have a backup of your database and that none of the three rows above are selected.

Now click delete to remove the unwanted rows.
~~
And your done! You can now go to the Newsletters admin page and send your newsletter to all subscribers!

I've done this in Zen Cart version 1.3.8a without any problems. You need to replicate your site and database and test this on a development site first to ensure that it doesn't break anything ... do it on a live site at your own risk. :)

Mat

... installation that has over 6,500 customers and 8,000 orders placed because the site owner was unable to use the Newsletter functionality.
Why do people believe that they get unlimited email with shared hosting??

Most all shared hosts have a limit as to the number of emails that one can send....

With 6,500 - if theywere to be posting a letter through USPS they would think twice or more about what they were sending

Bottomline is they should be using a mass mailing service

ACK! You're recommending DELETING things?

Be sure to remind everyone who listens to your advice that they should come running directly to you when they have problems with upgrades or want those queries back for the marketing reasons for which they are intended.

Better advice would be to start with turning off the Audience Counts in Admin->Configuration->Email Options. This stops those queries from being run to show you how many members are in those groups.
If that is not enough, then simply update each of those queries and remove the 'newsletter' text from the module column which determines "when" each is used.

DrByte:

Better advice would be to start with turning off the Audience Counts in Admin->Configuration->Email Options. This stops those queries from being run to show you how many members are in those groups.
If that is not enough, then simply update each of those queries and remove the 'newsletter' text from the module column which determines "when" each is used.

My research and searches into the problem failed to find this solution ... all I was able to find anywhere was to get your hosting company to increase the query time out. Your solution is naturally a lot better, but at the time that was the only way I could find to fix the problem.

Mat