MYSQL Error with Mysql version 8 and FullCalendar

[achartier]

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:

$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:

$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.

 

[troester]

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?

 

[achartier]

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.

 

[manjulaagarwal1955]

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.

 

[troester]

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.

 

[achartier]

Yes, that indeed solves the problem. Thankyou.

 

[troester]

https://github.com/Fabrik/fabrik/commit/e2e16eec018ce784a43409277f71508a7bc0a357