1064 error when trying to send customer email/newsletter

[lj20272]

Hi,

The other night I upgraded my database from 1.3.8a to 1.3.9h.

Everything seems to be working well other than when I try to send a customer email - i get the following error:

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as s left join zen_customers as c on c.customers_id = s.customers_id' at line 1
in:
[select c.customers_firstname, c.customers_lastname, s.email_address as customers_email_address from as s left join zen_customers as c on c.customers_id = s.customers_id ]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

I have read through some posts on similar problems and the following seems to be something that pops up.

Prior to upgrade, I had the newsletter subscribe and email exporter modules installed. I did not remove any of these before upgrading.

Could someone please describe what steps I need to take in order to get everything working again.

Thanks in advance

Amanda

[DrByte]

Prior to upgrade, I had the newsletter subscribe ... modules installed. I did not remove any of these before upgrading.

That's the problem.
In your database, you have a "query_builder" table that has an entry that was altered by the newsletter-subscribe addon, and is looking for a table that no longer exists, namely TABLE_SUBSCRIBE.
You'll need to delete that entry from the database table, or replace it with the original entry that existed before you installed that addon which changed it.

[lj20272]

Awesome, thanks for the quick reply.

I'm not too sure what I need to actually delete. Below is the SQL result for the query_builder table


Generation Time: Feb 10, 2011 at 08:07 PM
Generated by: phpMyAdmin 3.3.9 / MySQL 5.0.91-community-log
SQL query: SELECT * FROM `zen_query_builder` LIMIT 0, 30 ;
Rows: 11
query_id query_category query_name query_description query_string query_keys_list

1 email All Customers Returns all customers name and email address for sending mass emails (ie: for newsletters, coupons, GVs, messages, etc). select customers_email_address, customers_firstname, customers_lastname from TABLE_CUSTOMERS order by customers_lastname, customers_firstname, customers_email_address

2 email,newsletters Customer Account Newsletter Subscribers Returns name and email address of newsletter subscribers who have a customer account. select customers_firstname, customers_lastname, customers_email_address from TABLE_CUSTOMERS where customers_newsletter = '1'

3 email,newsletters Dormant Customers (>3months) (Subscribers) Subscribers who HAVE purchased something, but have NOT purchased for at least three months. select o.date_purchased, c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o WHERE c.customers_id = o.customers_id AND c.customers_newsletter = 1 GROUP BY c.customers_email_address HAVING max(o.date_purchased) <= subdate(now(),INTERVAL 3 MONTH) ORDER BY c.customers_lastname, c.customers_firstname ASC

4 email,newsletters Active customers in past 3 months (Subscribers) Newsletter subscribers who are also active customers (purchased something) in last 3 months. select c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o where c.customers_newsletter = '1' AND c.customers_id = o.customers_id and o.date_purchased > subdate(now(),INTERVAL 3 MONTH) GROUP BY c.customers_email_address order by c.customers_lastname, c.customers_firstname ASC

5 email,newsletters Active customers in past 3 months (Regardless of subscription status) All active customers (purchased something) in last 3 months, ignoring newsletter-subscription status. select c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o WHERE c.customers_id = o.customers_id and o.date_purchased > subdate(now(),INTERVAL 3 MONTH) GROUP BY c.customers_email_address order by c.customers_lastname, c.customers_firstname ASC

6 email,newsletters Administrator Just the email account of the current administrator select 'ADMIN' as customers_firstname, admin_name as customers_lastname, admin_email as customers_email_address from TABLE_ADMIN where admin_id = $SESSION:admin_id

7 email,newsletters Customers who have never completed a purchase For sending newsletter to all customers who registered but have never completed a purchase SELECT DISTINCT c.customers_email_address as customers_email_address, c.customers_lastname as customers_lastname, c.customers_firstname as customers_firstname FROM TABLE_CUSTOMERS c LEFT JOIN TABLE_ORDERS o ON c.customers_id=o.customers_id WHERE o.date_purchased IS NULL

8 email,newsletters All Newsletter Subscribers Returns name and email address of all Customer Account subscribers and all Newsletter-Only subscribers. select c.customers_firstname, c.customers_lastname, s.email_address as customers_email_address from TABLE_SUBSCRIBERS as s left join TABLE_CUSTOMERS as c on c.customers_id = s.customers_id

9 email,newsletters Newsletter-only Subscribers Returns email address of all confirmed Newsletter-Only subscribers. SELECT email_address as customers_email_address FROM TABLE_SUBSCRIBERS WHERE email_format != 'NONE' and confirmed = 1 and (customers_id IS NULL or customers_id = 0) order by email_address

10 email,newsletters Email Test Group - Newsletter-only subscribers Returns name and email address of Newsletter-only subscribers designated in Email test group configuration. SELECT s.email_address as customers_email_address FROM TABLE_SUBSCRIBERS as s LEFT JOIN TABLE_CONFIGURATION as q on LOCATE( s.email_address, q.configuration_value) >= 1 WHERE configuration_key = 'NEWSONLY_SUBSCRIPTION_TEST_GROUP'

11 email,newsletters Email Test Group - Customers Returns name and email address of Newsletter-only subscribers designated in Email test group configuration. SELECT c.customers_email_address as customers_email_address FROM TABLE_CUSTOMERS as c LEFT JOIN TABLE_CONFIGURATION as q on LOCATE( c.customers_email_address, q.configuration_value) >= 1 WHERE configuration_key = 'NEWSONLY_SUBSCRIPTION_TEST_GROUP'

[DrByte]

Delete the entries I've highlighted in your post above.

[lj20272]

Excellent!!! Have done as suggested and all is working fine.

Once again, thank you for your help.

Now, "if" I was to want to reinstall either of these modules at a later date, would it just be a matter of installing as if it were "new" installations?

[grneyes]

THANK YOU! Was having the same problem but all fixed now thanks to this thread.