• Register
    • Login
    • Search
    • Recent
    • Tags
    • Popular

    Please Note This forum exists for community support for the Mango product family and the Radix IoT Platform. Although Radix IoT employees participate in this forum from time to time, there is no guarantee of a response to anything posted here, nor can Radix IoT, LLC guarantee the accuracy of any information expressed or conveyed. Specific project questions from customers with active support contracts are asked to send requests to support@radixiot.com.

    Radix IoT Website Mango 3 Documentation Website Mango 4 Documentation Website

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

    Mango feedback
    3
    8
    1238
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • J
      jmatesanz last edited by

      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!!

      1 Reply Last reply Reply Quote 0
      • MattFox
        MattFox last edited by MattFox

        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...

        Do not follow where the path may lead; go instead where there is no path.
        And leave a trail - Muriel Strode

        1 Reply Last reply Reply Quote 0
        • J
          jmatesanz last edited by

          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...

          1 Reply Last reply Reply Quote 0
          • J
            jmatesanz last edited by

            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...

            0_1603884625626_Captura.PNG

            1 Reply Last reply Reply Quote 0
            • MattFox
              MattFox last edited by

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

              Fox

              Do not follow where the path may lead; go instead where there is no path.
              And leave a trail - Muriel Strode

              1 Reply Last reply Reply Quote 0
              • terrypacker
                terrypacker last edited by

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

                1 Reply Last reply Reply Quote 0
                • terrypacker
                  terrypacker last edited by

                  @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
                  
                  1 Reply Last reply Reply Quote 0
                  • J
                    jmatesanz last edited by

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

                    1 Reply Last reply Reply Quote 0
                    • First post
                      Last post