Mango Persistent TCP publisher constantly resets connection

[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.