I set up Signup for the first time, with CCK Date (6.x-2.4) enabled on my event node. As soon as cron ran I received my reminder email for the event. The node's date field was set for August 17 (a month from today), and the reminder was set for a day in advance. The email had the %node_start_time token, which came out correctly as August 17.

Everything else date related worked thus far, and i couldn't find any similar issues here. On a hunch, I dug through the code to find out where the date comparison was.

I found this was the value for $reminder_start in the _signup_date_reminder_sql function in date.inc:
DATE_SUB(STR_TO_DATE(field_date_value, '%Y-%m-%%dT%T'), INTERVAL s.reminder_days_before DAY)

The pattern in STR_TO_DATE had an extra % before the d, which was causing STR_TO_DATE to return NULL and mess up the date comparison. As a result, reminders were sent as soon as the function was called.

Being more of a designer then a developer, I just stuck a str_replace() in there to make the %% a % for a temporary fix (which works for now), but I dont have the developmental know-how to find the deeper cause of the problem. It might have something to do with the Date module, but I don't know.

One other thing: the date field I used only had year/month/day granularity, and therefor no timezone handling if that might be problematic.

Comments

echoz’s picture

I just encountered this too. Thanks, randaldroher, to be able to use this feature, I may have to also use your workaround if this isn't fixed. I'm wondering why this is not encountered by others, bumping to get some attention. Like you, I find no other issues, patches, nor this being addressed in the dev version.

echoz’s picture

More info - my case was on a site in development, it turns out I had Send reminder unchecked on the individual nodes, but checked on signup's settings (for future created nodes). I'm still not clear if this is a dangerous combination, since it also only happened once, and that was after the history table was cleared and the database moved to another installation. I'll have to do further testing.

echoz’s picture

I'm running this with uc_signup. Further testing shows reminder is sent on next cron run, rather than 1 day before, like the original poster reported. In addition, the cron run has unchecked the individual node setting for "Send reminder..". If this is re-checked and saved, running cron again will send another email. If anyone has this feature working, please advise.

mwidner’s picture

I'd like to hear if this is working, too. The hack above isn't applicable anymore as the date is getting calculated via SQL now, yet the problem remains.

whittonj’s picture

Version: 6.x-1.0-rc6 » 6.x-2.x-dev

Signing up to follow thread. having same problem. Originally thought it was an issue with Mime Mail, as plain text reminders were working at some point.

abowers’s picture

Initial tests for me show that this is working on my setup. I'm wondering if this is what is happening: Under Signup settings, I had "Send reminder 4 days before" checked for the default, as well as checked in the event node itself - however, once the email sent, it became unchecked. Send confirmation is still checked. Rechecking and running the cron sends the reminder e-mail again - which makes sense since it is still 4 days before the event. Leaving it unchecked and running the cron did not send a reminder e-mail. Rechecking and setting it to send 1 day before and running the cron has not sent either - again, we are still 4 days out - I will know next week if this worked since the event is on the 26th. Maybe this is just how it is supposed to work? It would allow one to send multiple reminder e-mails at different time periods by manually resetting (or perhaps through Rules integration) the checkbox and reminder period. Granted, this is just one test case.

Here's my setup for those interested:
Signup 6.x-1.0-rc6, with the following patch for Panels integration (http://drupal.org/node/603500)
Date 6.x-2.4
Content Construction Kit (CCK) 6.x-2.8

For my CCK date field, I am using Datetime (rather than Date or Datestamp), and have granularity set down to the minute. Additionally, it is a required field (both from and to) using the user's timezone handling, and I did set the Datetime field to be used with Signup. I received the reminder e-mail as expected (set to send 4 days before).

I also have a separate content type that is using a cck Date (rather than Datetime or Datestamp), have granularity set down to the minute. Additionally, it is a required field (just from, to is optional) using the site's timezone handling, and I did set the Date field to be used with Signup. I have not yet received the reminder e-mail for this test case, but that is expected - this event is in November.

echoz’s picture

@abowers, thanks for your detail on your testing, and your point about sending once and becoming unchecked now does make sense explaining that becoming unchecked is by design. What's different for me, and I think what the others here are confirming, is it sends the initial email on cron immediately, when it is not yet x days before. I am using field type "Date".

younggeezer’s picture

I spent most of the afternoon tracking this. The '%%d' in the conversion match string is coming from the date API. If I assume that it's incorrect in the date API and change it there, calendar events break. I tried randaldroher's str_replace hack, but I'm getting the same behavior, so I'm missing something. If I play with the mysql date conversion/add/subtract portion of the query in a mysql shell, it works correctly (after changing %%d to %d), but somehow the query always has no rows on completion.

So I'm at my limit, given that I don't really know php nor mysql. Still learning. And this is still broken.

--yg

abowers’s picture

@echoz, @younggeezer, I am using a Date field in one of my content types, as well, and it seems to be successful so far, though I have not tested it as extensively. However, the version of Date that I'm using is a bit older. What version of Date are you using? My setup is working with Date 6.x-2.4, maybe something changed in there in the most recent version? If so, I wonder if it would help to submit this to the Date issue queue as well?

echoz’s picture

Date 6.x-2.6 here

abowers’s picture

But then, I just noticed that @randaldroher mentioned was using 2.4, but mentions that has granularity set down to the day (rather than to the minute) and no timezone handling. Is this the case for everyone else? Maybe the change in Date module versions doesn't account for the difference with my setup and everyone else's.

I went ahead and verified that the content type with the Date CCK field is working so that I can be on the same page with a working setup.

Maybe this will help, in my case, I'm using %node_start_time in the reminder e-mail, and it inserts the date something like: "November 30, 2010 - 2:00pm" (I'm using Medium format in this case). Is it showing the date in the email as you would expect?

Could be a long shot: I noticed in the code that _signup_date_sql_math is differentiating between database type, mysql, mysqli, and pgsql. I'm not sure if it would matter, but could be a divergence point. In my setup, it is using MySQL and since MySQLi is enabled, I'm assuming that is what it is using, though I haven't verified. Maybe could be compatibility with PostgreSQL?

The only other thing I can think of is that when I enabled Signups in the content type, I chose the "Date field to use with signup:" in the content type, rather than setting it from the field itself. It doesn't seem like something that would matter, though.

echoz’s picture

I have date granularity set to Minute.
I can't retest my %node_start_time in the email right now, but I'm fairly sure I noticed it was coming through as the correct time, since I remember reading it and would have noted if it was the wrong time.
Using MySql and it does use mysqli.
That setting to choose the date field to use with signup - seems that the setting from either place is synced to the other, and shouldn't have an effect. Mine was set from the content type (and has matched setting checked on the field edit page). That seems to be working correctly.
I do NOT have Send confirmation checked.

@abowers, are you reporting that your signup enabled nodes do not send on cron if the date is farther out then the "Send reminder n day(s) before" setting?

younggeezer’s picture

@abowers, I'm using Date 6.x-2.6. Apparently I'm storing the dates as ISO time, not Datetime. I may try switching to Datetime to see if that has any effect. It will probably be Thursday evening before I get to it, though.

abowers’s picture

@echoz, yes, the reminder e-mails are not being sent on cron if the date is farther out than the "Send reminder n day(s) before" setting. In fact, in order to verify that the one using Date sent, I had to change the days to 35 days before since it is on November 30th (it was previously set to 4 days before, and I didn't want to wait). I have since rechecked it and set it to send 33 days for further testing - it did not send on any of the cron runs since, and I expect to get it tomorrow.

Let me know if you have any questions about my setup to help narrow it down. I am still hesitant to update to Date 2.6, just in case, even though others have reported the problem with 2.4. If it has something to do with that, I would expect that it is in the changes released in 2.5, since 2.6 didn't seem to have that many changes from 2.5.

joachim’s picture

DATE_SUB(STR_TO_DATE(field_date_value, '%Y-%m-%%dT%T'), INTERVAL s.reminder_days_before DAY)

This doesn't look right to me anyway. We'd need to check DateAPI module to see how it does queries.

xool’s picture

Subscribing

younggeezer’s picture

I updated to 6.x-1.0 of 09jan11 and just tried a bit of a test, and cannot duplicate the behavior I was seeing earlier. I did not get a reminder email immediately from the cron run.

Could some other folks please check? Thanks.

quagmire’s picture

subscribing

echoz’s picture

Version: 6.x-2.x-dev » 6.x-1.0

Revisiting testing this, hopeful after months have passed and modules updated, but no diff.

With Signup 6.x-1.0 and Date 6.x-2.7 I get the same results I reported earlier - the initial email reminder sent on cron immediately, when it is not yet x days before.

Send reminder checked in the settings, and for each test, on the node itself (correctly gets unchecked on cron run). Using with uc_signup.

coltrane’s picture

This bug sucks.

It's not the extra % before %d, that's on purpose to cause the desired %d to pass through Drupal and be used within MySQL for STR_TO_TIME(). KarenS addresses this http://drupal.org/node/319288#comment-1052865

The final query that's executed in MySQL is this

SELECT n.title, n.nid, n.type, s.reminder_email, s.forwarding_email, field_dates_value FROM node n INNER JOIN signup s ON s.nid = n.nid INNER JOIN content_type_event ON content_type_event.vid = n.vid  WHERE (s.send_reminder = 1) AND (n.type = 'event') AND ('2011-04-25 21:11:34' >= DATE_SUB(STR_TO_DATE(field_dates_value, '%Y-%m-%dT%T'), INTERVAL s.reminder_days_before DAY)) AND ('2011-04-25 21:11:34' <= DATE_ADD( STR_TO_DATE(field_dates_value, '%Y-%m-%dT%T'), INTERVAL 1 HOUR));

for an event with start date 2011-11-30 and reminder day 2. Yes, an event not suppose to be reminded about until 2011-11-28 passes the >=conditional as true when today is 2011-04-25. argh.

If I alter STR_TO_TIME on line 178 of date_api_sql.inc of date module to be DATE_FORMAT then this bug does not happen. I do not know however if DATE_FORMAT is incompatible with other uses of fields in various views.

still debugging

coltrane’s picture

I've proposed a patch at #1138210: date_sql_handler STR_TO_DATE math incorrect for date module to fix this

Leaving this issue open in case its wrong

adamdicarlo’s picture

Subscribing.

gjerdery’s picture

I also noted this in the thread where the patch mention in #21 is found, but the patch caused all events to not display on my calendar views.

jvieille’s picture

I seem to just be hit by this bug.
I don't know if it is related, but it came just after In updated MySQL 5.0 -> 5.1

sszawlim’s picture

Hello, the patch mention in #21 cause all events to not display on my calendar view.
Is there a fix yet.

ThX

sszawlim’s picture

here what i did so,
I update my date module to 6.x-2.x-dev (2011-Aug-18) and
updated the patch as #21.

Seems like fix the bugs, now able to see the events in the calendar module and when the corn run the reminder didn't go out. I have my reminder set for 1day before the events so, see the update work.

I'll post.

Thx

sszawlim’s picture

Yes, the update and patch fix the email reminder bug.

Thx for all the patch and post.

anis.khan’s picture

No solution to this but I was able to produce this error in the D7 version when the date field referenced allowed an end date to be collected. When the date field only allows a start date, the reminder email did not send on the cron job. HTH.

echoz’s picture

This did not fix it for me. As described in #26, I patched Date 6.x-2.x-dev and did not get the email set to send 1 day before event. In addition, every cron run results in an error for each content type with the signup enabled date (only one node of one of them was changed to send the email for this test). The error, in part, is: "Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC)" … signup/includes/cron.inc on line 46. I could not find anything in the database that was not in utf8_general_ci.

gjerdery’s picture

While updating Date to the most recent Dev version and applying the patch seem to fix the issue of email notifications appearing on time, and allows events to display in the calendar view - I'm still not able to see events listed in other varients of my calendar view (e.g. a list of upcoming events found in a block).

gjerdery’s picture

I found I had to change the granularity of my date filter in the views to day, from minute, in order to see the views.

echoz’s picture

@gjerdery, did you not get errors when cron runs, like I vaguely described above in #29 (only seen in watchdog, not on screen)? After retesting the patch on latest dev of Date, with changing date granularity of related view's date filter from minute to day (for which I need minute anyway, but wanted to test the patch), I still get these errors. I didn't bother to test the reminder email, since something is obviously not ok. Still hoping for a fix to get signup reminders working.

gjerdery’s picture

No, no error messages appear for me. My database also appears to be utf8_general_ci. I wish I had some more experience on the programming side to help figure this out. Someone I was describing the general issue to advised to make sure that both items being compared in the WHERE clause were actually DATETIME elements rather than strings. He said sometimes a >= comparison works on strings, but not always depending on collation... Sadly, this is where the conversation got a bit above my head.

rcschor’s picture

I rewrote the _signup_cron_send_reminders function in the signup/includes/cron.inc file and added a "adjustdate" function and it seems to be working sending email correctly for reminder dates when cron is run.

Here's the code: replace _signup_cron_send_reminders() and add the adjustdate()
--------------------------------

function _signup_cron_send_reminders() {

 $now = date("Y-m-d");
 $from = variable_get('site_mail', ini_get('sendmail_from'));
 $result = db_query('SELECT nid, reminder_days_before,reminder_email FROM {signup} WHERE send_reminder = %d',1);
 while ($signup_obj = db_fetch_object($result)) {
    $days_to_adjust = $signup_obj->reminder_days_before;
    $date_result = db_query('SELECT nid,field_date_value FROM {content_field_date} WHERE nid=%d',$signup_obj->nid);
    while ($content_field_date_obj = db_fetch_object($date_result)) {
      $date_value = $content_field_date_obj->field_date_value;
      $reminder_notification_date = adjustdate($date_value,0,0,-$days_to_adjust);
      if ($reminder_notification_date == $now){
        $node_result = db_query('SELECT title FROM {node} WHERE nid = %d',$signup_obj->nid);
        while ( $node_obj = db_fetch_object ($node_result) ) {
          $title = $node_obj->title;
          $node_nid = $signup_obj->nid;	
        }//end of node_obj
        $body = $signup_obj->reminder_email;
        $student_result = db_query('SELECT sid,uid, form_data FROM {signup_log} WHERE nid = %d',$content_field_date_obj->nid);
        while ( $signup_log_obj = db_fetch_object ($student_result) ) {
          $student_information_result = db_query('SELECT name, mail FROM {users} WHERE uid = %d',$signup_log_obj->uid);
          while ( $user_obj = db_fetch_object ($student_information_result)) {
            $node = new stdClass;
            $node->title = $title;
            $node->nid = $node_nid;
            $node->type= "event";
            $node->reminder_email=$body;
            $node->forwarding_email= NULL;
            $node->field_date_value=$content_field_date_obj->field_date_value;
            $signup = new stdClass;
            $signup->name = $user_obj->name;
            $signup->mail = $user_obj->mail;
            $signup->sid = $signup_log_obj->sid;
            $signup->anon_mail= NULL;
            $signup->form_data= $signup_log_obj->form_data;
            $params = array(
              'subject' =>'Reminder about ' . $title,
              'body'=>$body,
              'node' =>$node,	
              'signup' => $signup,
            );
            if (module_exists('token')){
               $params['body'] =  token_replace_multiple($body, array ('node' => node_load($signup_obj->nid),'signup' =>$signup,'global' => NULL));
	}; //end of if token
            $language = user_preferred_language($signup);
            drupal_mail('signup', 'singup_reminder_mail',$user_obj->mail,$language,$params,$from);
            }//end of $user_obj
          }//end of $signup_log_obj
          db_query("UPDATE {signup} SET send_reminder = 0 WHERE nid = %d", $signup_obj->nid);
       }//end of if notification
    }//end of $content_field_date_obj
 }//end of $signup_obj

}

function adjustdate($date,$years=0,$months=0,$days=0) {
 $year=substr($date, 0, 4);
 $month=substr($date, 5, 2);
 $day=substr($date, 8, 2);
 return date("Y-m-d", mktime(0,0,0,$month+$months,$day+$days,$year+ $years));
}

gjerdery’s picture

I got the code in #34 to work, with a few modifications. This code is directly referencing fields and tables based on the poster's cck defined content. I needed to modify references to field_date_value and content_field_date to match my own content type. This would work in a system that allows one and only one node type to accept signups. It seems like those references should be generalized to allow the use of multiple node types, and not involve hand customization. Thanks for a good start, rcschor! This seems like a more sustainable option than patching the Date module.

cybermache’s picture

I notice one comment on this feed talks about the reminder option within the signup node being deselected. I cannot determine, as of yet, when this deselection is occurring. My initial thought was when someone signs up to that specific node. But so far the checks on this show that that is not the case.

I guess my main thought is that maybe this isn't about how the CCK date is being processed that causes the reminder email to be sent but that this option within each signup node is being deselected somewhere along the line. And that that is the bug we are actually experiencing and should be looking for. I am also using the latest dev version and had had my reminder day settings set to '1 day' I am now testing it on '2 days' to see if the numerical value is the problem. This is on my development site and I do not experience or at least am unaware of this occurring on my public sites.

cybermache’s picture

couple quick test results. Number of days to send reminder has no affect on option being deselected neither does number of signups on node. Only thing that returns true is running cron deselects the reminder option on all nodes enabled with signup.

gjerdery’s picture

My understanding of how the reminder option works is that, during a cron, the system compares the current date/time with the calculated date/time that a reminder should be sent. If current time is equal to reminder time, emails are set and the check box option is unchecked. The issue is that for some reason the comparison always returns true on the first cron run, no matter what the reminder date is, then sends the email and unchecks the option box.

As an update to my post #35, for clarity I should note that I've tested the rewritten code from #34 with both Date 6.x-2.x-dev and 6.x-2.7, and it appears to work with both.

ghazlewood’s picture

I've just played with the code at http://drupal.org/node/1138210#comment-5520238 (#9) and switching to CAST from STR_TO_DATE for DATE_ADD and DATE_SUB selects the correct node (rather than all of them). I'm still testing to see if this is the best solution but it seems to solve the problem.

aLearningGuy’s picture

subscribe

Nique’s picture

Issue summary: View changes

I am using Date 6.x-2.10 and Signup 6.x-1.x-dev, and I am also having the problem of reminder e-mails being sent as soon as Cron runs.

I tried rcschor's code changes to signup/includes/cron.inc in #34, but it did not fix my problem, likely because I do not understand the code and thus cannot correctly make the changes recommended by gjerdery in #35.

My date field is called field_date and my content type is called trip. I recognize field_date_value in the code, but I do not understand what content_field_date does.