[JAR-1892] Aktivitet - går ned når specifik aktivitet vælges på 510-81102

Filip Bruman Kørt det i testen og denne lokalitet 510-81102 givet stadig fejl:

at System.Drawing.Bitmap..ctor(Stream stream) at WebApi.Bll.PolygonBllService.AddTextToFladeImage(MemoryStream bitmapStream, Boolean largeImage) in D:\a\1\s\src\Jar\WebApi\Bll\PolygonBllService.cs:line 498 at WebApi.Bll.PolygonBllService.GetSmallStaticSagsforloebImage_p(String sagsforloebId) in D:\a\1\s\src\Jar\WebApi\Bll\PolygonBllService.cs:line 269 at WebApi.Controllers.PolygonController.GetSmallStaticSagsforloebImage(RequestSmallStaticSagsforloebImageModel requestParams) in D:\a\1\s\src\Jar\WebApi\Controllers\PolygonController.cs:line 38 at lambda_method(Closure , Object , Object[] ) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c_DisplayClass6_1.b3(Object instance, Object[] methodParameters) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken) — End of stack trace from previous location where exception was thrown — at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Controllers.ApiControllerActionInvoker.d1.MoveNext() — End of stack trace from previous location where exception was thrown — at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Controllers.ActionFilterResult.d5.MoveNext() — End of stack trace from previous location where exception was thrown — at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.AuthorizationFilterAttribute.d3.MoveNext() — End of stack trace from previous location where exception was thrown — at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Controllers.ExceptionFilterResult.d6.MoveNext() — End of stack trace from previous location where exception was thrown — at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Web.Http.Controllers.ExceptionFilterResult.d6.MoveNext() — End of stack trace from previous location where exception was thrown — at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Dispatcher.HttpControllerDispatcher.d_15.MoveNext()

DorkasV commented 2 months ago

Hans Christian Ries Could you run this SQL query to see what images are for location 510-81102

SELECT TOP (100)
    [s_flade_billede].*
FROM [s_flade_billede]
    INNER JOIN e_flade ON [s_flade_billede].pkid = e_flade.gis_ref_nr
    INNER JOIN e_sagsforloeb ON e_flade.gis_ref_nr = e_sagsforloeb.flade_id
    INNER JOIN e_forurening_version ON e_sagsforloeb.forurening_id = e_forurening_version.pkid
    INNER JOIN r_forurening_identifikation ON e_forurening_version.pkid = r_forurening_identifikation.forurening_id
WHERE 
    ([picture_large] IS NOT NULL OR [picture] IS NOT NULL)
    AND r_forurening_identifikation.kode = '0000.001'
    AND r_forurening_identifikation.ekstern_identifikation = '510-81102'

DorkasV commented 2 months ago

resultatet er i regnearket:

ny-udtræk.xlsx

DorkasV commented 2 months ago

Hans Christian Ries Could you save results as CSV files, because when it is .xlsx some data is corrupted. I am interesting in column 'picture_large'

DorkasV commented 2 months ago

Gediminas Sprainys Her er en csv fil.

DorkasV commented 2 months ago

Hans Christian Ries

De to ser ikke ud til at være relaterede. Vil du ikke oprette den sidste som en ny Jira, så kan vi holde dem separat, tak.

DorkasV commented 2 months ago

Filip Bruman ny rsyd-90 er nu oprettet og jeg har fjernet den kommentar som intet havde med denne fejl. Hvad er status efter jeg sendte csv filen til jer?

DorkasV commented 2 months ago

Hans Christian Ries For location 510-81102, I found that a corrupted image was saved in a database. I prepared an SQL script that deletes that image, and from now on, this image will be generated from the Mapserver.

DELETE FROM s_flade_billede WHERE pkid = '2BAA23D4-1D1D-4230-8B27-EF1A8F2401B7'

Please try to run and check I you get an error.

DorkasV commented 2 months ago

Super ļæ½ļæ½ Gediminas Sprainys

"How can I find which images are corrupted?"

DorkasV commented 2 months ago

Gediminas Sprainys I will now run it in production.

DorkasV commented 2 months ago

Hans Christian Ries This delete SQL query that I provided before, will only work in the TEST environment. For production, it should use a different image ID. Here is a query that will find all corrupted large images:

SELECT
    AllImages.ekstern_identifikation,
    AllImages.Image_ID,
    AllImages.picture_large
FROM
(
    SELECT
r_forurening_identifikation.ekstern_identifikation,
s_flade_billede.pkid AS Image_ID,
cast(cast(s_flade_billede.picture_large as varbinary(max)) as varchar(max)) as picture_large
    FROM s_flade_billede
    INNER JOIN e_flade ON s_flade_billede.pkid = e_flade.gis_ref_nr
    INNER JOIN e_sagsforloeb ON e_flade.gis_ref_nr = e_sagsforloeb.flade_id
    INNER JOIN e_forurening_version ON e_sagsforloeb.forurening_id = e_forurening_version.pkid
    INNER JOIN r_forurening_identifikation ON e_forurening_version.pkid = r_forurening_identifikation.forurening_id
    WHERE
r_forurening_identifikation.kode = '0000.001'
AND s_flade_billede.picture_large IS NOT NULL
) AS AllImages
WHERE 
    AllImages.picture_large NOT LIKE '%PNG%'

If there are not many records, you can use this delete query to delete manually every corrupted image:

DELETE FROM s_flade_billede WHERE pkid = ''

Here is a query that will find all corrupted small images:

SELECT
    AllImages.ekstern_identifikation,
    AllImages.Image_ID,
    AllImages.picture
FROM
(
    SELECT
r_forurening_identifikation.ekstern_identifikation,
s_flade_billede.pkid AS Image_ID,
cast(cast(s_flade_billede.picture as varbinary(max)) as varchar(max)) as picture
    FROM s_flade_billede
    INNER JOIN e_flade ON s_flade_billede.pkid = e_flade.gis_ref_nr
    INNER JOIN e_sagsforloeb ON e_flade.gis_ref_nr = e_sagsforloeb.flade_id
    INNER JOIN e_forurening_version ON e_sagsforloeb.forurening_id = e_forurening_version.pkid
    INNER JOIN r_forurening_identifikation ON e_forurening_version.pkid = r_forurening_identifikation.forurening_id
    WHERE
r_forurening_identifikation.kode = '0000.001'
AND s_flade_billede.picture_large IS NOT NULL
) AS AllImages
WHERE 
    AllImages.picture NOT LIKE '%PNG%'

To delete you can use the same delete query.

This happened, for example, when an activity was created and, during that time, the MapServer was not accessible. Instead of an image, the error message was saved to the database.

DorkasV commented 2 months ago

Gediminas Sprainys There are 822 large images that are corrupted in our test database!

And 825 small images! Do you have a script that can loop through all the images found in large and small images and delete them?

DorkasV commented 2 months ago

Gediminas Sprainys Can you run the two scripts on the master JAR?

Kim Jacobsen Bemærk, at det er nok her tale om en generel fejl ved den første geojar komponent vi have i JAR systemet i starten af jar!

DorkasV commented 2 months ago

Hans Christian Ries I executed that script on Master JAR and there are 33 large corrupted images.

Here is an SQL script that automatically deletes large ‘dead’ corrupted images:

/*--- DELETES ALL CORRUPTED LARGE IMAGES --*/

SELECT
    AllImages.ekstern_identifikation,
    AllImages.Image_ID,
    AllImages.picture_large
INTO #TempAllLargeImages
FROM
(
    SELECT
r_forurening_identifikation.ekstern_identifikation,
s_flade_billede.pkid AS Image_ID,
cast(cast(s_flade_billede.picture_large as varbinary(max)) as varchar(max)) as picture_large
    FROM s_flade_billede
    INNER JOIN e_flade ON s_flade_billede.pkid = e_flade.gis_ref_nr
    INNER JOIN e_sagsforloeb ON e_flade.gis_ref_nr = e_sagsforloeb.flade_id
    INNER JOIN e_forurening_version ON e_sagsforloeb.forurening_id = e_forurening_version.pkid
    INNER JOIN r_forurening_identifikation ON e_forurening_version.pkid = r_forurening_identifikation.forurening_id
    WHERE
r_forurening_identifikation.kode = '0000.001'
AND s_flade_billede.picture_large IS NOT NULL
) AS AllImages
WHERE 
    AllImages.picture_large NOT LIKE '%PNG%'

/*
SELECT * FROM #TempAllLargeImages;
*/

DELETE 
    s_flade_billede 
FROM s_flade_billede
INNER JOIN #TempAllLargeImages temp 
    ON s_flade_billede.pkid = temp.Image_ID;

DROP TABLE #TempAllLargeImages;

Usually, when a large image is corrupted, the small image is also corrupted. The previous script should be sufficient for handling both of them. However, in rare cases, you can run this additional script to delete small images if they were missed by the first script:

/*--- DELETES ALL CORRUPTED SMALL IMAGES --*/

SELECT
    AllImages.ekstern_identifikation,
    AllImages.Image_ID,
    AllImages.picture
INTO #TempAllLargeImages
FROM
(
    SELECT
r_forurening_identifikation.ekstern_identifikation,
s_flade_billede.pkid AS Image_ID,
cast(cast(s_flade_billede.picture as varbinary(max)) as varchar(max)) as picture
    FROM s_flade_billede
    INNER JOIN e_flade ON s_flade_billede.pkid = e_flade.gis_ref_nr
    INNER JOIN e_sagsforloeb ON e_flade.gis_ref_nr = e_sagsforloeb.flade_id
    INNER JOIN e_forurening_version ON e_sagsforloeb.forurening_id = e_forurening_version.pkid
    INNER JOIN r_forurening_identifikation ON e_forurening_version.pkid = r_forurening_identifikation.forurening_id
    WHERE
r_forurening_identifikation.kode = '0000.001'
AND s_flade_billede.picture IS NOT NULL
) AS AllImages
WHERE 
    AllImages.picture NOT LIKE '%PNG%'

/*
SELECT * FROM #TempAllLargeImages;
*/

DELETE 
    s_flade_billede 
FROM s_flade_billede
INNER JOIN #TempAllLargeImages temp 
    ON s_flade_billede.pkid = temp.Image_ID;

DROP TABLE #TempAllLargeImages;

DorkasV commented 2 months ago

Filip Bruman kan du flytte dette æø til JAR, idet det et generelt problem i jar projektet?

Kim Jacobsen Aftalt på mødet i dag og jeg opretter en nyt æø, hvor man ændre aktiviteter fanen i JAR, som kan håndterer at korrumperede billeder ikke bevirker at JAR gå ned.

DorkasV commented 2 months ago

Hans Christian Ries

Er det ikke https://jar-cowi.atlassian.net/browse/JAR-1891 som du oprettet?

DorkasV commented 2 months ago

Filip Bruman Jo, men de andre regioner bør kunne se dette æø for at kunne finde de billedfejl der kan være i jar. scriptene vil hjælpe med at finde fejlene

DorkasV commented 2 months ago

Line Boel Hans Christian Ries

Er de to script workaround for issue?

DorkasV commented 1 month ago

Filip Bruman

Nej, ikke helt. De er super gode til at finde problem billederne. Men vi kan ikke bare slette billeder fra databasen. Billeder som har noget at gøre med sagstrinen kan vi ikke overlade til mapserveren at vise lokalitets geometrien, netop fordi disse billeder er historiske. Der må ikke være et nyt billede til en gammel afgørelse. Det vil forvirrer sagsbehandlerne. Ring hvis du skal have en bedre beskrivelse af problemet.

DorkasV commented 1 month ago

Hans Christian Ries Line Boel

De korrupte billeder indeholder ikke information om sagstrinet. Korrupt i denne kontekst betyder at de er fejlmeddelelser fra MapServer i billedformat.

En mulighed kunne være at vise et billede der angiver at der mangler information.

DorkasV commented 1 month ago