Hi, I have this issue:
WARN com.inductiveautomation.ignition.client.gateway_interface.GatewayConnectionManager - Connection to Gateway lost, due to exception.
com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Read timed out
The Problem is that the query takes 3:40 minutes in SQL Studio. But guess Ignition times out before that... How can I boost the time before time out?
My first suggestion would be to do some serious work on why this query is taking so long. Iâve done some super complicated queries involving hundreds of tables that take less than a second to return, once I got the table structure and indexing tuned. If youâre doing things like count or sum in your query, look at whether you can pre-aggregate data. And finally, make sure your db has enough memory and a fast drive â databases are memory hogs and I frequently see dbs just way too starved for memory.
Once you do all that, you might consider using a db tag for your query if you still have problems with time outs.
This is a production machine supplied by a vendor. We just replaced it. The Old one had the issue with SQL Studio, but executed perfectly (in a second) in Ignition. I have no control on that machine except for a read only user to query views. Can't do indexes or anything (Not if I could if I could anyway...) He're the query if you can find any better fix
Sorry my comments are in French 
The goal is to get my closed bundles ([AutViewPackageHeader]) from a Shift with the Sum of BoardFeet in [AutViewTallyInventory] by Product Code (ScoopCode).
I'm trying a different approach now, by dumping bundle by bundle with it's Tally info a temp table then do the sum on that table and the string formatting.
/*Dans mon script en python, j'execute chaque requĂȘte dans des transactions sĂ©parĂ©es et met la valeur dans
une variable pour l'utiliser dans les autres requĂȘtes d'aprĂšs, au lieu tout faire dans une requĂȘte comme ici.
Ătrangement dans mon script en python, qui utilise un driver SQL en Java, tout le script s'exĂ©cute en moins
de 5 seconds... Celui si dans Management Studio, il prend 3 min Ă 4 mins...
*/
DECLARE @Faction varchar(25),
@Equipe varchar(25),
@Debut datetime,
@Fin datetime,
@Shift int,
@Entrepot intSELECT @Shift=4200
SELECT @Faction=(SELECT(REPLICATE(' ',25-LEN(RTRIM([Shift])))+ [Shift])
FROM [AutologACCTrimmerHardWood].[dbo].[AutViewShiftHeader]
WHERE IDDetail = @Shift
)SELECT @Equipe=(SELECT(REPLICATE(' ',25-LEN(RTRIM([Crew])))+ [Crew])
FROM [AutologACCTrimmerHardWood].[dbo].[AutViewShiftHeader]
WHERE IDDetail = @Shift
)SELECT @Debut=(SELECT [StartDate]
FROM [AutologACCTrimmerHardWood].[dbo].[AutViewShiftHeader]
WHERE IDDetail = @Shift
)SELECT @Fin=(SELECT [EndDate]
FROM [AutologACCTrimmerHardWood].[dbo].[AutViewShiftHeader]
WHERE IDDetail = @Shift
)
/Changer Ă 210,300 ou 400/
SELECT @Entrepot=210
/*Dans mon script j'execute une requĂȘte qui me donne les entrepots dans un array et je for loop par entrepot.
Je ne pouvais pas faire pareille ici, alors il faut executĂ© la requĂȘte par entrepot manuellement.
*/
SELECT 'QUE' AS Localisation,
(SELECT([ScoopCode] + REPLICATE(' ',25-LEN(RTRIM([ScoopCode]))))
)AS CodeProduit,
(SELECT
(SELECT([Specie] + REPLICATE(' ',25-LEN(RTRIM([Specie]))))
) AS Specie
FROM [AutologACCTrimmerHardWood].[dbo].AutViewSpecieHeader
WHERE IDDetail = IDDetailSpecie
)AS Specie,
(SELECT [SortTbl]+(REPLICATE(' ',25-LEN(RTRIM([SortTbl]))))
)AS Classement,
(SELECT(
REPLICATE('0',25-LEN(RTRIM(CONVERT(varchar(10),CONVERT(DECIMAL(20,4),SUM([BoardFeetOut]))))))
+CONVERT(varchar(10),CONVERT(DECIMAL(20,4),SUM([BoardFeetOut])))
)
)AS PMP,
@Faction as Faction,
@Equipe as Equipe,
(SELECT CONVERT(varchar, @Debut, 101)
)AS DateDebutFaction,
(SELECT LEFT(CONVERT(varchar, @Fin, 108), 5)
)AS HeureDebutFaction,
CASE
WHEN Warehouse is NULL
THEN 210
ELSE Warehouse
END as EntrepotFROM [AutologACCTrimmerHardWood].[dbo].[AutViewTallyInventory] AS TALLY
WHERE [Pile] = 1 AND Warehouse = @Entrepot
AND IDDetailPackage IN
(SELECT [IDDetail]
FROM [AutologACCTrimmerHardWood].[dbo].[AutViewPackageHeader]
WHERE EndDate BETWEEN @Debut AND @Fin
)
GROUP BY [ScoopCode],IDDetailSpecie,[SortTbl], Warehouse
ORDER BY ScoopCode
Result looks like:
Localisation CodeProduit Specie Classement PMP Faction Equipe DateDebutFaction HeureDebutFaction Entrepot
QUE TLVE1U0440RW070 Tremble Tremble cour 00000000000000001289.0000 Jours # 211 08/11/2017 17:15 210
QUE TLVE1U0440RW080 Tremble Tremble cour 00000000000000000945.0000 Jours # 211 08/11/2017 17:15 210
QUE TLVESU0440RW070 Tremble Tremble cour 00000000000000002252.0000 Jours # 211 08/11/2017 17:15 210
QUE TLVESU0440RW080 Tremble Tremble cour 00000000000000001908.0000 Jours # 211 08/11/2017 17:15 210
Eric
Youâre forcing the database engine to do a lot of formatting. Itâs not a good idea since theyâre not designed for that. Let Phyton do it. Get your dataset raw and run a formatting process on it. The queries are not complex, they look like it because of all the formatting.
Besides that, itâs not easy to work on performance without access to the table/index structure. But you may be able to get some improvement by working with your Where clauses. Put always the most restrictive condition last. Think about which one would give you the smallest recordset. This way, each filter along the line gets the less possible work load.
Iâm not sure about MS SQL, this rule works in most databases, Oracle, MySQL. But I donât work much with MS, maybe they parse the other way around⊠or randomlyâŠ
Well good news, the SQL Genius of my vendor came back from her vacation. And she fixed the sloppy job of her colleague. The 3:40 minutes query takes 10 secs now!
Thanks to all of you for your input and tips. Iâll take them on the next project.
@KathyApplebaum, I have a similar issue where I for various reasons are not able to touch the db to make it behave. Iâs a single stored procedure call that Ignition seems to time out after 60 seconds no matter what I do. Can you conform that there is no setting or property that I can change that will extend the timeout?
If your query is timing out in the Client/Designer:
In the menu-bar go to Project->Properties->Client/Timing and change the timeout durations.
The default read timeout is 60,000ms or 60s.
I am getting this same error in my client on a screen that I have a button that runs
script similar this this:
try:
SProcCallMax = system.db.createSProcCall(âPalletGetUnProcessedCRâ, DBNAME)
SProcCallMax.registerInParam(1, system.db.INTEGER, lCell)
system.db.execSProcCall(SProcCallMax)
ds = SProcCallMax.getResultSet()
if ds is not None and ds.getRowCount() > 0:
###script code here (omitted)
except:
#log some exception script here (omitted)
Once I get this exception, all QUERY tags in ignition are failed and will not recover on the HMI.
They show up as RED boxes on the screens.
Also, on a different screen that has a TABLE on it that ALWAYS works. After the timeout,
it does not work.
Itâs like once the exception âTimeoutâ occurs, then the entire HMI is broken.
Even though I âcaughtâ the exception and logged it.
What am I doing wrong?
Why is the connection to the database unrecoverable after this error?
Is this some type of âspecialâ error?
Thanks
The only solution is to shutdown the client and start it up again.
Then