Mango Persistent TCP publisher constantly resets connection

[MaP]

I have some screenshots of the receiver side Trace Logs here:

Constraint A foreign key constrant fails - DataSourceId References DataSources_Old

Any ideas how to solve this?

[MaP]

This post is deleted!

[MaP]

Also encountering the below error while trying to construct a new publisher using only mango data source points like CPU temp.

The published data points section just hangs with a loading image after seeing this error

[phildunlap]

dataSources_old? You were playing around in your database schema! I assume you created a table dataSources_new like dataSources, then renamed dataSources to dataSources_old and moved dataSources_new to dataSources. Is there a reason you would try this?

If there isn't data that matters, I would fix it by exporting your configuration, going into the mysql command line, creating a database "mango2" and redirecting the connection string in your env.properties to this new database, and then importing your configuration again after Mango boots.

If there is data that matters, you can update the foreign key constraints to reference the right table (grumble grumble) by running some SQL:

ALTER TABLE dataPoints DROP FOREIGN KEY dataPointsFk1;
ALTER TABLE dataPoints ADD CONSTRAINT dataPointsFk1 FOREIGN KEY (dataSourceId) REFERENCES dataSources(id);

[MaP]

Hi Phil,

I just imported another sites data sources config into the mango and did a find replace to ensure unique XIDs. I was pretty sure this was a brand-new mango but I cant be sure that it hasnt been toyed with.

Is there a way to do a factory reset and clear all previous data/config from a mango?

[MaP]

Hi Phil,

I entered the SQL queries listed in your last post and received the following errors:

[MaP]

[phildunlap]

That's okay, the foreign key was the bigger problem. I'm going to remove the index recommendation from that post. But, it also looks like you're using the H2 web console now, but from your SQL error I think you're using MySQL? If so, running those commands in the H2 console won't help, you would have to run them in your MySQL database.

Using H2, you can cause a new Mango instance by renaming your Mango/databases directory while Mango is off. if using MySQL, you should create another database via CREATE DATABASE newMangoDatabaseName and then modify the connection string to point to this database. You would also need to rename the Mango/databases directory to set the old data aside, if you are using the NoSQL module.

[MaP]

Do you mean using the inbuilt SQL console on the Mango web interface to try the queries in the previous post?

[phildunlap]

The error you posted relating to dataSources_old is a com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException so I'm pretty certain you were using MySQL. Is there a chance you overwrote your env.properties file?

[MaP]

I dont think so -

#    Copyright (C) 2014 Infinite Automation Systems Inc. All rights reserved.
#    @author Matthew Lohbihler

###############################################################################
# TO OVERRIDE VALUES IN THIS FILE...
#
# Do not change the values in this file, because when you upgrade your core 
# your changes will be overwritten. Instead, create a new file called 
# <MA_HOME>/overrides/properties/env.properties and override properties 
# there. The overrides directory will never be overwritten by an upgrade, so 
# your customizations will be safe.
# 
###############################################################################

# The port at which Mango Automation will listen for browser connections
web.port=80
# The host interface to which Mango Automation will bind and listen for new connections
#  0.0.0.0 is the special interface that will force a bind to all available interfaces
web.host=0.0.0.0

# Should Mango Automation open (if possible) a browser window when it starts up?
web.openBrowserOnStartup=true

# Default database settings, NOTE that on windows this must be an absolute path
db.type=h2
db.url=jdbc:h2:${ma.home}/databases/mah2
db.location=${ma.home}/databases/mah2
db.port=8081
db.username=
db.password=
#For web console
db.web.start=false
db.web.port=8091


#db.type=derby
#db.url=${ma.home}/databases/madb
#db.username=
#db.password=

#General Database Settings
db.pool.maxActive=100
db.pool.maxIdle=10
db.update.log.dir=${ma.home}/logs/
# setting to show query times in the logs as INFO
db.useMetrics=false

# MySQL database settings. Your MySQL instance must already be running and configured before this can be used.
#db.type=mysql
#db.url=jdbc:mysql://localhost/<your mysql schema name>
#db.username=<your mysql username>
#db.password=<your mysql password>

# Database settings for conversion. If the db.* settings point to a new database instance, and the convert type setting
# is set, Mango Automation will attempt to convert from the convert.db.* settings to the db.* settings
# Note that database conversions should not be performed in the same step as an upgrade. First upgrade, then convert.
convert.db.type=
convert.db.url=${convert.db.url}
convert.db.username=${convert.db.username}
convert.db.password=${convert.db.password}

# The location of the Mango Automation store from which to get license files.
store.url=http://store.infiniteautomation.com

# SSL control
ssl.on=false
ssl.port=8443
ssl.keystore.location=/location/to/keystore/file.jks
ssl.keystore.password=freetextpassword

# System time zone. Leave blank to use default VM time zone.
timezone=

#Rest API Configuration
rest.enabled=true
#For using other than timestamp
rest.customDateOutputFormat=YYYY-MM-dd HH:mm:ss.SSS Z
rest.customDateInputFormat=YYYY-MM-dd HH:mm:ss.SSS Z

#For rest API Documentation at /swagger/index.html
swagger.enabled=false
swagger.mangoApiVersion=v1


#Distributor Settings
distributor=IA

#Jetty Thread Pool Tuning
web.threads.msIdleTimeout=10000
web.threads.minimum=10
web.threads.maximum=200
web.websocket.pingTimeoutMs=10000

#Jetty JSP Configuration
# See here for options: http://www.eclipse.org/jetty/documentation/9.2.10.v20150310/configuring-jsp.html
web.jsp.development=true
web.jsp.genStringsAsCharArray=true
web.jsp.trimSpaces=false
web.jsp.classdebuginfo=false
web.jsp.supressSmap=true
web.jsp.compilerClassName=org.apache.jasper.compiler.JDTCompiler
web.jsp.compiler=modern
web.jsp.fork=false
web.jsp.keepgenerated=true

#Regex used to match serial ports so they show up in the menu
serial.port.linux.regex=((ttymUSB)[0-9]{1,3}|rs(485)-[0-9])
serial.port.linux.path=/dev/
serial.port.windows.regex=
serial.port.windows.path=
serial.port.osx.path=/dev/
serial.port.osx.regex=(cu|tty)..*

[MaP]

The persistent TCP receiver is using MySQL while the local mango is using H2 i believe. Will this cause the issue?

[phildunlap]

No, the TCP receiver will be using the database of the instance it is running on, and the database of the receiver and the publisher do not need to be the same. Have you been re-evaluating if your problem was solved? We have covered multiple paths to resolution, for each database.

If your receiver is the one with the SQL error, and it is on MySQL, that is where i was advising to fix the foreign key problem.

[MaP]

I recently raised a support ticket for this - please disregard the Support Ticket - we applied this fix to the enterprise mango and the problem seems to have gone .

We also applied this to the maintenance events table. I think during our upgrade from H2 -> MySQL this problem occurred.

Thankyou for your help and suggestions for getting this problem fixed.