I have installed the account reminder module. However, unfortunately there appears to be an issue when the cron task runs. The following errors are observed in the logs:

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 '(NOW(), b.last_reminder) AS date_diff FROM users a LEFT JOIN ac query: SELECT a.*, b.msg_cnt, DATEDIFF(NOW(), b.last_reminder) AS date_diff FROM users a LEFT JOIN account_reminder b ON b.uid=a.uid WHERE a.access=0 AND a.uid!=0 AND status=1 in /drupal/includes/database.mysql.inc on line 120.

Comments

maartenvg’s picture

maartenvg commented

It appears that your MySQL version doesn't understand the function DATEDIFF that is used in that query. DATEDIFF() was added in MySQL 4.1.1. according to their site. Can you confirm that your version of MySQL is before 4.1.1?

dmc’s picture

dmc commented

Thanks for the information. I have contacted the provider of the server and you are correct. The version of the mySQL database is 4.0. Is there an alternative that can be used ?

polar-bear’s picture

polar-bear commented

I am having the same problem and my host's SQL version is 3.23.58!

Nigeria’s picture

Nigeria commented

Any fixes for this problem.

I'm on 4.0.27

Indemnity83’s picture

Indemnity83 commented

Here is a workaround that doesn't use the DATEDIFF() function, and instead calculates the number of days by first converting both dates into UNIX timestamps, finding their difference (this is a value in seconds) and dividing by 86400 (the number of seconds in a day). Lastly the whole thing is run through a FLOOR() function to get a whole number.

Replace line 132 of account_reminder.module with the following (this is the line right after "//Add users to the account_reminder table who are not already").

$result=db_query("SELECT a.*, b.msg_cnt, FLOOR((UNIX_TIMESTAMP() - UNIX_TIMESTAMP(b.last_reminder))/86400) AS date_diff FROM {users} a LEFT JOIN {account_reminder} b ON b.uid=a.uid WHERE a.access=0 AND a.uid!=0 AND status=1");

krystalepic’s picture

krystalepic commented

I replaced that code and I still got the error

user warning: Unknown column 'account_reminder.uid' in 'where clause' query: DELETE ke_account_reminder.* FROM ke_account_reminder,ke_users WHERE account_reminder.uid=users.uid AND access!=0 AND users.uid!=0 AND status=1 in D:\root\Krystalepic\story\includes\database.mysql.inc on line 172.

Thanks

jaydub’s picture

jaydub commented
Version: 4.7.x-1.x-dev » 5.x-1.x-dev
Status: Active » Fixed

The latest commit to CVS should take care of these issues. I've changed the table
structure to accomodate PostgreSQL and altered the date logic in the SQL as a result.

Please take a chance to test out!

Anonymous’s picture

Anonymous (not verified) commented
Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.