How to deal with SQLite3 :: BusyException that occurs when uploading a large number of images using ActiveStorage in seeds.rb etc.

Problems that occur

When running Rails rails db: seed, the following error may occur.

$ rails db:seed
rails aborted!
ActiveRecord::StatementInvalid: SQLite3::BusyException: database is locked
/path-to-your-app/db/seeds.rb:69:in `<main>'

Code example where an error occurs

The code around the error is as follows.

# ...

User.destroy_all

100.times do |n|
  user = User.create!(
    email: "sample-#{n}@example.com",
    password: 'password',
    name: Faker::Name.name
  )
  image_url = Faker::Avatar.image(slug: user.email, size: '150x150')
  #Set up avatar using Active Storage
  user.avatar.attach(io: URI.parse(image_url).open, filename: 'avatar.png')
end

# ...

In addition, this application uses SQLite3.

default: &default
  adapter: sqlite3
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

development:
  <<: *default
  database: db/development.sqlite3

Cause of the error

This error is caused by two factors:

--Since ActiveStorage is used, images are uploaded and deleted asynchronously. --SQLite3 is vulnerable to concurrency (Reference)

When User.destroy_all is executed, the image deletion process is executed asynchronously for the existing data. If you have a large amount of existing data, read and write queries will be issued to the database in parallel.

Similarly, user.avatar.attach also performs image upload processing asynchronously. In the above code example, we tried to create 100 user data, so read and write queries are also issued to the database in parallel.

SQLite3 :: BusyException occurs when SQLiter3 cannot withstand concurrency.

Error workaround

Considering the performance restrictions of SQLite3, delete and upload images should be performed synchronously. Specifically, add the following two lines to config/seeds.rb.

+ActiveStorage::AnalyzeJob.queue_adapter = :inline
+ActiveStorage::PurgeJob.queue_adapter = :inline

 User.destroy_all

 100.times do |n|
   user = User.create!(
     email: "sample-#{n}@example.com",
     password: 'password',
     name: Faker::Name.name
   )
   image_url = Faker::Avatar.image(slug: user.email, size: '150x150')
   user.avatar.attach(io: URI.parse(image_url).open, filename: 'avatar.png')
 end

ActiveStorage :: AnalyzeJob is a class of ActiveJob used at the time of upload and ActiveStorage :: PurgeJob is used at the time of deletion. By changing this queue_adapter to: inline, you can upload and delete images synchronously.

Reference https://api.rubyonrails.org/classes/ActiveJob/QueueAdapters/InlineAdapter.html

Operation check environment

Recommended Posts

How to deal with SQLite3 :: BusyException that occurs when uploading a large number of images using ActiveStorage in seeds.rb etc.
How to deal with Selenium :: WebDriver :: Error :: UnknownError that occurs in Dokcer environment etc.
How to test including images when using ActiveStorage and Faker
Create a large number of records with one command using the Ruby on Rails seeds.rb file
How to deal with the event that Committee :: InvalidRequest occurs in committee during Rspec file upload test
[Java] JUnit that NG if a method with a large number of lines is detected using black magic
[Personal memo] How to interact with a random number generator in Java
How to find the total number of pages when paging in Java
How to insert processing with any number of elements in iterative processing in Ruby
How to get the ID of a user authenticated with Firebase in Swift
[Laravel] How to deal with out of memory error when composer require [Docker]
I made a sample of how to write delegate in SwiftUI 2.0 using MapKit
How to delete a controller etc. using a command
How to get the class name of the argument of LoggerFactory.getLogger when using SLF4J in Java
[Docker] How to update using a container on Heroku and how to deal with Migrate Error
[Ruby] 5 errors that tend to occur when scraping with Selenium and how to deal with them
How to deal with errors in Rails s could not find a JavaScript runtime.
Summary of how to use the proxy set in IE when connecting with Java
[Spring Dtata JPA] How to deal with the problem that DB change cannot be detected when you want to process API synchronously with a single thread in Spring Boot.
How to sort in ascending / descending order with SQLite
[How to insert a video in haml with Rails]
How to delete untagged images in bulk with Docker
How to convert A to a and a to A using AND and OR in Java
How to batch initialize arrays in Java that I didn't know when I was a beginner
How to manage the difference in each environment with yml without increasing the number of RAILS_ENV