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 Elaine Li <el...@gmail.com> on 2011/07/13 22:09:24 UTC

deletedPkQuery fails

Hi Folks,

I am trying to use the deletedPkQuery to enable deltaImport to remove
the inactive products from solr.
I am keeping getting the syntax error saying the query syntax is not
right. I have tried many alternatives to the following query. Although
all of them work in the mysql prompt directly, no one works in solr
handler. Can anyone give me some hint to debug this type of problem?
Is there anything special about deletedPkQuery I am not aware of?

deletedPkQuery="select p.pId as id from products p join products_large
pl on p.pId=pl.pId where p.pId= ${dataimporter.delta.id} and
pl.deleted='' having count(*)=0"

Jul 13, 2011 4:02:23 PM
org.apache.solr.handler.dataimport.DataImporter doDeltaImport
SEVERE: Delta Import Failed
org.apache.solr.handler.dataimport.DataImportHandlerException: Unable
to execute query: select p.pId as id from products p join
products_large pl on p.pId=pl.pI
d where p.pId=  and pl.deleted='' having count(*)=0 Processing Document # 1
        at org.apache.solr.handler.dataimport.DataImportHandlerException.wrapAndThrow(DataImportHandlerException.java:72)
        at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(JdbcDataSource.java:253)
        at org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:210)
        at org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:39)
        at org.apache.solr.handler.dataimport.SqlEntityProcessor.initQuery(SqlEntityProcessor.java:58)
        at org.apache.solr.handler.dataimport.SqlEntityProcessor.nextDeletedRowKey(SqlEntityProcessor.java:91)
        at org.apache.solr.handler.dataimport.EntityProcessorWrapper.nextDeletedRowKey(EntityProcessorWrapper.java:258)
        at org.apache.solr.handler.dataimport.DocBuilder.collectDelta(DocBuilder.java:636)
        at org.apache.solr.handler.dataimport.DocBuilder.doDelta(DocBuilder.java:258)
        at org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:172)
        at org.apache.solr.handler.dataimport.DataImporter.doDeltaImport(DataImporter.java:352)
        at org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:391)
        at org.apache.solr.handler.dataimport.DataImporter$1.run(DataImporter.java:370)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL serv
er version for the right syntax to use near 'and pl.deleted='' having
count(*)=0' at line 1
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown
Source)
        at java.lang.reflect.Constructor.newInstance(Unknown Source)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
        at com.mysql.jdbc.Util.getInstance(Util.java:382)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3603)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3535)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1989)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2150)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2570)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:779)
        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:622)
        at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(JdbcDataSource.java:246)

Elaine

Re: deletedPkQuery fails

Posted by Elaine Li <el...@gmail.com>.
I tried putting single quote like following, it does not work.
deletedPkQuery="select p.pId as id from products p join products_large
pl on p.pId=pl.pId where p.pId= '${dataimporter.delta.id}' and
pl.deleted='' having count(*)=0"

I don't have quotes in where p.pId =${dataimporter.delta.id} for my
deltaImportQuery and there is no problem there.
The pId is a INT in my db. Why do i need to put quotes around it? Is this a
special requirement for deletedPkQuery?

Thanks.

Elaine

On Thu, Jul 14, 2011 at 4:53 PM, Shalin Shekhar Mangar <
shalinmangar@gmail.com> wrote:
> On Thu, Jul 14, 2011 at 1:39 AM, Elaine Li <el...@gmail.com>
wrote:
>
>> Hi Folks,
>>
>> I am trying to use the deletedPkQuery to enable deltaImport to remove
>> the inactive products from solr.
>> I am keeping getting the syntax error saying the query syntax is not
>> right. I have tried many alternatives to the following query. Although
>> all of them work in the mysql prompt directly, no one works in solr
>> handler. Can anyone give me some hint to debug this type of problem?
>> Is there anything special about deletedPkQuery I am not aware of?
>>
>> deletedPkQuery="select p.pId as id from products p join products_large
>> pl on p.pId=pl.pId where p.pId= ${dataimporter.delta.id} and
>> pl.deleted='' having count(*)=0"
>>
>
> You need to put the ${dataimporter.delta.id in quotes.
>
> --
> Regards,
> Shalin Shekhar Mangar.
>

Re: deletedPkQuery fails

Posted by Shalin Shekhar Mangar <sh...@gmail.com>.
On Thu, Jul 14, 2011 at 1:39 AM, Elaine Li <el...@gmail.com> wrote:

> Hi Folks,
>
> I am trying to use the deletedPkQuery to enable deltaImport to remove
> the inactive products from solr.
> I am keeping getting the syntax error saying the query syntax is not
> right. I have tried many alternatives to the following query. Although
> all of them work in the mysql prompt directly, no one works in solr
> handler. Can anyone give me some hint to debug this type of problem?
> Is there anything special about deletedPkQuery I am not aware of?
>
> deletedPkQuery="select p.pId as id from products p join products_large
> pl on p.pId=pl.pId where p.pId= ${dataimporter.delta.id} and
> pl.deleted='' having count(*)=0"
>

You need to put the ${dataimporter.delta.id in quotes.

-- 
Regards,
Shalin Shekhar Mangar.

Re: deletedPkQuery fails

Posted by Juan Grande <ju...@gmail.com>.
Hi Elaine, I think you have a syntax error in your query. I'd recommend you
to first try the query using a SQL client, until you get it right.

This part seems strange to me:

and pl.deleted='' having count(*)=0

*Juan*



On Wed, Jul 13, 2011 at 5:09 PM, Elaine Li <el...@gmail.com> wrote:

> Hi Folks,
>
> I am trying to use the deletedPkQuery to enable deltaImport to remove
> the inactive products from solr.
> I am keeping getting the syntax error saying the query syntax is not
> right. I have tried many alternatives to the following query. Although
> all of them work in the mysql prompt directly, no one works in solr
> handler. Can anyone give me some hint to debug this type of problem?
> Is there anything special about deletedPkQuery I am not aware of?
>
> deletedPkQuery="select p.pId as id from products p join products_large
> pl on p.pId=pl.pId where p.pId= ${dataimporter.delta.id} and
> pl.deleted='' having count(*)=0"
>
> Jul 13, 2011 4:02:23 PM
> org.apache.solr.handler.dataimport.DataImporter doDeltaImport
> SEVERE: Delta Import Failed
> org.apache.solr.handler.dataimport.DataImportHandlerException: Unable
> to execute query: select p.pId as id from products p join
> products_large pl on p.pId=pl.pI
> d where p.pId=  and pl.deleted='' having count(*)=0 Processing Document # 1
>        at
> org.apache.solr.handler.dataimport.DataImportHandlerException.wrapAndThrow(DataImportHandlerException.java:72)
>        at
> org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(JdbcDataSource.java:253)
>        at
> org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:210)
>        at
> org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:39)
>        at
> org.apache.solr.handler.dataimport.SqlEntityProcessor.initQuery(SqlEntityProcessor.java:58)
>        at
> org.apache.solr.handler.dataimport.SqlEntityProcessor.nextDeletedRowKey(SqlEntityProcessor.java:91)
>        at
> org.apache.solr.handler.dataimport.EntityProcessorWrapper.nextDeletedRowKey(EntityProcessorWrapper.java:258)
>        at
> org.apache.solr.handler.dataimport.DocBuilder.collectDelta(DocBuilder.java:636)
>        at
> org.apache.solr.handler.dataimport.DocBuilder.doDelta(DocBuilder.java:258)
>        at
> org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:172)
>        at
> org.apache.solr.handler.dataimport.DataImporter.doDeltaImport(DataImporter.java:352)
>        at
> org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:391)
>        at
> org.apache.solr.handler.dataimport.DataImporter$1.run(DataImporter.java:370)
> Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
> You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL serv
> er version for the right syntax to use near 'and pl.deleted='' having
> count(*)=0' at line 1
>        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
> Method)
>        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown
> Source)
>        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown
> Source)
>        at java.lang.reflect.Constructor.newInstance(Unknown Source)
>        at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
>        at com.mysql.jdbc.Util.getInstance(Util.java:382)
>        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
>        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3603)
>        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3535)
>        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1989)
>        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2150)
>        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620)
>        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2570)
>        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:779)
>        at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:622)
>        at
> org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(JdbcDataSource.java:246)
>
> Elaine
>