citadel database missing trace

[Mike@DTSI]

Once again we have a problem.

Client is running 6.1 (Build 27).  Missing data from a few objects for 2 and a half months!  Suddenly reappears. No machine down tme, no errors, just gone.  Tried to rebuild database, no luck.

This is becoming a problem, as much as it would pain me to move to a new software platform we may have to at this point.  Constant database corruptions cannot happen when the clients need these reports for internal and other health type agencies.  

Citadel is a platform that simply is not reliable.  6.2 show the same errors as well.

Example problem.  Tanks record current, high and low values that are trended.  The low and high disappear but current stays.  And all of the points for the pump (control, monitor and status) are gone ont he same dates.  Then suddenly reappear later with no system restarts at all.

EDIT: We know the system was working, we log onto spreadsheets as well, the data is correct in there. But the report system is automated in lookout, not an excel type program.

Thanks, again,

Mike

Message Edited by Mike@DTSI on 06-19-2009 01:27 PM

[Ryan.S]

Mike,

Could you please upload your Citadel database and the server process to ftp://ftp.ni.com/incoming? 

If the database is too large, you can archive and upload part of the database with problem data.

Also tell me which object has the missing data in database.

[Mike@DTSI]

Sorry took so long, lots of work.

Uploaded to ftp.ni.com/incoming as TCWDdb070209.zip

Hypertrends show data, SQL query shows 0 for all of june and july, thus far.  Some months show some days as 0 others are correct.  SQL link is completely correct verified through a seperate SQL query generated same information.

Thanks

Mike

[Ryan.S]

Mike,

There are several traces disappear during some periods. For example, Server\Tank1\CP\LevelHigh, it has no data from 1/2 to 6/3. Actually it has stopped logging for many times, but this is the longest period. I notice that several other traces stopped logging at exact the same time. They all stopped, and then restarted together. It's really weird...

Then, I checked the alarm/event database. There was no obvious communication problem. So, the data quality is supposed to be good.

One thing I notice in the event database is the "DBFolderT" and "SYSFolderT". I see the events like "DBFolderT was changed to ... at the source". These events happened exactly everytime the logging resumed. For example, 6/3, 11/13, 8/18, etc. And it didn't happen from 1/2 to 6/3. Where are they connected? Are they changed by user input? From the event, they seem to be related to the database.

If your client can give us the process file, could you upload the process file to the ftp? I want to take a look at the process, and even try to run it for a while.

[Mike@DTSI]

Ok going to check now.  DBFolderT and SysFolderT are only connected to a datatable object, which is where the settings are saved.  Use does not change these.  It is part of the backup routine which is manually run. 

Thanks for the work so far, this is the only system that does it like this.  3 other systems use the same code base, functionally identical with no issues.

Mike

[Mike@DTSI]

Got some screenshots

1 - This is one week view from today (07/02-07/08).  The green line (the logical connection for the Control Bit (\\.\ServerWell4\BC)) shows data as being in the database for the hypertrend.(The gold and dark red lines are other wells, which have the same issues.)

2. This is the SQL pull using the datatable object.  Pulls from the Citadel database.  Looking at the days 07/01-07/08 it shows 0 for everything, but as you see in the last pic, hypertrend sees the data.  The SQL string:

SELECT MATH_ETM(TO_DISCRETE("TCWDSCADA/Server/Well4/BC")), MATH_starts(TO_DISCRETE("TCWDSCADA/Server/Well4/BC")), MATH_stops(TO_DISCRETE("TCWDSCADA/Server/Well4/BC")), MATH_ETM(TO_DISCRETE("TCWDSCADA/Server/Well4/BM")), MATH_starts(TO_DISCRETE("TCWDSCADA/Server/Well4/BM")), MATH_stops(TO_DISCRETE("TCWDSCADA/Server/Well4/BM")), MATH_etm(TO_DISCRETE("TCWDSCADA/Server/Well4/BS")), MATH_starts(TO_DISCRETE("TCWDSCADA/Server/Well4/BS")), MATH_stops(TO_DISCRETE("TCWDSCADA/Server/Well4/BS")) FROM IntData WHERE LocalTime BETWEEN '2009-7-2' AND '2009-8-2' and IntInterval ='1'

BC=Control, BM=Monitor, BS=Status.  The reason we pull the LocalTime from 2 days into month and after the month is because the citadel database returns values from the days previous.  Its wierd stuff.

July shows all 0.  April has 2 days (04/01-04/02) 

Well 4 in this example runs everyday.  weekends, weekdays, everyday.

DBFolderT and SysfolderT have no remote connections except for the remote property is linked to a datatable field.  the datatable is only loaded on system start, or manually which doesnt happen for this system. 

Thanks again

Mike

[Ryan.S]

Mike,

It looks like a different problem. You original problem was the stopped logging, but this one looks like a SQL problem. The data is logged, and being displayed on hypertrend, but SQL doesn't work. But I still want you to check the database by MAX. Open MAX, create a trace view of the Well4/BC, let's make sure the data is logged.

Do you see any active alarms about the SQL query in Lookout?

The query for April seems correct. I checked the database you gave me. The Well4/BC has no data after 4/2. So it is correct to get two results in April. At least, this SQL query is correct. The logging might have problem at that moment.

I tried to query your database with the same SQL statement. I'm able to get the data from 7-2. Is the SQL executed remotely? I mean, is it a remote query from different computer?

The weird date in SQL you mentioned is a known bug. It happens only when you use MATH function in SQL. The "BETWEEN" means "localtime >=xxx and localtime < xxx". The workaround for this problem is to use "localtime > xxx and localtime <xxx". Use ">" instead of ">=" will work.

[Ryan.S]

This is what I got from query.

It is from 6/2 to 8/2. I use "BETWEEN". So the result is from 6/1. I got 32 results, because the database you gave me has no data after 7/2. The last two lines are for 7/1 and 7/2. So, at least I think your query should have returned data for these two days.

Please look at the trace view in MAX. If the trace is there, your SQL qeury has something wrong, but it's weird that you are able to query April...

What if you query June?

[Mike@DTSI]

June is missing days as well.  No ODBC or SQL errors have been found.  Process works correctly on other systems.

Mike

[Ryan.S]

Is this the same database as the one you gave me?

In the database you gave me, there is no data before 6/3, but from 6/20 to 6/30, there are valid data. I'm able to query it.

Could you check the database by MAX? If the SQL functions well on your computer, the problem is in the logging. But the database you gave me does have the data after 6/20. If you cannot find data after 6/20 in your database, it will be really confusing. The data was logged, and then removed unexpectedly? Or the data was there, but failed to be queried?

Do you have CitadelControl object in your process?