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.

The reason I jump on mySQL 8 is because someone said it worked. I'll try an older version.

https://forum.mango-os.com/topic/4686/mysql-version/6?_=1614639710932

I thought this should be a straightforward process. I installed Mango 3.77 on a headless ubuntu server 20.04 and mysql 8+. Moved the mango files and config to new env and worked fine under H2. Then configured env.properties to include mySQL and convert.

I get this error. Duplicate column? Any ideas?

INFO 2021-03-01T09:21:02,784 (com.serotonin.m2m2.Lifecycle.loadLic:670) - License file not found
INFO 2021-03-01T09:21:02,870 (com.serotonin.m2m2.db.BasePooledProxy.initializeImpl:36) - Initializing pooled connection
manager
WARN 2021-03-01T09:21:05,381 (com.serotonin.m2m2.db.upgrade.DBUpgrade.checkUpgrade:98) - Upgrading 'core' from 1 to 2
INFO 2021-03-01T09:21:05,383 (com.serotonin.m2m2.db.upgrade.DBUpgrade.createUpdateLogOutputStream:169) - Writing upgrad
e log to /opt/mango/logs/com.serotonin.m2m2.db.upgrade.Upgrade1.log
FATAL 2021-03-01T09:21:05,578 (com.serotonin.m2m2.db.AbstractDatabaseProxy.initialize:185) - Exception initializing data
base proxy: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [alter table publishers
add column rtdata longblob; ]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate
column name 'rtdata'
com.serotonin.ShouldNeverHappenException: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL gr
ammar [alter table publishers add column rtdata longblob; ]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSy
ntaxErrorException: Duplicate column name 'rtdata'
at com.serotonin.m2m2.db.upgrade.DBUpgrade.checkUpgrade(DBUpgrade.java:104) ~[mango-3.7.7.jar:?]
at com.serotonin.m2m2.db.upgrade.DBUpgrade.checkUpgrade(DBUpgrade.java:36) ~[mango-3.7.7.jar:?]
at com.serotonin.m2m2.db.AbstractDatabaseProxy.initialize(AbstractDatabaseProxy.java:171) ~[mango-3.7.7.jar:?]
at com.serotonin.m2m2.Lifecycle.databaseInitialize(Lifecycle.java:732) ~[ma-priv-3.7.7.jar:?]
at com.serotonin.m2m2.Lifecycle.initialize(Lifecycle.java:285) ~[ma-priv-3.7.7.jar:?]
at com.serotonin.m2m2.Main.main(Main.java:159) ~[ma-priv-3.7.7.jar:?]
Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [alter table publishers a
dd column rtdata longblob; ]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate c
olumn name 'rtdata'
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.ja
va:93) ~[spring-jdbc-5.1.7.RELEASE.jar:5.1.7.RELEASE]

Hello,
I can test publish/subscribe via python but can't make the subscribe work on Mango. Attached is the data source config. What am I missing?

))

ERROR 2021-02-01T21:09:38,258 (com.infiniteautomation.m2m2.mqtt.client.MqttClientDataSourceRT.connect:712) - ERROR initializing MQTT Client
com.serotonin.ShouldNeverHappenException: @ "DISCONNECTING" don't know how to handle event: "CONNECT_ASYNC"
        at com.infiniteautomation.m2m2.mqtt.client.MqttClientDataSourceRT.handleBokerConnectionevent(MqttClientDataSourceRT.java:264) ~[?:?]
        at com.infiniteautomation.m2m2.mqtt.client.MqttClientDataSourceRT.connect(MqttClientDataSourceRT.java:710) ~[?:?]
        at com.infiniteautomation.m2m2.mqtt.client.MqttClientDataSourceRT.access$1200(MqttClientDataSourceRT.java:44) ~[?:?]
        at com.infiniteautomation.m2m2.mqtt.client.MqttClientDataSourceRT$4.run(MqttClientDataSourceRT.java:756) ~[?:?]
        at java.util.TimerThread.mainLoop(Timer.java:556) ~[?:?]
        at java.util.TimerThread.run(Timer.java:506) ~[?:?]
Exception in thread "Timer-2" com.serotonin.ShouldNeverHappenException: @ "DISCONNECTING" don't know how to handle event: "INITIALIZATION_FAILED"
        at com.infiniteautomation.m2m2.mqtt.client.MqttClientDataSourceRT.handleBokerConnectionevent(MqttClientDataSourceRT.java:264)
        at com.infiniteautomation.m2m2.mqtt.client.MqttClientDataSourceRT.connect(MqttClientDataSourceRT.java:715)
        at com.infiniteautomation.m2m2.mqtt.client.MqttClientDataSourceRT.access$1200(MqttClientDataSourceRT.java:44)
        at com.infiniteautomation.m2m2.mqtt.client.MqttClientDataSourceRT$4.run(MqttClientDataSourceRT.java:756)
        at java.base/java.util.TimerThread.mainLoop(Timer.java:556)
        at java.base/java.util.TimerThread.run(Timer.java:506)
ERROR 2021-02-01T21:09:39,080 (com.infiniteautomation.m2m2.mqtt.client.MqttClientDataSourceRT$2.onFailure:703) - MQTT Connection not established
ERROR 2021-02-01T21:10:30,899 (com.infiniteautomation.m2m2.mqtt.client.MqttClientDataSourceRT$2.onFailure:703) - MQTT Connection not established

@robmalone Mango did not even what to start up with JDK 1.14. I did recover the old jdk I was using on the crashed system and will revert to that.

thx

I'm getting this as well.

I restored from a windows crash and using the recommended AdoptOpenJDK JDK. I was using the old JDK from Oracle prior. I now get this error and I know the port works fine as I can communicate using a modbus master tool.

I am using the rest API in my user module, components, etc. and works fine. However, I recently read on a post an example using a call on the pointvalues object. e.g.

PointValues.getPointValuesForXid(...)

I tried it and works fine and was wondering this officially documented?

pjc

Thanks. It will probably end up a user component.

pjc

I would like to retrieve the historical values stored via cron/meta-data point that are run once an hr and per month for a chart. When I perform a mysql query, I get what I expect. e.g. if there are 5 entries I get 5 entries.
MySql query returns what I expect.
e.g.

select pointValue, from_unixtime(ts/1000) from pointvalues where datapointid=474

via ma-point-values I get the 5 + a current value which is the same as the last log value. I am using these to seed a chart and always get an extra point that mirrors that of what was saved via cron.

<ma-point-values point="point1" values="point1Values" from="dateBar.from" to="dateBar.to" rollup="{{dateBar.rollupType}}" rollup-interval="{{dateBar.rollupIntervals}} {{dateBar.rollupIntervalPeriod}}">
</ma-point-values>

<p>There are {{point1Values.length}} rows to display.</p>


<md-table-container>
    <table md-table>
        <thead md-head>
        <tr>
            <th md-column>TimeStamp</th>
            <th md-column>Point Value</th>
        </tr>
        </thead>
        <tbody md-body>
        <tr ng-repeat="value in point1Values">
            <td md-cell>{{value.timestamp | maMoment:'format':'ll LTS'}}</td>
            <td md-cell>{{value.value}}</td>
        </tr>
        </tbody>
    </table>
</md-table-container>

This is an output on the per month points. I don't want the Dec 3 to return. mySql only has the 1 entry.

Thanks. It worked.

How does the parent item drop down list get populated in the create menu item from the dashboard designer? via the register a menu item in a user module? I am trying to create a menu bar at the top using the suggestions in forum and nothing but the canned entries appear.

I;m obviously missing something.

pjc

I'm updating once 1 minute. Did not seem to bad. So far no issue with mysql.

I converted to mysql and it runs a lot faster. Will convert to no-sql when the POC is done.

I'm getting out of memory errors which I never received before. What are the conditions when one has to tune the memory allocation? This app is relatively small.

Two things changed - Updated to latest version & added some Charting, Charting seems to be the issue. If I don't load the screen then system runs as before.

JRE 1.8.0_171-b11/JDK 1.8.0_162
Windows 10 Pro 16GB/i7

Latest mango
Loaded chart screen and walked away
Out of memory
Exported DB and Reimported
Size down to ~680MB
Loaded chart screen and walked way again
h2 db at mem fault time time ~1.4GB
restarted instance
h2 db at 1.9GB
95 datapoints enabled 146 configued

Partial Log output

    org.springframework.core.convert.support.StreamConverter@451068cf
        org.springframework.core.convert.support.StringToArrayConverter@18a9d44f
        org.springframework.core.convert.support.StringToCollectionConverter@1917c302

   CharacterEncodingFilter.FILTERED=true
   org.springframework.security.web.csrf.CsrfFilter@3f45dfec.FILTERED=true
   javax.servlet.http.HttpServletResponse=org.springframework.security.web.header.HeaderWriterFilter$HeaderWriterResponse@12fb9bc6
   org.springframework.security.web.FilterChainProxy.APPLIED=true
   _csrf=org.springframework.security.web.csrf.DefaultCsrfToken@592cbe52
   __spring_security_filterSecurityInterceptor_filterApplied=true
   org.springframework.web.servlet.DispatcherServlet.THEME_SOURCE=WebApplicationContext for namespace 'REST_DISPATCHER-servlet': startup date [Thu Oct 25 16:23:20 MDT 2018]; parent: Root WebApplicationContext
   com.serotonin.m2m2.web.mvc.spring.security.RateLimitingFilter.FILTERED=true
   etagFilter.FILTERED=true
   org.springframework.web.servlet.HandlerMapping.pathWithinHandlerMapping=/v2/point-values/time-period/DP_3d092d65-2bcd-4c20-b411-0880fa10802e
   org.springframework.security.web.csrf.CsrfToken=org.springframework.security.web.csrf.DefaultCsrfToken@592cbe52
   org.springframework.web.servlet.HandlerMapping.uriTemplateVariables={xid=DP_3d092d65-2bcd-4c20-b411-0880fa10802e}
   org.springframework.web.servlet.DispatcherServlet.THEME_RESOLVER=org.springframework.web.servlet.theme.FixedThemeResolver@78b025a4
':
java.lang.OutOfMemoryError: GC overhead limit exceeded
Exception in thread "Log4j2-TF-5-AsyncLoggerConfig--2" java.lang.OutOfMemoryError: GC overhead limit exceeded
Exception in thread "high-pool-2-thread-6" java.lang.OutOfMemoryError: GC overhead limit exceeded
Exception in thread "Scheduler-959055079" java.lang.OutOfMemoryError: GC overhead limit exceeded
Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler in thread "Scheduler-959055079"

Exception in thread "qtp818859466-607" java.lang.OutOfMemoryError: GC overhead limit exceeded

Chart Code t0 see what can be done out of the box.

        <ma-get-point-value point-xid="DP_5ced8162-7dc9-4946-ac53-4ae8118e476d" point="pH_PV"></ma-get-point-value>
    <ma-get-point-value point-xid="DP_3d092d65-2bcd-4c20-b411-0880fa10802e" point="EC_PV"></ma-get-point-value>
    <ma-get-point-value point-xid="DP_23418117-43a1-4d9b-b4fb-651c6cb3a5da" point="AMBT_PV"></ma-get-point-value>
    <ma-get-point-value point-xid="DP_e89fe385-4758-4861-8c16-f8cb6577f280" point="TUBT_PV"></ma-get-point-value>
        <ma-get-point-value point-xid="DP_808d90e3-ccb8-43b9-ac0a-e52267ded197" point="CO2_PV"></ma-get-point-value>
    <ma-get-point-value point-xid="DP_5a3863c8-4ba6-4ca0-a22d-d319d1626cf6" point="RELH_PV"></ma-get-point-value>



<ma-point-values point="pH_PV" values="pHValues" from="dateBar.from" to="dateBar.to" rollup="{{dateBar.rollupType}}" rollup-interval="{{dateBar.rollupIntervals}} {{dateBar.rollupIntervalPeriod}}">
</ma-point-values>
<ma-point-values point="EC_PV" values="ECValues" from="dateBar.from" to="dateBar.to" rollup="{{dateBar.rollupType}}" rollup-interval="{{dateBar.rollupIntervals}} {{dateBar.rollupIntervalPeriod}}">
</ma-point-values>




<ma-serial-chart style="height: 300px; width: 100%" series-1-values="pHValues" series-1-title="AT-001"  series-1-color="yellow" series-2-values="ECValues"  series-2-title="AT-002" series-2-color="green" series-2-axis="right" legend="true" balloon="true"
options="{valueAxes:[{minimum:'0', axisColor:'yellow', color:'yellow', title: 'pH'}, {axisColor:'green', color:'green', title:'EC'}]}">
</ma-serial-chart>



<ma-point-values point="AMBT_PV" values="AMBTValues" from="dateBar.from" to="dateBar.to" rollup="{{dateBar.rollupType}}" rollup-interval="{{dateBar.rollupIntervals}} {{dateBar.rollupIntervalPeriod}}">
</ma-point-values>
<ma-point-values point="TUBT_PV" values="TUBTValues" from="dateBar.from" to="dateBar.to" rollup="{{dateBar.rollupType}}" rollup-interval="{{dateBar.rollupIntervals}} {{dateBar.rollupIntervalPeriod}}">
</ma-point-values>




<ma-serial-chart style="height: 300px; width: 100%" series-1-values="AMBTValues" series-1-title="TT-101"  series-1-color="yellow" series-2-values="TUBTValues"  series-2-title="TT-001" series-2-color="green" series-2-axis="right" legend="true" balloon="true"
options="{valueAxes:[{minimum:'0', axisColor:'yellow', color:'yellow', title: 'Ambient (C)'}, {axisColor:'green', color:'green', title:'Tub (C)'}]}">
</ma-serial-chart>

<ma-point-values point="CO2_PV" values="CO2TValues" from="dateBar.from" to="dateBar.to" rollup="{{dateBar.rollupType}}" rollup-interval="{{dateBar.rollupIntervals}} {{dateBar.rollupIntervalPeriod}}">
</ma-point-values>
<ma-point-values point="RELH_PV" values="RELHValues" from="dateBar.from" to="dateBar.to" rollup="{{dateBar.rollupType}}" rollup-interval="{{dateBar.rollupIntervals}} {{dateBar.rollupIntervalPeriod}}">
</ma-point-values>




<ma-serial-chart style="height: 300px; width: 100%" series-1-values="CO2TValues" series-1-title="AT-102"  series-1-color="yellow" series-2-values="RELHValues"  series-2-title="AT-101" series-2-color="green" series-2-axis="right" legend="true" balloon="true"
options="{valueAxes:[{minimum:'0', axisColor:'yellow', color:'yellow', title: 'CO2 ppm'}, {axisColor:'green', color:'green', title:'Rel Humidity'}]}">
</ma-serial-chart>

Thanks. I

Thanks for the reply.
I removed the need for a controller albeit at the expense of not exactly what I wanted. I was access to the mdp-time-picker as setup under under mango so I can get events. I also did no see how to include moment in my module. Tried different ways but did not work.

I ended up with this for now.

<div layout="row" flex="" layout-wrap="" layout-align="space-between">
    <ma-get-point-value point-xid="DP_c373b0f5-9962-42da-8f4e-732e4928549d" point="DY_103_OFT_CV"></ma-get-point-value>
    <ma-get-point-value point-xid="DP_c5b8682a-5404-4cff-8440-71b178164cc9" point="DY_103_ONT_CV"></ma-get-point-value>
    <ma-get-point-value point-xid="DP_4de82242-f4df-4144-8d81-4657084a714a" point="DY_103_OFT_SP"></ma-get-point-value>
    <ma-get-point-value point-xid="DP_01f2db2e-2aa4-466d-a9c5-f06e4420a3cb" point="DY_103_ONT_SP"></ma-get-point-value>
    <md-card-content>
        Growing Chamber
        <div layout="column">
            <div layout="row">
                <mdp-time-picker mdp-placeholder="Off Time" mdp-format="hh:mm A" mdp-auto-switch="true" ng-model="offTime">
                    <p>Current Off Time: {{DY_103_OFT_CV.value * 1000 | maMoment:'format':'hh:mm A'}}</p>
                    <md-button ng-show="offTime != null" ng-click="DY_103_OFT_SP.setValue(offTime.valueOf() / 1000)" class="md-raised">Update</md-button>
                </mdp-time-picker>
                <mdp-time-picker mdp-placeholder="On Time" mdp-format="hh:mm A" mdp-auto-switch="true" ng-model="onTime">
                    <p>Current Off Time: {{DY_103_ONT_CV.value * 1000 | maMoment:'format':'hh:mm A'}}</p>
                    <md-button ng-show="onTime != null" ng-click="DY_103_ONT_SP.setValue(onTime.valueOf() / 1000)" class="md-raised">Update</md-button>
                </mdp-time-picker>
            </div>
        </div>

core 3.4.5 + mangoApi 3.4.6
Hello I'm experimenting with the mdp-time-picker where I store the light on/off times in arduino based controller as a Unix EPOCH. Works fine so far via the watch list. I have CV (current value) and SP (setpoint). I slapped together some very temporary code and everything works except when I click "test restore" all values restore accept for the HH::MM value in in the time-picker. When one drills down on it, the underlying info is updated and if I hit Ok then it gets refreshed. see attached screen shot

not so nice HTML stuff

<div layout="row" flex="" layout-wrap="" layout-align="space-between">
    <ma-get-point-value point-xid="DP_c373b0f5-9962-42da-8f4e-732e4928549d" point="DY_103_OFT_CV"></ma-get-point-value>
    <ma-get-point-value point-xid="DP_c5b8682a-5404-4cff-8440-71b178164cc9" point="DY_103_ONT_CV"></ma-get-point-value>
    <ma-get-point-value point-xid="DP_4de82242-f4df-4144-8d81-4657084a714a" point="DY_103_OFT_SP"></ma-get-point-value>
    <ma-get-point-value point-xid="DP_01f2db2e-2aa4-466d-a9c5-f06e4420a3cb" point="DY_103_ONT_SP"></ma-get-point-value>
    <md-card-content>
        Growing Chamber
        <div layout="column" ng-controller="lightController" ng-init="init(1535557320,1535557920)">
            <p>{{DY_103_OFT_CV.value}} PO:{{pendingOffTime}}
            </p>
            <p>{{persistOffTime()}}</p>
            <md-button ng-click="init2(DY_103_OFT_CV.value)" class="md-raised"> Test Restore</md-button>
            <mdp-time-picker mdp-placeholder="Off Time" mdp-format="hh:mm" mdp-open-on-click mdp-auto-switch="true" ng-model="pendingOffTime"></mdp-time-picker>
            <md-button ng-click="DY_103_OFT_SP.setValue(persistOffTime())" class="md-raised">Set to {{persistOffTime()}}</md-button>
            <p>END GC</p>
        </div>

userModule.js - not so nice code.

define(['angular', 'require'], function(angular, require) {
'use strict';

var userModule = angular.module('userModule', ['maUiApp']);

userModule.component('myComponent', {
    bindings: {
        name: '@?'
    },
    template: '<span>Hello {{$ctrl.name}}</span>'
});

// d
userModule.controller('lightController', ['$scope', function($scope) {
  $scope.init = function(aOffTime, aOnTime)
  {

 	$scope.currentOffTime = new Date ( aOffTime * 1000);
   $scope.pendingOffTime = new Date ( aOffTime * 1000);
  };

//$scope.currentOffTime = new Date(0);
//$scope.pendingOffTime = new Date(0);
  $scope.persistOffTime = function() {  return ( $scope.pendingOffTime.valueOf() / 1000 ) }

$scope.init2 = function( aOffTime)
{
	$scope.pendingOffTime.setTime(aOffTime * 1000 );
	//return( $scope.pendingOffTime.toLocaleTimeString() );

//return( "co:" + aOffTime * 1000 );	
}
}]);

return userModule;

}); // define

Thanks. I missed that one. Will look to use to avoid the points hierarchy,

pjc

Ok. Fair enough as a tag based model provides flexibility in forming queries. Is there some info on what that will look like? Are they assigned at a data point level or span beyond that? Do you have an ETA for that functionality?

pjc

Thanks. Those scenarios look plausible and will explore them.

pjc