SQL error when trying to send e-mail.

Hi all,

I'm getting SQL errors when trying to email a customer, email coupons etc. I'm running 1.3.8a and have recently moved to a new server (Lunarpages).

Any help will be much appreciated.

Here's the error when trying to send a coupon.

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 '= subdate(now(),INTERVAL 3 MONTH) ORDER BY c.customers_lastname, c.customers_fir' at line 1
in:
[select o.date_purchased, c.customers_email_address, c.customers_lastname, c.customers_firstname from customers c, 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 ]
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.

Here's the error when trying to email from the customer list.

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 '= subdate(now(),INTERVAL 3 MONTH) ORDER BY c.customers_lastname, c.customers_fir' at line 1
in:
[select o.date_purchased, c.customers_email_address, c.customers_lastname, c.customers_firstname from customers c, 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 ]
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.

Surely someone has seen this error before and knows how to fix it?

I'm not feeling the love :cry:

Hi,

Do you know what version of mySql you are using.

see your Admin->tools->server info page in the Zen Cart backend

Database: MySQL 5.0.67-community

Any further ideas guys i'm really loosing hope with this problem :cry:

There is a potential problem related to the HAVING or GROUP BY clause structure if you've got certain seldom-used STRICT mode settings (namely ONLY_FULL_GROUP_BY) enabled in your database, but if that were the problem it would be telling you that certain fields are missing from the GROUP BY clause, instead of telling you that you have a general syntax error.

I've not been able to replicate the problem you're reporting. The syntax works fine.

If the problem started after moving to a new server, then perhaps there's something amuck with your new server's configuration and may require engaging your new hoster's technical support team.

DrByte -- I've reproduced this problem too. Migrated to a new server with 1.3.7 upgraded to 1.3.8a.

Server OS: Linux 2.6.30.5-43.fc11.i686.PAE Database: MySQL 5.1.37
Server Date: 09/16/2009 10:27:04 Database Date: 09/16/2009 10:27:04
Server Up Time: 10:27:04 up 16:15, 0 users, load average: 0.00, 0.00, 0.00 HTTP Server: Apache/2.2.13 (Fedora)
PHP Version: 5.2.9 (Zend: 2.2.0) PHP Memory Limit: 64M PHP Safe Mode: Off
PHP File Uploads: On Max Size: 2M POST Max Size: 8M
Database Data Size: 119,958 kB Database Index Size: 2,736 kB

Error found: admin>tools>send mail>

ADDITIONAL INFO FROM LOG FILE:
PHP Warning: constant() [<a href='function.constant'>function.constant</a>]: Couldn't find constant TABLE_SUBSCRIBERS in /var/www/html/includes/functions/audience.php on line 113,

I've not encountered any additional errors -- yet...

Any ideas on where to look?

Thanks in advance! Ken

rnet:

PHP Warning: constant(): Couldn't find constant TABLE_SUBSCRIBERS in /var/www/html/includes/functions/audience.php on line 113

TABLE_SUBSCRIBERS is not part of a default Zen Cart install. It's something you added with an addon, presumably the "newsletter subscribe" addon. Inside that addon there has to be a file that defines TABLE_SUBSCRIBERS, and you need to have that file uploaded to your site in order for it to work. In fact, you probably need to do that for both the admin and non-admin sides of your site. So, go back and double check all the installation steps for that addon and make sure you've not missed anything or haven't broken something during your upgrade by wiping out one or more of its changes when replacing files.