Load in SQLite Adapter is Called Inconsistently

[vkoves]

Running the app locally, I've had issues where it seems load is not called in the SQLite adapter, as I get the following error once in a while:

SQLite3::SQLException: no such function: least:

I've tracked this down to the adapter, and removing the least just makes the next function error, which makes it seem that the load(klass) in the adapter is not being called.

[BagpipesJohnson]

I know the gem doesn't say that it supports SQLite....but has anyone found a way around this?

[dblue]

Someone on Upwork referenced this question. Here's how I resolved the issue:

Geokit requires SQL functions that are not defined in SQLite3. The Geokit adaptor solves this by using SQLite's create_function (through ActiveRecord) to create the additional functions. However, SQLite's user-defined functions only exist in memory for that particular connection.

Geokit::Adapters::Sqlite3.load is called when the Geokit adapter is initialized but it is separate and independent from the ActiveRecord adapter. So, if the original ActiveRecord is lost (or if a new one is created), the Geokit adapter is oblivious to that.

If you're using Puma (Rail's default server) or some other multithreaded server, the first connection works because the user-defined functions have been declared in SQLite3. BUT, if additional instances are created for other threads, those instances do not have the user-defined functions. And because you have no way of knowing whether the first thread or a subsequent thread is handling the connection, you're subject to intermittent errors regarding the missing user-defined functions. The problem doesn't present in production because you're using a database that saves the user-defined functions.

• Option 1: Use an initializer in config/initializers that creates the user-defined functions whenever a SQLite3 connection is initialized.
• Option 2: Hook into the :checkout callback on the ActiveRecord's ConnectionAdaptor to create the user-defined functions whenever a SQLite3 connection is initialized.
• Option 3: Try using webrick instead.
• Option 4: Don't use SQLite3.