Data Source gone after upgrade to 3.6

[thutchis]

@phildunlap I am not familar with the databits parameter. How would I determine that?

[phildunlap]

It should be in any old JSON exports that include that data source.

[thutchis]

@phildunlap OK, I get it now. Databits is the serial parameter of the data source. The data source is the problem because it disappeared when I upgraded to Mango 3.6, leaving the data point "orphaned". Do I need to recreate the original data source with the same XID from an old backup?

[phildunlap]

Did importing the JSON I provided (with the data source xid set to the xid of the missing data source) not solve it?

I would look up the XID if you don't know it either via the SQL console page in Mango with select xid, name from dataSources; or in an old JSON backup. Then you could try setting its data bits via the JSON import.

To maintain the data in the data point if there is data worth maintaining, recreating the data source will create a couple more steps. Let's try to fix it. Another maybe easier way to fix it would be to create a new serial data source with the right settings, then go to the SQL console page (you may need to enable it under edit menu, and have the sql console module installed) and,

select id, name from dataSources;
-- find the id of the old and new serial data sources
update dataSources set data=(select data from dataSources where id=[new id here]) where id=[old id here];

And then you should be able to see it on the data sources page, and delete the new serial data source.

[thutchis]

@phildunlap said in Data Source gone after upgrade to 3.6:

update dataSources set data=(select data from dataSources where id=[new id here]) where id=[old id here];

This is the error message get when I submit the SQL Query:

update dataSources set data=(select data from dataSources where id=[DS_bf3ed68c-3ced-491a-af92-d228a4ce345c]) where id=[DS_80cdd749-28ad-4612-93d4-cac1ea998a1c];

ShouldNeverHappenException: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "UPDATE DATASOURCES SET DATA=(SELECT DATA FROM DATASOURCES WHERE ID=[[*]DS_BF3ED68C-3CED-491A-AF92-D228A4CE345C]) WHERE ID=[DS_80CDD749-28AD-4612-93D4-CAC1EA998A1C]; "; expected "ALL, ANY, SOME"; SQL statement: update dataSources set data=(select data from dataSources where id=[DS_bf3ed68c-3ced-491a-af92-d228a4ce345c]) where id=[DS_80cdd749-28ad-4612-93d4-cac1ea998a1c]; [42001-199]

Apparently the SQL syntax is wrong. I am not enough of an SQL expert to find it.

[thutchis]

@phildunlap If I put your SQL statement into the "SQL" update window I get the following error:
BadSqlGrammarException: StatementCallback; bad SQL grammar [update dataSources set data=(select data from dataSources where id=[DS_bf3ed68c-3ced-491a-af92-d228a4ce345c]) where id=[DS_80cdd749-28ad-4612-93d4-cac1ea998a1c];]; nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "UPDATE DATASOURCES SET DATA=(SELECT DATA FROM DATASOURCES WHERE ID=[[*]DS_BF3ED68C-3CED-491A-AF92-D228A4CE345C]) WHERE ID=[DS_80CDD749-28AD-4612-93D4-CAC1EA998A1C]; "; expected "ALL, ANY, SOME"; SQL statement: update dataSources set data=(select data from dataSources where id=[DS_bf3ed68c-3ced-491a-af92-d228a4ce345c]) where id=[DS_80cdd749-28ad-4612-93d4-cac1ea998a1c]; [42001-199]
Press Escape to dismiss. Press Control-"o" to

[phildunlap]

I was intending you to replace the whole square bracket portion with the ID (the number), so

select id, name from dataSources;
-- find the id of the old and new serial data sources
update dataSources set data=(select data from dataSources where id=[new id here]) where id=[old id here];
-- example of with some random IDs,
update dataSources set data=(select data from dataSources where id=2) where id=1;

Come to think of it, I'm not sure where I started using square brackets that way - certainly not coding. I think maybe it was composition, like the [one] example[s] didn't illustrate the right syntax but I'm not sure.

[thutchis]

@phildunlap I wondered if that was your intention so I tried leaving out he square brackets, still get errors for some reason.

BadSqlGrammarException: StatementCallback; bad SQL grammar [update dataSources set data=(select data from dataSources where id=DS_bf3ed68c-3ced-491a-af92-d228a4ce345c) where id=DS_80cdd749-28ad-4612-93d4-cac1ea998a1c;]; nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "UPDATE DATASOURCES SET DATA=(SELECT DATA FROM DATASOURCES WHERE ID=DS_BF3ED68C-3CED[*]-491A-AF92-D228A4CE345C) WHERE ID=DS_80CDD749-28AD-4612-93D4-CAC1EA998A1C; "; expected "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, GROUP, HAVING, WINDOW, QUALIFY, UNION, EXCEPT, MINUS, INTERSECT, ORDER, OFFSET, FETCH, LIMIT, SAMPLE_SIZE, FOR, [, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )"; SQL statement: update dataSources set data=(select data from dataSources where id=DS_bf3ed68c-3ced-491a-af92-d228a4ce345c) where id=DS_80cdd749-28ad-4612-93d4-cac1ea998a1c; [42001-199]
Press Escape to dismiss. Press Control-"o" to

[phildunlap]

That is an XID. Note above the update statement I do another select, but only for the ID and name columns, to find the ID.

XID columns are strings, and could work too, but they need to be inside single quotes to behave as strings.

[thutchis]

@phildunlap said in Data Source gone after upgrade to 3.6:

select id, name from dataSources;

Sorry, I mixed up "id" and "xid". That worked and I can now see the "disappeared" original serial data source on the data sources page.

This seems to have fixed all the issues I had after upgrading to 3.6.x. No more errors in ma.log, The datapoint is now logging the serial data properly and the event handler is sending me the results again via email.

Thanks so much for your patience, I am not a java or javascript programmer so I really struggle with this.

[phildunlap]

Glad we got it figured out!