Thingworx8 with MSQL Database connection error

[mvolanti]

Hi everyone,

I'm installing thingworx 8 with MSQL. After deploy war, thingworx give me error below.

I configured properly the local Database: 

My platform-settings:

{

"PlatformSettingsConfig": {

"BasicSettings": {

"BackupStorage": "/ThingworxBackupStorage",

"DatabaseLogRetentionPolicy": 7,

"EnableBackup": true,

"EnableHA": false,

"EnableSystemLogging": true,

"HTTPRequestHeaderMaxLength": 2000,

"HTTPRequestParameterMaxLength": 2000,

"Storage": "/ThingworxStorage"

}

},

"MssqlPersistenceProviderPackage": {

"ConnectionInformation": {

"acquireIncrement": 5,

"acquireRetryAttempts": 3,

"acquireRetryDelay": 10000,

"checkoutTimeout": 1000000,

"driverClass": "com.microsoft.sqlserver.jdbc.SQLServerDriver",

"fetchSize": 5000,

"idleConnectionTestPeriod": 60,

"initialPoolSize": 5,

"jdbcUrl":"jdbc:sqlserver://192.168.200.149:1433;databaseName=thingworx;applicationName=Thingworx;",

"maxConnectionAge": 0,

"maxIdleTime": 0,

"maxIdleTimeExcessConnections": 300,

"maxPoolSize": 100,

"maxStatements": 100,

"minPoolSize": 5,

"numHelperThreads": 8,

"password": "MyPassword",

"testConnectionOnCheckout": false,

"unreturnedConnectionTimeout": 0,

"username": "twadmin"

},

"StreamProcessorSettings": {

"maximumBlockSize": 2500,

"maximumQueueSize": 250000,

"maximumWaitTime": 10000,

"numberOfProcessingThreads": 5,

"scanRate": 5,

"sizeThreshold": 1000

},

"ValueStreamProcessorSettings": {

"maximumBlockSize": 2500,

"maximumWaitTime": 10000,

"maximumQueueSize": 500000,

"numberOfProcessingThreads": 5,

"scanRate": 5,

"sizeThreshold": 1000

}

}

}

In the tomcat I found this warn and errors:

017-07-06 00:38:08.490-0700 [L: WARN] [O: c.m.v.r.BasicResourcePool] [I: ] [U: SuperUser] [S: ] [T: C3P0PooledConnectionPoolManager[identityToken->1hgf4id9p6c2jwmxpek65|76831556]-HelperThread-#1] com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@48fb8ca3 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (3). Last acquisition attempt exception:

2017-07-06 00:38:08.530-0700 [L: WARN] [O: c.m.v.r.BasicResourcePool] [I: ] [U: SuperUser] [S: ] [T: C3P0PooledConnectionPoolManager[identityToken->1hgf4id9p6c2jwmxpek65|76831556]-HelperThread-#1] Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@5f610a03 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.

2017-07-06 00:38:08.496-0700 [L: WARN] [O: c.m.v.r.BasicResourcePool] [I: ] [U: SuperUser] [S: ] [T: C3P0PooledConnectionPoolManager[identityToken->1hgf4id9p6c2jwmxpek65|76831556]-HelperThread-#3] com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@5eeff28b -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (3). Last acquisition attempt exception:

2017-07-06 00:38:08.538-0700 [L: WARN] [O: c.m.v.r.BasicResourcePool] [I: ] [U: SuperUser] [S: ] [T: C3P0PooledConnectionPoolManager[identityToken->1hgf4id9p6c2jwmxpek65|76831556]-HelperThread-#3] Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@5f610a03 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.

2017-07-06 00:38:08.558-0700 [L: ERROR] [O: c.t.s.ThingWorxServer] [I: ] [U: SuperUser] [S: ] [T: http-nio-8080-exec-2] [message: Connections could not be acquired from the underlying database!]

2017-07-06 00:38:08.495-0700 [L: WARN] [O: c.m.v.r.BasicResourcePool] [I: ] [U: SuperUser] [S: ] [T: C3P0PooledConnectionPoolManager[identityToken->1hgf4id9p6c2jwmxpek65|76831556]-HelperThread-#0] com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@4ae5e1e0 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (3). Last acquisition attempt exception:

2017-07-06 00:38:08.570-0700 [L: WARN] [O: c.m.v.r.BasicResourcePool] [I: ] [U: SuperUser] [S: ] [T: C3P0PooledConnectionPoolManager[identityToken->1hgf4id9p6c2jwmxpek65|76831556]-HelperThread-#0] Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@5f610a03 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.

2017-07-06 00:38:08.493-0700 [L: WARN] [O: c.m.v.r.BasicResourcePool] [I: ] [U: SuperUser] [S: ] [T: C3P0PooledConnectionPoolManager[identityToken->1hgf4id9p6c2jwmxpek65|76831556]-HelperThread-#4] com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@525e3774 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (3). Last acquisition attempt exception:

2017-07-06 00:38:08.573-0700 [L: WARN] [O: c.m.v.r.BasicResourcePool] [I: ] [U: SuperUser] [S: ] [T: C3P0PooledConnectionPoolManager[identityToken->1hgf4id9p6c2jwmxpek65|76831556]-HelperThread-#4] Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@5f610a03 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.

2017-07-06 00:38:08.494-0700 [L: WARN] [O: c.m.v.r.BasicResourcePool] [I: ] [U: SuperUser] [S: ] [T: C3P0PooledConnectionPoolManager[identityToken->1hgf4id9p6c2jwmxpek65|76831556]-HelperThread-#2] com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@119f8f7f -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (3). Last acquisition attempt exception:

2017-07-06 00:38:08.574-0700 [L: WARN] [O: c.m.v.r.BasicResourcePool] [I: ] [U: SuperUser] [S: ] [T: C3P0PooledConnectionPoolManager[identityToken->1hgf4id9p6c2jwmxpek65|76831556]-HelperThread-#2] Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@5f610a03 is interrupting all Threads waiting on a resource to check out. Will try again in response to new client requests.

2017-07-06 00:38:08.596-0700 [L: INFO] [O: c.t.p.p.PersistenceProvider] [I: ] [U: SuperUser] [S: ] [T: http-nio-8080-exec-2] Connection ThingworxPersistenceProvider, type MssqlPersistenceProviderPackage stopped

2017-07-06 00:38:08.600-0700 [L: WARN] [O: c.t.p.p.PersistenceProvider] [I: ] [U: SuperUser] [S: ] [T: http-nio-8080-exec-2] Could not find persistenceProviderPackage MssqlPersistenceProviderPackage and thus could not stop data processing

2017-07-06 00:38:08.634-0700 [L: ERROR] [O: c.t.s.ThingWorxBootstrapper] [I: ] [U: SuperUser] [S: ] [T: http-nio-8080-exec-2] [message: Unable to initialize and start system: Connections could not be acquired from the underlying database!]

2017-07-06 00:38:08.635-0700 [L: ERROR] [O: c.t.s.ThingWorxBootstrapper] [I: ] [U: SuperUser] [S: ] [T: http-nio-8080-exec-2] *** CRITICAL ERROR ON STARTUP: Unable to initialize and start system: Connections could not be acquired from the underlying database!

2017-07-06 00:38:08.635-0700 [L: ERROR] [O: c.t.s.ThingWorxBootstrapper] [I: ] [U: SuperUser] [S: ] [T: http-nio-8080-exec-2] *** Web Application STATE is being set to ERROR! ***

2017-07-06 00:39:25.391-0700 [L: ERROR] [O: c.t.s.a.AuthenticationFilter] [I: ] [U: ] [S: ] [T: http-nio-8080-exec-10] Could not handle request

2017-07-06 00:39:41.003-0700 [L: ERROR] [O: c.t.s.a.AuthenticationFilter] [I: ] [U: ] [S: ] [T: http-nio-8080-exec-5] Could not handle request

Any suggestion?

Thanks

[posipova · Accepted solution]

Try chaning your platform-settings.json to the following format (replace your password and username accordingly):

{

  "PersistenceProviderPackageConfigs": {

  "MssqlPersistenceProviderPackage": {

  "ConnectionInformation": {

     "driverClass": "com.microsoft.sqlserver.jdbc.SQLServerDriver",

  "jdbcUrl": "jdbc:sqlserver://localhost:1433;databaseName=thingworx;applicationName=Thingworx;",

  "password": "ts",

  "username": "twadmin"

  }

  }

  }

}

[LGrosz]

I have a similar error, however, the MS SQL database is located on a different server (not locally).  How do you recommend the platform-settings.json setting to be configured ?

Currently this is what I have:

{
        "PersistenceProviderPackageConfigs": {
                "MssqlPersistenceProviderPackage": {
                        "ConnectionInformation": {
                            "driverClass": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
                                "jdbcUrl": "jdbc:sqlserver://SQL-server-01:1433;databaseName=thingworx;applicationName=Thingworx;",
                                "password": "password",
                                "username": "TWADMIN"
                        }
                }
}

[VladimirRosu]

Can you replace the name SQL-server-01 with the IP, just to check if the name resolution works ok?