When attempting to remove old or duplicated releases, I receive a nested transaction error

[Chris Cunningham]

 
       We are attempting to use the attached script to automate the process of removing duplicate or very old releases in our common-components repository.  When we run this script, or similar scripts, the script exits with the exception attached.  A small conversation with support adjusted the script a small amount as well as provided some insight that the script might be doing too large a change to the database and that is why it cannot run. Support reports the script runs normally on their environment. Since no solution was found, it was recommended I post here and see if anyone else is having a similar problem and/or has a solution available.  As of right now, we are having to put a large quantity of man hours towards keeping space open on the repository. 

Thanks,
  Chris

[Bradley Beck]

Try leaving out the tx.deleteAsset statement, since tx.deleteComponent will also delete the associated assets. e.g.

import org.sonatype.nexus.repository.storage.StorageFacet

import org.sonatype.nexus.repository.storage.Query

def cutoffDate = new Date().minus(30)

def repo = repository.repositoryManager.get('common-components')

StorageFacet storageFacet = repo.facet(StorageFacet)

def tx = storageFacet.txSupplier().get()

tx.begin()

tx.findAssets(Query.builder().where('last_downloaded <').param(cutoffDate).build(), [repo]).each { asset ->

  def component = tx.findComponent(asset.componentId())

  def count = tx.countComponents(Query.builder().where('name').eq(component.name()).and('version >').param(component.version()).build(), [repo])

  if (count > 0) {

    log.info("Delete asset ${asset.name()} as it has not been downloaded since 3 months and has a newer version");

    tx.deleteComponent(component)

  }

}

tx.commit()

[Chris Cunningham]

Hey Bradley,

   Thanks for checking this out for me.  I ran your script and received the same error as before regarding nested database transactions.  Is there a setting in nexus or a capability we need to add to enable that behavior?

Thanks,
  Chris

[Stuart McCulloch]

How are you triggering the script?

--
You received this message because you are subscribed to the Google Groups "Nexus Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nexus-users+unsubscribe@glists.sonatype.com.
To post to this group, send email to nexus...@glists.sonatype.com.

To view this discussion on the web visit https://groups.google.com/a/glists.sonatype.com/d/msgid/nexus-users/1697c6d6-ded3-4dfd-89ba-ad5adccc2e44%40glists.sonatype.com.

For more options, visit https://groups.google.com/a/glists.sonatype.com/d/optout.

[Chris Cunningham]

We have created it as a script task in nexus settings. Screenshot is that task.

On Tuesday, August 29, 2017 at 10:14:35 AM UTC-6, mcculls wrote:

How are you triggering the script?

On 29 Aug 2017 16:59, "Chris Cunningham" <cmcu...@gmail.com> wrote:

Hey Bradley,

   Thanks for checking this out for me.  I ran your script and received the same error as before regarding nested database transactions.  Is there a setting in nexus or a capability we need to add to enable that behavior?

Thanks,
  Chris

--
You received this message because you are subscribed to the Google Groups "Nexus Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to nexus-users...@glists.sonatype.com.

To post to this group, send email to nexus...@glists.sonatype.com.

[Bradley Beck]

The example I provided was run via a scripting task.

It should also be noted that a component may have many assets, so you may want to construct a set of components that are to be deleted and then delete those in a separate loop.

Also, the current script will trigger the deletion of a component if a pom has not been downloaded in 90 days but the jar may have been downloaded more recently. Something to think about.

On Tue, Aug 29, 2017 at 11:14 AM Stuart McCulloch <mcc...@sonatype.com> wrote:

How are you triggering the script?

On 29 Aug 2017 16:59, "Chris Cunningham" <cmcu...@gmail.com> wrote:

Hey Bradley,

   Thanks for checking this out for me.  I ran your script and received the same error as before regarding nested database transactions.  Is there a setting in nexus or a capability we need to add to enable that behavior?

Thanks,
  Chris

--
You received this message because you are subscribed to the Google Groups "Nexus Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to nexus-users...@glists.sonatype.com.

To post to this group, send email to nexus...@glists.sonatype.com.

To view this discussion on the web visit https://groups.google.com/a/glists.sonatype.com/d/msgid/nexus-users/1697c6d6-ded3-4dfd-89ba-ad5adccc2e44%40glists.sonatype.com.

For more options, visit https://groups.google.com/a/glists.sonatype.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Nexus Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to nexus-users...@glists.sonatype.com.

To post to this group, send email to nexus...@glists.sonatype.com.

To view this discussion on the web visit https://groups.google.com/a/glists.sonatype.com/d/msgid/nexus-users/CAB4-bMMqyRxc6m7uWSX8roFHqmXChJS6snL8jOO73B-SqhG%2BFQ%40mail.gmail.com.

[Kelly Robinson]

Another thing to be wary of is that 'version' is a String, and therefore won't necessarily sort as expected. For example, when sorting these valid semver versions as Strings, the result is not correct: '2.1.1' < '2.1.10' == true

On Tue, Aug 29, 2017 at 9:28 AM, Bradley Beck <brad...@sonatype.com> wrote:

The example I provided was run via a scripting task.

It should also be noted that a component may have many assets, so you may want to construct a set of components that are to be deleted and then delete those in a separate loop.

Also, the current script will trigger the deletion of a component if a pom has not been downloaded in 90 days but the jar may have been downloaded more recently. Something to think about.

On Tue, Aug 29, 2017 at 11:14 AM Stuart McCulloch <mcc...@sonatype.com> wrote:

How are you triggering the script?

On 29 Aug 2017 16:59, "Chris Cunningham" <cmcu...@gmail.com> wrote:

Hey Bradley,

   Thanks for checking this out for me.  I ran your script and received the same error as before regarding nested database transactions.  Is there a setting in nexus or a capability we need to add to enable that behavior?

Thanks,
  Chris

--
You received this message because you are subscribed to the Google Groups "Nexus Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to nexus-users+unsubscribe@glists.sonatype.com.

To post to this group, send email to nexus...@glists.sonatype.com.

To view this discussion on the web visit https://groups.google.com/a/glists.sonatype.com/d/msgid/nexus-users/1697c6d6-ded3-4dfd-89ba-ad5adccc2e44%40glists.sonatype.com.

For more options, visit https://groups.google.com/a/glists.sonatype.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Nexus Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to nexus-users+unsubscribe@glists.sonatype.com.

To post to this group, send email to nexus...@glists.sonatype.com.
To view this discussion on the web visit https://groups.google.com/a/glists.sonatype.com/d/msgid/nexus-users/CAB4-bMMqyRxc6m7uWSX8roFHqmXChJS6snL8jOO73B-SqhG%2BFQ%40mail.gmail.com.
For more options, visit https://groups.google.com/a/glists.sonatype.com/d/optout.

--

You received this message because you are subscribed to the Google Groups "Nexus Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to nexus-users+unsubscribe@glists.sonatype.com.

To post to this group, send email to nexus...@glists.sonatype.com.

To view this discussion on the web visit https://groups.google.com/a/glists.sonatype.com/d/msgid/nexus-users/CAEz28gjzL6vcF7ZUQau%2BB6gj_cC%3DtvjN2DnK0591055vnB0pQw%40mail.gmail.com.

[Kelly Robinson]

Sorry, I grabbed the wrong example by accident :( This one actually shows the problem, where alpha indicates a pre-release and yet is considered to be a higher version by the naive string sort: '2.1.alpha' > '2.1' == true

[Chris Cunningham]

Thanks for pointing our that string limitation, we'll check for that with our revisions.  Ive been working with the primary admin of our nexus environment and based on the behavior of all scrips we are pushing through it there seems to be a broken process maintaining a lock on the database, as it currently will not allow any transactions of any kind to occur.  Even an empty transaction where no changes occur exits with nested transaction errors.  Once we can safely restart the environment we will try the suggested changes here and report back.

Thanks,
  Chris

[Chris Cunningham]

Sorry for the delay on this, took a while before we had a good time to bounce the nexus server.  We also took the opportunity to update to the latest version of nexus.  Unfortunately this did not resolve the problems we were having.  Upon restart we attempted to run the script written as Bradley has it on this thread, and received a null pointer exception which I have attached.  I think this is due to the loop not checking if asset.ComponentID() is null.  After this run however all of our scripts began returning the NestedDBTX! exception we experienced before.  Is there a way to kill a deadlocked transaction in nexus? 

Thanks,

  Chris 

[Bradley Beck]

Restarting Nexus Repository Manager should clear up any deadlocked transactions.

--

You received this message because you are subscribed to the Google Groups "Nexus Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to nexus-users...@glists.sonatype.com.

To post to this group, send email to nexus...@glists.sonatype.com.

To view this discussion on the web visit https://groups.google.com/a/glists.sonatype.com/d/msgid/nexus-users/d82ce8a9-35b4-4731-a360-63e0600d9129%40glists.sonatype.com.

[Chris Cunningham]

I just wanted to update this thread with the final working version of this script. The nested error was due to the script crashing and not closing the transaction.  On a healthy server this script will clear all old data from the specified repository just fine, and its doing it once a week for us with continued success. Thank you to those who helped in this thread and Sonatype support:

import org.sonatype.nexus.repository.storage.StorageFacet
import org.sonatype.nexus.repository.storage.Query


def cutoffDate = new Date().minus(90)

def repo = repository.repositoryManager.get('common-components')


StorageFacet storageFacet = repo.facet(StorageFacet)


def tx = storageFacet.txSupplier().get()


tx.begin()
tx.findAssets(Query.builder().where('last_downloaded <').param(cutoffDate).build(), [repo]).each { asset ->

 if (asset.componentId() != null) {
    def component = tx.findComponent(asset.componentId())
    if(component != null){

    def count = tx.countComponents(Query.builder().where('name').eq(component.name()).and('version >').param(component.version()).build(), [repo])
    if (count > 0) {
        log.info("Delete asset ${asset.name()} as it has not been downloaded since 3 months and has a newer version");

       // tx.deleteComponent(component)
    }
}
}
}
tx.commit()

[Faheem Ahmad]

How could we use this script to delete components based on version number no the date range ? I would like to keep latest 10 version of each components and delete the rest.

Any help is appreciated.

Thanks,

Faheem

[Chris Cunningham]

You could refactor this script to do that.  You would just need to replace the last downloaded filter with a check for version.  Otherwise I would check this out http://www.sonatype.org/nexus/2015/07/13/sonatype-nexus-delete-artifacts-based-on-a-selection/  I think this script already does what you're looking to do.