Errors while writing to sqlite DB

[stevenjackson]

Not sure if this is a bug, but it could be depending on suture's philosophy. Should production legacy code break while I'm recording calls?

I had a separate sqlite client open to verify suture was recording (while the app was running), which doesn't seem unreasonable if I install this for a few days and want to see if it has captured "enough" information yet.

Code

    def current_category
      Suture.create(:current_category, {
                    old: method(:old_current_category),
                    args: [ @places[@current_player] ]
      })
    end

    def old_current_category(place)
      return 'Pop' if place == 0
      return 'Pop' if place == 4
      return 'Pop' if place == 8
      return 'Science' if place == 1
      return 'Science' if place == 5
      return 'Science' if place == 9
      return 'Sports' if place == 2
      return 'Sports' if place == 6
      return 'Sports' if place == 10
      return 'Rock'
    end

stack trace

/Users/stevejackson/.rvm/gems/ruby-1.9.3-p551@trivia/gems/sqlite3-1.3.11/lib/sqlite3/statement.rb:108:in `step': database is locked (SQLite3::BusyException)
    from /Users/stevejackson/.rvm/gems/ruby-1.9.3-p551@trivia/gems/sqlite3-1.3.11/lib/sqlite3/statement.rb:108:in `block in each'
    from /Users/stevejackson/.rvm/gems/ruby-1.9.3-p551@trivia/gems/sqlite3-1.3.11/lib/sqlite3/statement.rb:107:in `loop'
    from /Users/stevejackson/.rvm/gems/ruby-1.9.3-p551@trivia/gems/sqlite3-1.3.11/lib/sqlite3/statement.rb:107:in `each'
    from /Users/stevejackson/.rvm/gems/ruby-1.9.3-p551@trivia/gems/sqlite3-1.3.11/lib/sqlite3/database.rb:158:in `to_a'
    from /Users/stevejackson/.rvm/gems/ruby-1.9.3-p551@trivia/gems/sqlite3-1.3.11/lib/sqlite3/database.rb:158:in `block in execute'
    from /Users/stevejackson/.rvm/gems/ruby-1.9.3-p551@trivia/gems/sqlite3-1.3.11/lib/sqlite3/database.rb:95:in `prepare'
    from /Users/stevejackson/.rvm/gems/ruby-1.9.3-p551@trivia/gems/sqlite3-1.3.11/lib/sqlite3/database.rb:134:in `execute'
    from /Users/stevejackson/.rvm/gems/ruby-1.9.3-p551@trivia/gems/suture-1.1.1/lib/suture/wrap/sqlite.rb:17:in `block in init'
    from /Users/stevejackson/.rvm/gems/ruby-1.9.3-p551@trivia/gems/suture-1.1.1/lib/suture/wrap/sqlite.rb:11:in `tap'
    from /Users/stevejackson/.rvm/gems/ruby-1.9.3-p551@trivia/gems/suture-1.1.1/lib/suture/wrap/sqlite.rb:11:in `init'
    from /Users/stevejackson/.rvm/gems/ruby-1.9.3-p551@trivia/gems/suture-1.1.1/lib/suture/adapter/dictaphone.rb:13:in `initialize'
    from /Users/stevejackson/.rvm/gems/ruby-1.9.3-p551@trivia/gems/suture-1.1.1/lib/suture/surgeon/observer.rb:7:in `new'
    from /Users/stevejackson/.rvm/gems/ruby-1.9.3-p551@trivia/gems/suture-1.1.1/lib/suture/surgeon/observer.rb:7:in `operate'
    from /Users/stevejackson/.rvm/gems/ruby-1.9.3-p551@trivia/gems/suture-1.1.1/lib/suture/create/performs_surgery.rb:4:in `perform'
    from /Users/stevejackson/.rvm/gems/ruby-1.9.3-p551@trivia/gems/suture-1.1.1/lib/suture/create.rb:10:in `create'
    from /Users/stevejackson/projects/trivia/ruby/lib/ugly_trivia/game.rb:91:in `current_category'
    from /Users/stevejackson/projects/trivia/ruby/lib/ugly_trivia/game.rb:76:in `roll'
    from bin/trivia:18:in `<main>'

[searls]

I honestly don't know much about opening multiple SQLite clients to the same SQLite file, other than it's supposedly supported, but that StackOverflow is similarly full of similar error messages. I'd recommend reading up a little bit on the client's options and then the SQLite wrapper that Suture contains to see if there's a configuration tweak you can apply to get what you need

[wadestuart]

Take a look at sqlite3_busy_timeout default sqlite will do an instant timeout when trying to access a locked database, you can use this method to ensure it retries after waiting as in sqlite3_busy_timeout( db, 100 ); or better yet take a look at #busy_handler {|count| ... } ⇒ Object which allows you to set a more robust retry - > fail.

This should probably be set in the gem assuming that you will be running in rails environments as you will likely be hitting locked db in most deployments (including dev when running puma such as rails 5).

[searls]

@wadestuart I'm fine with setting sqlite defaults that resolve this issue, but not if it'll cause issues for others (since I'm not assuming that Suture's always used by Rails apps. Anyway if that's the case, I'd love to see a PR that does this

Separately, I'm intentionally tepid about telling people to set up sqlite recordings in production for reasons like this. It simply won't work for lots of types of deployments. What I'd much rather see is a redis wrapper that can be configured as an alternative to sqlite. And I want @samphippen to pay for it, if at all possible

[fables-tales]

😱

[wadestuart]