MSMSSQL connection exception not allowed to disable data sources and import json configuration

---

Hello,

The problem I found is when you connect with a MSSQL data base and there is not connection with it. Then you cannot disable all the data sources and you cannot import a json configuration of data sources. When there is connection with the data base the behaivour is correct.

I´m working whit Mango v3.6.4.

The configuration of mi data source is an SQL data source :
{
"dataSources":[
{
"xid":"DS_SQL_KINEO_status",
"name":"SQL_status",
"enabled":true,
"type":"SQL",
"alarmLevels":{
"POLL_ABORTED":"URGENT",
"STATEMENT_EXCEPTION":"URGENT",
"DATA_SOURCE_EXCEPTION":"URGENT"
},
"purgeType":"YEARS",
"updatePeriods":1,
"updatePeriodType":"MINUTES",
"connectionUrl":"jdbc:sqlserver://10.120.50.24:1433;databaseName=master;",
"driverClassname":"com.microsoft.sqlserver.jdbc.SQLServerDriver",
"password":"password",
"rowBasedQuery":false,
"selectStatement":"",
"useMangoDatabase":false,
"username":"username",
"quantize":false,
"useCron":false,
"editPermission":"",
"purgeOverride":false,
"purgePeriod":1
}
],
"dataPoints":[
]
}
The driver I use is in this link: https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15
I´m using mssql-jdbc-8.4.1.jre8.jar

I paste part of the ma.log:

INFO 2020-10-19T00:05:00,022 (com.serotonin.m2m2.rt.maint.work.BackupWorkItem.execute:101) - Starting backup WorkItem.
INFO 2020-10-19T00:05:00,022 (com.serotonin.m2m2.rt.maint.work.DatabaseBackupWorkItem.execute:108) - Starting database backup WorkItem.
INFO 2020-10-19T00:05:00,025 (com.serotonin.m2m2.rt.maint.work.DatabaseBackupWorkItem.createLogOutputStream:224) - Writing backup log to /opt/mango/logs/com.serotonin.m2m2.rt.maint.work.DatabaseBackupWorkItem.log
INFO 2020-10-19T00:05:11,623 (com.serotonin.m2m2.rt.maint.work.BackupWorkItem.execute:181) - Finished backup WorkItem.
INFO 2020-10-19T00:24:02,086 (com.serotonin.m2m2.sql.SqlDataSourceRT.initialize:512) - Error while initializing data source
com.microsoft.sqlserver.jdbc.SQLServerException: No se pudo realizar la conexi�n TCP/IP al host 10.120.50.24, puerto 1433. Error: "connect timed out. Verifique las propiedades de conexi�n, compruebe que hay una instancia de SQL Server ejecut�ndose en el host y aceptando las conexiones TCP/IP en el puerto y compruebe que no hay ning�n firewall bloqueando las conexiones TCP en el puerto.".
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:285) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2462) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:668) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2695) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2362) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2213) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1276) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:861) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at java.sql.DriverManager.getConnection(DriverManager.java:677) ~[java.sql:?]
at java.sql.DriverManager.getConnection(DriverManager.java:228) ~[java.sql:?]
at com.serotonin.m2m2.sql.SqlDataSourceRT.initialize(SqlDataSourceRT.java:501) ~[?:?]
at com.serotonin.m2m2.sql.SqlDataSourceRT.manageConnection(SqlDataSourceRT.java:280) ~[?:?]
at com.serotonin.m2m2.sql.SqlDataSourceRT.doPoll(SqlDataSourceRT.java:297) ~[?:?]
at com.serotonin.m2m2.rt.dataSource.PollingDataSource.doPollNoSync(PollingDataSource.java:228) ~[mango-3.6.4.jar:?]
at com.serotonin.m2m2.rt.dataSource.PollingDataSource.scheduleTimeoutImpl(PollingDataSource.java:183) ~[mango-3.6.4.jar:?]
at com.serotonin.m2m2.rt.dataSource.PollingDataSource$1.scheduleTimeout(PollingDataSource.java:85) ~[mango-3.6.4.jar:?]
at com.serotonin.m2m2.util.timeout.TimeoutTask.run(TimeoutTask.java:59) ~[mango-3.6.4.jar:?]
at com.serotonin.timer.Task.runTask(Task.java:179) ~[mango-3.6.4.jar:?]
at com.serotonin.timer.TaskWrapper.run(TaskWrapper.java:23) ~[mango-3.6.4.jar:?]
at com.serotonin.timer.OrderedThreadPoolExecutor$OrderedTaskCollection.run(OrderedThreadPoolExecutor.java:336) ~[mango-3.6.4.jar:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[?:?]
at java.lang.Thread.run(Thread.java:834) [?:?]
WARN 2020-10-19T00:32:01,135 (com.serotonin.m2m2.rt.dataSource.PollingDataSource.incrementUnsuccessfulPolls:150) - Data Source SQL_KINEO_status aborted 3154 polls since it started.
INFO 2020-10-19T01:05:17,052 (com.serotonin.m2m2.sql.SqlDataSourceRT.initialize:512) - Error while initializing data source
com.microsoft.sqlserver.jdbc.SQLServerException: No se pudo realizar la conexi�n TCP/IP al host 10.120.50.24, puerto 1433. Error: "connect timed out. Verifique las propiedades de conexi�n, compruebe que hay una instancia de SQL Server ejecut�ndose en el host y aceptando las conexiones TCP/IP en el puerto y compruebe que no hay ning�n firewall bloqueando las conexiones TCP en el puerto.".
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:285) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2462) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:668) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2695) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2362) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2213) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1276) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:861) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at java.sql.DriverManager.getConnection(DriverManager.java:677) ~[java.sql:?]
at java.sql.DriverManager.getConnection(DriverManager.java:228) ~[java.sql:?]
at com.serotonin.m2m2.sql.SqlDataSourceRT.initialize(SqlDataSourceRT.java:501) ~[?:?]
at com.serotonin.m2m2.sql.SqlDataSourceRT.manageConnection(SqlDataSourceRT.java:280) ~[?:?]
at com.serotonin.m2m2.sql.SqlDataSourceRT.doPoll(SqlDataSourceRT.java:297) ~[?:?]
at com.serotonin.m2m2.rt.dataSource.PollingDataSource.doPollNoSync(PollingDataSource.java:228) ~[mango-3.6.4.jar:?]
at com.serotonin.m2m2.rt.dataSource.PollingDataSource.scheduleTimeoutImpl(PollingDataSource.java:183) ~[mango-3.6.4.jar:?]
at com.serotonin.m2m2.rt.dataSource.PollingDataSource$1.scheduleTimeout(PollingDataSource.java:85) ~[mango-3.6.4.jar:?]
at com.serotonin.m2m2.util.timeout.TimeoutTask.run(TimeoutTask.java:59) ~[mango-3.6.4.jar:?]
at com.serotonin.timer.Task.runTask(Task.java:179) ~[mango-3.6.4.jar:?]
at com.serotonin.timer.TaskWrapper.run(TaskWrapper.java:23) ~[mango-3.6.4.jar:?]
at com.serotonin.timer.OrderedThreadPoolExecutor$OrderedTaskCollection.run(OrderedThreadPoolExecutor.java:336) ~[mango-3.6.4.jar:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[?:?]
at java.lang.Thread.run(Thread.java:834) [?:?]

Thanks in advance!!

Ok, for a start, since datasource and point config infirmation is installed in here, the expected behaviour is correct.
What are you doing to cause such behaviour?
There shouldnt be a connection issue unless the connection to your DB is truly that bad...

If a configure first the SQL data source and the DB is not up for example, when I import a DB json configuration freeze the mango source edition and the import procces too, then I have to restart mango by console. It seems that when the data source is saved, first it check the DB connection with a very high timeout that you cannot configure and the proccess of the data source edition is running but freeze in the check connection DB. It could be MSSQL driver fault maybe I don´t know...

An other note if you restart mango and you dont have connection with the data base each data source took so much time to start (100 to 300 seconds) , it took so much time to start mango (some hours) if you have quite DB data sources...

Looks like a system bug to me... the driver only facilitates getting data to and from the DB.

Fox

@jmatesanz there is currently no setting for this on the SQL Data Source. The timeout will default to no timeout ever.

@jmatesanz after further investigation you can configure this in the connection URL. See all the connection properties here:

https://docs.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-ver15

Specifically

loginTimeout

Thanks for your reply I configure the URL with login time Out and now it work correctly.