1. We suggest you do NOT update to Joomla 3.8.10 until we can resolve an issue it causes with caching in Fabrik. If you do install it, you'll need to disable Joomla's "System Cache" in the global System settings.
  2. Apologies for the recent server outage, a planned migration by our host provider to a new location turned into a bit of a nightmare.

something in a recent update is causing an sql error (i think on prefilters)

Discussion in 'Professional Support' started by skyrun, Jan 9, 2018.

  1. skyrun

    skyrun Active Member

    Level: Professional
    after a recent update of fabrik from git, i am getting this error when opening my lists that have joins.
    i can't see the whole query being generated (even with fabrikdebug=1) but i assume the 'IN ()' is what is causing the issue.

    Code (Text):
    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 ') AND `Property_Reservations`.`id` IN () AND `srms_wholesale_reservation`.`id` I' at line 143
    i rolled back the fabrik update and the same fabrik setup works fine.

    not working here: https://testlocation.skyrun.com/skytrax-reservations/search

    has something changed in the logic used to create the fabrik sql recently (as in the last day or two)?
     
  2. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
  3. skyrun

    skyrun Active Member

    Level: Professional
    worked! thanks.
     
    cheesegrits likes this.
  4. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    Just FYI, that change was to fix a long standing issue with filtering on joined data (like repeat groups). Prior to this fix, filtering just didn't work on second or subsequent repeated joins, it only worked on the first join.

    What I'm not sure about with the fix I just applied, which prevents an illegal query on an empty set, like "WHERE foo IN ()", is whether, if the set is empty, we should be selecting anything, or returning no rows.

    I'll try and find some time today to test some more.

    -- hugh
     
  5. skyrun

    skyrun Active Member

    Level: Professional
    did you make some more updates today on this perhaps? upon initial update, the sql error was gone, but we found some other errors related to filtering during the day that caused all data to now show up on a search of an element from a join (to oversimplify).

    just now i updated fabrik again and it seems to have been resolved and i think i see all of the data. but i think i'm seeing it and push it out to our production sites and keep having to recover fabrik to a version from a few days back, so before i push this update out, wanted to see if you perhaps found something else to change and made more changes to how filters in joined tables works today.
     
  6. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    No, I didn't make any more changes. And if by "caused all data to now show up on a search of an element from a join", you mean you were getting results where there shouldn't have been, that may be the problem I was worrying might be an issue in my previous post. I need to set up some more test cases and figure that one out.

    -- hugh
     
  7. skyrun

    skyrun Active Member

    Level: Professional
    yes, it is still broke. it's hard to notice because it's returning some things but not everything... weird...

    so if you need a testcase, this does not work with the current code:
    1. userid is 'hugh' and password is probably the one you had for 'rod' (2 words no space, ends in s)
    2. goto https://testlocation.skyrun.com/skytrax-reservations/search
    3. search for shepherds lodge property. note that it shows 5 pages at 50 display per page, but only 7 show on the first page. that's what happens with this join error and it seems to be happening on every list i have.
     
  8. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    Unfortunately that doesn't really help as I don't know what it's supposed to show.

    I looked at getting an Akeeba copy to set up locally, but the archive is close to a gig. Is there anything I can exclude from a vackup that would cut that down, and not break anything? Like images?

    -- hugh
     
  9. skyrun

    skyrun Active Member

    Level: Professional
    this is how you can tell:
    'note the nav shows 5 pages at 50 display per page (250 total items), but only 7 show on the first page.' so needs 50 on the first page of 50. not 7.

    looks like when it's making paging decisions, seems to be including all record. but it's not displaying them all when it shows the rows.
     
  10. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    Yup, I can see the symptoms, but it's of no use unless I can actually step through the code and see what's happening.

    I'm happy to install that test site here, but I'll either need a smaller archive, or an ftp login so I can ftp it - fetching a gigabyte through http is a non starter.

    -- hugh
     
  11. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    Fyi, I reverted my original changes until I can find some time to work on this code again.

    Sent from my HTC6545LVW using Tapatalk
     
  12. skyrun

    skyrun Active Member

    Level: Professional
    good. thanks. i have not noticed an issue with the way it was(is now back to) working but nearly all of my lists were failing to show all results on the new code.

    i updated from GIT and can validate that it looks like the filtering behavior is back to working as it was. (thanks again...)

    also i am happy to provide FTP and mysql credentials to my test server/db so that you can debug with my lists/forms as use cases vs. creating your own test environment. just let me know when you would like to work on it and i can set it up.

    or note that the akeeba backup i sent is on AWS's S3. i think filezilla and most every ftp client supports s3 buckets now, so i think it may work. link to s3 bucket sent on PM.
     

Share This Page