Clear faulty AI tags from Daminion using SQL

[k1w]

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?

[WilfriedB]

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 '[  ]'.

[Katerina Karpenko]

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.

 

[WilfriedB]

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.

[Katerina Karpenko]

Wilfried, are there any errors in column Error in the cloudvision table for those 19 items?

[WilfriedB]

The Error column is empty, but values = [ ] .... not sure, how many spaces are between the brackets.

[Katerina Karpenko]

I think there is just one space between the brackets. The rows with such value should be found after the query.

[WilfriedB]

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.

[WilfriedB]

Interesting, the result for all 19 items:

 

 

[k1w]

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.

[WilfriedB]

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.

[Daria Kotilainen]

Hi Wilfried, 

 

Please let me know if you are available for a remote session to take a look at this? Please email me your open time slots

 

Kind regards

Daria

[WilfriedB]

43 minutes ago, Daria Kotilainen said:

Please email me your open time slots

Done.