Bug in NSI web service with referencepartial parameter: "String or binary data would be truncated." - SDMXRI-1052 +SDMXRI-1208 (MSDB 6.8)
The query https://dotstatcor-dev2.main.oecd.org/PMdesignNSIUpdaterService/Rest/dataflow/KH_NIS/DF_GDP/1.0/?references=all&detail=referencepartial fails with the error "String or binary data would be truncated". Note that in this case the content constraint ID-s generated for the codelists are very long. According to the SDMX standard ID-s can be up to 255 characters, but it seems that ID-s are set to be max 50 characters in some places.
Error from the log:
INFO|Estat.Sri.MappingStoreRetrieval.Engine.PartialConceptSchemeRetrievalEngine|2019-05-06 08:12:46,870|Executing query for partial conceptscheme:
SELECT CC.CON_ID AS SYSID, IT.ID AS ID, LN.TEXT, LN.LANGUAGE, LN.TYPE
FROM ARTEFACT_VIEW DSDA
INNER JOIN DSD DSD ON DSDA.ART_ID = DSD.DSD_ID
INNER JOIN (
SELECT COMP.DSD_ID, COMP.COMP_ID, CON.CON_SCH_ID, CON.CON_ID
FROM COMPONENT COMP
INNER JOIN CONCEPT CON ON COMP.CON_ID = CON.CON_ID
UNION
SELECT COMP.DSD_ID, COMP.COMP_ID, CON.CON_SCH_ID, CON.CON_ID
FROM COMPONENT COMP
INNER JOIN CONCEPT_ROLE CR ON COMP.COMP_ID = CR.COMP_ID
INNER JOIN CONCEPT CON ON CR.CON_ID = CON.CON_ID
) CC ON CC.DSD_ID = DSD.DSD_ID
INNER JOIN ITEM IT ON CC.CON_ID = IT.ITEM_ID
LEFT JOIN LOCALISED_STRING LN ON LN.ITEM_ID = IT.ITEM_ID WHERE DSDA.AGENCY = @Agency AND DSDA.ID = @id AND DSDA.VERSION = @version AND CC.CON_SCH_ID = @cshId
INFO|org.estat.sri.sqlquerylogger|2019-05-06 08:12:46,871|
SELECT CC.CON_ID AS SYSID, IT.ID AS ID, LN.TEXT, LN.LANGUAGE, LN.TYPE
FROM ARTEFACT_VIEW DSDA
INNER JOIN DSD DSD ON DSDA.ART_ID = DSD.DSD_ID
INNER JOIN (
SELECT COMP.DSD_ID, COMP.COMP_ID, CON.CON_SCH_ID, CON.CON_ID
FROM COMPONENT COMP
INNER JOIN CONCEPT CON ON COMP.CON_ID = CON.CON_ID
UNION
SELECT COMP.DSD_ID, COMP.COMP_ID, CON.CON_SCH_ID, CON.CON_ID
FROM COMPONENT COMP
INNER JOIN CONCEPT_ROLE CR ON COMP.COMP_ID = CR.COMP_ID
INNER JOIN CONCEPT CON ON CR.CON_ID = CON.CON_ID
) CC ON CC.DSD_ID = DSD.DSD_ID
INNER JOIN ITEM IT ON CC.CON_ID = IT.ITEM_ID
LEFT JOIN LOCALISED_STRING LN ON LN.ITEM_ID = IT.ITEM_ID WHERE DSDA.AGENCY = @Agency AND DSDA.ID = @id AND DSDA.VERSION = @version AND CC.CON_SCH_ID = @cshId
INFO|Estat.Sri.MappingStoreRetrieval.Engine.IdentifiableAnnotationRetrieverEngine|2019-05-06 08:12:46,872|Executing query for identifiable annotations : select T.CON_ID as SYSID, AN.ANN_ID, AN.ID, AN.TITLE, AN.TYPE, AN.URL, AT.LANGUAGE, AT.TEXT from ANNOTATION AN LEFT OUTER JOIN ANNOTATION_TEXT AT ON AN.ANN_ID = AT.ANN_ID INNER JOIN ITEM_ANNOTATION AA ON AA.ANN_ID = AN.ANN_ID INNER JOIN CONCEPT T ON T.CON_ID = AA.ITEM_ID WHERE T.CON_SCH_ID = @p_id
INFO|org.estat.sri.sqlquerylogger|2019-05-06 08:12:46,872|select T.CON_ID as SYSID, AN.ANN_ID, AN.ID, AN.TITLE, AN.TYPE, AN.URL, AT.LANGUAGE, AT.TEXT from ANNOTATION AN LEFT OUTER JOIN ANNOTATION_TEXT AT ON AN.ANN_ID = AT.ANN_ID INNER JOIN ITEM_ANNOTATION AA ON AA.ANN_ID = AN.ANN_ID INNER JOIN CONCEPT T ON T.CON_ID = AA.ITEM_ID WHERE T.CON_SCH_ID = @p_id
INFO|Estat.Sri.MappingStoreRetrieval.Engine.ArtefactRetrieverEngine`1[[Org.Sdmxsource.Sdmx.Api.Model.Mutable.Codelist.ICodelistMutableObject, Estat.SdmxSource.SdmxAPI, Version=1.14.1.0, Culture=neutral, PublicKeyToken=null]]|2019-05-06 08:12:46,872|Executing Query: 'SELECT T.CL_ID as SYSID, A.ID, A.AGENCY, dbo.versionToString(A.VERSION1, A.VERSION2, A.VERSION3) as VERSION, A.VALID_FROM, A.VALID_TO, A.URI, A.IS_FINAL, LN.TEXT, LN.LANGUAGE, LN.TYPE , IS_PARTIAL FROM CODELIST T INNER JOIN ARTEFACT A ON T.CL_ID = A.ART_ID LEFT OUTER JOIN LOCALISED_STRING LN ON LN.ART_ID = A.ART_ID WHERE A.ID = @id AND dbo.isEqualVersion(A.VERSION1, A.VERSION2, A.VERSION3, @version1,@version2,@version3)=1 AND A.AGENCY = @Agency ORDER BY A.ART_ID ' with 'Target :Codelist - Agency Id: KH_NIS - Maintainable Id: CL_ECON_INDICATOR - Version: 1.0'
INFO|org.estat.sri.sqlquerylogger|2019-05-06 08:12:46,872|SELECT T.CL_ID as SYSID, A.ID, A.AGENCY, dbo.versionToString(A.VERSION1, A.VERSION2, A.VERSION3) as VERSION, A.VALID_FROM, A.VALID_TO, A.URI, A.IS_FINAL, LN.TEXT, LN.LANGUAGE, LN.TYPE , IS_PARTIAL FROM CODELIST T INNER JOIN ARTEFACT A ON T.CL_ID = A.ART_ID LEFT OUTER JOIN LOCALISED_STRING LN ON LN.ART_ID = A.ART_ID WHERE A.ID = @id AND dbo.isEqualVersion(A.VERSION1, A.VERSION2, A.VERSION3, @version1,@version2,@version3)=1 AND A.AGENCY = @Agency ORDER BY A.ART_ID
INFO|org.estat.sri.sqlquerylogger|2019-05-06 08:12:46,872|select T.CL_ID as SYSID, AN.ANN_ID, AN.ID, AN.TITLE, AN.TYPE, AN.URL, AT.LANGUAGE, AT.TEXT from ANNOTATION AN LEFT OUTER JOIN ANNOTATION_TEXT AT ON AN.ANN_ID = AT.ANN_ID INNER JOIN ARTEFACT_ANNOTATION AA ON AA.ANN_ID = AN.ANN_ID INNER JOIN CODELIST T ON T.CL_ID = AA.ART_ID WHERE T.CL_ID = @p_id
INFO|Estat.Sri.MappingStoreRetrieval.Engine.ItemSchemeRetrieverEngine`2[[Org.Sdmxsource.Sdmx.Api.Model.Mutable.Codelist.ICodelistMutableObject, Estat.SdmxSource.SdmxAPI, Version=1.14.1.0, Culture=neutral, PublicKeyToken=null],[Org.Sdmxsource.Sdmx.Api.Model.Mutable.Codelist.ICodeMutableObject, Estat.SdmxSource.SdmxAPI, Version=1.14.1.0, Culture=neutral, PublicKeyToken=null]]|2019-05-06 08:12:46,872|Counting items: SELECT COUNT(1) AS COUNT FROM DSD_CODE WHERE CL_ID=146
INFO|org.estat.sri.sqlquerylogger|2019-05-06 08:12:46,873|SELECT COUNT(1) AS COUNT FROM DSD_CODE WHERE CL_ID=146
INFO|Estat.Sri.MappingStoreRetrieval.Engine.ItemSchemeRetrieverEngine`2[[Org.Sdmxsource.Sdmx.Api.Model.Mutable.Codelist.ICodelistMutableObject, Estat.SdmxSource.SdmxAPI, Version=1.14.1.0, Culture=neutral, PublicKeyToken=null],[Org.Sdmxsource.Sdmx.Api.Model.Mutable.Codelist.ICodeMutableObject, Estat.SdmxSource.SdmxAPI, Version=1.14.1.0, Culture=neutral, PublicKeyToken=null]]|2019-05-06 08:12:46,873|ItemScheme has 95 items.
INFO|org.estat.sri.sqlquerylogger|2019-05-06 08:12:46,874| CREATE TABLE #TEMP_PROCESS_CONSTRAINTS_TABLE
(
COMP_ID VARCHAR(50
) NOT NULL,
CODE_SYS_ID BIGINT NOT NULL,
CODE_ID VARCHAR(50
) NOT NULL,
PARENT_CODE_SYS_ID BIGINT,
INCLUDED BIT DEFAULT 0
PRIMARY KEY(COMP_ID, CODE_SYS_ID)
)
INFO|org.estat.sri.sqlquerylogger|2019-05-06 08:12:46,894|PROCESS_CONSTRAINTS
ERROR|Estat.Sri.Ws.Rest.StructureResourceErrorHandler|2019-05-06 08:12:46,895|System.Data.SqlClient.SqlException (0x80131904): String or binary data would be truncated
.
at Estat.Sri.MappingStoreRetrieval.Engine.ConstrainedCodeListRetrievalEngine.FillCodes(ICodelistMutableObject itemScheme, Int64 parentSysId, IStructureReference requestedStructureRef)
at Estat.Sri.MappingStoreRetrieval.Engine.ConstrainedCodeListRetrievalEngine.<>c__DisplayClass2_0.b__0(ICodelistMutableObject o, Int64 l)
at Estat.Sri.MappingStoreRetrieval.Engine.ArtefactRetrieverEngine`1.HandleDetailLevel(ComplexStructureQueryDetailEnumType detail, Func`3 retrieveDetails, IEnumerable`1 artefactPkPairs, IComplexAnnotationReference annotationWhere)
at Estat.Sri.MappingStoreRetrieval.Engine.ArtefactRetrieverEngine`1.RetrieveArtefacts(ArtefactSqlQuery sqlQuery, ComplexStructureQueryDetailEnumType detail, Func`2 commandBuilder, Func`3 retrieveDetails, Action`2 extraFields, IComplexAnnotationReference annotationReference)
at Estat.Sri.MappingStoreRetrieval.Engine.ConstrainedCodeListRetrievalEngine.Retrieve(IStructureReference codelistRef, IStructureReference requestedStructureRef)
at Estat.Sri.MappingStoreRetrieval.Manager.CrossConstrainedReferenceRetrievalManager.GetMutableMaintainable(IStructureReference constrainableArtefactRef, IStructureReference structureRef, IStructureReference structureParentRef, IList`1 allowedDataflows)
at Estat.Sri.MappingStoreRetrieval.Manager.CrossConstrainedReferenceRetrievalManager.<>c__DisplayClass10_0.b__0(IStructureReference reference)
at Estat.Sri.MappingStoreRetrieval.Engine.CrossReferenceResolverMutableEngine.GetMutableObject(Func`2 retrievalManager, IStructureReference structureReference)
at Estat.Sri.MappingStoreRetrieval.Engine.CrossReferenceResolverMutableEngine.ResolveReferencesInternal(IMutableObjects beans, Int32 numberLevelsDeep, Func`2 retrievalManager, IDictionaryOfSets`2 missingReferences)
at Estat.Sri.MappingStoreRetrieval.Engine.CrossReferenceResolverMutableEngine.ResolveReferences(IMutableObjects beans, Int32 numberLevelsDeep, Func`2 retriever)
at Estat.Sri.MappingStoreRetrieval.Engine.CrossReferenceResolverMutableEngine.ResolveReferences(IMaintainableMutableObject artefact, Int32 numberLevelsDeep, Func`2 retriever)
at Estat.Sri.MappingStoreRetrieval.Manager.CrossConstrainedReferenceRetrievalManager.GetCrossReferencedStructures(IMaintainableMutableObject constrainableArtefact, IMaintainableMutableObject maintainableArtefact, IList`1 allowedDataflows)
at Estat.Sri.MappingStoreRetrieval.Manager.CrossConstrainedReferenceRetrievalManager.GetDescendants(IMaintainableMutableObject constrainableArtefact, IList`1 allowedDataflows)
at Estat.Sri.MappingStoreRetrieval.Manager.CrossConstrainedReferenceRetrievalManager.getReferences(StructureReferenceDetailEnumType referenceDetail, IMaintainableMutableObject maintainable, IList`1 allowedDataflows)
at Estat.Sri.MappingStoreRetrieval.Manager.CrossConstrainedReferenceRetrievalManager.ProcessConstraints(IRestStructureQuery structureQuery, IMutableObjects mutableObjects, IList`1 allowedDataflows)
at Estat.Nsi.StructureRetriever.Manager.AuthMutableStructureSearchManagerBase.GetMaintainables(IRestStructureQuery structureQuery, IList`1 allowedDataflows)
at Estat.Nsi.StructureRetriever.Manager.StructureRetrieverAvailableData.GetMaintainables(IRestStructureQuery structureQuery)
at Estat.Sri.Ws.Controllers.Manager.RetrieverManager.GetMutableObjects(IRetrieverFactory factory, SdmxSchema sdmxSchema, IRestStructureQuery query) in c:\git\NSIWS\src\Controllers\Manager\RetrieverManager.cs:line 660
at Estat.Sri.Ws.Controllers.Manager.RetrieverManager.<>c__DisplayClass15_0.b__0(IRetrieverFactory factory) in c:\git\NSIWS\src\Controllers\Manager\RetrieverManager.cs:line 229
at Estat.Sri.Ws.Controllers.Manager.RetrieverManager.TryRetrieverFactory(IRetrieverFactory retrieverFactory, Func`2 structureRetriever) in c:\git\NSIWS\src\Controllers\Manager\RetrieverManager.cs:line 376
at Estat.Sri.Ws.Controllers.Manager.RetrieverManager.<>c__DisplayClass29_0.b__0(IRetrieverFactory factory) in c:\git\NSIWS\src\Controllers\Manager\RetrieverManager.cs:line 504
at Estat.Sri.Ws.Controllers.Manager.RetrieverManager.TryFactories(Func`2 tryRetrieverFactory) in c:\git\NSIWS\src\Controllers\Manager\RetrieverManager.cs:line 731
at Estat.Sri.Ws.Controllers.Manager.RetrieverManager.ParseRequest(SdmxSchema sdmxSchema, IRestStructureQuery query) in c:\git\NSIWS\src\Controllers\Manager\RetrieverManager.cs:line 230
at Estat.Sri.Ws.Controllers.Controller.StructureRequestController.ParseRequest(IRestStructureQuery query, WebHeaderCollection headers) in c:\git\NSIWS\src\Controllers\Controller\StructureRequestController.cs:line 163
at Estat.Sri.Ws.Rest.StructureResource.ProcessRequest(String structure, String agencyId, String resourceId, String version, WebOperationContext ctx) in c:\git\NSIWS\src\NSIWebServices\StructureResource.cs:line 456
at Estat.Sri.Ws.Rest.StructureResource.GetStructure(String structure, String agencyId, String resourceId, String version) in c:\git\NSIWS\src\NSIWebServices\StructureResource.cs:line 163
at Estat.Sri.Ws.Rest.StructureResourceErrorHandler.GetStructure(String structure, String agencyId, String resourceId, String version) in c:\git\NSIWS\src\NSIWebServices\StructureResourceErrorHandler.cs:line 93
ClientConnectionId:cadae9c7-c670-403d-bd6f-fe32c4ee5576
Error Number:50000,State:14,Class:16
ERROR|Org.Sdmxsource.Sdmx.Api.Exception.SdmxException|2019-05-06 08:12:46,895|String or binary data would be truncated.
Org.Sdmxsource.Sdmx.Api.Exception.SdmxInternalServerException: String or binary data would be truncated.
ERROR|Estat.Sri.Ws.Controllers.Builder.WebFaultExceptionRestBuilder|2019-05-06 08:12:46,895|SdmxError : Internal Server Error, code : 500
ERROR|Estat.Sri.Ws.Controllers.Builder.WebFaultExceptionRestBuilder|2019-05-06 08:12:46,895|String or binary data would be truncated.
Org.Sdmxsource.Sdmx.Api.Exception.SdmxInternalServerException: String or binary data would be truncated.
Eurostat asked OECD to take this story on board (as OECD is the "owner" of the referencepartial implementation). Please make the related merge request to ticket https://webgate.ec.europa.eu/CITnet/jira/projects/SDMXRI/issues/SDMXRI-1052.