1. Fabrik 3.9 has been released. If you have updated Joomla to 3.9, this is a required update.
    Dismiss Notice

Joomla search plugin... error 500 ?

Discussion in 'Community' started by lcollong, May 6, 2019.

  1. lcollong

    lcollong FabriKant d'applications web

    Level: Community
    Hi,

    Using the J! search plugin to allow for a global search across several tables, I'm facing errors 500
    I've tried to simplify all what I can (only one list, very few non-joined elements...) but it still there.. Si I've investigate and found the following query triggering the error :

    Code (Text):
    SELECT DISTINCT `contact`.`cont_id` AS __pk_val0 FROM `contact`  WHERE ( MATCH(`contact`.`cont_nom`) AGAINST ('+bouch*' IN BOOLEAN MODE) ) ORDER BY `contact`.`cont_nom` ASC
    Running it in phpmyadmin shows up exactly the same error. Looking at the index, it shows Fabrik created an index on the cont_nom column. If I change this index from "Index" to "fulltext", the query give back the expected result without throwing errors.
    However I had to change it as it does not allow "distinct" together with an order by clause on a column which is not part of the select. Here is the final query giving expected results :

    Code (Text):
    SELECT `contact`.`cont_id` AS __pk_val0, cont_nom FROM `contact` WHERE ( MATCH(`contact`.`cont_nom`) AGAINST ('+bouch*' IN BOOLEAN MODE) ) ORDER BY `contact`.`cont_nom` ASC
    Fabrik is supposed to create index as needed isnt'it ? Why did it miss this one ? Is it related to something wrong with my DB or my table ? Could it be the mix of MyIsam / InnoDB mix in the tables (don't know why ?) ? Any idea welcome...

    Thanks,

    Laurent
     

    Attached Files:

  2. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    Hmmm, no idea why it didn't create the fulltext index.

    I'll see if I can replicate this. Also not sure about DISTINCT. Do you have repeat list joins on this list, and if so, do you have "merge" set?

    -- hugh
     
  3. lcollong

    lcollong FabriKant d'applications web

    Level: Community
    Yes indeed. Both repeat join on another list together with merge rows set.
    Also I forgot to mention that if you search for a 3 or less characters word it does not crash. It crashes as soon as you search for a 4 characters word or more. Probably due to internal MySQL coding ?....
     
  4. lcollong

    lcollong FabriKant d'applications web

    Level: Community
    If there is something I can do to go further on testing this issue ?
    I have the feeling that it was working on some previous site I built. Is this a recent downgrade ?
     
  5. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    I just haven't had time to look at it. I'll try and make time this week.

    -- hugh
     
  6. lcollong

    lcollong FabriKant d'applications web

    Level: Community
    I know time is the most precious thing we are all after... Any chance ?
     
  7. lcollong

    lcollong FabriKant d'applications web

    Level: Community
    Friendly bump.
     
  8. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    OK, I did some research ... the reason we don't create FULLTEXT indexes is that the index has to be created on the exact list of fields, in the same order, as the MATCH (...) AGAINST clause. So if you are matching against multiple columns (which the search plugin allows), say "MATCH(foo, bar, wibble) AGAINST (...)", the index would have to be created as "FULLTEXT (foo, bar, wibble)" (not FULLTEXT(bar, foo, wibble), etc). Which is a nightmare to maintain.

    HOWEVER ... MySQL shouldn't require a FULLTEXT index when doing a match "IN BOOLEAN MODE". It'll be slower, but it doesn't require the index.

    I'm still looking into the DISTINCT thing.

    -- hugh
     
  9. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    So I'm still confused as to why it's bitching at you about the fulltext index, when that query is IN BOOLEAN MODE.

    Can you test something for me ... try without the fulltext index, using the original query but without the DISTINCT.

    -- hugh
     
  10. lcollong

    lcollong FabriKant d'applications web

    Level: Community
    Hi Hugh,

    Thanks for looking at it.

    After changing back the 'cont_nom' index to "INDEX", this reduced from original query :
    Code (Text):
    SELECT DISTINCT `contact`.`cont_id` AS __pk_val0 FROM `contact`  WHERE ( MATCH(`contact`.`cont_nom`) AGAINST ('+bouch*' IN BOOLEAN MODE) )
    Give this error :
    Code (Text):
    Erreur dans la requĂȘte (1191): Can't find FULLTEXT index matching the column list
    and exactly the same error without the "DISTINCT" clause.

    I did the test using adminer 4.3.1 and :
    Just to be sure, I did it also using phpmyadmin on the same DB and it gives (hopefully) the same result.

    Current index :
    Index
    PRIMARY cont_id
    INDEX cont_ref_agence
    INDEX cont_ref_type(10)
    INDEX cont_nom
     
  11. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    Oh, hang on, is the table InnoDB?

    Looks like the implentations of FULLTEXT are different between MyISAM and InnoDB, and the latter does require a FULLTEXT index even IN BOOLEAN MODE.

    -- hugh
     
  12. lcollong

    lcollong FabriKant d'applications web

    Level: Community
    Initially, I thought you've got it but...

    For some reasons, some of our table are InnoDB powered where as some others are MyIsam (see snapshot). I guess some tables were created using the MySQL WorkBench (DBr designer) and linked to Fabrik after whereas some others were directly created from fabrik.
    I've changed all to the tables (including "contact") to InnoDB. The reduced SQL from the first post of this thread was working ! But the Joomla search still answer with an error 500... I've tried to find the query using the J! debug but I've got lost in the mode/list.php build queries staff...
    Finally I switch all the tables back to the MyISAM engine but the error is different. It seems not accept the "MATCH AGAINST" syntax ....
    I'll reverse to "everything under InnoDB" and try to be more successful in finding where the model throw en error exactly (it says line 948) but it seems to be earlier in the buildquery part.

    Edit :
    Well, actually I'm getting lost. I've reversed to "all innoDB" and reduced the filter list to only the column "nom" of "contacts". I'm back to the error I had with "all MyISAM" :

    Code (Text):
    500 Fabrik has generated an incorrect query for the list Projets: <br /><br /><pre>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 '(''., CONCAT_WS(''., CONCAT_WS(''.) AGAINST ('+bouch*' IN BOOLEAN MODE) OR MATCH' at line 40</pre><br /><pre> SELECT SQL_CALC_FOUND_ROWS DISTINCT `projet`.`proj_id` AS `projet___proj_id`, `projet`.`proj_id` AS `projet___proj_id_raw`, `projet`.`proj_creation_time` AS `projet___proj_creation_time`, `projet`.`proj_creation_time` AS `projet___proj_creation_time_raw`, `projet`.`proj_modif_time` AS `projet___proj_modif_time`, `projet`.`proj_modif_time` AS `projet___proj_modif_time_raw`, `projet`.`proj_creation_user` AS `projet___proj_creation_user`, `projet`.`proj_creation_user` AS `projet___proj_creation_user_raw`, `projet`.`proj_nom` AS `projet___proj_nom`, `projet`.`proj_nom` AS `projet___proj_nom_raw`, `projet`.`proj_code_statut` AS `projet___proj_code_statut_raw`, `dictionnaire`.`dico_txt2` AS `projet___proj_code_statut`, (SELECT GROUP_CONCAT(lookup.age_nom SEPARATOR '//..*..//') FROM projet_repeat_proj_code_agence LEFT JOIN agence AS lookup ON lookup.age_id = projet_repeat_proj_code_agence.proj_code_agence WHERE projet_repeat_proj_code_agence.parent_id = `projet`.`proj_id`) AS `projet___proj_code_agence`, (SELECT GROUP_CONCAT(id SEPARATOR '//..*..//') FROM projet_repeat_proj_code_agence WHERE parent_id = `projet`.`proj_id`) AS `projet___proj_code_agence_raw`, (SELECT GROUP_CONCAT(proj_code_agence SEPARATOR '//..*..//') FROM projet_repeat_proj_code_agence WHERE projet_repeat_proj_code_agence.parent_id = `projet`.`proj_id`) AS `projet___proj_code_agence_id`, (SELECT GROUP_CONCAT(params SEPARATOR '//..*..//') FROM projet_repeat_proj_code_agence WHERE parent_id = `projet`.`proj_id`) AS `projet___proj_code_agence___params`, `projet`.`proj_ref_respcom` AS `projet___proj_ref_respcom_raw`, `syn4p_users`.`name` AS `projet___proj_ref_respcom`, `projet`.`proj_ref_organisation` AS `projet___proj_ref_organisation_raw`, `organisation`.`orga_nom` AS `projet___proj_ref_organisation`, `projet`.`proj_ref_clicom` AS `projet___proj_ref_clicom_raw`, CONCAT_WS('', CONCAT(`contact_1`.cont_prenom,' ',`contact_1`.cont_nom)) AS `projet___proj_ref_clicom`, `projet`.`proj_ref_clitech` AS `projet___proj_ref_clitech_raw`, CONCAT_WS('', CONCAT(`contact_0`.cont_prenom,' ',`contact_0`.cont_nom)) AS `projet___proj_ref_clitech`, `projet`.`proj_ref_cliadmin` AS `projet___proj_ref_cliadmin_raw`, CONCAT_WS('', CONCAT(`contact`.cont_prenom,' ',`contact`.cont_nom)) AS `projet___proj_ref_cliadmin`, `projet`.`proj_remarque` AS `projet___proj_remarque`, `projet`.`proj_remarque` AS `projet___proj_remarque_raw`, `projet`.`proj_id` AS slug , `projet`.`proj_id` AS `__pk_val` FROM `projet` LEFT JOIN `contact` AS `contact` ON `contact`.`cont_id` = `projet`.`proj_ref_cliadmin` LEFT JOIN `contact` AS `contact_0` ON `contact_0`.`cont_id` = `projet`.`proj_ref_clitech` LEFT JOIN `organisation` AS `organisation` ON `organisation`.`orga_id` = `projet`.`proj_ref_organisation` LEFT JOIN `#__users` AS `syn4p_users` ON `syn4p_users`.`id` = `projet`.`proj_ref_respcom` LEFT JOIN `contact` AS `contact_1` ON `contact_1`.`cont_id` = `projet`.`proj_ref_clicom` LEFT JOIN `dictionnaire` AS `dictionnaire` ON `dictionnaire`.`dico_code_unique` = `projet`.`proj_code_statut` WHERE ( (MATCH(`projet`.`proj_nom`) AGAINST ('+bouch*' IN BOOLEAN MODE) OR MATCH( `organisation`.`orga_nom`) AGAINST ('+bouch*' IN BOOLEAN MODE) OR MATCH( CONCAT_WS(''., CONCAT_WS(''., CONCAT_WS(''.) AGAINST ('+bouch*' IN BOOLEAN MODE) OR MATCH( CONCAT(`contact_1`.cont_prenom) AGAINST ('+bouch*' IN BOOLEAN MODE) OR MATCH(' '.,' '.,' '.) AGAINST ('+bouch*' IN BOOLEAN MODE) OR MATCH(`contact_1`.cont_nom))) AGAINST ('+bouch*' IN BOOLEAN MODE) OR MATCH( CONCAT(`contact_0`.cont_prenom) AGAINST ('+bouch*' IN BOOLEAN MODE) OR MATCH(`contact_0`.cont_nom))) AGAINST ('+bouch*' IN BOOLEAN MODE) OR MATCH( CONCAT(`contact`.cont_prenom) AGAINST ('+bouch*' IN BOOLEAN MODE) OR MATCH(`contact`.cont_nom))) AGAINST ('+bouch*' IN BOOLEAN MODE) OR MATCH( `projet`.`proj_remarque`) AGAINST ('+bouch*' IN BOOLEAN MODE)) )
     

    Attached Files:

    Last edited: Jul 10, 2019
  13. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    That CONCAT_WS thing looks like you may be trying to do a search on a multiselect join (one set to checkbox or multiselect)?

    That won't work, unfortunately, as the values it then tries to search on are themselves subqueries.

    -- hugh
     
  14. lcollong

    lcollong FabriKant d'applications web

    Level: Community
    Ok. Let's forget that kind of complex search for now.
    I've deactivated the joomla search plugin in all my list except one "organisations" alias "companies" (different than the "contacts" one on which my setup was too complicated).
    All my tables have been converted to InnoDB.
    I've set filters columns to only one ('nom') which hold the name of the company as a regular field (Varchar 45).
    I've a fabrik's regular column filter on this one (dropdown) which works as expected. Also a "filter all" able to show that rows corresponding the filtered text. Both work very well.
    The joomla search on "bouch" give an error :
    Code (Text):
    500 Fabrik has generated an incorrect query for the list Organisations: <br /><br /><pre>Can't find FULLTEXT index matching the column list</pre>
    And, indeed, the column "nom" has an index on ie which is not "fulltext"....

    If I delete the index, it gives the same result.
    If I set a new column on which the were no filtering... same error (country)
    If I set a filter on this column country, it creates an index rather than a full text and I got an error...
    If I convert this index to fulltext... it works !

    What is wrong in my setup ? Is the joomla search on InnoDB supposed to work ? Should I revert to all MyISAM ?

    Thanks
     
  15. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    Nothing wrong with your setup. Fabrik is expecting MyISAM. We don't play well with certain aspects of InnoDB - one of those aspects being FULLTEXT. As I mentioned in a previous post, MyISAM will quite happily do "MATCH AGAINST ... IN BOOLEAN MODE" with no FULLTEXT. InnoDB won't - it throws the error you are getting.

    We don't create FULLTEXT indexes. As explained in that previous post, they would have to be created on the exact fields in the exact same order as the search ... and that would be a nightmare to maintain. Every time you changed what elements you have in the J! global search, we'd have to blow away the FULLTEXT and recreate it, after figuring out which exact order those fields would appear in in our search query ... with the added complication that fields on separate tables have to be grouped into separate MATCH AGAINST queries ... then if you had multiple copies of a list with different search all fields ... :/

    So you'll either have to use MyISAM, or create your own FULLTEXT index(es) to match the query being generated for our J! search.

    BTW, another aspect of InnoDB we don't play well with (and the main reason we don't officially support InnoDB) is foreign key constraints. Don't use them. Or you may well run into nasty problems.

    -- hugh
     
  16. lcollong

    lcollong FabriKant d'applications web

    Level: Community
    I've converted all the non-joomla tables to MyISAM and forgot to search on complex columns (multiselect Dbjoin). Everything seems to work.
    We'll do some more extensive tests and see what is going on.
    Thanks for your help and explanations.
     
    cheesegrits likes this.
  17. lcollong

    lcollong FabriKant d'applications web

    Level: Community
    Hi Hugh,

    As a follow up on this thread, I've found another way to achieve what we need. Here under the explanations (sorry for the length) as it could be useful to some others. However, I still have two minor glitches that may rise some idea in your brain.... :)

    We have around 30 tables in our apps (kind of CRM/ERP). On several of them we use the "search all" filter on top of the list view which is working very well to show up the relevant lines in the list whatever the kind of elements (including multi-select using a underlying repeat table).

    The idea was to use the "global search" of Joomla to do a cross table search on many columns. Unfortunately, it does not work on complex elements such as multiselect DBjoin.

    Think about finding everything related to the city of Paris. You search for "Paris" and it give you back contacts living in Paris, company'address based on this city, contract concerning the city, suppliers about you wrote a note related to the city, invoices, etc...
    One solution would be to display each of these lists and use the search all "paris" in each of them.... tedious !

    As we need both the search all feature on complex element on the list level and some kind of "google like global cross list search" we can't use the J! search plugin as per the conclusion of this thread.

    So the idea is to use Fabrik list modules to display some lines of each list. Modules are stacked vertically using condensed list view template without any "features" (pagination, edit/add buttons, filters, etc...). Just a simple "table" with the name of the list on the top and the results.

    On top of this stack, there is a fabrik form module. It displays a single element (searching text) and the setup is to not record in database. The form itself has a redirect plugin to himself. Here is the Fabrik's magic ! The redirect URL fills also the search for all the modules (4 result modules version) :
    Code (Text):

    index.php?option=com_content&view=article&id=10&Itemid=197&___chercher={___chercher}&fabrik_list_filter_all_45_mod_fabrik_list_104={___chercher}&fabrik_list_filter_all_46_mod_fabrik_list_106={___chercher}&fabrik_list_filter_all_47_mod_fabrik_list_107={___chercher}&fabrik_list_filter_all_48_mod_fabrik_list_109={___chercher}
     
    Itemid #197 is a menu item called "Search" which is showing a dummy article. The search module and the list module showing the results are set to display only on this menu item.

    "___chercher" is the name of the element in the search form.

    The first numbers (45, 46, 47, 48) are the list ids showing in the different modules. The second numbers (104, 106, 107, 109) are the module ids themselves set to show the corresponding lists.

    All modules are ajaxified.

    So when you type "Paris" in the search form, press submit, it refresh backs all the module contents with the relevant lines in each list containing what you are searching for. Without any code... easy, nice and great !

    We use specialized list (s_suppliers, s_invoices, etc...) to display in the module so it's easy to setup the published elements and the ones showing and/or searching for independently of the main list used in the app.
    All these specialized lists have access level set to "nobody" except list view. So no buttons nor edit link are shown.
    Custom link on some elements are used to link the results to the right detail view (or form view or filtered main list view).

    It's very flexible as you can search for the columns you want, display them, sort them, etc... Using some jlayouts override on pagination and some css allow to condense module results as much as possible so page is handy and comfortable to watch.

    However I got two things missing to achieve a perfect UX. First the "enter" key as submit feature does not work on the search form (related to the module display ?). Need to click on submit button (renamed "search"). So I added some js on keydown on the element that triggers the submit button when "hearing" the right key :

    Code (Javascript):
    if (e.key == 'enter') {
    /*  console.log('enter !'); */
      var form = Fabrik.getBlock('form_49');
      form.doSubmit(new Event.Mock(form._getButton('Submit')), form._getButton('Submit'));
    }
    The second one is more complex and I did not found the right way to get rid of it. When you hit the submit button, it shows a "spinning wheel" div during the time you are waiting for the ajax response. But it's stop too quickly. It does not wait (of course !) for the completion of the list module ajax return. So it's a bit strange as you have the search form displaying back "normally" then after one second, all the list module are reseted to "nothing to show", then after 3-4 seconds, you get all the list modules refreshing showing the expected results.

    I think the idea would be to let the form's "spinning div" on until all the ajax return are completed. I don't know where to start. Should I catch each module ajax "update" completion ? Any suggestion ?

    Thanks,
     
    Last edited: Nov 3, 2019

Share This Page