Mango 3.7 restore

[BG]

Hello All,
The restore process was almost a success.

The process went ok. I managed to get the NoSQL back up restored via the legacy pages, the MySQL restored via the new system settings pages and the json configuration imported via the new configuration import/export page.

The automatic back ups did not back up the filestore but I happened to have a recent copy of that folder so I was able to restore all of the images I created for my dashboards as well as the excel report templates in use,

The only problem I encountered and it is a major problem for us, is that we had to install 3.7.12 as it is the only Mango 3 option available and that version does not have the Point Links feature.

All of our loggers used the Point Link Feature to change a flag data cell on each row of data in the MySQL table after the row was read into Mango.

I am now left with trying to figure out how to get the MySQL data row read into Mango using the Scripting Data Source. And I am struggling but I will put that into the forum under a new thread with more details.

Cheers
Brian

[MattFox]

@BG feel free to tag me, I can help. Alternatively, let me know what version of mango 3 you need

Fox

[BG]

@MattFox Thank you for your offer of help Matt.

I think I will stick with version 3.7.12 for now an figure out how to get the system running without the point links. After the recent crash it has highlighted the need to stay more relevant in regards to database versions and Linux versions which also mean we should think about moving towards Mango 5 with a stop over in Mango 4 just to be sure everything is still working. And in no future that we see for the Mango service will point links return. A shame because I found them an easy and simple way to achieve what we needed.

[BG]

@MattFox Instead of starting a new thread I will explain what we are doing here. I don't think too many people are searching for Mango 3 solutions anymore.

Our dataloggers have the ability to store its data locally during a mobile data connection loss. Once the data connection is restored the logger could potentially upload 100K rows of data to the MySQL database in a very short period of time. The other eccentricity of the loggers is that the digital channels only send data on a state change. we do not get an the current digital state value on every record that is sent.

So we need to be able to read the data from earliest to latest, and if there is no new data, mango needs to be able to go back to the last non null digital point found in the already read rows of data.

So to make sure Mango reads the data into the NoSQL database sequentially in timestamp order, we use the flag method to mark which rows have been read and identify the next row to be read.

We use a SQL data source to bring the data into mango, then use a Meta data source to scale the data for display on the dashboards, trigger alarms etc, as you know.

In the SQL data source we have a series of coalesce select statements as shown:

Select date, flag,
coalesce(
         	(SELECT an1 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1), 
         	(SELECT an1 FROM staging_all where grd_id = 0100 and flag = 1 and an1 IS NOT NULL order by date desc limit 1)
                     ) as an1,
coalesce(
         	(SELECT an2 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1), 
         	(SELECT an2 FROM staging_all where grd_id = 0100 and flag = 1 and an2 IS NOT NULL order by date desc limit 1)
                     ) as an2
coalesce(
         	(SELECT i1 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1), 
         	(SELECT i1 FROM staging_all where grd_id = 0100 and flag = 1 and i1 IS NOT NULL order by date desc limit 1)
                     ) as i1,
coalesce(
         	(SELECT i2 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1), 
         	(SELECT i2 FROM staging_all where grd_id = 0100 and flag = 1 and i2 IS NOT NULL order by date desc limit 1)
                     ) as i2
from staging_all 
where grd_id = 0100
and flag = 0
order by date asc 
limit 1;

This works well for us and as you can see gives us the choice of picking the earliest unread or the latest read row of data.

I then have a data point in the SQL data source called zflag that had this statement:

UPDATE GRDXF.staging_all SET flag= ?  WHERE grd_id=0100 AND flag=0 ORDER BY date ASC LIMIT 1;

The question mark is the integer parameter assigned via the point link which just says "Return 1;"

I tried putting that line in the SQL data source code as below without the parameter ?:

Select date, flag,
coalesce(
         	(SELECT an1 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1), 
         	(SELECT an1 FROM staging_all where grd_id = 0100 and flag = 1 and an1 IS NOT NULL order by date desc limit 1)
                     ) as an1,
coalesce(
         	(SELECT an2 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1), 
         	(SELECT an2 FROM staging_all where grd_id = 0100 and flag = 1 and an2 IS NOT NULL order by date desc limit 1)
                     ) as an2,
coalesce(
         	(SELECT i1 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1), 
         	(SELECT i1 FROM staging_all where grd_id = 0100 and flag = 1 and i1 IS NOT NULL order by date desc limit 1)
                     ) as i1,
coalesce(
         	(SELECT i2 FROM staging_all where grd_id = 0100 and flag = 0 order by date asc limit 1), 
         	(SELECT i2 FROM staging_all where grd_id = 0100 and flag = 1 and i2 IS NOT NULL order by date desc limit 1)
                     ) as i2

from staging_all 
where grd_id = 0100
and flag = 0
order by date asc 
limit 1;

UPDATE GRDXF.staging_all SET flag= 1  WHERE grd_id=0100 AND flag=0 ORDER BY date ASC LIMIT 1;

This works manually in the MySQL workbench but this failed in Mango. I think the reason it fails in Mango is that the SQL data source is a read only unless the data point has the Modify Table attribute ticked. So I can't add the update statement to the data source SQL code.

I did some digging in the forum from the time when IAS was going to remove the point links after version 3.7.7 and the only comment I could find was that point links could be accomplished in the Scripting data source but didn't give much more detail on how. I apologise if the "How" was in the early form but i didn't find it.

So I started to look there and first of all I can't see how to create a connection to the MySQL database as in the SQL data source.

Then as I am writing this I started to realise that I think I am supposed to use the Scripting data source to recreate the Source Point and the Target point that was chosen as drop downs in the Point Link. And if that IS the case, that is where I am stuck.
I am researching about using Ecmascript to use target and source points to provide a parameterised integer to my SQL data point that can modify the table.

Once I have that figured out I think our system will be back to normal.

Thank you for your time in reading this lengthy post. :)

[MattFox]

@BG Sounds like you're on the right track. Your point links in scripting datasources will work by using them as context points, one of which (the source) is used to update the context and you set the value of the target point with
target.set(source.value)

I'll need to think a little re your sql conundrum though...

Fox

[MattFox]

Are these database tables on the same sql server as mango? Or even in the mango Db?

May be able to leverage the sql console module...

Fox

[BG]

@MattFox Hello Matt,
Yes they are on the same server just different databases. I hadn't thought about copying the data to a table in the Mango MySQL Db.

Would that solve anything?

Cheers
Brian

[MattFox]

@BG I don't know if
USE DATABASE (try from the sql console)
will work (permissions in MySQL will play a role here), but I have written something to call the update method from that page via the java classes inside of mango JavaScript code:

 var dbProxy,JDBC;

var DatabaseProxy=com.serotonin.m2m2.db.MySQLProxy;
var sqlConsole = com.serotonin.m2m2.db.dao.SqlConsole;
if(!dbProxy)
{   
    dbProxy =  com.serotonin.m2m2.Common.getBean(DatabaseProxy.class);
    dbProxy.getContext();
}   

 if(!JDBC)
    JDBC = dbProxy.getJdbcTemplate();
    

var sqlClassInstance = new sqlConsole(JDBC);
var sqlQueryResult = JDBC.update(sqlquery);
com.serotonin.m2m2.Common.getBean(DatabaseProxy.class).terminate();

print (sqlQueryResult);

Fox

[BG]

@MattFox Hello Matt,
Sorry for my ignorance, but I ran your code in my SQL console and I got an error.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'var dbProxy,JDBC,sqlClassInstance; var DatabaseProxy=com.serotonin.m2m2.db...' at line 1

I am not sure if I was supposed to change any of the the values.

Cheers
Brian

[MattFox]

@BG sorry instantiated sqlClassInstance twice... Have edited the code above.
Please try again
Fox