When I set up SCADA signals with a 24-hour period, it works and I am able to view SCADA historical results in both the SCADA Signal preview menu or SCADA elements in the model. However, if I change to another time frame (such as 48 hours), WaterGEMS crashes suddenly and without any error messages. I thought at first that it was due to the complexity of the model and the large number of signals, but the problem is reproducible with an empty model and only a single signal. Version information: 10.01.01.04 32-bit The following procedure works without issue: Create new file, with change tracking off. Open SCADA Signal Create new DataSource OLEDB Connection String: *entered appropriate connection string* Advanced Options: Date/Time Prefix/Suffix: double quote (") Field Name Prefix/Suffix: space ( ) Test Connection succeeds Set the Table Name, Signal, Value and Timestamp Fields Set to Historical Set Custom SQL Statements: Available Signals: set rowcount=0 select Tagname from ihTags Historical: set rowcount=0, samplingmode=rawbytime select Tagname,Value,TimeStamp,Quality from ihRawData where (@requestedsignals) and ((TimeStamp>=08/20/2018) and (TimeStamp<=08/21/2018)) and Quality = "Good NonSpecific" Time Range: set rowcount=0, samplingmode=rawbytime select Min(TimeStamp),Max(TimeStamp) from ihRawData Select SCADA Signals: select a single signal from the list Click Okay, and view the selected Signal in the preview window, press Refresh The data points for the 24 hour period display correctly. However, if the exact same procedure is followed, except the second timestamp is changed to (TimeStamp<=08/22/2018), when you click Okay from entering the Custom SQL Statements, the program freezes for about 30 seconds, then crashes completely. There is no warning message or 'crash report' dialogue. If you change the date range to 8/21/2018 - 8/22/2018, it works fine, but a date range of 8/21/2018 - 8/23/2018 crashes. So there doesn't seem to be a problem with a specific date, just with ranges greater than 24 hours.

Hi Stephen, I have been playing around with the GE Historian some more. I was able to reproduce the same DB_E_ABORTLIMITREACHED issue, albeit without any crash. That seems to be a side effect, like you found, of specifying a date range that results in too much data being returned. The amount of data is by default limited to 5000 rows, but the part of your query where you have set rowcount=0 , turns off that limit. I would guess then that keeping some kind of limit in place is a reasonable idea. Now, I'm not sure whether things have changed in different GE versions, but the version I am using seems to be working a lot better if I use a query like this. set rowcount=5000, samplingmode=rawbytime select Tagname,Value,TimeStamp,Quality from ihRawData where Tagname=(@requestedsignals) and (TimeStamp>=@startdatetime) and (TimeStamp<=@enddatetime) and Quality = "Good NonSpecific" I'm getting data back and it's coming back very fast now. Some issues I am still looking into... Trying to specify multiple tags in GE isn't working out for me or at least I can't find any suitable syntax that will work with multiple @requestedsignals. Maybe the LIKE keyword is a possibility to try to reduce number of returned rows to a minimum. Worst case, @requestedsignals could be left out of the query. The samplingmode in GE seems to answer way too many points at too fine a time increment (one every second). I'm trying to figure out how to decrease the resolution of the data, but not having a lot of luck at this point. It's like nothing that is written in the GE documentation has any effect. Given, however, that I am dealing with simulated data every 1 second, maybe this is not an issue for you? I'll post again if I find out anything new. Wayne.

This discussion has been locked.

You can no longer post new replies to this discussion. If you have a question you can start a new discussion

WaterGEMS crashes if SCADA Datasource set for other than 24 hours

Offline Stephen Jackson over 5 years ago

When I set up SCADA signals with a 24-hour period, it works and I am able to view SCADA historical results in both the SCADA Signal preview menu or SCADA elements in the model. However, if I change to another time frame (such as 48 hours), WaterGEMS crashes suddenly and without any error messages.

I thought at first that it was due to the complexity of the model and the large number of signals, but the problem is reproducible with an empty model and only a single signal.

Version information: 10.01.01.04 32-bit

The following procedure works without issue:

Create new file, with change tracking off.
Open SCADA Signal
Create new DataSource
1. OLEDB
2. Connection String: *entered appropriate connection string*
3. Advanced Options:
  1. Date/Time Prefix/Suffix: double quote (")
  2. Field Name Prefix/Suffix: space ( )
4. Test Connection succeeds
Set the Table Name, Signal, Value and Timestamp Fields
Set to Historical
Set Custom SQL Statements:
1. Available Signals: set rowcount=0 select Tagname from ihTags
2. Historical: set rowcount=0, samplingmode=rawbytime select Tagname,Value,TimeStamp,Quality from ihRawData where (@requestedsignals) and ((TimeStamp>=08/20/2018) and (TimeStamp<=08/21/2018)) and Quality = "Good NonSpecific"
3. Time Range: set rowcount=0, samplingmode=rawbytime select Min(TimeStamp),Max(TimeStamp) from ihRawData
Select SCADA Signals: select a single signal from the list
Click Okay, and view the selected Signal in the preview window, press Refresh
1. The data points for the 24 hour period display correctly.

However, if the exact same procedure is followed, except the second timestamp is changed to (TimeStamp<=08/22/2018), when you click Okay from entering the Custom SQL Statements, the program freezes for about 30 seconds, then crashes completely. There is no warning message or 'crash report' dialogue.

If you change the date range to 8/21/2018 - 8/22/2018, it works fine, but a date range of 8/21/2018 - 8/23/2018 crashes. So there doesn't seem to be a problem with a specific date, just with ranges greater than 24 hours.

Top Replies

Parents

0 Offline Jesse Dringoli Wed, Nov 28 2018 9:43 PM
Hi Stephen,

Can you provide the details of the crash dialog? If it is the standard Bentley crash dialog, you should see an icon in the lower-left corner, which will open the text of the error.

Does this only happen with custom SQL statements?

Regards,

Jesse Dringoli
Technical Support Manager, OpenFlows
Bentley Communities Site Administrator
Bentley Systems, Inc.
Cancel
Vote Up 0 Vote Down

Cancel

Reply

0 Offline Jesse Dringoli Wed, Nov 28 2018 9:43 PM
Hi Stephen,

Can you provide the details of the crash dialog? If it is the standard Bentley crash dialog, you should see an icon in the lower-left corner, which will open the text of the error.

Does this only happen with custom SQL statements?

Regards,

Jesse Dringoli
Technical Support Manager, OpenFlows
Bentley Communities Site Administrator
Bentley Systems, Inc.
Cancel
Vote Up 0 Vote Down

Cancel

Children

0 Offline Stephen Jackson Thu, Nov 29 2018 8:25 AM in reply to Jesse Dringoli
Unfortunately there is no crash dialogue. WaterGEMS just exits straight to desktop. I was able to find the 'View SCADA Log' files in /AppData/Local/Temp. I'll post those as two following comments with some notes. I also found the crash dumps in /AppData/Local/CrashDumps - I could send you those if it would be helpful.

I can't connect without custom SQL statements. There are no entries in the 'Select SCADA Signals' dialogue if I haven't set the custom SQL. (That's true whether I don't select Historical/Real Time, or if I select either of those two options.)
Cancel
Vote Up 0 Vote Down

Cancel
0 Offline Stephen Jackson Thu, Nov 29 2018 8:29 AM in reply to Stephen Jackson
First Log:

In this test, I followed the procedure above, using a 24 hour query, and tested that it gave results by Refreshing in the Preview SCADA Signal window. Then I went back into the data source and simply adjusted 8/21/2018 to 8/22/2018, and it froze and then crashed when I pressed Okay. I've marked the part in the error log where I tested the 8/21/2018 value without issue.

*** FileLogger initialized with a maximum size of: '1,048,576 bytes [1.0 MB]'. ***
2018-11-29 08:12:17.03: [INF]: License context set by IdahoApplicationModel.
2018-11-29 08:12:43.54: [INF]: Connection string is empty.
2018-11-29 08:12:43.76: [INF]: Connection string is empty.
2018-11-29 08:12:49.27: [INF]: Connection string is empty.
2018-11-29 08:12:52.12: [INF]: Connection string is empty.
2018-11-29 08:13:09.35: [INF]: Attempting to open 'SCADA Datasource - 1' connection.
2018-11-29 08:13:09.63: [INF]: 'SCADA Datasource - 1' connection opened.
2018-11-29 08:13:09.73: [INF]: Attempting to open 'SCADA Datasource - 1' connection.
2018-11-29 08:13:09.85: [INF]: 'SCADA Datasource - 1' connection opened.
2018-11-29 08:13:55.30: [INF]: Setting AvailableSignalsSelectStatement for 'SCADA Datasource - 1' to 'set rowcount=0 select Tagname from ihTags'.
2018-11-29 08:13:55.30: [INF]: Setting HistoricalSelectStatement for 'SCADA Datasource - 1' to 'set rowcount=0, samplingmode=rawbytime select Tagname,Value,TimeStamp,Quality from ihRawData where (@requestedsignals) and ((TimeStamp>=08/20/2018) and (TimeStamp<=08/21/2018)) and Quality = "Good NonSpecific"'.
2018-11-29 08:13:55.30: [INF]: Setting HistoricalDateTimeRangeSelectStatement for 'SCADA Datasource - 1' to 'set rowcount=0, samplingmode=rawbytime select Min(TimeStamp),Max(TimeStamp) from ihRawData'.

*** The code above worked without issue. Below, I just opened the datasource back up and changed 8/21/2018 to 8/22/2018. ***

2018-11-29 08:15:16.66: [INF]: Attempting to open 'SCADA Datasource - 1' connection.
2018-11-29 08:15:16.77: [INF]: 'SCADA Datasource - 1' connection opened.
2018-11-29 08:15:43.80: [ERR]: Unable to get column meta data for 'SCADA Datasource - 1'. SQL statement 'set rowcount=0, samplingmode=rawbytime select Tagname,Value,TimeStamp,Quality from ihRawData where ((TimeStamp>=08/20/2018) and (TimeStamp<=08/21/2018)) and Quality = "Good NonSpecific"'. Message: ''IhOLEDB.iHistorian.1' failed with no error message available, result code: DB_E_ABORTLIMITREACHED(0x80040E31).'.
2018-11-29 08:15:43.80: [WRN]: GetColumnDataType obtained a null ColumnInfo instance for signal: 'Value'.
2018-11-29 08:16:14.70: [ERR]: Unable to get column meta data for 'SCADA Datasource - 1'. SQL statement 'set rowcount=0, samplingmode=rawbytime select Tagname,Value,TimeStamp,Quality from ihRawData where ((TimeStamp>=08/20/2018) and (TimeStamp<=08/21/2018)) and Quality = "Good NonSpecific"'. Message: ''IhOLEDB.iHistorian.1' failed with no error message available, result code: DB_E_ABORTLIMITREACHED(0x80040E31).'.
2018-11-29 08:16:23.29: [INF]: Setting HistoricalSelectStatement for 'SCADA Datasource - 1' to 'set rowcount=0, samplingmode=rawbytime select Tagname,Value,TimeStamp,Quality from ihRawData where (@requestedsignals) and ((TimeStamp>=08/20/2018) and (TimeStamp<=08/22/2018)) and Quality = "Good NonSpecific"'.
2018-11-29 08:17:11.46: [ERR]: Unable to get column meta data for 'SCADA Datasource - 1'. SQL statement 'set rowcount=0, samplingmode=rawbytime select Tagname,Value,TimeStamp,Quality from ihRawData where ((TimeStamp>=08/20/2018) and (TimeStamp<=08/22/2018)) and Quality = "Good NonSpecific"'. Message: ''IhOLEDB.iHistorian.1' failed with no error message available, result code: DB_E_ABORTLIMITREACHED(0x80040E31).'.
Cancel
Vote Up +1 Vote Down

Cancel
0 Offline Stephen Jackson Thu, Nov 29 2018 8:31 AM in reply to Stephen Jackson
Second Log:

In this test, I first tried to connect without using Custom SQL Statements, and the dialogue for Select SCADA Signals had no signals available. Then I put in the Custom SQL Statements with the date range 8/20/2018 - 8/22/2018 and it crashed once I pressed Okay on the Custom SQL Dialogue.

*** FileLogger initialized with a maximum size of: '1,048,576 bytes [1.0 MB]'. ***
2018-11-29 07:55:22.96: [INF]: License context set by IdahoApplicationModel.
2018-11-29 07:56:25.58: [INF]: Connection string is empty.
2018-11-29 07:56:25.82: [INF]: Connection string is empty.
2018-11-29 07:56:40.47: [INF]: Connection string is empty.
2018-11-29 07:56:42.49: [INF]: Connection string is empty.
2018-11-29 07:56:44.32: [INF]: Connection string is empty.
2018-11-29 07:56:46.07: [INF]: Attempting to open 'SCADA Datasource - 1' connection.
2018-11-29 07:56:46.27: [INF]: 'SCADA Datasource - 1' connection opened.
2018-11-29 07:56:46.35: [INF]: Attempting to open 'SCADA Datasource - 1' connection.
2018-11-29 07:56:46.47: [INF]: 'SCADA Datasource - 1' connection opened.
2018-11-29 07:56:57.03: [ERR]: Failed to acquire data reader for 'SCADA Datasource - 1'. SQL statement: 'select distinct [Tagname] from [ihRawData]'. Message: ''IhOLEDB.iHistorian.1' failed with no error message available, result code: DB_E_NOTABLE(0x80040E37).'
2018-11-29 07:57:00.93: [INF]: Attempting to open 'SCADA Datasource - 1' connection.
2018-11-29 07:57:01.04: [INF]: 'SCADA Datasource - 1' connection opened.
2018-11-29 07:57:01.04: [ERR]: Failed to acquire data reader for 'SCADA Datasource - 1'. SQL statement: 'select distinct [Tagname] from [ihRawData]'. Message: ''IhOLEDB.iHistorian.1' failed with no error message available, result code: DB_E_NOTABLE(0x80040E37).'
2018-11-29 07:57:28.87: [ERR]: Failed to acquire data reader for 'SCADA Datasource - 1'. SQL statement: 'select distinct [Tagname] from [ihRawData]'. Message: ''IhOLEDB.iHistorian.1' failed with no error message available, result code: DB_E_NOTABLE(0x80040E37).'
2018-11-29 07:59:21.78: [INF]: Connection string is empty.
2018-11-29 08:01:15.24: [INF]: Connection string is empty.
2018-11-29 08:01:15.41: [INF]: Connection string is empty.
2018-11-29 08:01:19.98: [INF]: Connection string is empty.
2018-11-29 08:01:22.27: [INF]: Connection string is empty.
2018-11-29 08:01:38.66: [INF]: Attempting to open 'SCADA Datasource - 1' connection.
2018-11-29 08:01:38.79: [INF]: 'SCADA Datasource - 1' connection opened.
2018-11-29 08:01:38.87: [INF]: Attempting to open 'SCADA Datasource - 1' connection.
2018-11-29 08:01:38.99: [INF]: 'SCADA Datasource - 1' connection opened.
2018-11-29 08:02:24.16: [INF]: Setting AvailableSignalsSelectStatement for 'SCADA Datasource - 1' to 'set rowcount=0 select Tagname from ihTags'.
2018-11-29 08:02:24.16: [INF]: Setting HistoricalSelectStatement for 'SCADA Datasource - 1' to 'set rowcount=0, samplingmode=rawbytime select Tagname,Value,TimeStamp,Quality from ihRawData where (@requestedsignals) and ((TimeStamp>=08/20/2018) and (TimeStamp<=08/22/2018)) and Quality = "Good NonSpecific"'.
2018-11-29 08:02:24.16: [INF]: Setting HistoricalDateTimeRangeSelectStatement for 'SCADA Datasource - 1' to 'set rowcount=0, samplingmode=rawbytime select Min(TimeStamp),Max(TimeStamp) from ihRawData'.
2018-11-29 08:03:12.21: [ERR]: Unable to get column meta data for 'SCADA Datasource - 1'. SQL statement 'set rowcount=0, samplingmode=rawbytime select Tagname,Value,TimeStamp,Quality from ihRawData where ((TimeStamp>=08/20/2018) and (TimeStamp<=08/22/2018)) and Quality = "Good NonSpecific"'. Message: ''IhOLEDB.iHistorian.1' failed with no error message available, result code: DB_E_ABORTLIMITREACHED(0x80040E31).'.
Cancel
Vote Up +1 Vote Down

Cancel
0 Offline Stephen Jackson Thu, Nov 29 2018 9:26 AM in reply to Stephen Jackson
From these logs, it looks to me like three odd things are happening:

When the date range is 24 hours, it does not appear to try to execute the HistoricalSelectStatement as soon as you press Okay from the Custom SQL dialogue, which makes sense because you would only want to execute that when you are looking at a particular signal (if it is trying to execute the statement, it didn't make any note of that fact in the log). But when the date range is 48 hours, it does try to execute that statement, and gets an error from Historian. I don't know why it is trying to execute the statement at this point - it seems like you wouldn't want to do that until you were looking at a specific signal in either the Preview window or a SCADA Element Graph.

The error from Historian is due to the fact that the query WaterGEMS is sending is dropping off the " where (@requestedsignals) " part of the query, so it is not filtered down to a specific Tagname. The number of returned results exceeds Historian's capacity, and it gives a query error.

WaterGEMS error handling is not catching this error from Historian, so rather than alerting the user that too many results have been returned, or at least providing a 'WateGEMS has stopped working' dialogue, it is doing a hard crash.
Cancel
Vote Up +1 Vote Down

Cancel
0 Offline Stephen Jackson Thu, Nov 29 2018 10:04 AM in reply to Stephen Jackson
Aha - closing in on an answer! It looks like what WaterGEMS is doing is trying to verify the HistoricalSelectStatement as soon as the Custom SQL dialogue is closed, and the way it is verifying it is by dropping off the @requestedsignals filter. This means it is getting results for *all* Tagnames simultaneously. When the date range is 24 hours, this verifies correctly. But when the date range is larger, there are too many results, and Historian returns an exception that 'Execution stopped because a resource limit was reached.' WaterGEMS isn't prepared for that, and so it crashes.

That means the 24/48 hour limit is specific to my particular Historian database (that happens to be the tipping point with the current number of tags and values in our database), but the general problem is that WaterGEMS is trying to verify a query while stripping it of important filters, which risks getting too many results back.

I've verified this using a Python connection to Historian, using the query in the logs that is causing an error. If I execute the query ' set rowcount=0, samplingmode=rawbytime select Tagname,Value,TimeStamp,Quality from ihRawData where ((TimeStamp>=08/20/2018) and (TimeStamp<=08/21/2018)) and Quality = "Good NonSpecific"' with the 24 hour range, I get results, if I execute it with a 48 hour window, I get an error that a resource limit was reached.
Cancel
Vote Up +1 Vote Down

Cancel