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