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 <elaine.bing...@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
>
