how to remove queries from medium/large queues

[carmen....@googlemail.com]

Hello,

This is regarding i2b2 v. 1.7.12a using a SQL Server database.

I encountered a strange issue for some queries that take a long time to execute, they end up in medium or large queues, but before they finish they get interrupted because of the large amount of records inserted. The database transaction log for tempdb becomes full due to ACTIVE_TRANSACTION during large inserts.

So the process that executes the insert is terminated, but a new process is started shortly in the same queue executing the same query (insert). The new query suffers the same fate, it's terminated and and it starts over in a new process. 

I restarted Wildfly hoping that it will get rid of these queries. I want them removed from the queue(s). However, immediately after startup, they are started again. In the wildfli-stdout.log I can see this message:

"STARTING MEDIUM_QUEUE FOR <query_instance_id>.." (Could be LARGE_QUEUE for other queries.)

I did try to modify the records in the QT_QUERY_INSTANCE tables, updating the BATCH_MODE value for these queries to ERROR and make sure they have a non null  end_date. I did that while Wildfly was shut down. However as soon as I started Wildfly, the queries were started again in the queue.

I can't seem to be able to kill them off these for good, they will run like that forever.

Any suggestions?

Thank you,

Carmen 

[Joseph Kabariti]

Have you tried updating the DELETE_FLAG and DELETE_DATE in the QT_QUERY_MASTER table and then restart wildfly? I think that might stop it from restarting. In the past, we've resorted to restarting the instance if it kept persisting.

Joseph

[carmen....@googlemail.com]

Thank you Joseph. I did try setting the delete flag and delete date, it did not change anything.

I dug up the source code (ProcessQueue.java) and it does not look like these queries should be placed in queues at startup.

Their batch mode value in the query instance table is "RUNNING" or "ERROR" before starting Wildfly. According to the source code only queries with batch mode= one of the queues (eg. MEDIUM_QUEUE etc.) are being picked up and placed in queues when the application starts.

What do you mean by restarting the instance? Is it the application or the SQL services that you're restarted when this issue kept persisting?

Thank you,

Carmen

[Phillips, Lori C.]

You can also try to set the delete_Flag to ‘Y’ in the qt_query_instance table.

Then restart wildlfy.

 

From: 'carmen....@googlemail.com' via i2b2 Install Help <i2b2-ins...@googlegroups.com>
Sent: Friday, December 4, 2020 11:24 AM
To: i2b2 Install Help <i2b2-ins...@googlegroups.com>
Subject: Re: how to remove queries from medium/large queues

 

        External Email - Use Caution        

--
You received this message because you are subscribed to the Google Groups "i2b2 Install Help" group.
To unsubscribe from this group and stop receiving emails from it, send an email to i2b2-install-h...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/i2b2-install-help/d196cc9c-0460-4eb2-bf65-ee961dd026cdn%40googlegroups.com.

The information in this e-mail is intended only for the person to whom it is addressed. If you believe this e-mail was sent to you in error and the e-mail contains patient information, please contact the Mass General Brigham Compliance HelpLine at http://www.massgeneralbrigham.org/complianceline . If the e-mail was sent to you in error but does not contain patient information, please contact the sender and properly dispose of the e-mail.

[carmen....@googlemail.com]

Thank you Lori. After debugging a bit, I found out that the app is looking at the query tables from all CRC databases for the hive, to find the queries that need to be placed in queues at startup. We do have 2 CRC databases, although one of them is part of a project we don't currently use, and has a snapshot of the query tables from the first CRC database at a specific times. The query history tables in that additional CRC database had the query records that were causing this issue (having batch_mode = MEDIUM_QUEUE or LARGE_QUEUE and null end_Date). 

Once I updated the batch mode value for those query instance records, the issue went away.

Thank you both for your help.