19 Replies Latest reply on Nov 20, 2014 3:10 PM by Gregor

    Unexpected behavior of AFSDK when searching for AFElements by path

    eignert

      I’m currently encountering a rather strange behavior using the AFSDK (Version 2.6.1.6238).

       

      The following C# code shows you an example:

       

       

       
      //// Search AFElement by GUID
      AFElement testElement = AFElement.FindElement(new PISystems()["zbghpi01-af"], new Guid("7e41c6ac-6e5d-11e4-9b38-b8ac6f2902a1"));
      Console.WriteLine("testElement.UniqueID = {0}", testElement.UniqueID);
      Console.WriteLine("testElement.GetPath() = {0}", testElement.GetPath());
      
      //// Search the same element by its path
      List<string> searchPaths = new List<string>() { testElement.GetPath() };
      AFKeyedResults result = AFElement.FindElementsByPath(searchPaths, null);
      Console.WriteLine("result.Count = {0}", result.Count); 
      

      First we search an AFElement by the GUID and output the UniqueID and the Path to the Console to make sure it was found. Afterwards, we use the path of the found AFElement to search for it again and output the number of found elements.
      Usually we would expect the count to always have a value of 1. In our case we get the output 0 which means the element is not found by its own path!? -> please see attachment.

       

      The strange thing is that we have AFElements in our AFDatabase that have been created some time ago (with AF 2.5) and those AFElements are being found correctly with the same code.

       

      Additional information: The Security on all elements in the AF-DB is the same and when testing this I had Admin-Rights to the AF-Server / Database. PSE shows all elements correctly.

       

      I've sent this issue to the Techsupport (#594519) and got the information the AFSDK is supported through vCampus... So... does anyone have an Idea what could go wrong here?

       

      BR, Thomas

        • Re: Unexpected behavior of AFSDK when searching for AFElements by path
          eignert
          Sorry for the formatting... I don't know why but when I go into the Edit-View for the post the text looks just fine (maybe because I copied the text over from the original mail to the techsupport). Also the preview works as expected. (Maybe IE11 problems?) As I can't delete and recreate it - would someone from the admins have the possibility to please check if it's possible to reformat the post into a human readable form ;-) ?
            • Re: Unexpected behavior of AFSDK when searching for AFElements by path
              Marcos Vainer Loeff

              Hello Thomas,

               

              I have fixed some issues in your code snippet and added an IF in order to find out if the correct element paths are being used In this case, the only input of the function would be the element path. Please don't forget to change the server name and element path to the element which is having issues.

               

               

               
                          AFElement myElement = AFObject.FindObject(@"\\SERVERNAME\AFSDK Test\Cdt158") as AFElement;
              
                          //// Search AFElement by GUID 
                          AFElement testElement = AFElement.FindElement(new PISystems()["SERVERNAME"], myElement.ID);
                          Console.WriteLine("testElement.UniqueID = {0}", testElement.UniqueID);
                          Console.WriteLine("testElement.GetPath() = {0}", testElement.GetPath());
              
                          if(myElement.GetPath()==testElement.GetPath())
                          {
                              Console.WriteLine("Both elements have the same path.");
                          }
                          else
                          {
                              Console.WriteLine("Error: Both elements don't have the same path.");
                          }
              
                          //// Search the same element by its path 
                          List<string> searchPaths = new List<string>() { testElement.GetPath() }; 
                          AFKeyedResults<string, AFElement> result = AFElement.FindElementsByPath(searchPaths, null); 
                          Console.WriteLine("result.Count = {0}", result.Count);
              

               

               

              Let us know the results!

                • Re: Unexpected behavior of AFSDK when searching for AFElements by path
                  eignert

                  Hello Marcos,

                   

                  thanks for the quick response. Please see the screenshot attached.

                   

                  Your example shows that both elements have the same path - but the count at the very end is still 0.
                  Seems like the .FindObject() and .FindElementsByPath() methods are behaving differently... For now using the AFObject.FindObject() should work for me but it would be really interesting why this happens?

                   

                  Thanks Thomas

                    • Re: Unexpected behavior of AFSDK when searching for AFElements by path
                      jstarnes

                      If you use the following code, what is the error returned?

                       

                      IDictionary<string, string> errors;

                       

                      AFNamedCollectionList<AFElement> result = AFElement.FindElementsByPath(searchPaths, null, out errors);

                       

                      Console.WriteLine("result.Count = {0}", result.Count);

                       

                      Also, if you look at the PIFD database in SQL, can you tell me how many rows are in the afelementpathcachelist table?

                       

                      Whenever an element is created/updated/deleted, we add an entry to the afelementpathcache table. We then later process that table and update the list of paths accordingly. Thus, I'm wondering if this table has stopped processing.

                        • Re: Unexpected behavior of AFSDK when searching for AFElements by path
                          eignert

                          Hi,

                           

                          the error I'm getting is

                           

                          Object for path '\\ZBGHPI01-af\Wacker.PI.WebServices\BD-L\BGH\VAE\T001\SILO T001AB312' was not found.

                           

                          The afelementpathcachelist-Table in the PIFD-Database has 228 rows in it.. so you think processing of this table has stopped? What might cause this and how can we get this running again?

                           

                          Thanks
                          Thomas

                            • Re: Unexpected behavior of AFSDK when searching for AFElements by path
                              jstarnes

                              In order to determine the root cause, you would need to take a backup of the PIFD database in SQL and submit it to tech support for examination. To start processing rows again, you can just edit an element name and check it in. If it still doesn't start processing rows, you can manually call the stored procedure [usp_AFElementCache_refresh] in SQL. Note that stored procedure will completely rebuild all the paths for every element and thus could take awhile for a large system. Also, until the count in the afelementpathcachelist table is zero, you will not be able to find all possible paths with the FindElementsByPath method.

                                • Re: Unexpected behavior of AFSDK when searching for AFElements by path
                                  eignert

                                  Ok I've executed the Stored Procedure b/c this issue was kind of urgent...The table afelementpathcachlist is now empty and my queries seem to work.

                                   

                                  Just for my understanding: What component is usually executing this SP? Is the AF Server process doing this frequently or is the SP executed every time another SP is called in the SQL DB etc. ?

                                   

                                  Thanks a lot for the quick help!

                                    • Re: Unexpected behavior of AFSDK when searching for AFElements by path
                                      jstarnes

                                      Certain operations (not all) such as updating an element name will trigger a background thread in the AFServer to execute a similar stored procedure called [usp_AFElementCache_refresh_inc], which processes some rows in the afelementpathcachelist table.

                                        • Re: Unexpected behavior of AFSDK when searching for AFElements by path
                                          eignert

                                          So today I rebooted our AFServer to make sure we don't have unwanted side effects...

                                           

                                          I created new AFElements and got new entries in the afelementpathcachelist table which won't disappear.
                                          Looking into the eventlog of the AFServer brought up the following error message:

                                           

                                          Function Void HierarchyPathTask() at line: 4629 in file c:\Builds\713\AF\AF 2.6\Sources\Server\Common\Service Implementation\Shared\Service.cs

                                          System.Data.SqlClient.SqlException (0x80131904): Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the like operation.
                                             at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
                                             at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
                                             at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
                                             at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
                                             at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
                                             at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
                                             at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
                                             at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
                                             at OSIsoft.AF.Service.AFService.HierarchyPathTask() in c:\Builds\713\AF\AF 2.6\Sources\Server\Common\Service Implementation\Shared\Service.cs:line 4645
                                          ClientConnectionId:9c002a74-c442-491a-85b4-dbd499db9480

                                           

                                          After seeing this I tried executing the Stored Procedure usp_AFElementCache_refresh_inc manually which resulted in the same error (Debugprint parameter was set to 1):

                                           

                                          2014-11-19 08:50:32.623
                                          new elementref
                                          2014-11-19 08:50:32.630
                                          2014-11-19 08:50:32.683
                                          Level = 4
                                          Msg 468, Level 16, State 9, Procedure usp_afelementcache_refresh_inc_newelementref, Line 86
                                          Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the like operation.

                                           

                                          I've checked some other AFServers that we have running and couldn't find any similar issue. All servers were set up in exactly the same way and have the same versions.
                                          Can you please advise what we can do to fix this?

                                            • Re: Unexpected behavior of AFSDK when searching for AFElements by path
                                              pcombellick

                                              This error is likely caused by a mismatch in the collation between the PIFD database and the MASTER database.  Since the AF code and install logic don't set the collation, the collation of the PIFD database is inherited from the collation of the MASTER database when the PIFD database is created.  I am aware of two situations that can cause this mismatch: (1) the PIFD database was created on a different instance of SQL Server, which had a collation different than the current SQL Server instance, and then moved to the current SQL Server instance, or (2) the collation of the master database was changed after the PIFD database was created.

                                               

                                              Run this T-SQL command from SSMS to determine the collation of your Master, Tempdb, and PIFD database:

                                               

                                              SELECT SERVERPROPERTY('Collation') AS [Master Collation],

                                               

                                              DATABASEPROPERTYEX('tempdb','collation') [TempDB COLLATION],

                                               

                                              DATABASEPROPERTYEX('PIFD','collation') [PIFD COLLATION];

                                               

                                              I recommend that you contact OSIsoft Tech Support for assistance in resolving this conflict.

                                               

                                              Regards,

                                               

                                              Paul

                                               

                                              AF Dev

                                                • Re: Unexpected behavior of AFSDK when searching for AFElements by path
                                                  eignert

                                                  The result of those queries explains some things: We've moved our AF databases to a new SQL Server and it seems as if our datacenter team used a different collation for the server installation. We moved our databases to the new server and now there is a mismatch between server (and also system databases) collation and the PIFD collation.

                                                   

                                                  As far as I could see the [usp_AFElementCache_refresh_inc] uses temp-tables that are created with the server collation. Performing a like operation with this temp-table and a table from the DB gives the error.

                                                   

                                                  Now that we've identified the problem I'll contact techsupport to get advice on how to deal with this situation.