1. NOTICE: If you are using Fabrik and update to Joomla 3.10, you will need to update to Fabrik 3.10. And, if you are using Fabrik, do not upgrade to Joomla 4, we do not have a supported version ready for release. More information on a release date coming soon. Also, please note that Fabrik 3.10 will not install on any Joomla sites less than 3.8.
    Dismiss Notice

MYSQL Error with Mysql version 8 and FullCalendar

Discussion in 'Community' started by achartier, Aug 13, 2020.

Thread Status:
Not open for further replies.
  1. achartier

    achartier Active Member

    Level: Community
    On my development system, Ubuntu 20.04.1 LTS, PHP 7.4.3 and Mysql 8.0.21 when I open the ChooseAddEvent window in full calendar I am getting an SQL error 1525 Incorrect DATETIME value: ''

    In plugins/fabrik_visualization/fullcalendar/models/fullcalendar.php around line 529 we have the following:
    Code (Text):
    $query->where(FabrikString::safeColName($endField) . ' >= ' . $db->quote($calStart));
    $query->where(FabrikString::safeColName($startField) . ' <= ' . $db->quote($calEnd));
    The values for $calStart and $calEnd are obtained at the start of the function with these 2 statements:
    Code (Text):
    $calStart = $input->get('startDate', '');
    $calEnd   = $input->get('endDate', '');
    but neither of those input values are in the ajax request so the values are assigned a null string ''.

    In Mysql version 8 apparently you can no longer compare a DATETIME to '' and Mysql throws the subject error.

    So, I do not know whether these 2 values should be defined or where they would be defined. I tried hard coding dates in the vis setup limits area but this did not help.

    If the values are indeed empty then substituting IS NOT NULL instead of the comparatives would probably solve the problem but I imagine there is a reason for these values.
     
  2. troester

    troester Well-Known Member Staff Member

    Level: Community
    As far as I can see these values are holding the start and enddate of the displayed calendar view (month, week...), so limiting the events to load and to display. Otherwise the query would load the complete list.

    If I add a var_dump I get e.g. $calStart 2020-07-26 for August month view etc.

    I didn't manage to see them empty (running php7.2)

    They seem to be set in plugins\fabrik_visualization\fullcalendar\fullcalendar.js line 69

    So beside of having some defensive coding for the start/end WHERE why are the values empty in your case?
     
  3. achartier

    achartier Active Member

    Level: Community
    That is true, but, that is for the initial calendar load. That function is not called for the chooseAddEvent Window. When this window is loaded a similar call is made but the dates are not included in the Ajax call.
     
  4. manjulaagarwal1955

    manjulaagarwal1955 Author

    Level: Community
    Try to upgrade it Mysql 8.0.21 and then try to open it in full, because sometimes calendar takes time to load features, but i think upgrading this would work.
     
  5. troester

    troester Well-Known Member Staff Member

    Level: Community
    Not sure where this is done, but it's calling getEvents in a multi-list fullcalendar for the given lists + one time with listid='' (and start/enddate not defined).

    Which isn't catched by
    if ((!empty($listid) && $this_listid != $listid) (around line 428)

    Doing
    if (empty($listid)|| (!empty($listid) && $this_listid != $listid) )

    should solve this.
     
  6. achartier

    achartier Active Member

    Level: Community
    Yes, that indeed solves the problem. Thankyou.
     
  7. troester

    troester Well-Known Member Staff Member

    Level: Community
Thread Status:
Not open for further replies.

Share This Page