Jump to content

Clear faulty AI tags from Daminion using SQL


Recommended Posts

I posted a question at the end of this topic, but since it didn't get an answer, and it is different enough to warrant a separate post, I am doing so here. 🙂

I am still have issues with some photos with some AI tags not able to be updated. I think that WilfriedB's SQL query from the previous thread is likely the culprit.

select * from cloudvision where value = '[ ]'

 

What I'm not sure of is the best way to manually update the rows in that table for Daminion to recognize that they need to be regenerated?

Should I set column 'value' to NULL, or delete the problematic rows from table cloudvision entirely? Or will either approach work?

And if I do that, are there other tables that need to be updated to keep everything in sync, or is just updated cloudvision enough?

  • Like 1
Link to post
Share on other sites

Agree, but I am also still curious, why does this happen? After I got to work with the help of a Daminion developer several months ago, I got another 19 items resisting to get AI tags. It is an extremely low percentage, given the fact that I am able to tag 1,000 images each month, but I cannot see any reason, why those are not tagged and the value s set to '[  ]'.

Link to post
Share on other sites

Hello,

To update data in table "cloudvision" you can use this SQL query. It selects problematic rows(with an empty value) and clears info about startdate and enddate. It allows you to generate AI labels again for the items.

update cloudvision set startdate = null, enddate = null, error = null where value = '' or value = '[ ]';

Please, make a backup of the catalog before the execution of the query.

Please, let us know if it helps you.

Regards, Kate.

 

Link to post
Share on other sites

Thanks Katerina! The update was performed (and committed), but only for a single row:

UPDATE statement (CLOUDVISION) executed successfully: 1 row affected  - Connection: NetCatalog1: 1428ms

After that, it was still not possible to get AI labels for those 19 items. Then I tried to request them for a newer image, which worked fine immediately.

Link to post
Share on other sites

Tried now another update with '[ ]' (i.e. one space) and many more than 19 rows were updated. Trying the request AI labels again, I found this error:

Status(StatusCode=ResourceExhausted, Detail="Resource has been exhausted (e.g. check quota).")   bei System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   bei Grpc.Core.Internal.AsyncCall`2.UnaryCall(TRequest msg)
   bei Grpc.Core.Calls.BlockingUnaryCall[TRequest,TResponse](CallInvocationDetails`2 call, TRequest req)
   bei Grpc.Core.DefaultCallInvoker.BlockingUnaryCall[TRequest,TResponse](Method`2 method, String host, CallOptions options, TRequest request)
   bei Grpc.Core.Interceptors.InterceptingCallInvoker.<BlockingUnaryCall>b__3_0[TRequest,TResponse](TRequest req, ClientInterceptorContext`2 ctx)
   bei Grpc.Core.ClientBase.ClientBaseConfiguration.ClientBaseConfigurationInterceptor.BlockingUnaryCall[TRequest,TResponse](TRequest request, ClientInterceptorContext`2 context, BlockingUnaryCallContinuation`2 continuation)
   bei Grpc.Core.Interceptors.InterceptingCallInvoker.BlockingUnaryCall[TRequest,TResponse](Method`2 method, String host, CallOptions options, TRequest request)
   bei Google.Cloud.Vision.V1.ImageAnnotator.ImageAnnotatorClient.BatchAnnotateImages(BatchAnnotateImagesRequest request, CallOptions options)
   bei Google.Api.Gax.Grpc.ApiCall.GrpcCallAdapter`2.CallSync(TRequest request, CallSettings callSettings)
   bei Google.Api.Gax.Grpc.ApiCallRetryExtensions.<>c__DisplayClass1_0`2.<WithRetry>b__0(TRequest request, CallSettings callSettings)
   bei Google.Api.Gax.Grpc.ApiCall`2.<>c__DisplayClass10_0.<WithCallSettingsOverlay>b__1(TRequest req, CallSettings cs)
   bei Google.Api.Gax.Grpc.ApiCall`2.Sync(TRequest request, CallSettings perCallCallSettings)
   bei Google.Cloud.Vision.V1.ImageAnnotatorClientImpl.BatchAnnotateImages(BatchAnnotateImagesRequest request, CallSettings callSettings)
   bei Google.Cloud.Vision.V1.ImageAnnotatorClient.BatchAnnotateImages(IEnumerable`1 requests, CallSettings callSettings)
   bei Google.Cloud.Vision.V1.ImageAnnotatorClient.Annotate(AnnotateImageRequest request, CallSettings settings)
   bei Google.Cloud.Vision.V1.ImageAnnotatorClient.AnnotateSingleFeatureType[T](Type featureType, Func`2 annotationExtractor, Image image, ImageContext context, Int32 maxResults, CallSettings callSettings)
   bei Google.Cloud.Vision.V1.ImageAnnotatorClient.DetectLabels(Image image, ImageContext context, Int32 maxResults, CallSettings callSettings)
   bei CVis.ImageTask.Run() in C:\DaminionAutoGit\code\CVis\ImageTask.cs:Zeile 86.

Link to post
Share on other sites
11 hours ago, Katerina Karpenko said:

Hello,

To update data in table "cloudvision" you can use this SQL query. It selects problematic rows(with an empty value) and clears info about startdate and enddate. It allows you to generate AI labels again for the items.


update cloudvision set startdate = null, enddate = null, error = null where value = '' or value = '[ ]';

Please, make a backup of the catalog before the execution of the query.

Please, let us know if it helps you.

Regards, Kate.

 

Thanks! Ran the statement and 730 rows were updated. Trying to regenerate AI labels now, and will report back.

Link to post
Share on other sites

While the SQL Update did not do me any good, it did have some negative side effect. Since yesterday was the first of the month, it was easy to see, how many requests have been performed and charged to my Google account. This morning, I was surprised seeing "513 count" for my Label Detection Operations on the Billing page of the Google Cloud Platform, but I requested only 125 (or 126?) items to Auto Generate Tags yesterday. To understand, what happened, I ran this SQL select:

select * from cloudvision where enddate >= date_trunc('month', CURRENT_DATE) order by value desc;

Even more confusing: The result showed 540 rows! A closer look revealed:

  • 125 rows where value like '[ { "mid": "%'     
    Correct, I verified those to be items, I requested yesterday and they are tagged correctly
  • 26 rows where error like('Status(StatusCode=ResourceExhausted%')
  • 1 row where error = 'not found thumbnail'
    This seems to be an item I requested AI tags for several months ago, but meanwhile deleted that image
  • 388 rows where value = '[ ]'
    And this is problem: No AI tags generated, but the requests are charged against my Google account (388 + 125 = 513)

Only one of the 388 failed requests is a newer image I probably requested yesterday, but no AI tags were generated for it.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...