Fabrik 500 error on all front end pages

[rackem]

I just started receiving the following 500 Error on my home page (which does NOT contain any Fabrik content) as well as similar errors on ALL front end pages:

Error: 500 - Fabrik has generated an incorrect query for the list
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 'FROM' at line 2 SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT
FROM

I am quite stumped and hope someone is able to offer suggestions for troubleshooting. Here is some background information.

The backend functions normally and I am able to view all Fabrik content.

Prior to the error, I removed several old Fabrik lists that were no longer in use and emptied the Joomla #_rediret_links table. Everything worked properly immediately after this was done. This error started to occur sometime afterward even though no other things were done to the site. So perhaps it has something to do with caching? I went through and cleared all caches but no change.

It seems the query is massively incomplete but it isn't clear to me why it is even being performed when there is no Fabrik content to display several pages of the site.

I don't see any clues in the site or host error logs and there is no change in the error message if error reporting changed to maximum.

Disabling the Fabrik system plugin changes the error message to: "Error: 400 - Please ensure you have installed and published the Fabrik system plugin"

Any ideas please?

 

[Bauer]

I had a similar problem after 'cleaning up' some fields just the other day. Turns out I had some javascript validations that were referencing some of the fields I removed, and that was causing the 500 error. I had to go through the #__fabrik_jsactions table until I found the culprit.

Are you sure you don't have a module or article that is using a fabrik (content) plugin?

Using my trusty File Search&Replace, the only fabrik file that uses SQL_CALC_FOUND_ROWS is components/fabrik/models/list.php (line 2601)
So that sounds like a good starting point for debugging.

And the only files that call that buildQuerySelect function are
components/fabrik/models/form.php
components/fabrik/models/list.php
plugins/fabrik_visualization/calendar/models/calendar.php
But I also have a file named components/fabrik/models/cache.php - that I have, for some reason unknown to my memory, renamed to cache.php.OLD - which also calls the buildQuerySelect function.

After Looking at the latest github files, that cache.php file is no longer included in the fabrik package.
So if you still have that cache.php, try deleting or renaming it - maybe for some reason something is including that file and calling the buildQuerySelect function?

Also try disabling the plugins/fabrik_visualization/calendar/models/calendar.php file. Maybe that plugin is what's causing the problem.

Sorry no real solutions - but maybe those are a few places to start looking.

 

[rackem]

Thank you very much for the suggestions Bauer! It does sound like there must be a linked item of some sort that was missed. That cache.php certainly sounds like a possible culprit but I do not see a components/fabrik/models/cache.php file or any cache.php file in a Fabrik directory. Like you mentioned, the only thing I could think of present on every page was a module or menu item but that all seemed clean at the time.

For now I have recovered from a recent backup to get the site back up. I plan to re-perform my clean up more incrementally (waiting for a while after each change) to hopefully isolate the cause a little better. Step one is removing the related menu items, articles, and modules - so far, so good it seems. If (or more likely, when) the error comes back, hopefully these hints will help track down the issue. I will post back with anything I find.

Happy New Year!

 

[rackem]

Just an update. I went through the whole process again and received the same error. I can say it was NOT due to a lingering menu item or module. Once again, I "finished" the cleanup process and the site operated normally even after clearing Joomla and my host caching. The error occurred at least an hour afterwards so apparently there is another cache involved that I am unaware of.

However, I did find quite a few entries in the #_fabrik_jsactions table like you mentioned that were linked to the elements and custom JS files that were removed. I tried removing the entries using phpMyAdmin but the error continued. The mysterious cache is a complicating factor though and I wasn't able to wait around with the site down. So, had to recover from backup again and ponder how to proceed. Now that all the fabrik stuff is present, I'll probably edit the pertinent items to remove the JS stuff and then verify the entries are removed from the #_fabrik_jsactions table.

Bauer's post got me curious if there could be any other lingering items in other #_fabrik tables. I noticed an entry in the #_fabrik_log table from a couple years ago:

message_type = plg.cron.php
message =
php
listid = 1
SELECT SQL_CALC_FOUND_ROWS DISTINCT ...

The "..." refers to a query to one of the tables I am trying to remove. I am wondering if I can remove this entry and/or what this could be?

I also noticed 29 entries in the #_fabrik_form_sessions table. Most of these have a time_date = 0000-00-00 00:00:00 but all appear to be unnecessary at this point. I wonder if it is OK to empty this table as well?

A final observation is that when I went to remove my old Fabrik lists (empty trash), I got the choice what to delete. I chose "lists, forms, groups, and elements" however, Fabrik only deleted the lists. I needed to manually go in and remove the other stuff. Not sure if this could be related to this issue or if this is a bug.

 

[troester]

I think you can (and should) truncate fabrik_session (this is for partly saved multipage forms etc), so it is some sort of "cache ", too.
fabrik_log is just a log table (used e.g. by cron jobs) and can be truncated, too (but should not create any issue)

List "empty trash" with group/form/elements is working on my site (tested with a simple list without joins)
Didn't it delete any group/elements? or only some?

 

[rackem]

Thank you troester for the info on emptying the session and log tables.

List "empty trash" with group/form/elements is working on my site (tested with a simple list without joins)
Didn't it delete any group/elements? or only some?

It may have deleted some of the form/groups/elements but the majority remained. My recollection was that it had worked fine in the past on other lists. The lists I am removing here are several years old, actually the first ones created on this site. So maybe there are some 2.5 beta / 3.2 version issues going on?

 

[Bauer]

Since you say this happens some time after the 'cleanup' - the only other culprit I can think of besides some cache would be the cron plugins. Do you have some sort of fabrik Schedules Task set up that is referencing those cleaned files? Try disabling all of the fabrik cron plugins and see if your problem goes away. Search for 'cron' in Joomla plugin extensions and you'll see all the fabrik cron-related plugins. Maybe one of them is referencing those cleaned files and triggering a server error?

ALSO - I forgot about mentioning the fabrik Connections table. If that has incorrect settings it will cause fabrik to fart out on you (and, if I remember correctly, produce a 500 error). Open the #___fabrik_connections table via phpMyAdmin and check that the database settings are correct - and set as Published,Default.

90% of the time where I have had 500 server errors it was because of database join elements. I.e. either a concatenated label or WHERE condition was referencing a table or element that no longer exists. Via phpMyAdmin look at the #__fabrik_elements table sorted by 'plugin' and take note (write down ID) of databasejoin elements where 'Published' is set as 0 - then you can disable them all via Query: update #__fabrik_elements set published=0 where plugin='databasejoin'. If that allows access without a 500 error, then you found your problem. Then re-enable each of those databasejoin elements until you find the one that was causing the error and work at getting that (or those) element corrected.

 

[rackem]

Well, it seems this problem has been fixed Bauer and I have you to thank very much!

I think the issue was in the crons - I had 3 pointing to these obsolete lists. Only 1 was published (I run most of my crons manually) and the increment was set to 1 day. I feel pretty sheepish about that so thank you again for your help & patience in pointing it out.

There were also quite a few entries in jsactions table on my obsolete elements. I cleared those out manually before deleting the elements this time. I also found several that were on elements that had been deleted well before this time so it seems these aren't being cleared properly when an element is removed. I'm not sure how long they were there but the site had been working with them there.

I also emptied the #_fabrik_form_sessions and #_fabrik_log tables with no issues.

After all that, I was pleasantly surprised when I went to delete the lists this time, the process properly deleted the related forms, groups, and elements too. So perhaps there is a check in there somewhere to prevent foolish admin like me to screw up my site, lol

So, all of the Fabrik lists, etc. I was trying to remove are now gone and the site has been operating properly for 16 hours. I ran my one published cron manually just to verify no issues.

 

[Bauer]

Well I'm as happy as you that I was able to help. (Isn't that what this Community forum is all about?)