sql error when using Search for 'Any words' or 'Exact Phrase'

[Borox]

Hi,

Search for 'All words' works, but when I use the options 'Any words' or 'Exact Phrase'
I get an sql error :

 

[cheesegrits]

Ah, OK, that's because MySQL (or MariaDB) don't support using CONCAT() or CONCAT_WS() in a fulltext search. And what's happening is you must have a join or a CDD with a "CONCAT label", which we build with CONCAT_WS() in the query. We account for this when doing internal Fabrik searches in fulltext, but apparently not for the J! global search.

I've had a go at fixing this, as of this commit (and the one after it, which fixes the fix!):

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

... so you'll need to do a full github update if you want to pick up the fix.

You will also need to edit any joins/CDDs, and make sure you specify a regular "Label" field, as well as any CONCAT labels, which will be used for fulltext searches. Not ideal, as it won't be searching exactly what is displayed for the label of that element, but there simply isn't a way of doing fulltext searches on "generated" data, they have to be done on simple fields that can be indexed.

-- hugh

 

[Borox]

Thanks Hugh I try that and I'll let you know

 

[Borox]

After a github update I don't have anymore the error on CONCAT_WS but I have another error:

plan_comptable.compte_id is not a column from the table 'plan_comptable' (that's right it's a column from the table 'budgets_d_details'

It seems to be relative to this CDD:

I tried to delete the CONCAT label, but I still have the same error.

If I unpublish this element, there is no more the error, so it's definitly a problem with this CDD but I don't see what setup I can do.

 

[cheesegrits]

Meh. That's going to be a pig to track down.

is that CDD on the main table, or on a joined/repeated group?

-- hugh

 

[Borox]

The CDD is not in a repeated group but 2 tables used are joined (table 'Budget' and table 'budget_de_tails')

I use the CDD here:

'Budget imput?' is from 'Budget' and 'Compte comptable imput?' is from 'Budget_d_tails' and this is the CDD element shown upper.

The CDD uses a third table ('plan comptable') to display some text ( called 'rubrique') instead of a number ('compte_id'/'Compte comptable' from 'budget_d_tail')

What's strange is that, even if I delete the 'CONCAT label' which refers to the table 'plan comptable', the error continues to refers to this table...I tried to delete all the cache I found...it's the same...I have the impression that I'm missing something

 

[Borox]

Hi,

An update on the error I have when using search.
Depending on what I search, I have or not an error.

For example:
If I search fo 'comp', no error:


If I search for 'compo' instead of 'comp':
ERROR 500


The error is on
MATCH(`travaux_taches`.`travaux_taches_nom`, `#__users_0`.`name`, `travaux_taches`.`travaux_taches_commentaire`) AGAINST ('compo*' IN BOOLEAN MODE)

and more precisely on `#__users_0`.`name` (#__users_0 is an alias of #__user), if I delete it the request has no error

If someone has an idea...thanks guys

 

[cheesegrits]

The only way I could figure this one out would be to debug it locally - get an Akeeba backup of your site, install it here, and step through the code in my PHP development environment. I simply can't reproduce your setup or that error from your description.

And that isn't something I can do as part of subscription support, even at the Pro level. Once we're at the point of having something so "corner case" and hard to reproduce that I have to do xdebug'ing on it, we have to charge something to cover that time (it takes about an hour to download the Akeeba, install it locally, then set up a PHP Storm project on it, before I can even start the debugging process). The good news is that once I can use xdebug and step through in PHP Storm, I can pretty much always very quickly find and fix the issue.

-- hugh

 

[Borox]

Hi Hugh,

I understand, just give me the cost to do that and how to proceed.

Thanks

 

[mediaateam]

Hi @Borox - I'll direct message you on cost and how to proceed.

 

[cheesegrits]

I'll get Robbie to contact you. The problem with any kind of issue like this is, it's hard to give a fixed quote, as although I can identify the issue quickly, until I do, I have no idea how easy / hard it will be to fix.

-- hugh

 

[Borox]

Hi Hugh,

I was in touch with Robbie. Here is something to help reproducing the problem:



At the bottom of the list you can access to all the results



When you click on 'Voir tous les r?sultats' it shows the search page:



And if we put 'compo' instead of 'comp' the 500 error is displayed.

Thanks Hugh.

 

[cheesegrits]

OK, I'm logged in to your site and doing an Akeeba backup now, I'll install it locally later this evening, get back to you in the morning.

-- hugh

 

[Borox]

Thanks Hugh.

 

[cheesegrits]

I've installed your site here through Akeeba kickstart, but I'm having problems getting it to load. the browser just sits in a loop, constantly reloading the URL, and never displaying anything.

Can you think of any plugin you might be running that could cause this?

-- hugh

 

[cheesegrits]

Ah HAH! It's something in the htaccess.

Finally got a login page to load by renaming that.

-- hugh

 

[Borox]

I was about answering...that I didn't know I use AKEEBA Kickstart quit a lot and never saw that.
Good to know that my htaccess can do that

Nicolas

 

[cheesegrits]

I think it's because of your canonical redirect to https, and something to do with my test box's Apache setup for https.

That kind of ate up my time this morning, so I had to move on to some other stuff. I'll come back to this later this evening.

-- hugh

 

[cheesegrits]

OK ...

The problem is indeed because of the join. For the global fulltext searching, we produce a where clause like this:

WHERE
    (
        MATCH (
            `travaux_taches`.`travaux_taches_nom`,
            `nkqs9_users_0`.`name`,
            `travaux_taches`.`travaux_taches_commentaire`
        ) AGAINST ('compo*' IN BOOLEAN MODE)
    )

... with all the elements you've included from that list as searchable in one MATCH (...) AGAINST statement. Unfortunately, MySQL doesn't let you include fields from multiple tables in a single MATCH, and the way it needs to be is ...

WHERE
    (
        MATCH (
            `travaux_taches`.`travaux_taches_nom`,
            `travaux_taches`.`travaux_taches_commentaire`
        ) AGAINST ('compo*' IN BOOLEAN MODE)
        OR
        MATCH (
            `nkqs9_users_0`.`name`
        ) AGAINST ('compo*' IN BOOLEAN MODE)
    )

I think this is fixable. I'm looking at the code now. It's not trivial, but as long as I can get it done in the next 30 minutes or so, I'm happy with including it in the cost you've already paid for the basic evaluation of the problem. If it turns into a more complex task, we may have to bill some more time, or you may need to just turn off searching of the user name.

Note that one other issue I think we will run across is that the #__users.name field won't automatically have a fulltext index built on it. That's another issue entirely. When you specify an element to use in global search, we usually automatically create a fulltext index for it. But for join elements, where the column needing indexing is on a different table, that becomes somewhat problematic. Again, possible to fix, just one of those things we've never got round to, as it's such a corner case issue.

So if I do get the basic WHERE clause structure fixed, you'll still need to create a fulltext index on that #__users.name column by hand, in something like phpMyAdmin.

-- hugh

 

[Borox]

OK Hugh, Thanks