topic How could I know if a query goes to SOLR or DB? in Alfresco Archive

How could I know if a query goes to SOLR or DB?

spilby — Tue, 19 Jan 2016 16:22:51 GMT

Hi,I'm using Alfresco 4.2.5.1. I configure it to do transactional queries and go to DB first. But I want to confirm that all works ok and my queries are transactional and go to DB. How can I see it? I view solr/report/ page, for example, but I don't understand what parameters indicates if my query g

Re: How could I know if a query goes to SOLR or DB?

afaust — Tue, 19 Jan 2016 16:47:45 GMT

Hello,

you need to alter your logging settings to check if searches really do go to the DB. Specifically you'd want to set

log4j.logger.org.alfresco.repo.search.impl.solr.DbOrIndexSwitchingQueryLanguage=debug

in your log4j.properties. This will log out (alfresco.log) how the search system directs queries and falls back from DB to index if necessary.

Regards
Axel

Re: How could I know if a query goes to SOLR or DB?

spilby — Wed, 20 Jan 2016 12:47:00 GMT

Thanks AFaust!

Then I'm doing something wrong with the queries, I can see it… When I put this on my log, shows what is doing:


2016-01-20 13:15:46,552 DEBUG [org.alfresco.repo.search.impl.solr.DbOrIndexSwitchingQueryLanguage] [http-apr-8090-exec-2] Trying db query for db-afts for SearchParameters [language=fts-alfresco, query=TYPE:"{exp.model}voc_exp" AND =@dFolder\:dNumber:"0801930008" AND @exp\:num_exp:"test new 3", stores=[workspace://SpacesStore], queryParameterDefinitions=[], excludeDataInTheCurrentTransaction=false, sortDefinitions=[], locales=[], mlAnalaysisMode=null, limitBy=UNLIMITED, permissionEvaluation=EAGER, limit=500, allAttributes=[], textAttributes=[], maxItems=2147483647, skipCount=0, defaultFTSOperator=OR, defaultFTSFieldOperator=OR, queryTemplates={}, namespace=http://www.alfresco.org/model/content/1.0, maxPermissionChecks=2147483647, maxPermissionCheckTimeMillis=-1, defaultFieldName=TEXT, fieldFacets=[], useInMemorySort=null, maxRawResultSetSizeForInMemorySort=null, excludeTenantFilter=false, queryConsistency=DEFAULT]

2016-01-20 13:15:46,822 DEBUG [org.alfresco.repo.search.impl.solr.DbOrIndexSwitchingQueryLanguage] [http-apr-8090-exec-2] DB query failed for db-afts for SearchParameters [language=fts-alfresco, query=TYPE:"{exp.model}voc_exp" AND =@dFolder\:dNumber:"0801930008" AND @exp\:num_exp:"test new 3", stores=[workspace://SpacesStore], queryParameterDefinitions=[], excludeDataInTheCurrentTransaction=false, sortDefinitions=[], locales=[], mlAnalaysisMode=null, limitBy=UNLIMITED, permissionEvaluation=EAGER, limit=500, allAttributes=[], textAttributes=[], maxItems=2147483647, skipCount=0, defaultFTSOperator=OR, defaultFTSFieldOperator=OR, queryTemplates={KEY_REPLACELONESTAR=ISNODE:T}, namespace=http://www.alfresco.org/model/content/1.0, maxPermissionChecks=2147483647, maxPermissionCheckTimeMillis=-1, defaultFieldName=TEXT, fieldFacets=[], useInMemorySort=null, maxRawResultSetSizeForInMemorySort=null, excludeTenantFilter=false, queryConsistency=DEFAULT]
org.alfresco.repo.search.impl.querymodel.QueryModelException: 00200001 Analysis mode not supported for DB DEFAULT

2016-01-20 13:15:46,823 DEBUG [org.alfresco.repo.search.impl.solr.DbOrIndexSwitchingQueryLanguage] [http-apr-8090-exec-2] Using SOLR query: db-afts for SearchParameters
‍‍‍‍‍‍‍‍

Seeing this log I understand that first try to do a DB query, but the query is not supported and then try a SOLR query that works ok.

But why my query is not supported by FTS? This is my query:


TYPE:"{exp.model}voc_exp" AND =@exp\:dNumber:"0801930008" AND @exp\:num_exp:"test new 3"
‍‍‍

and in my model these parameters are defined like here:


<namespace uri="exp.model" prefix="exp"/>
<type name="exp:voc_exp">
 <properties>
  <property name="exp:dNumber" tab="-1" editVisible="false" createVisible="false" editable="false" inheritable="false" calculable="false" >
      <title>Code number</title>
      <type>d:text</type>
      <index enabled="true">
          <atomic>false</atomic>
          <stored>false</stored> 
          <tokenised>false</tokenised>
      </index>
  </property>

<property name="exp:num_exp" editVisible="true" createVisible="true" editable="true" inheritable="false" calculable="true">
    <title>Expedient number</title>
    <type>d:text</type>
    <mandatory>true</mandatory>
    <index enabled="true">
        <atomic>false</atomic>
        <stored>false</stored> 
        <tokenised>both</tokenised>
    </index>
    <constraints>
        <constraint ref="desal:stringLength100" />
    </constraints>
</property>   
</properties>
</type>
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Both are d:text indexed values, and the type is defined ok. I'm not using PATH or similars. What is not supported? I need to do a DB search (not SOLR), and I'm using Alfresco 4.2.5.1. And I add on my alfresco-global.properties this:


########################
## Force transactional #
########################

solr.query.cmis.queryConsistency=TRANSACTIONAL_IF_POSSIBLE
solr.query.fts.queryConsistency=TRANSACTIONAL_IF_POSSIBLE

### Optional patch that adds required indexes to the DB ###
system.metadata-query-indexes.ignored=false
‍‍‍‍‍‍‍‍‍‍‍

Thanks in advance

Re: How could I know if a query goes to SOLR or DB?

spilby — Thu, 21 Jan 2016 08:12:00 GMT

A comment about this… the "@" symbol is not supported by direct DB FTS queries?? Maybe is the problem? I read on wiki that is optional, not no supported.

Anyway, I try to delete this symbol, and the "\" to escape. Now my query is:

TYPE:"{exp.model}voc_exp" AND =exp:dNumber:"0801930008" AND exp:num_exp:"test new 3"

But the problem is the same: "Analysis mode not supported for DB DEFAULT"

What's the problem on this query? How will be the correct supported query?

Re: How could I know if a query goes to SOLR or DB?

afaust — Thu, 21 Jan 2016 09:42:09 GMT

Hello,

the problem is not the @ character, but that you have not prefixed the third fragment searching for exp:num_exp with a =
The DB AFTS only supports exact text property matches, so you must always use =exp:num_exp:"test new 3" or =@exp:num_exp:"test new 3"

Regards
Axel

Re: How could I know if a query goes to SOLR or DB?

spilby — Thu, 21 Jan 2016 16:08:48 GMT

Perfect! I try putting = before all properties to search and goes to BD without error of syntax.

The problem now is… I can't put "=" because I need to do a case insensitive search, in order to find "test", "TEST", "Test", etc.

When I add the "=" the search being exact and case sensitive. How can I do this search to DB but with a case insensitive search?

Re: How could I know if a query goes to SOLR or DB?

afaust — Thu, 21 Jan 2016 17:25:56 GMT

Hello,

you can't perform case insensitive searches via DB AFTS. It is just that way…

Regards
Axel

Re: How could I know if a query goes to SOLR or DB?

spilby — Fri, 22 Jan 2016 13:36:00 GMT

Oh… Well… I try to think another solutions. Maybe is possible to configure the DB to perform the case insensitive. Or at least, update all the properties that I need to search and do an UpperCase, in order to store all in Alfresco in UpperCase, for example. I thank that search to DB with insensitive searches was a common request and exists a method/query to do it. What a shame… Anyway, thanks for your information Axel.