When attempting to run a delta-import, I'm getting an error stating that the
query can't be executed. The query works fine when executed at a psql prompt.
I've included the contents of my data-config.xml (with hostname, username, and
password details changed) and the error messages that I see in solr.log below.
If there's anything else I should include (schema.xml, solrconfig.xml, etc),
let me know and I'll be more than happy to send them along.
(Note: I only wrote the deltaQuery and deltaImportQuery - the rest of
data-config.xml is someone else's work.)
data-config.xml:
<?xml version="1.0" encoding="UTF-8"?>
<dataConfig>
<dataSource
name="ds-1"
driver="org.postgresql.Driver"
user="myuser"
password="strongpassword"
type="JdbcDataSource"
url="jdbc:postgresql://777.555.444.333:5432/ttd"/>
<document name="ttd">
<entity name="destination" dataSource="ds-1" pk="destID"
query="SELECT
dd.id || COALESCE(ddt.destination_type, '') || COALESCE(df.feature, '') ||
COALESCE(dc.cuisine, '') as destID,
dd.id as did,
dd.name as dName,
dd.web_description as dDescription,
dd.editors_pick as dPick,
dd.open_table_id as onLineReservation,
dd.critics_rating as dStar,
ddt.destination_type as category,
df.feature as feature,
dc.cuisine as cuisine,
tad.neighborhood_name as neighborhood,
tad.latitude as latitude,
tad.longitude as longitude,
tad.priority as priority,
tad.neighborhood_slug as neighborhoodSlug,
pc.city as city,
pz.zip_code as zipCode,
pp.street_address as street,
pcty.county as county,
ps.state as state
FROM public.destinations_destination dd
LEFT JOIN public.destinations_destination_destination_type dddt ON dd.id =
dddt.destination_id
LEFT JOIN public.destinations_destinationtype ddt ON
dddt.destinationtype_id = ddt.id
LEFT JOIN public.destinations_destination_features ddf ON dd.id =
ddf.destination_id
LEFT JOIN public.destinations_feature df ON ddf.feature_id = df.id
LEFT JOIN public.destinations_destination_cuisine_types ddct ON dd.id =
ddct.destination_id
LEFT JOIN public.destinations_cuisine dc ON ddct.cuisine_id = dc.id
LEFT JOIN public.ttd_api_destination tad ON dd.id = tad.api_id
LEFT JOIN public.places_place pp ON dd.place_id = pp.id
LEFT JOIN public.places_zipcode pz ON pp.zip_code_id = pz.id
LEFT JOIN public.places_city pc ON pp.city_id =pc.id
LEFT JOIN public.places_county pcty ON pc.county_id = pcty.id
LEFT JOIN public.places_state ps ON pcty.state_id = ps.id;"
deltaQuery="SELECT * into TEMPORARY tempttdDest FROM (SELECT
dd.id || COALESCE(ddt.destination_type, '') || COALESCE(df.feature, '') ||
COALESCE(dc.cuisine, '') as destID,
dd.id as did,
dd.created as dcreated,
dd.updated as dupdated,
dd.name as dName,
dd.web_description as dDescription,
dd.editors_pick as dPick,
dd.open_table_id as onLineReservation,
dd.critics_rating as dStar,
ddt.destination_type as category,
df.feature as feature,
dc.cuisine as cuisine,
tad.neighborhood_name as neighborhood,
tad.latitude as latitude,
tad.longitude as longitude,
tad.priority as priority,
tad.neighborhood_slug as neighborhoodSlug,
pc.city as city,
pz.zip_code as zipCode,
pp.street_address as street,
pcty.county as county,
ps.state as state
FROM public.destinations_destination dd
LEFT JOIN public.destinations_destination_destination_type dddt ON dd.id =
dddt.destination_id
LEFT JOIN public.destinations_destinationtype ddt ON
dddt.destinationtype_id = ddt.id
LEFT JOIN public.destinations_destination_features ddf ON dd.id =
ddf.destination_id
LEFT JOIN public.destinations_feature df ON ddf.feature_id = df.id
LEFT JOIN public.destinations_destination_cuisine_types ddct ON dd.id =
ddct.destination_id
LEFT JOIN public.destinations_cuisine dc ON ddct.cuisine_id = dc.id
LEFT JOIN public.ttd_api_destination tad ON dd.id = tad.api_id
LEFT JOIN public.places_place pp ON dd.place_id = pp.id
LEFT JOIN public.places_zipcode pz ON pp.zip_code_id = pz.id
LEFT JOIN public.places_city pc ON pp.city_id =pc.id
LEFT JOIN public.places_county pcty ON pc.county_id = pcty.id
LEFT JOIN public.places_state ps ON pcty.state_id = ps.id) AS
coalesced_all;
SELECT destID from tempttdDest WHERE dcreated >
'${dih.last_index_time}.00000-00' OR dupdated >
'${dih.last_index_time}.00000-00';"
deltaImportQuery="SELECT
destID,
did,
dName,
dDescription,
dPick,
onLineReservation,
dStar,
category,
feature,
cuisine,
neighborhood,
latitude,
longitude,
priority,
neighborhoodSlug,
city,
zipCode,
street,
county,
state
FROM tempttdDest
WHERE destID = '${dih.delta.id}';" >
<field column="destID" name="destID"/>
<field column="did" name="did"/>
<field column="dName" name="dName"/>
<field column="dDescription" name="dDescription"/>
<field column="dPick" name="dPick"/>
<field column="onLineReservation" name="onLineReservation"/>
<field column="dStar" name="dStar"/>
<field column="neighborhood" name="neighborhood"/>
<field column="latitude" name="latitude"/>
<field column="longitude" name="longitude"/>
<field column="priority" name="priority"/>
<field column="neighborhoodSlug" name="neighborhoodSlug"/>
<field column="seouri" name="seouri"/>
<field column="active" name="active"/>
<field column="category" name="category"/>
<field column="feature" name="feature"/>
<field column="cuisine" name="cuisine"/>
<field column="startTime" name="startTime"/>
<field column="city" name="city"/>
<field column="street" name="street"/>
<field column="zipCode" name="zipCode"/>
<field column="county" name="county"/>
<field column="state" name="state"/>
</entity>
</document>
</dataConfig>
And the errors I see in solr.log when attempting to run the delta-import:
ERROR - 2015-04-13 19:09:33.389;
org.apache.solr.handler.dataimport.DataImporter; Delta Import Failed
java.lang.RuntimeException:
org.apache.solr.handler.dataimport.DataImportHandlerException: Unable to
execute query: SELECT * into TEMPORARY tempttdDest FROM (SELECT dd.id ||
COALESCE(ddt.destination_type, '') || COALESCE(df.feature, '') ||
COALESCE(dc.cuisine, '') as destID, dd.id as did, dd.created as
dcreated, dd.updated as dupdated, dd.name as dName,
dd.web_description as dDescription, dd.editors_pick as dPick,
dd.open_table_id as onLineReservation, dd.critics_rating as dStar,
ddt.destination_type as category, df.feature as feature, dc.cuisine
as cuisine, tad.neighborhood_name as neighborhood, tad.latitude as
latitude, tad.longitude as longitude, tad.priority as priority,
tad.neighborhood_slug as neighborhoodSlug, pc.city as city,
pz.zip_code as zipCode, pp.street_address as street, pcty.county as
county, ps.state as state FROM public.destinations_destination dd
LEFT JOIN public.destinations_destination_destination_type dddt ON dd.id =
dddt.destination_id LEFT JOIN public.destinations_destinationtype ddt ON
dddt.destinationtype_id = ddt.id LEFT JOIN
public.destinations_destination_features ddf ON dd.id = ddf.destination_id
LEFT JOIN public.destinations_feature df ON ddf.feature_id = df.id LEFT
JOIN public.destinations_destination_cuisine_types ddct ON dd.id =
ddct.destination_id LEFT JOIN public.destinations_cuisine dc ON
ddct.cuisine_id = dc.id LEFT JOIN public.ttd_api_destination tad ON dd.id
= tad.api_id LEFT JOIN public.places_place pp ON dd.place_id = pp.id
LEFT JOIN public.places_zipcode pz ON pp.zip_code_id = pz.id LEFT JOIN
public.places_city pc ON pp.city_id =pc.id LEFT JOIN public.places_county
pcty ON pc.county_id = pcty.id LEFT JOIN public.places_state ps ON
pcty.state_id = ps.id) AS coalesced_all; SELECT destID from tempttdDest
WHERE dcreated > '2015-04-10 17:57:04.00000-00' OR dupdated > '2015-04-10
17:57:04.00000-00'; Processing Document # 1
at
org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:270)
at
org.apache.solr.handler.dataimport.DataImporter.doDeltaImport(DataImporter.java:440)
at
org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:478)
at
org.apache.solr.handler.dataimport.DataImportHandler.handleRequestBody(DataImportHandler.java:179)
at
org.apache.solr.handler.RequestHandlerBase.handleRequest(RequestHandlerBase.java:135)
at org.apache.solr.core.SolrCore.execute(SolrCore.java:1904)
at
org.apache.solr.servlet.SolrDispatchFilter.execute(SolrDispatchFilter.java:659)
at
org.apache.solr.servlet.SolrDispatchFilter.doFilter(SolrDispatchFilter.java:362)
at
org.apache.solr.servlet.SolrDispatchFilter.doFilter(SolrDispatchFilter.java:158)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at
org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)
at
org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
at
org.apache.tomcat.util.net.AprEndpoint$SocketWithOptionsProcessor.run(AprEndpoint.java:2379)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at
org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.apache.solr.handler.dataimport.DataImportHandlerException:
Unable to execute query: SELECT * into TEMPORARY tempttdDest FROM (SELECT
dd.id || COALESCE(ddt.destination_type, '') || COALESCE(df.feature, '') ||
COALESCE(dc.cuisine, '') as destID, dd.id as did, dd.created as
dcreated, dd.updated as dupdated, dd.name as dName,
dd.web_description as dDescription, dd.editors_pick as dPick,
dd.open_table_id as onLineReservation, dd.critics_rating as dStar,
ddt.destination_type as category, df.feature as feature, dc.cuisine
as cuisine, tad.neighborhood_name as neighborhood, tad.latitude as
latitude, tad.longitude as longitude, tad.priority as priority,
tad.neighborhood_slug as neighborhoodSlug, pc.city as city,
pz.zip_code as zipCode, pp.street_address as street, pcty.county as
county, ps.state as state FROM public.destinations_destination dd
LEFT JOIN public.destinations_destination_destination_type dddt ON dd.id =
dddt.destination_id LEFT JOIN public.destinations_destinationtype ddt ON
dddt.destinationtype_id = ddt.id LEFT JOIN
public.destinations_destination_features ddf ON dd.id = ddf.destination_id
LEFT JOIN public.destinations_feature df ON ddf.feature_id = df.id LEFT
JOIN public.destinations_destination_cuisine_types ddct ON dd.id =
ddct.destination_id LEFT JOIN public.destinations_cuisine dc ON
ddct.cuisine_id = dc.id LEFT JOIN public.ttd_api_destination tad ON dd.id
= tad.api_id LEFT JOIN public.places_place pp ON dd.place_id = pp.id
LEFT JOIN public.places_zipcode pz ON pp.zip_code_id = pz.id LEFT JOIN
public.places_city pc ON pp.city_id =pc.id LEFT JOIN public.places_county
pcty ON pc.county_id = pcty.id LEFT JOIN public.places_state ps ON
pcty.state_id = ps.id) AS coalesced_all; SELECT destID from tempttdDest
WHERE dcreated > '2015-04-10 17:57:04.00000-00' OR dupdated > '2015-04-10
17:57:04.00000-00'; Processing Document # 1
at
org.apache.solr.handler.dataimport.DataImportHandlerException.wrapAndThrow(DataImportHandlerException.java:71)
at
org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(JdbcDataSource.java:279)
at
org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:236)
at
org.apache.solr.handler.dataimport.JdbcDataSource.getData(JdbcDataSource.java:40)
at
org.apache.solr.handler.dataimport.SqlEntityProcessor.initQuery(SqlEntityProcessor.java:59)
at
org.apache.solr.handler.dataimport.SqlEntityProcessor.nextModifiedRowKey(SqlEntityProcessor.java:84)
at
org.apache.solr.handler.dataimport.EntityProcessorWrapper.nextModifiedRowKey(EntityProcessorWrapper.java:267)
at
org.apache.solr.handler.dataimport.DocBuilder.collectDelta(DocBuilder.java:781)
at
org.apache.solr.handler.dataimport.DocBuilder.doDelta(DocBuilder.java:338)
at
org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:223)
... 24 more
Caused by: java.lang.NullPointerException
at
org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.<init>(JdbcDataSource.java:277)
... 32 more
Andrew Gilbertson
