Persistent TCP not Automatically Reconnecting after Reboot

[mihairosu]

Module Core 3.5.6 on both
Module Persistent 3.5.6 on both

Our MangoES's Mango Persistent TCP connection is not working after Mango restarts,

It is connecting to our Enterprise install.

I have to manually go and Stop the publisher and then Start the publisher, which then fixes this problem.

I see this error at the top:

 Push to Main Historian: No route to host (Host unreachable) Acknowledge
 Push to Main Historian: No route to host (Write failed) Acknowledge

There are also some errors in ma.log but I'm not sure if it has anything to do with it:

ERROR 2019-02-04T12:26:41,807 (com.serotonin.m2m2.Lifecycle.runtimeManagerInitialize:806) - RuntimeManager initialization failure
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT ert.id,ert.data,ert.xid,ert.name,ert.userId,ert.editPermission,ert.reportReadPermission,ert.filename,ert.fileStoreName,ert.preventPurge,ert.sche$
SELECT ert.id,ert.data,ert.xid,ert.name,ert.userId,ert.editPermission,ert.reportReadPermission,ert.filename,ert.fileStoreName,ert.preventPurge,ert.schedule,ert.scheduleCron,ert.email,ert.zipData,ert.emailTemplateFilename,u.username FROM$
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:234) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1402) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:388) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:446) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:456) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at com.serotonin.db.DaoUtils.query(DaoUtils.java:246) ~[mango-3.5.6.jar:?]
        at com.serotonin.m2m2.db.dao.AbstractBasicDao.getAll(AbstractBasicDao.java:667) ~[mango-3.5.6.jar:?]
        at com.infiniteautomation.mango.excelreports.RTMDefinition.initialize(RTMDefinition.java:34) ~[?:?]
        at com.serotonin.m2m2.rt.RuntimeManagerImpl.startRTMDefs(RuntimeManagerImpl.java:253) ~[mango-3.5.6.jar:?]
        at com.serotonin.m2m2.rt.RuntimeManagerImpl.initialize(RuntimeManagerImpl.java:151) ~[mango-3.5.6.jar:?]
        at com.serotonin.m2m2.Lifecycle.runtimeManagerInitialize(Lifecycle.java:803) ~[ma-priv-3.5.6.jar:?]
        at com.serotonin.m2m2.Lifecycle.initialize(Lifecycle.java:353) ~[ma-priv-3.5.6.jar:?]
        at com.serotonin.m2m2.Main.main(Main.java:138) ~[ma-priv-3.5.6.jar:?]
Caused by: org.h2.jdbc.JdbcSQLException: Column "ERT.FILESTORENAME" not found; SQL statement:
SELECT ert.id,ert.data,ert.xid,ert.name,ert.userId,ert.editPermission,ert.reportReadPermission,ert.filename,ert.fileStoreName,ert.preventPurge,ert.schedule,ert.scheduleCron,ert.email,ert.zipData,ert.emailTemplateFilename,u.username FROM$
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.message.DbException.get(DbException.java:179) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.message.DbException.get(DbException.java:155) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:147) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.command.dml.Select.prepare(Select.java:839) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.command.Parser.prepareCommand(Parser.java:263) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.Session.prepareLocal(Session.java:578) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.engine.Session.prepareCommand(Session.java:519) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1204) ~[h2-1.4.196.jar:1.4.196]
        at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:73) ~[h2-1.4.196.jar:1.4.196]
        at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:433) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:376) ~[spring-jdbc-5.0.10.RELEASE.jar:5.0.10.RELEASE]
        ... 10 more

[phildunlap]

Hi mihairosu,

With regard to the stack trace, that would suggest to me that for some reason when you updated the Excel Reports module a database upgrade didn't run as intended. Perhaps this would resolve itself if you restarted, which could be the case if you restored a database backup this boot. Otherwise, you could run this SQL:

ALTER TABLE excelReportTemplates ADD COLUMN fileStoreName VARCHAR(100);
UPDATE excelReportTemplates SET fileStoreName='EXCEL_REPORT_TEMPLATES';
ALTER TABLE excelReportTemplates MODIFY COLUMN fileStoreName VARCHAR(100) NOT NULL;

and ensure the databaseSchemaVersion is now 3 (if it was 2 before running the SQL, then restarting stood a chance of fixing.

select * from systemSettings where settingname='databaseSchemaVersion.excelReports';
-- update if necessary
update systemSettings set settingvalue='3' where settingname='databaseSchemaVersion.excelReports';

You may also need to move your Excel Report templates from Mango/web/modules/excelReports/web/templates/ to Mango/filestore/EXCEL_REPORT_TEMPLATES/

---

To the question on the connection, I wonder, are you are publishing to the IP or the hostname?

The only thing I could think of that may cause that would be an exception in handling the NoRouteToHostException (which is handled, and a reconnect would be retried in exactly the same manner as restarting the publisher), which would have produced a message in the ma.log like,

Send thread PersistentSenderRT.SendThread failed with an exception

followed by a stack trace.

[mihairosu]

Hey Phil,

The first query:

ALTER TABLE excelReportTemplates ADD COLUMN fileStoreName VARCHAR(100);

is giving this error:

org.h2.jdbc.JdbcSQLException: Method is only allowed for a query. Use execute or executeUpdate instead of executeQuery; SQL statement: ALTER TABLE excelReportTemplates ADD COLUMN fileStoreName VARCHAR(100); [90002-196]

As far as your second point, we are connecting via IP.

I searched all of the ma.log files (older rotated ones) and could not find anything with "exception" and "PersistentSenderRT " in the same line (with grep).

Also, our other MangoES v2 has no problems at all with this (same module versions), so it's pretty weird.

[phildunlap]

Method is only allowed for a query.

You are using the SQL console? Use the submit update button, not the submit query button.

As far as your second point, we are connecting via IP.
I searched all of the ma.log files (older rotated ones) and could not find anything with "exception" and "PersistentSenderRT " in the same line (with grep).

Hmm. You could try to restart Mango and see.

I guess the only long term set-it-and-forget-it solution that occurs to me would be an email event handler on that event to restart the publisher.

var publisher = com.serotonin.m2m2.db.dao.PublisherDao.getInstance().getByXid("PUB_12345")
publisher.setEnabled(false);
com.serotonin.m2m2.Common.runtimeManager.savePublisher(publisher);
RuntimeManager.sleep(15000); //take a break in case we trigger the event again...
publisher.setEnabled(true);
com.serotonin.m2m2.Common.runtimeManager.savePublisher(publisher);
return CANCEL;

[mihairosu]

@phildunlap said in Persistent TCP not Automatically Reconnecting after Reboot:

ALTER TABLE excelReportTemplates ADD COLUMN fileStoreName VARCHAR(100);

Submit Update Button gives me this:

StatementCallback; bad SQL grammar [ALTER TABLE excelReportTemplates ADD COLUMN fileStoreName VARCHAR(100);]; nested exception is org.h2.jdbc.JdbcSQLException: Duplicate column name "FILESTORENAME"; SQL statement: ALTER TABLE excelReportTemplates ADD COLUMN fileStoreName VARCHAR(100); [42121-196]
 

Okay Phil. I'll keep an eye on it, see if I figure out anything.

[phildunlap]

Hmm... Might you have restarted or something? Your original post has an error about the column not existing, but that has an error about it existing. Something has changed!

[mihairosu]

Ah you know what, that error would have been prior to at least one restart I imagine, so you're right.

[mihairosu]

After today's reboot (for OS and module updates) everything is working well. It could also have been the previous time the modules were updated, as we only reboot these for updates.