No Such Table error from SQLite memory DB via DataMapper

Question

I have a Ruby program that uses DataMapper as an ORM to talk to an in-memory SQLite DB. This has been working fine, however I just recently added a new DM class, and corresponding table. To my surprise, things now blow up during an auto_migrate!

here is the SQL generated by DataMapper:

~ (0.000390) PRAGMA table_info("sensationd_channels")
~ (0.000010) PRAGMA table_info("sensationd_commands")
~ (0.000009) PRAGMA table_info("sensationd_configurations")
~ (0.000052) PRAGMA table_info("sensationd_measurements")
~ (0.000028) SELECT sqlite_version(*)
~ (0.000035) DROP TABLE IF EXISTS "sensationd_channels"
~ (0.000009) PRAGMA table_info("sensationd_channels")
~ (0.000423) CREATE TABLE "sensationd_channels" ("id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "channel" INTEGER NOT NULL, "name" VARCHAR(50), "precision" INTEGER DEFAULT 11, "gain" INTEGER DEFAULT 1, "differential" BOOLEAN DEFAULT 'f', "configuration_id" INTEGER NOT NULL)
~ (0.000191) CREATE INDEX "index_sensationd_channels_configuration" ON "sensationd_channels" ("configuration_id")
~ (0.000015) DROP TABLE IF EXISTS "sensationd_commands"
~ (0.000009) PRAGMA table_info("sensationd_commands")
~ (0.000153) CREATE TABLE "sensationd_commands" ("id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "action" INTEGER DEFAULT 1, "complete" BOOLEAN DEFAULT 'f', "issued_at" TIMESTAMP, "completed_at" TIMESTAMP)
~ (0.000015) DROP TABLE IF EXISTS "sensationd_configurations"
~ (0.000009) PRAGMA table_info("sensationd_configurations")
~ (0.000155) CREATE TABLE "sensationd_configurations" ("id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "created_on" TIMESTAMP, "modified_on" TIMESTAMP, "name" VARCHAR(50) NOT NULL, "active" BOOLEAN)
~ (0.000015) DROP TABLE IF EXISTS "sensationd_measurements"
~ (0.000009) PRAGMA table_info("sensationd_measurements")
~ (0.000152) CREATE TABLE "sensationd_measurements" ("id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "timestamp" TIMESTAMP, "measurement" VARCHAR(65535) NOT NULL, "channel_id" INTEGER NOT NULL, "channel_configuration_id" INTEGER NOT NULL)
~ (0.000175) CREATE INDEX "index_sensationd_measurements_channel" ON "sensationd_measurements" ("channel_id", "channel_configuration_id")
~ (0.000083) SELECT "id", "created_on", "modified_on", "name", "active" FROM "sensationd_configurations" WHERE "active" = 't' ORDER BY "id" LIMIT 1
~ (0.000073) INSERT INTO "sensationd_configurations" ("created_on", "modified_on", "name", "active") VALUES ('2011-08-01T12:36:18-07:00', '2011-08-01T12:36:18-07:00', 'Test U6-Pro Configuration, differential.', 't')
~ (0.000109) SELECT "id", "action", "complete", "issued_at", "completed_at" FROM "sensationd_commands" ORDER BY "issued_at" DESC LIMIT 1
~ (0.000086) INSERT INTO "sensationd_channels" ("channel", "name", "precision", "gain", "differential", "configuration_id") VALUES (0, '0', 11, 0, 't', 1)
~ no such table: sensationd_commands (code: 1, sql state: , query: SELECT "id", "action", "complete", "issued_at", "completed_at" FROM "sensationd_commands" ORDER BY "issued_at" DESC LIMIT 1, uri: sqlite3::memory:?scheme=sqlite&user=&password=&host=&port=&query=&fragment=&adapter=sqlite3&path=:memory:)

It looks like its creating the table goes fine, but then it can't be found just a few lines later. I would think I had mis-configured the DB connection, except that the other tables are found and work just fine.

Software at play:

• Ruby 1.9.2p289 via RVM
• SQLite3 @3.7.7.1 via MacPorts
• DataMapper gem v 1.1.0

Does anyone know why this is gimping out, and what I can do about it?

Daunt

I've been having the same problem, but haven't found a solution :( – Are

In my case after monkeying with it further it looks like it may be a threading issue. Accessing a table too quickly in a new thread after creating it seems to cause DataMapper to emit SQL that makes SQLite sad. Moving the database access in the second thread later makes it work properly, even though the logged SQL sequence seemed valid in both cases. – Daunt

Answer 1

The problem is, I suspect, due to the thread pooling which DataMapper (or more accurately, DataObjects, the database driver DataMapper uses) does automatically. The database connection isn't shared between threads. This is fine (and beneficial, even) for something like postgresql or mysql or even sqlite3 as a 'file-backed' database. In the case of sqlite3's in memory store, the connection is the database. So additional threads will fail for that reason. Also, after a period of inactivity (~1 min?), the thread will get scavenged and the database will go away too.

If it is this, I'm not sure there's an easy work around. You might be able to modify do_sqlite3 to avoid this. Another alternative that should be basically as fast, is to use a file-backed sqlite3 DB on a ramdrive.

Detribalize

This is sounding likely. I will test today by returning to the "problematic" DB access pattern, and then swapping between in-memory and on-disk storage. – Daunt

I can't say with absolute certainty this is the problem, but if I switch to an on-disk store from an in-memory store with all other code the same, the problem does go away. Thanks for the help! – Daunt

Too late I know but maybe can help someone. You can use memory SQLite shared cache using the special filename file::memory:?cache=shared. This allows separate database connections to share the same in-memory database. sqlite.org/inmemorydb.html – Tamartamara

@Robertiano, you should write this as an additional answer. Most valuable comment here. – Accretion

Answer 2

I copy my comment as answer by suggestion of @Lukas_Skywalker

Too late I know but maybe it can help someone. You can use memory SQLite shared cache using the special filename file::memory:?cache=shared. This allows separate database connections to share the same in-memory database. sqlite.org/inmemorydb.html

Tamartamara