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.
SQL Datasources not querying H2 in 3.6
-
Hi!
After migrate from 3.5 to 3.6, my SQL Datasources are not able to query H2. It says the tables are "not found". It works fine when using the same query in SQL Console.The error:
class org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "EVENTS" not found; SQL statement: select count(id) as EVENTCOUNT from events; [42102-199]DS json:
{
"dataSources":[
{
"xid":"DS_40b9a8a4-8206-428f-9c1b-8dac016196c0",
"name":"!!!Andre-SQL-EVENTCOUNT",
"enabled":true,
"type":"SQL",
"alarmLevels":{
"POLL_ABORTED":"URGENT",
"STATEMENT_EXCEPTION":"URGENT",
"DATA_SOURCE_EXCEPTION":"URGENT"
},
"purgeType":"YEARS",
"updatePeriods":1,
"updatePeriodType":"MINUTES",
"connectionUrl":"jdbc:h2:/opt/mango/databases/mah2",
"driverClassname":"org.h2.Driver",
"password":"",
"rowBasedQuery":false,
"selectStatement":"select count(id) as EVENTCOUNT from events;",
"username":"",
"quantize":false,
"useCron":false,
"editPermission":"ds-mngt",
"purgeOverride":false,
"purgePeriod":1
}
]
}
Thanks. -
Hi Ava,
This is almost certainly caused by the name of the H2 database file changing to include the version of H2 in the filename. This was in the course of upgrading H2 from 196 to 199 which required doing a dump and restore. The advantage of using the newer version is that the LOB expansion that caused countless Mango users to shrink their H2 databases manually and in some cases regularly has been fixed.
Currently the version is 199, so the filename is mah2-199.h2.db now. This means the connection URL needs to be modified to:
jdbc:h2:/opt/mango/databases/mah2.199I think given this it would probably be in the module's interest to add a way to say it's this Mango's database, to avoid this issue if we update H2 again / it requires an actual upgrade path like 196 to 199 did.
Thanks for bringing this to our attention!
