We’ve developed some resources to help you work effectively from home during COVID-19 Click to learn more

Rest API - Some endpoints returns KeyNotFoundException

Hi,

I have very strange behavior in one onsite customer environment.

The problem just exist in their production environment. (they have several other environments in place where this does not exist)

Environment:

SuperOffice 8.5 R10 on Windows Sever 2016 with only .net 4.8 installed (not 3.5/.3.0/2.0)
SQL 2016

To the problem: 

the customer has built some own integrations (in this case an internal API) that uses the endpoint:

/sales/api/v1/list/category

It should return the available categories in the database but instead we get:

{

  "Error": true,

  "ErrorType": "KeyNotFoundException",

  "Message": "The given key was not present in the dictionary.",

  "ErrorSource": "mscorlib"

}

the same goes for:

/sales/api/v1/list/business
and also the one without any list specified 
/sales/api/v1/list

other list endpoints work

for instance:

/sales/api/v1/list/country
/sales/api/v1/list/<UDL_ownListName>

So the API seams to work but not for certain list?? 

How can this be? What can be wrong? 

I have compared the database in prod with test-environment (they all comes from the same design-database)

So list-ids, names, descriptions etc are identical?

I have suspected something with .Net , Windows Server and also if it could be some corruption in the database.
But have not so far found anything yet. Since the /sales/api/v1/list does not work, it migt be that some other list has gone corrupt?

I would be glad if someone could point me in some kind direction here??

As I have one envirionment with only .net 4.8 (and API works there) I guess .net 3.5/3.0/2.0 should not be needed?

Would be an Idea to run all or some debsetup ConsitencyChecks?
Repair the SuperOffice WEB/net server installation ?

Could it be something in the SQL server side? 

Best regards

/Anders

  

RE: Rest API - Some endpoints returns KeyNotFoundException

Try using the MDO list endpoint?

Might work

Av: Christian Mogensen 26. apr 2020

RE: Rest API - Some endpoints returns KeyNotFoundException

Hi Christian,

Thanks for your answer.

did a quick test and its seams that /sales/api/v1/mdolist/category

returns:
{
"TableRight": null,
"FieldProperties": {},
"Id": 10,
"Name": "Ej definierad",
"ToolTip": "Kategori ej definierad för denna kund (default)",
"Deleted": false,
"Rank": 8,
"Type": "category",
"ChildItems": [],
"IconHint": "",
"ColorBlock": 0,
"ExtraInfo": "",
"StyleHint": "",
"FullName": ""
}

vs
/sales/api/v1/List/Category/items (in an working environment)

{
"TableRight": null,
"FieldProperties": {},
"Id": 10,
"Name": "Ej definierad",
"Tooltip": "Kategori ej definierad för denna kund (default)",
"Deleted": false,
"UdListDefinitionId": 97,
"Rank": 8
}

So quite similar.

In this case readOnly MDO list might work as an workaround, but it does not solve my issues and probably not an acceptable solution / answer by the customer. ( since it is working in 3 other dev and staging environments , I think I must solve why the standard api/v1/list/category/items endpoint not working in this 2 node production environment but works in lower dev/test/staging environments (staging also a 2 node setup and working fine).

I am out of ideas where to look next. If enabling all loggin I can the only thing thats show up in the SO-WEB log is an internal-500 error when calling the non working endpoints. that might indicate some missconfiguration at the prod-servers that is not in the other environments. But I guess it also could be someting in the database causing the the problem (no Idea what it could be though).

Have a good evening!

/Anders

 

  

Av: Anders Larsson 26. apr 2020

RE: Rest API - Some endpoints returns KeyNotFoundException

Hi

I have been able to determine that the Application Server and the installation of .Net etc is ok in the non working environment.

I could determine this by temporarly connect a working datbase to the non working environment and also the other way around eg in a working environment connect the non working datbase and emediatley get the same error.

The leads me to say that ther are something corrupt etc in the non working database. (also that SQL-server has another patch level, will try to get that patch, I doubt it helps but still stranger things has happened before)

What could cause the endpoints:

/api/v1/list (should return a list o lists that is available ,but that does not work)

and 

/api/v1/list/category/items
/api/v1/list/business/items 

to return:

{
"Error": true,
"ErrorType": "KeyNotFoundException",
"Message": "The given key was not present in the dictionary.",
"ErrorSource": "mscorlib"
}

How to find what causing this in a database, where to start looking? 
Does anyone haves suggestions where to start looking I would be interested to get any tip.

One interesting observation I could see was 

/api/v1/list/country - not working

but

/api/v1/list/country/items - working ??

Also UD-Lists works 

So what can cause this behavior,
could some SQL server setting affect this?
Language setting etc?

My next move will be to investigate the possibillity to create an sdb of the non functional database and restore that into an Isolated working environment to se if error exist after restore to another SQL-server. (If not working then its 100% sure something within the database causing the issues in the API-Endpoints)

 

Question:

Are the underlying svc webservices used by the REST-API under the hood? 
Could there be anything there pointing to incorrect version of the that layer in the database ?
eg /Sevices87 /services88

Best regards

/Anders

Av: Anders Larsson 28. apr 2020

RE: Rest API - Some endpoints returns KeyNotFoundException

The REST APIs call the WebServices under the hood, but they use the latest version at the time of compile.

If you have a remote configuration, you could potentially connect to an older Services version on the back end.

 

Av: Christian Mogensen 28. apr 2020

RE: Rest API - Some endpoints returns KeyNotFoundException

Ok, thanks Christian.

In this case I a quite sure this is not the problem since my test from yesterday points to something inside the database causing this. the Servers are not using remote net-server config. they are installed as standalone solutions.

Other endpoints seems to work as they should like:
/api/v1/contact/<contact_id> 

/api/v1/person/<person_id> 

/api/v1/user/<associate_id> 

/api/v1/schema/<tableName> 

to name a few.

The problem seems to be related to the /api/v1/list and underling lists where some work but others don't.

That is really disturbing. (would be nice if the system in future versions, returned errocodes could hint bit mor then the generic:
Possible?  

{
"Error": true,
"ErrorType": "KeyNotFoundException",
"Message": "The given key was not present in the dictionary.",
"ErrorSource": "mscorlib"
}

/Anders

 

Av: Anders Larsson 28. apr 2020

RE: Rest API - Some endpoints returns KeyNotFoundException

Hi

An additional finding I just did:

If using the LIST-Id in the no working environment

/sales/api/v1/list/97/items

/sales/api/v1/list/97

It returns the values from that list with out problem? 

So it seems like there are problem accessing the lists by their "API-names" ? 

Are the endpoint "list-names" defined in the SuperOffice database somewhere? 

Best regards

/Anders

Av: Anders Larsson 29. apr 2020

RE: Rest API - Some endpoints returns KeyNotFoundException

Hi,

I have now been able to solve this issue..

The problem was in the datbase, in the udlistdefinition-table. 

On the list-Item: Id 116 (SW:"E-postmall";US:"E-mail template";NO:"E-postmal")

the field "ListTableId" had a negativ value of -31073 , very strange. (correct value should be 30003, accoriding to other working databases at this specific customer) 

How in the earth could this had happen?  

I susspects that something went wrong in the 8.5 SR4 upgrade. (that took place back in 2019), judging from registration date.

I can not confirm this, since the API has not been used since recently.

Anyone had this problem, with fault values for new lists introduced in SuperOffice ? 

Another Question:
Before there were a section in datbase documentation , tables by number ?
I can not find that anymore? Anyone how knows how to find documentation for tables by their tableId? 

/Best regards

Av: Anders Larsson 29. apr 2020

RE: Rest API - Some endpoints returns KeyNotFoundException

Hi Anders,

Been following your saga all along but not able to help as it's not something I've ever heard of before. I find it fascinating you held on to the idea that the problem resided in the database data, which ultimately you were able to confirm and fix yourself. I know that must of felt very rewarding after the fact. Great work! 

If you have the TTL table records that go back that far, you might be able to narrow it down to who/what actually changed it.

Something I can help you out with though is the search table by number... that's in the docs, just now exposed as a link that changes the view. 

Hope this helps!

Av: Tony Yates 29. apr 2020

RE: Rest API - Some endpoints returns KeyNotFoundException

Hi Anders, good detective work. Back in the day we had similar issues with those listTableIds. You should also check that "GUI - Web panels" and "Chat Protocols" also have correct 30000+ values, so you don't get other weird errors in the future.

See this thread.

Av: Frode Lillerud 30. apr 2020

RE: Rest API - Some endpoints returns KeyNotFoundException

Thanks Tony and Frode.
Yeah it took some time narrow it down, but piece by piece hunted the root cause down.

Will look into the TTL to see if I can find anything there that may be the cause of setting this value.

Great that table number is viewed again! 


I guess the 3XXXX, tablenumbers cant be written out there since that can depend from installation to installation?
After the CDD concept was brought to the database development?

Or does new tables that are introduced always have high numbers nowdays? (I guess not)
(like bellow or is it just certain tables that get those high Id:s? I mean webpanel and chat are not new?)
A bit confused here :-) GUI-Webpanel for instance, here the data resides in the crm8.extapp (table 133)
Are the 3XXXX, some sort of "virtual containers" with a derviate from a real physical table?

Interesting to here that at least someone has had similar problems , yes the chat and webpanel also has high numers

SW:"GUI – Webbpanel";US:"GUI - Web panel";NO:"GUI - Webpanel"  (30001)
Chat Protocols 7 Instant Messaging Protocols (30002)
SW:"E-postmall";US:"E-mail template";NO:"E-postmal" (30003)

Have a nice weekend!

/Anders

Av: Anders Larsson 30. apr 2020