Guys,

I am facing a situation in one of our projects that I need to perform a
cleanup to remove some documents after we perform an update via DIH.
The big issue right now comes from the fact that when we call the DIH with
clean=false, the postImportDeleteQuery is not executed.

My setup is currently arranged like this:
- A SQL Server stored procedure that receives a parameter (specified in the
URL) and returns the records to be indexed
- The procedure is able to return all the records (for a full-import) or
only the updated records (for a delta-import)
- This procedure returns valid and deleted records, from this point comes
the need to run a postImportDeleteQuery to remove the deleted ones.

Everything works fine when I run a full-import, I am running always with
clean=true, and then the whole index is rebuilt.
When I need to do an incremental update, the records are updated correctly,
but the command to delete the other records is not executed.

I've tried several combinations, with different results:
- Running full-import with clean=false: the records are updated but the ones
that needs to be deleted stays on the index
- Running delta-import with clean=false: the records are updated but the
ones that needs to be deleted stays on the index
- Running delta-import with clean=true: all records are deleted from the
index and then only the records returned by the procedure are on the index,
except the deleted ones.

I don't see any way to achieve my goal, without changing the process that I
do to obtain the data.
Since this is a very complex stored procedure, with tons of joins and custom
processing, I am trying everything to avoid messing with it.

See below a copy of my data-config.xml file. I made it simpler omitting all
the fields, since it's out of scope of the issue:
<?xml version="1.0" encoding="UTF-8" ?>
<dataConfig>
<dataSource type="JdbcDataSource"
driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://myserver;databaseName=mydb;user=username;password=password;responseBuffering=adaptive;"

/>
<document>
<entity name="entity_one"
pk="entityid"
transformer="RegexTransformer"
query="EXEC some_stored_procedure ${dataimporter.request.someid}"
preImportDeleteQuery="status:1" postImportDeleteQuery="status:1"
>
<field column="field1" name="field1" splitBy=";" />
<field column="field2" name="field2" splitBy=";" />
<field column="field3" name="field3" splitBy=";" />
</entity>

<entity name="entity_two"
pk="entityid"
transformer="RegexTransformer"
query="EXEC someother_stored_procedure ${dataimporter.request.someotherid}"
preImportDeleteQuery="status:1" postImportDeleteQuery="status:1"
>
<field column="field1" name="field1" />
<field column="field2" name="field2" />
<field column="field3" name="field2" />
</entity>
</document>
</dataConfig>

Any ideas or pointers that might help on this one?

Many thanks,
Alexandre

Search the list for my post "DIH - deleting documents, high performance
(delta) imports, and passing parameters" which shows my solution a
similar problem.

Ephraim Ofir

-----Original Message-----
From: Alexandre Rocco [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 24, 2011 11:24 PM
To: [EMAIL PROTECTED]
Subject: DIH import and postImportDeleteQuery

Guys,

I am facing a situation in one of our projects that I need to perform a
cleanup to remove some documents after we perform an update via DIH.
The big issue right now comes from the fact that when we call the DIH
with
clean=false, the postImportDeleteQuery is not executed.

My setup is currently arranged like this:
- A SQL Server stored procedure that receives a parameter (specified in
the
URL) and returns the records to be indexed
- The procedure is able to return all the records (for a full-import) or
only the updated records (for a delta-import)
- This procedure returns valid and deleted records, from this point
comes
the need to run a postImportDeleteQuery to remove the deleted ones.

Everything works fine when I run a full-import, I am running always with
clean=true, and then the whole index is rebuilt.
When I need to do an incremental update, the records are updated
correctly,
but the command to delete the other records is not executed.

I've tried several combinations, with different results:
- Running full-import with clean=false: the records are updated but the
ones
that needs to be deleted stays on the index
- Running delta-import with clean=false: the records are updated but the
ones that needs to be deleted stays on the index
- Running delta-import with clean=true: all records are deleted from the
index and then only the records returned by the procedure are on the
index,
except the deleted ones.

I don't see any way to achieve my goal, without changing the process
that I
do to obtain the data.
Since this is a very complex stored procedure, with tons of joins and
custom
processing, I am trying everything to avoid messing with it.

See below a copy of my data-config.xml file. I made it simpler omitting
all
the fields, since it's out of scope of the issue:
<?xml version="1.0" encoding="UTF-8" ?>
<dataConfig>
<dataSource type="JdbcDataSource"
driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://myserver;databaseName=mydb;user=username;passwordpassword;responseBuffering=adaptive;"

/>
<document>
<entity name="entity_one"
pk="entityid"
transformer="RegexTransformer"
query="EXEC some_stored_procedure ${dataimporter.request.someid}"
preImportDeleteQuery="status:1" postImportDeleteQuery="status:1"
>
<field column="field1" name="field1" splitBy=";" />
<field column="field2" name="field2" splitBy=";" />
<field column="field3" name="field3" splitBy=";" />
</entity>

<entity name="entity_two"
pk="entityid"
transformer="RegexTransformer"
query="EXEC someother_stored_procedure
${dataimporter.request.someotherid}"
preImportDeleteQuery="status:1" postImportDeleteQuery="status:1"
>
<field column="field1" name="field1" />
<field column="field2" name="field2" />
<field column="field3" name="field2" />
</entity>
</document>
</dataConfig>

Any ideas or pointers that might help on this one?

Many thanks,
Alexandre

Hi Ephraim,

Thank you so much for the input.
I was able to find your thread on the archives and got your solution to
work.

In fact, when using $deleteDocById and $skipDoc it worked like a charm. This
feature is very useful, it's a shame it's not properly documented.
The only downside is the one you mentioned that the stats are not updated,
so if I update 13 documents and delete 2, DIH would tell me that only 13
documents were processed. This is bad in my case because I check the end
result to generate an error e-mail if needed.

You also mentioned that if the query contains only deletion records, a
commit would not be automatically executed and it would be necessary to
commit manually.

How can I commit manually via DIH? I was not able to find any references on
the documentation.

Thanks!
Alexandre

On Wed, May 25, 2011 at 5:14 AM, Ephraim Ofir <[EMAIL PROTECTED]> wrote:

> Search the list for my post "DIH - deleting documents, high performance
> (delta) imports, and passing parameters" which shows my solution a
> similar problem.
>
> Ephraim Ofir
>
> -----Original Message-----
> From: Alexandre Rocco [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, May 24, 2011 11:24 PM
> To: [EMAIL PROTECTED]
> Subject: DIH import and postImportDeleteQuery
>
> Guys,
>
> I am facing a situation in one of our projects that I need to perform a
> cleanup to remove some documents after we perform an update via DIH.
> The big issue right now comes from the fact that when we call the DIH
> with
> clean=false, the postImportDeleteQuery is not executed.
>
> My setup is currently arranged like this:
> - A SQL Server stored procedure that receives a parameter (specified in
> the
> URL) and returns the records to be indexed
> - The procedure is able to return all the records (for a full-import) or
> only the updated records (for a delta-import)
> - This procedure returns valid and deleted records, from this point
> comes
> the need to run a postImportDeleteQuery to remove the deleted ones.
>
> Everything works fine when I run a full-import, I am running always with
> clean=true, and then the whole index is rebuilt.
> When I need to do an incremental update, the records are updated
> correctly,
> but the command to delete the other records is not executed.
>
> I've tried several combinations, with different results:
> - Running full-import with clean=false: the records are updated but the
> ones
> that needs to be deleted stays on the index
> - Running delta-import with clean=false: the records are updated but the
> ones that needs to be deleted stays on the index
> - Running delta-import with clean=true: all records are deleted from the
> index and then only the records returned by the procedure are on the
> index,
> except the deleted ones.
>
> I don't see any way to achieve my goal, without changing the process
> that I
> do to obtain the data.
> Since this is a very complex stored procedure, with tons of joins and
> custom
> processing, I am trying everything to avoid messing with it.
>
> See below a copy of my data-config.xml file. I made it simpler omitting
> all
> the fields, since it's out of scope of the issue:
> <?xml version="1.0" encoding="UTF-8" ?>
> <dataConfig>
> <dataSource type="JdbcDataSource"
> driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
> url="jdbc:sqlserver://myserver;databaseName=mydb;user=username;password> password;responseBuffering=adaptive;"
>
> />
> <document>
> <entity name="entity_one"
> pk="entityid"
> transformer="RegexTransformer"
> query="EXEC some_stored_procedure ${dataimporter.request.someid}"
> preImportDeleteQuery="status:1" postImportDeleteQuery="status:1"
> >
> <field column="field1" name="field1" splitBy=";" />
> <field column="field2" name="field2" splitBy=";" />
> <field column="field3" name="field3" splitBy=";" />
> </entity>
>
> <entity name="entity_two"
> pk="entityid"
> transformer="RegexTransformer"
> query="EXEC someother_stored_procedure
> ${dataimporter.request.someotherid}"
> preImportDeleteQuery="status:1" postImportDeleteQuery="status:1"

The "failure to commit" bug with $deleteDocById can be fixed by applying patch SOLR-2492.  This patch also partially fixes the "no updated stats" bug in that it increments 1 for every call to $deleteDocById and $deleteDocByQuery.  Note that this might result in inaccurate counts if the id given with $deleteDocById doesn't exist or is duplicated.  Obviously this is not a complete fix for stats using $deleteDocByQuery as this command would normally be used to delete >1 doc at a time.

The patch is for Trunk but it might work with 3.1 also.  If not, it likely only needs minor tweaking.  

The jira ticket is here:  https://issues.apache.org/jira/browse/SOLR-2492

James Dyer
E-Commerce Systems
Ingram Content Group
(615) 213-4311
-----Original Message-----
From: Alexandre Rocco [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 25, 2011 12:54 PM
To: [EMAIL PROTECTED]
Subject: Re: DIH import and postImportDeleteQuery

Hi Ephraim,

Thank you so much for the input.
I was able to find your thread on the archives and got your solution to
work.

In fact, when using $deleteDocById and $skipDoc it worked like a charm. This
feature is very useful, it's a shame it's not properly documented.
The only downside is the one you mentioned that the stats are not updated,
so if I update 13 documents and delete 2, DIH would tell me that only 13
documents were processed. This is bad in my case because I check the end
result to generate an error e-mail if needed.

You also mentioned that if the query contains only deletion records, a
commit would not be automatically executed and it would be necessary to
commit manually.

How can I commit manually via DIH? I was not able to find any references on
the documentation.

Thanks!
Alexandre

On Wed, May 25, 2011 at 5:14 AM, Ephraim Ofir <[EMAIL PROTECTED]> wrote:

> Search the list for my post "DIH - deleting documents, high performance
> (delta) imports, and passing parameters" which shows my solution a
> similar problem.
>
> Ephraim Ofir
>
> -----Original Message-----
> From: Alexandre Rocco [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, May 24, 2011 11:24 PM
> To: [EMAIL PROTECTED]
> Subject: DIH import and postImportDeleteQuery
>
> Guys,
>
> I am facing a situation in one of our projects that I need to perform a
> cleanup to remove some documents after we perform an update via DIH.
> The big issue right now comes from the fact that when we call the DIH
> with
> clean=false, the postImportDeleteQuery is not executed.
>
> My setup is currently arranged like this:
> - A SQL Server stored procedure that receives a parameter (specified in
> the
> URL) and returns the records to be indexed
> - The procedure is able to return all the records (for a full-import) or
> only the updated records (for a delta-import)
> - This procedure returns valid and deleted records, from this point
> comes
> the need to run a postImportDeleteQuery to remove the deleted ones.
>
> Everything works fine when I run a full-import, I am running always with
> clean=true, and then the whole index is rebuilt.
> When I need to do an incremental update, the records are updated
> correctly,
> but the command to delete the other records is not executed.
>
> I've tried several combinations, with different results:
> - Running full-import with clean=false: the records are updated but the
> ones
> that needs to be deleted stays on the index
> - Running delta-import with clean=false: the records are updated but the
> ones that needs to be deleted stays on the index
> - Running delta-import with clean=true: all records are deleted from the
> index and then only the records returned by the procedure are on the
> index,
> except the deleted ones.
>
> I don't see any way to achieve my goal, without changing the process
> that I
> do to obtain the data.
> Since this is a very complex stored procedure, with tons of joins and
> custom
> processing, I am trying everything to avoid messing with it.
>
> See below a copy of my data-config.xml file. I made it simpler omitting

Hi James,

Thanks for the heads up!
I am currently on version 1.4.1, so I can apply this patch and see if it
works.
Just need to assess if it's best to apply the patch or to check on the
backend system to see if only delete requests were generated and then do not
call DIH.

Previously, I found another open issue, created from Ephraim:
https://issues.apache.org/jira/browse/SOLR-2104

It's the same issue, but it hasn't had any updates yet.

Regards,
Alexandre

On Wed, May 25, 2011 at 3:17 PM, Dyer, James <[EMAIL PROTECTED]>wrote:

> The "failure to commit" bug with $deleteDocById can be fixed by applying
> patch SOLR-2492.  This patch also partially fixes the "no updated stats" bug
> in that it increments 1 for every call to $deleteDocById and
> $deleteDocByQuery.  Note that this might result in inaccurate counts if the
> id given with $deleteDocById doesn't exist or is duplicated.  Obviously this
> is not a complete fix for stats using $deleteDocByQuery as this command
> would normally be used to delete >1 doc at a time.
>
> The patch is for Trunk but it might work with 3.1 also.  If not, it likely
> only needs minor tweaking.
>
> The jira ticket is here:  https://issues.apache.org/jira/browse/SOLR-2492
>
> James Dyer
> E-Commerce Systems
> Ingram Content Group
> (615) 213-4311
>
>
> -----Original Message-----
> From: Alexandre Rocco [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, May 25, 2011 12:54 PM
> To: [EMAIL PROTECTED]
> Subject: Re: DIH import and postImportDeleteQuery
>
> Hi Ephraim,
>
> Thank you so much for the input.
> I was able to find your thread on the archives and got your solution to
> work.
>
> In fact, when using $deleteDocById and $skipDoc it worked like a charm.
> This
> feature is very useful, it's a shame it's not properly documented.
> The only downside is the one you mentioned that the stats are not updated,
> so if I update 13 documents and delete 2, DIH would tell me that only 13
> documents were processed. This is bad in my case because I check the end
> result to generate an error e-mail if needed.
>
> You also mentioned that if the query contains only deletion records, a
> commit would not be automatically executed and it would be necessary to
> commit manually.
>
> How can I commit manually via DIH? I was not able to find any references on
> the documentation.
>
> Thanks!
> Alexandre
>
> On Wed, May 25, 2011 at 5:14 AM, Ephraim Ofir <[EMAIL PROTECTED]> wrote:
>
> > Search the list for my post "DIH - deleting documents, high performance
> > (delta) imports, and passing parameters" which shows my solution a
> > similar problem.
> >
> > Ephraim Ofir
> >
> > -----Original Message-----
> > From: Alexandre Rocco [mailto:[EMAIL PROTECTED]]
> > Sent: Tuesday, May 24, 2011 11:24 PM
> > To: [EMAIL PROTECTED]
> > Subject: DIH import and postImportDeleteQuery
> >
> > Guys,
> >
> > I am facing a situation in one of our projects that I need to perform a
> > cleanup to remove some documents after we perform an update via DIH.
> > The big issue right now comes from the fact that when we call the DIH
> > with
> > clean=false, the postImportDeleteQuery is not executed.
> >
> > My setup is currently arranged like this:
> > - A SQL Server stored procedure that receives a parameter (specified in
> > the
> > URL) and returns the records to be indexed
> > - The procedure is able to return all the records (for a full-import) or
> > only the updated records (for a delta-import)
> > - This procedure returns valid and deleted records, from this point
> > comes
> > the need to run a postImportDeleteQuery to remove the deleted ones.
> >
> > Everything works fine when I run a full-import, I am running always with
> > clean=true, and then the whole index is rebuilt.
> > When I need to do an incremental update, the records are updated
> > correctly,
> > but the command to delete the other records is not executed.
> >
> > I've tried several combinations, with different results:
> > - Running full-import with clean=false: the records are updated but the

Great.  I wasn't aware of the other issue.  I put a link on the 2 issues in JIRA so people can know in the future.

James Dyer
E-Commerce Systems
Ingram Content Group
(615) 213-4311
-----Original Message-----
From: Alexandre Rocco [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 25, 2011 2:34 PM
To: [EMAIL PROTECTED]
Subject: Re: DIH import and postImportDeleteQuery

Hi James,

Thanks for the heads up!
I am currently on version 1.4.1, so I can apply this patch and see if it
works.
Just need to assess if it's best to apply the patch or to check on the
backend system to see if only delete requests were generated and then do not
call DIH.

Previously, I found another open issue, created from Ephraim:
https://issues.apache.org/jira/browse/SOLR-2104

It's the same issue, but it hasn't had any updates yet.

Regards,
Alexandre

On Wed, May 25, 2011 at 3:17 PM, Dyer, James <[EMAIL PROTECTED]>wrote:

> The "failure to commit" bug with $deleteDocById can be fixed by applying
> patch SOLR-2492.  This patch also partially fixes the "no updated stats" bug
> in that it increments 1 for every call to $deleteDocById and
> $deleteDocByQuery.  Note that this might result in inaccurate counts if the
> id given with $deleteDocById doesn't exist or is duplicated.  Obviously this
> is not a complete fix for stats using $deleteDocByQuery as this command
> would normally be used to delete >1 doc at a time.
>
> The patch is for Trunk but it might work with 3.1 also.  If not, it likely
> only needs minor tweaking.
>
> The jira ticket is here:  https://issues.apache.org/jira/browse/SOLR-2492
>
> James Dyer
> E-Commerce Systems
> Ingram Content Group
> (615) 213-4311
>
>
> -----Original Message-----
> From: Alexandre Rocco [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, May 25, 2011 12:54 PM
> To: [EMAIL PROTECTED]
> Subject: Re: DIH import and postImportDeleteQuery
>
> Hi Ephraim,
>
> Thank you so much for the input.
> I was able to find your thread on the archives and got your solution to
> work.
>
> In fact, when using $deleteDocById and $skipDoc it worked like a charm.
> This
> feature is very useful, it's a shame it's not properly documented.
> The only downside is the one you mentioned that the stats are not updated,
> so if I update 13 documents and delete 2, DIH would tell me that only 13
> documents were processed. This is bad in my case because I check the end
> result to generate an error e-mail if needed.
>
> You also mentioned that if the query contains only deletion records, a
> commit would not be automatically executed and it would be necessary to
> commit manually.
>
> How can I commit manually via DIH? I was not able to find any references on
> the documentation.
>
> Thanks!
> Alexandre
>
> On Wed, May 25, 2011 at 5:14 AM, Ephraim Ofir <[EMAIL PROTECTED]> wrote:
>
> > Search the list for my post "DIH - deleting documents, high performance
> > (delta) imports, and passing parameters" which shows my solution a
> > similar problem.
> >
> > Ephraim Ofir
> >
> > -----Original Message-----
> > From: Alexandre Rocco [mailto:[EMAIL PROTECTED]]
> > Sent: Tuesday, May 24, 2011 11:24 PM
> > To: [EMAIL PROTECTED]
> > Subject: DIH import and postImportDeleteQuery
> >
> > Guys,
> >
> > I am facing a situation in one of our projects that I need to perform a
> > cleanup to remove some documents after we perform an update via DIH.
> > The big issue right now comes from the fact that when we call the DIH
> > with
> > clean=false, the postImportDeleteQuery is not executed.
> >
> > My setup is currently arranged like this:
> > - A SQL Server stored procedure that receives a parameter (specified in
> > the
> > URL) and returns the records to be indexed
> > - The procedure is able to return all the records (for a full-import) or
> > only the updated records (for a delta-import)
> > - This procedure returns valid and deleted records, from this point
> > comes
> > the need to run a postImportDeleteQuery to remove the deleted ones.