Column 'ts' not found Error on executing scheduled report

[KMKAR]

I'm getting a weird behavior on my new ScadaLTS implementation. Details: Windows 10.0.18362.1010, Java 1.8.0_261, Tomcat 7.0.105, ScadaLTS 2.2.1, MariaDB 10.5. Modbus TCP connection with 10 coil/holding reg configured.

I've a report running hourly that exports all points information for the last hour changes/variations. The report data is always empty, locally and externally (through email).

I put all log settings to DEBUG and found this on mango.log every hour when the report schedule runs: INFO 2020-09-02 01:00:00,006 (com.serotonin.mango.rt.maint.work.ReportWorkItem.queueReport:73) - Queuing report with id 1 INFO 2020-09-02 01:00:00,024 (com.serotonin.mango.rt.maint.work.ReportWorkItem.queueReport:73) - Queuing report with id 2 INFO 2020-09-02 01:00:00,039 (com.serotonin.mango.rt.maint.work.ReportWorkItem.queueReport:95) - Queued report with id 1, instance id 83 INFO 2020-09-02 01:00:00,039 (com.serotonin.mango.rt.maint.work.ReportWorkItem.execute:106) - Running report with id 1, instance id 83 INFO 2020-09-02 01:00:00,039 (com.serotonin.mango.rt.maint.work.ReportWorkItem.queueReport:95) - Queued report with id 2, instance id 84 ERROR 2020-09-02 01:00:00,085 (com.serotonin.mango.rt.maint.BackgroundProcessing$1.run:59) - Error in work item org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select min(rd.ts), max(rd.ts) from reportInstancePoints rp join reportInstanceData rd on rp.id=rd.reportInstancePointId where rp.reportInstanceId=? ]; SQL state [S0022]; error code [0]; Column 'ts' not found.; nested exception is java.sql.SQLException: Column 'ts' not found. at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:639) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:668) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:688) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:696) at org.scada_lts.mango.service.ReportService.setReportTime(ReportService.java:278) at org.scada_lts.mango.service.ReportService.runReport(ReportService.java:271) at com.serotonin.mango.db.dao.ReportDao.runReport(ReportDao.java:530) at com.serotonin.mango.rt.maint.work.ReportWorkItem.execute(ReportWorkItem.java:141) at com.serotonin.mango.rt.maint.BackgroundProcessing$1.run(BackgroundProcessing.java:56) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: java.sql.SQLException: Column 'ts' not found. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860) at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1076) at com.mysql.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:2757) at org.apache.commons.dbcp.DelegatingResultSet.getLong(DelegatingResultSet.java:247) at org.scada_lts.mango.service.ReportService$1.processRow(ReportService.java:282) at org.springframework.jdbc.core.JdbcTemplate$RowCallbackHandlerResultSetExtractor.extractData(JdbcTemplate.java:1493) at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:651) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:589) ... 12 more INFO 2020-09-02 01:00:00,085 (com.serotonin.mango.rt.maint.work.ReportWorkItem.execute:106) - Running report with id 2, instance id 84 DEBUG 2020-09-02 01:00:00,741 (freemarker.log.Log4JLoggerFactory$Log4JLogger.debug:81) - report/reportChart.ftl[en_US,Cp1252,parsed] using cached since C:\Program Files (x86)\Apache Software Foundation\Tomcat 7.0\webapps\ScadaLTS\WEB-INF\ftl\report\reportChart.ftl didn't change. INFO 2020-09-02 01:00:00,757 (com.serotonin.mango.rt.maint.work.ReportWorkItem.execute:235) - Finished running report with id 2, instance id 84

Checked and the reportInstanceData.ts column exists on DB and has data properly populated.

Any ideas? Thanks

[Limraj]

Thanks for detailed bug reporting.

1. Does the database run on your query: select min(rd.ts), max(rd.ts) from reportInstancePoints rp join reportInstanceData rd on rp.id = rd.reportInstancePointId where rp.reportInstanceId = 83 ?
2. Is the error repeatable?
3. You can check in the context.xml file in tomcat_home / conf / that you have the correct scheme set (<Resource (...) url=?>), most likely you have multiple schemas and in this file context is set with the reportInstanceData table but without the "ts" column. If it is correct here, you can have the same table created on the default user without the ts column.

[Limraj]

This error occurs if "Incetion" or "latest" is selected. So everything's fine with the base configuration. Could you please confirm that it is about "Specific dates" and checking one of these checkboxes?

[Limraj]

For query (green): select min(rd.ts), max(rd.ts) from reportInstancePoints rp join reportInstanceData rd on rp.id = rd.reportInstancePointId where rp.reportInstanceId = 83 The ResultSet type rs variable does not contain a "ts" field, so the call to rs.getLong("ts") (red) fails with this error. This can be easily corrected by adding a ts column to the query.

[KMKAR]

1. Does the database run on your query: select min(rd.ts), max(rd.ts) from reportInstancePoints rp join reportInstanceData rd on rp.id = rd.reportInstancePointId where rp.reportInstanceId = 83 ?
2. Is the error repeatable?
3. You can check in the context.xml file in tomcat_home / conf / that you have the correct scheme set (<Resource (...) url=?>), most likely you have multiple schemas and in this file context is set with the reportInstanceData table but without the "ts" column. If it is correct here, you can have the same table created on the default user without the ts column.

First, thanks for your promptly response!

1. DB does run properly that query: select min(rd.ts), max(rd.ts) from reportInstancePoints rp join reportInstanceData rd on rp.id = rd.reportInstancePointId where rp.reportInstanceId = 83; /* Affected rows: 0 Found rows: 1 Warnings: 0 Duration for 1 query: 0.000 sec. */ min(rd.ts) max(rd.ts) "1598818833093" "1599009488667"

2. Yes, it's happening every time the report runs (hourly)

3. I don't have such configuration on tomcat_home/conf/context.xml I searched without success for reportInstanceData in all .xml files. Can you share more details about it?

Answering the other comment you shared:

This error occurs if "Incetion" or "latest" is selected. So everything's fine with the base configuration. Could you please confirm that it is about "Specific dates" and checking one of these checkboxes?

I also have a major issue while using "Inception"/"Latest" which consists in that the report shows directly as "Failed". Do you want me to open an issue for this one also? In this initial issue report #1365 I'm using the option "Relative to report time" / "Previous 1 hour":

I'm not available to run ScadaLTS on docker for now, so if any commit/build is needed to fix these issues I'll need a .war release to test it in my test environment. Thank you very much in advance.

[Limraj]

1. Please confirm if the problem actually occurs at the relative report time, I could not bring it to this setting, and the error you are writing about is closely related to the "specific date".

2. 'I also have a major issue while using "Inception"/"Latest" which consists in that the report shows directly as "Failed". Do you want me to open an issue for this one also?' It is with this setting that this error is associated. However, if you think it's something else, create a issue, we'll check. :)

3. We will be releasing the scada next week, can you wait until then? A war will be prepared with the new version 2.3.

4. However, if you can create an issue regarding dockerm issues, we'll look at that too.

Regards.

[KMKAR]

1. Please confirm if the problem actually occurs at the relative report time, I could not bring it to this setting, and the error you are writing about is closely related to the "specific date".

You're correct. It's using the relative report time. My configuration for the issue that I'm seeing related to the 'ts' column is the following:

2. 'I also have a major issue while using "Inception"/"Latest" which consists in that the report shows directly as "Failed". Do you want me to open an issue for this one also?'
   It is with this setting that this error is associated. However, if you think it's something else, create a issue, we'll check.  :)

Using Specific Dates / Inception-Latest in my opinion is another issue as you mentioned before in a previous comment.

3. We will be releasing the scada next week, can you wait until then? A war will be prepared with the new version 2.3.

Sure! Let me know is you want more info or some specific testing on my side.

Thanks

[Limraj]

Okay, I see that in "sepcific dates" you have "Inception" and "Latest" marked in gray, could you do something like this: 1) Check "specific dates"; 2) Uncheck the two checkboxes "Inception" and "Latest"; 3) Save the report; 4) Switch the configuration to "relative time" 5) Save the report;

And let me know if this problem occurs now? :) The problem that you are writing about is related to "specific dates", but additionally we would have a problem with the fact that despite "Specific dates" unchecked, it takes the "Specific dates" configuration.

[KMKAR]

1. Check "specific dates";
2. Uncheck the two checkboxes "Inception" and "Latest";
3. Save the report;
4. Switch the configuration to "relative time"
5. Save the report;

And let me know if this problem occurs now? :) The problem that you are writing about is related to "specific dates", but additionally we would have a problem with the fact that despite "Specific dates" unchecked, it takes the "Specific dates" configuration.

You're right, after doing the steps on my test report that runs every hour and exports all points information for the last hour changes/variations I now can see the data variation, both locally and externally (through email):

I'll document this on the implementation notes that I'm creating for all future reports, even if its grayed out, I'll disable the inception/latest check-boxes.

Still I'll wait for the 2.3 release on .war file I'll re-do all the test again.

[KMKAR]

I can confirm after more testing that using the last configuration based on the guideline shared by @Limraj to disable the checkboxes for inception/latest, the hourly report is now working correctly.

Still... On mango.log I can see the same ERROR message every time the hourly report is executed:

ERROR 2020-09-06 17:00:00,842 (com.serotonin.mango.rt.maint.BackgroundProcessing$1.run:59) - Error in work item org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select min(rd.ts), max(rd.ts) from reportInstancePoints rp join reportInstanceData rd on rp.id=rd.reportInstancePointId where rp.reportInstanceId=? ]; SQL state [S0022]; error code [0]; Column 'ts' not found.; nested exception is java.sql.SQLException: Column 'ts' not found.

[Limraj]

Could you please send us a full log? :)

[KMKAR]

:) 1365_log_20200906.zip

In mango.log this is latest occurrence of the error: ERROR 2020-09-06 21:00:00,104 (com.serotonin.mango.rt.maint.BackgroundProcessing$1.run:59) - Error in work item

Let me know if I can help in any other way. Thanks

[Limraj]

Thanks, do you have any other reports set up? In any case, this bug is in the same place, the fix should fix it.

[KMKAR]

Thanks, do you have any other reports set up?

Just only one on all my testing in order to not generate inconsistent logging:

In any case, this bug is in the same place, the fix should fix it.

Great! I'll wait then for the 2.3 .war release. Thanks!

[Limraj]

Hi, unfortunately I don't have good information, this fix is ​​related to one of the more important features and we need more time to test it. Can you wait for version 2.4? :)

[KMKAR]

Hi, unfortunately I don't have good information, this fix is ​​related to one of the more important features and we need more time to test it. Can you wait for version 2.4? :)

Sure. In the meantime I'll set up the 2.3.1 release in another separated machine.

[Limraj]

@KMKAR Hi, is this functionality already operational?