You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by abhijit bashetti <ba...@rediffmail.com> on 2015/06/10 14:43:24 UTC
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.
I have checked out the solr code from svn... trying it by adding logs to it...but the logs are not reflected and i m not able to move it.
I am not very sure very it is wrong ...but just a wild guess that something is wrong at variableResolverImpl.replaceTokens or at the TemplateString.fillTokens...
I will keep on it but if you know/get a chance to look at it, it would be of great help from your end..
Regards,Abhijit
Re: Solr date variable resolver is not working with MySql
Posted by Alexandre Rafalovitch <ar...@gmail.com>.
Some reason, you email is complete unreadable with a lot of nbsp
instead of spaces. Maybe it is trying to send as broken HTML?
You may want to try to reformat the message and resend.
Regards,
Alex.
----
Solr Analyzers, Tokenizers, Filters, URPs and even a newsletter:
http://www.solr-start.com/
On 10 June 2015 at 22:43, abhijit bashetti
<ba...@rediffmail.com> wrote:
>
> 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.
> I have checked out the solr code from svn... trying it by adding logs to it...but the logs are not reflected and i m not able to move it.
> I am not very sure very it is wrong ...but just a wild guess that something is wrong at variableResolverImpl.replaceTokens or at the TemplateString.fillTokens...
> I will keep on it but if you know/get a chance to look at it, it would be of great help from your end..
> Regards,Abhijit
Re: Solr date variable resolver is not working with MySql
Posted by Shawn Heisey <ap...@elyograg.org>.
On 6/10/2015 6:43 AM, abhijit bashetti wrote:
<snip>
> >= to_date('[?, '28/05/2015 11:13:50']', 'DD/MM/YYYY HH24:MI:SS')
<snip>
> 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.
> I have checked out the solr code from svn... trying it by adding logs to it...but the logs are not reflected and i m not able to move it.
> I am not very sure very it is wrong ...but just a wild guess that something is wrong at variableResolverImpl.replaceTokens or at the TemplateString.fillTokens...
> I will keep on it but if you know/get a chance to look at it, it would be of great help from your end..
You should not need to use "to_date" with MySQL. Do you know whether
MySQL will accept DD/MM/YYYY for dates on your system? If it will, you
should be able to use this directly in your query:
doc.index_state_modification_date >= '${dih.request.lastIndexDate}'
If I were you, I would use YYYY/MM/DD for dates, just to be sure that
locale settings won't cause it to be misinterpreted.
Thanks,
Shawn