MySQL timeout during batch analysis - sentiments module only

[jjroelofs]

Bug Description

The sentiments analyzer consistently fails with MySQL timeout errors during batch processing, while all other analyze modules (brand voice, content marketing audit, content security audit) work correctly.

Note: Not sure if related but I recently renamed this module: https://www.drupal.org/project/project_composer/issues/3539699

It was renamed from analyze_ai_sentiment to analyze_ai_sentiment (plural) to work around a drupal.org infrastructure problem. Not sure if related but it's something we can look into, search and replace can have some nasty side-effects.

Steps to reproduce

Video

Showing you video of both the steps working correctly on ai_analyze_security_audit module and breaking in ai_analyze_sentiments:

https://app.screencastify.com/watch/4aZ8UIEgeZz84TSdW8Ni

Steps

1. Install and enable https://www.drupal.org/project/analyze_ai_sentiments
2. Go to /admin/config/content/analyze-settings and enable Sentiments analyzer on some (node) type
3. Go to /admin/config/analyze/sentiments/batch and run the batch feature on that node type (preferable with not too many nodes to save AI costs)

Expected result: Successfully analyzed n items

Bug behavior: An AJAX HTTP error occurred. HTTP Result Code: 500 Debugging information follows. Path: /batch?id=31&op=do_nojs&op=do StatusText: 500 Service unavailable (with message) ResponseText: The website encountered an unexpected error. Try again later.

Error Details

Error Message:

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away: SELECT 1 FROM information_schema.tables WHERE ("table_schema" = :db_condition_placeholder_8) AND ("table_name" = :db_condition_placeholder_9)

Error Location:

• /web/core/lib/Drupal/Core/Config/DatabaseStorage.php:121
• Occurs during batch processing operations
• Triggered by automated cron after batch operation timeout

Environment

• Working Modules: Brand Voice, Content Marketing Audit, Content Security Audit all process batches successfully
• Failing Module: Sentiments Analysis only
• Individual Analysis: Works perfectly when analyzing single entities through the UI
• Batch Analysis: Consistently times out and fails

Investigation Summary

What We've Confirmed:

1. Plugin Manager Issue (Fixed): Initially used DefaultPluginManager instead of AnalyzePluginManager - this was corrected to match working modules
2. Batch Processing Logic: Confirmed identical to working modules (5 entities per chunk)
3. Service Dependencies: All properly injected and configured
4. Individual Analysis: Functions correctly, suggesting the analyzer plugin itself works

What We've Tried:

1. ✅ Fixed plugin manager from DefaultPluginManager to AnalyzePluginManager
2. ❌ Reduced batch size from 5 to 1 entity per chunk - did not resolve timeout
3. ✅ Compared batch service implementations - found to be identical to working modules
4. ✅ Verified service configuration - matches other analyze modules

Code Comparison Results:

• Batch Forms: Identical structure across all modules
• Batch Services: Same processing logic and error handling
• Plugin Managers: Now correctly using AnalyzePluginManager
• Service Injection: Properly configured

Hypothesis

The sentiments analysis appears to be significantly more complex or slower than other analyze operations, causing individual entity analysis to exceed MySQL's connection timeout threshold during batch processing. This suggests the AI analysis requests themselves may be more intensive.

Current Status

• ✅ Brand Voice: Batch processing works
• ✅ Content Marketing Audit: Batch processing works
• ✅ Content Security Audit: Batch processing works
• ❌ Sentiments Analysis: Batch processing fails with MySQL timeouts

The issue appears to be specific to the computational complexity or duration of the sentiments analysis AI requests rather than the batch processing infrastructure itself.

[stefanospetrakis]

I have not did experienced any MySQL timeouts, prior to fixing the plugin id (I fixed that too quickly and was not experiencing the MySQL timeouts).

What I did find was a faulty implementation of the 'finished' state of the Batch API.
So,regarding the second point "Batch Processing Logic: Confirmed identical to working modules (5 entities per chunk)"
I would argue that the Batch Processing Logic has some issues.
I checked the other repos and opened related issues.

In short: The 'finished' state is related to the current operation, and therefore related to the 'sandbox' data and states.
It could probably be completely dropped (not use it), since by using chunking, we know in advance how many operations we are going to perform.
I also finished the implementation of the 'limit' form element and also added a 'chunk' form element to allow setting the chunk size and allow dealing with performance issues (which I haven't experienced in my local) but was the suspicion behind this issue.

Finally, as shown on the video too, this solution should fix the numbers showing the progress which are erroneous (e.g. 10 out of 5 finished).