RecordNotUnique error occurs when multiple scheduler instances run simultaneously with MySQL

[lupinthe14th]

Description

When multiple Solid Queue scheduler instances run simultaneously, ActiveRecord::RecordNotUnique errors occur every minute during recurring job execution.

Environment

• solid_queue version: 1.2.1
• Rails version: 7.2.2.2
• Ruby version: 3.4.4
• Database: MySQL 8.0 (AWS Aurora Serverless v2)
• Infrastructure: AWS ECS Fargate (running with multiple tasks)

Error Message

ActiveRecord::RecordNotUnique: Trilogy::ProtocolError: 1062:
Duplicate entry 'solid_queue_job_count-2025-09-11 01:17:00.000000' for key
'solid_queue_recurring_executions.index_solid_queue_recurring_executions_on_task_key_and_run_at'

Steps to Reproduce

1. Launch multiple tasks on ECS Fargate (each task runs as scheduler + worker)
2. Configure a job to run every minute in recurring.yml:

solid_queue_job_count:
  command: 'SolidQueueJobCountJob.perform_now'
  schedule: every minute

3. Multiple schedulers attempt to enqueue the same recurring job simultaneously, causing duplicate key errors

Current Configuration

config/recurring.yml:

solid_queue_job_count:
  command: 'SolidQueueJobCountJob.perform_now'
  schedule: every minute

config/application.rb:

config.active_job.queue_adapter = :solid_queue unless Rails.env.test?
config.solid_queue.connects_to = { database: { writing: :primary } }

Expected Behavior

Even with multiple scheduler instances present, recurring jobs should be processed by only one instance without duplicate key errors.

Actual Behavior

Each scheduler instance independently attempts to enqueue recurring jobs, resulting in database unique constraint violations.

Related Issues

This issue may be related to #636.

Current Workarounds

We are considering the following approaches:

1. Create a dedicated ECS service for the scheduler, limiting it to a single instance
2. Control scheduler enable/disable via environment variables

Suggestion

Would it be possible to consider strengthening duplicate prevention mechanisms at the database level, such as using different transaction isolation levels or INSERT IGNORE for MySQL?

Running multiple application instances is a common deployment pattern, and the single scheduler instance constraint can be an operational limitation in many scenarios.

[rosa]

This is on purpose, precisely to allow you to run multiple schedulers without worrying about enqueuing the same task more than once. It's handled in https://github.com/rails/solid_queue/blob/c44520165e69c4f4ed189c82c943be3c9d58d1bf/app/models/solid_queue/recurring_execution.rb#L21-L23

This suggests something else is going on, so that the exception is not correctly rescued there. Could you provide the full stack trace from the exception?

[lupinthe14th]

@rosa Thank you for the quick response. Here's the full stack trace as requested:

See: https://gist.github.com/lupinthe14th/170e921eef73cfb1a4b2f628dce7cb23

[rosa]

THanks @lupinthe14th! This looks like some inconsistency/issue in the Trilogy adapter. I need to debug a bit further using that, to find out why it's not raising the expected exception there.

[chrisdebruin]

I am seeing the same issue with mysql2 gem.

Stack trace can be found here: https://gist.github.com/chrisdebruin/076b3212cf0376cd5c79d0cf5c2aca23