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.

Share This Page