Search code examples
mysqloraclesolrdataimporthandler

Solr date variable resolver is not working with MySql

I have used Solr 3.3 version as Data Import Handler(DIH) with Oracle. Its working fine for me.

Now I am trying the same with Mysql. With the change in database, I have changed the query used in data-config.xml for MySql.

The query has variables which are passed url in http. The same thing works fine in Oracle with variable resolver but not in MySql.

The query is :

SELECT DISTINCT doc.document_id ,
  doc.first_version_id,
  doc.acl_id,                    
  fol.folder_id                 
FROM ds_document_c doc,
  ds_folder fol
WHERE doc.cabinet_id = ${dataimporter.request.cabinetId}
  AND fol.folder_id = doc.document_folder_id
  AND doc.index_state_modification_date >= to_date('${dataimporter.request.lastIndexDate}', 'DD/MM/YYYY HH24:MI:SS')

and the Url is : 

localhost:8983/solr/dataimport?command=full-import&clean=true&commit=true&cabinetId=17083360&lastIndexDate='24/05/2015 00:00:00'

Solr is building the query as below : 

SELECT DISTINCT doc.document_id ,
  doc.first_version_id,
  doc.acl_id,                    
  fol.folder_id                 
FROM ds_document_c doc,
  ds_folder fol
WHERE doc.cabinet_id = 24
AND fol.folder_id = doc.document_folder_id
AND doc.index_state_modification_date >= to_date('[?, '28/05/2015 11:13:50']', 'DD/MM/YYYY HH24:MI:SS')

I am not able to figure it our why the date variable is not resloved properly in this case.

Because of to_date('[?, '28/05/2015 11:13:50']' is not in a proper MySql syntax, I am getting MySql Syntax error.

I get the following error 

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 '[?, '28/05/2015 11:13:50'], 'DD/MM/YYYY HH24:MI:SS')))' at line 1

Anyone knows where is the problem? Why is the variable resolver not working as expected?

Note : to_date is function written by us in MySql.

Solution

  • I checked out the source code for solr and tried to solve my issue.

    I had a fix for it and its working for me.

    The variable resolve in case of date is somehow making a array and so it appends the 

    '[?, '28/05/2015 11:13:50']'.

    In the TemplateString.java in method fillTokens(VariableResolver resolver) I have added a code which removes the extra part added to the date.

    if (i < s.length) {
   if(s[i].startsWith("[")){
      String temp = s[i].replace("[?,", "");
      temp = temp.replace("]", "");
      sb.append(temp);
   }else{
      sb.append(s[i]);
   }
}

    With this change, variable resolver appends the date as '28/05/2015 11:13:50' and removed my MySql syntax error.

    (Note : I didn't had much time to analyse why date variable resolved as an array. I have done a temporary fix and which solved my issue.)