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

solid_queue/app/models/solid_queue/recurring_execution.rb

Lines 21 to 23 in c445201

	rescue ActiveRecord::RecordNotUnique
	raise AlreadyRecorded
	end

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