Fixing Error 2006 MySQL server has gone away for Newsletters?

[fozzyo]

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