mmzeeman/esqlite

Erlang NIF for sqlite

esqlite's Issues

esqlite3:receive_answer/2

What is the point of timeout? If a DB call is created, it must reply with something. Returning a timeout error because it takes too long for reply is not a good solution. I don't see any other scenario than disk being overloaded and calls are slow. In this case a write might succeed, even though timeout error was returned. Not only will it return an error when it should not have, the process using eqslite will sometime later receive the actual result as an erroneous message.

Another issue is handling any received message and throwing an error if it does not come from NIF. If esqlite is used within a gen_server that receives a call while waiting for DB, it will cause ugly errors that often only show up under stress tests or production use.

sqlite3_errmsg does not work for SQLITE_MISUSE responses

The error routine should add the response code in the error tuple and set a different message for misuse responses.

See: http://www.sqlite.org/c3ref/errcode.html

`exec` return type is `integer() | {error, error_message()}` but returns `ok | {error, error_message()}`

I don't know how Clang works, let alone erlang nifs, but I can see that there is a do_exec function whose last line is:

return make_atom(env, "ok");

So, I guess we may just change the return types. Although, having the actual number of affected rows could be great. (in an ok tuple)

Sporadic read latencies

Hi @mmzeeman

We're noticing random/sporadic read latencies on some simple queries:

SELECT id FROM docs WHERE ((user = 'john.doe') AND (((status = 'draft') OR (status = 'onhold')) OR (status = 'onprogress'))) ORDER BY creation_ts DESC LIMIT 201125, 5;

On average, this request takes 190 ms, but occasionally it can take up to 600 ms.

1. Any idea what causes this behavior?
2. New esqlite uses Dirty scheduler: could this be the problem?
3. How can we mitigate these read latencies?

Config:
. Erlang (macOS + Ubuntu 22.04 LTS): 26.2.3
. esqlite: 0.8.6

Help appreciated

Compilation issue on 0.2.5

On Debian 8 (jessie) I'm having a compilation issue, see message below, this error doesn't occur if I pin to the previous release 0.2.4.
For info, I use esqlite elixir/sqlitex.

===> Compiling pc
===> Compiling esqlite
===> Compiling /app/deps/esqlite/c_src/esqlite3_nif.c
===> /app/deps/esqlite/c_src/esqlite3_nif.c: In function �make_row�:
/app/deps/esqlite/c_src/esqlite3_nif.c:537:5: error: �for� loop initial declarations are only allowed in C99 or C11 mode
for(int i = 0; i < size; i++)
^
/app/deps/esqlite/c_src/esqlite3_nif.c:537:5: note: use option -std=c99, -std=gnu99, -std=c11 or -std=gnu11 to compile your code
/app/deps/esqlite/c_src/esqlite3_nif.c: At top level:
/app/deps/esqlite/c_src/esqlite3_nif.c:107:1: warning: �make_row_tuple� defined but not used [-Wunused-function]
make_row_tuple(ErlNifEnv *env, ERL_NIF_TERM value)
^

Docker images -> https://github.com/areski/excdr-pusher/tree/master/docker

Let me know if I can provide more information to help solving this issue.
Thanks!

Use sqlite3_open_v2 to support only read mode

It could be useful to use sqlite3_open_v2, instead of sqlite_open.

In this way it will be possible to have more control in which way the database is used.

https://www.sqlite.org/c3ref/open.html

Investigate server-process-edition branch.

https://sqlite.org/src/timeline?r=server-process-edition

html readme

https://news.ycombinator.com/item?id=17766799

Appears to be a performance improvement.

insert with parameters and get autoincrement value

I don't see a way to execute a parametrized insert and get value of the autoincremented PK. There's either a parametrized prepare/bind/step which returns '$done' or non-parametrized insert which is dangerous to use because of possible SQL injection. Am I missing something?

assertion failure on sqlite connection garbage collection

I've been playing about with esqlite and Sqlitex (an Elixir wrapper for sqlite) quite a bit lately. When I run the Sqlitex tests I frequently get assertion failures coming from here: https://github.com/mmzeeman/esqlite/blob/master/c_src/queue.c#L170

I've tried to do a bit of debugging using this patch:

diff --git a/c_src/esqlite3_nif.c b/c_src/esqlite3_nif.c
index 87787f7..a2ecbef 100644
--- a/c_src/esqlite3_nif.c
+++ b/c_src/esqlite3_nif.c
@@ -205,30 +206,32 @@ command_create()
 /*
  *
  */
-static void
+static void
 destruct_esqlite_connection(ErlNifEnv *env, void *arg)
 {
     esqlite_connection *db = (esqlite_connection *) arg;
     esqlite_command *cmd = command_create();
-
-    /* Send the stop command
+
+    printf("Destructing 0x%08X\n", (int)db);
+
+    /* Send the stop command
      */
     cmd->type = cmd_stop;
     queue_push(db->commands, cmd);
     queue_send(db->commands, cmd);
-
-    /* Wait for the thread to finish
-     */
+
     enif_thread_join(db->tid, NULL);
     enif_thread_opts_destroy(db->opts);
      */
     queue_destroy(db->commands);

     if(db->db)
-        sqlite3_close_v2(db->db);
+      sqlite3_close_v2(db->db);
+
+    printf("Destructed 0x%08X\n", (int)db);
 }

From which I get the output:

Constructed 0x16145540
Constructed 0x18601730
Destructing 0x18601730
Thread ended 0x18601730
Destructed 0x18601730
Constructed 0x18601730
Destructing 0x18601730
Thread ended 0x18601730
Destructed 0x18601730
Constructed 0x18601730
Destructing 0x18601730
Thread ended 0x18601730
Destructed 0x18601730
Constructed 0x18601730
Destructing 0x18601730
Thread ended 0x18601730
Destructed 0x18601730
Constructed 0x162000D8
Destructing 0x162000D8
Thread ended 0x162000D8
Destructed 0x162000D8
Constructed 0x18601730
Destructing 0x18601730
Thread ended 0x18601730
Destructed 0x18601730
Constructed 0x18601730
Destructing 0x18601730
Thread ended 0x18601730
Destructed 0x18601730
Constructed 0x16141320
Destructing 0x16141320
Thread ended 0x16141320
Destructed 0x16141320
Constructed 0x16141320
Destructing 0x16141320
Thread ended 0x16141320
Destructed 0x16141320
Constructed 0x16141320
Destructing 0x16141320
Thread ended 0x16141320
Destructed 0x16141320
Constructed 0x162000D8
Destructing 0x162000D8
Destructing 0x162000D8
Assertion failed: (queue->message == NULL && "Attempting to send multiple messages."), function queue_send, file c_src/queue.c, line 170.
[1] 90976 abort mix test

If you look towards the end, it appears destruct_esqlite_connection was called twice for the same connection. As I understand it this function is called from the erlang GC, so I'm not sure how it manages to get called twice, but it does appear to be happening.

I'm running this on a mac with:

Erlang/OTP 17 [erts-6.3] [source] [64-bit] [smp:8:8] [async-threads:10] [hipe] [kernel-poll:false] [dtrace]
Interactive Elixir (1.0.2) - press Ctrl+C to exit (type h() ENTER for help)

Move to rebar3-compatible build

It'd be nice to build the NIF using rebar3-compatible Makefile template with pre/post hooks as described here: https://www.rebar3.org/docs/building-cc

On OS X somehow sqlite3 of system is used.

Not sure how this is possible as the nif itself contains the correct version and source_id.

==> esqlite (eunit)
Compiled test/esqlite_test.erl
esqlite_test: sqlite_version_test...failed
in function esqlite_test:'-sqlite_version_test/0-fun-0-'/2 (test/esqlite_test.erl, line 301)
in call from esqlite_test:sqlite_version_test/0 (test/esqlite_test.erl, line 301)
**error:{assertEqual_failed,[{module,esqlite_test},
{line,301},
{expression,"esqlite3 : step ( Stmt )"},
{expected,{row,{<<"3.8.8.2">>}}},
{value,{row,{<<"3.8.5">>}}}]}

esqlite_test: sqlite_source_id_test...failed
in function esqlite_test:'-sqlite_source_id_test/0-fun-0-'/2 (test/esqlite_test.erl, line 308)
in call from esqlite_test:sqlite_source_id_test/0 (test/esqlite_test.erl, line 308)
**error:{assertEqual_failed,[{module,esqlite_test},
{line,308},
{expression,"esqlite3 : step ( Stmt )"},
{expected,{row,{<<"2015-01-30 14:30:45 7757fc721220"...>>}}},
{value,{row,{<<"2014-08-15 22:37:57 c8ade949"...>>}}}]}

Litestream capability to ESQLite

Hi @mmzeeman

This is related to our discussion on Erlang forum here:
https://erlangforums.com/t/raft-on-top-of-esqlite/3583

Please let me know how you want to receive the support. We've got a few options here:

1. GitHub already allow sponsors by adding a link to esqlite main page.
2. We can setup a wire transfer (please chat me in private).

Can't wait to see this new feature included.

Case clause error $busy

Hello there! Thanks for this library, I'm a big fan :)

I've got a web application that uses SQLite. On each request it opens a connection to SQLite.

I added a few images to a HTML page it serves, the requested from the same application and sent using file:sendfile/5, as I have done in applications that use other databases. I was surprised to see that once deployed to https://fly.io/ the images would load very slowly and sometimes 500 error.

I checked the logs and I found the application was crashing with a case clause error on the atom $busy. I found the atom here:

esqlite/c_src/esqlite3_nif.c

Line 825 in 89366d4

return make_atom(env, "$busy");

Now I look closer the images do (less frequently) load slowly outside of Fly, though they have yet to crash. Perhaps the network latency is making the issue worse.
Do you have any idea what the issue might be here?

OTP: 26, ERTS: 14.2.2

Thanks,
Louis

edit: If I replace sendfile reading and then sending the file as a bit string I get the same problem.

New release for OTP18

Would you please add a new release to hex.pm with the OTP18 fixes? Thank you.

Binding printable only binaries and strings do not always result in a text, but a blob

This query only works when Name is an atom. It should also work when Name is either a list or a binary.

case esqlite3:q(<<"SELECT count(type) FROM sqlite_master WHERE type='table' AND name=?;">>, 
                [Name], Connection) of
        [{1}] -> true;
        [{0}] -> false
    end.

Fatal error C1083: Cannot open include file: 'stdlib.h': No such file or directory

From: elixir-sqlite/sqlite_ecto2#206

Hello, I'm compiling esqlite3_nif.c on Windows 10 using VisualCppBuildTools 14.0.25420.1 (Chocolatey Package):

c:\Program Files\erl9.3\erts-9.3\include\erl_nif.h(71): fatal error C1083: Cannot open include file: 'stdlib.h': No such file or directory

** (Mix) Could not compile dependency :esqlite, "escript.exe "c:/Users/usr/.mix/rebar3" bare compile --paths "c:/Users/usr/Desktop/proj/_build/dev/lib/*/ebin"" command failed. 
You can recompile this dependency with "mix deps.compile esqlite", update it with "mix deps.update esqlite" or clean it with "mix deps.clean esqlite"

mix deps.compile esqlite fails while linking esqlite3_nif.so on ARM

Specifically, it's a Raspberry Pi 4 where this is failing.

The exact error message is:

** (Mix) Could not compile dependency :esqlite, "/home/pi/.mix/rebar3 bare compile --paths="/home/pi/Documents/programming stubs/friends/_build/dev/lib/*/ebin"" command failed. You can recompile this dependency with "mix deps.compile esqlite", update it with "mix deps.update esqlite" or clean it with "mix deps.clean esqlite"

What can I do to fix this and make it compile?

Named parameters

Hello! Thank you for this excellent library, I'm really enjoying it, very useful.

Is there a way to use named parameters with this library?
From reading the source it seems this is not possible. Would it be possible to add this feature? I find named parameters can help make larger queries a bit easier to read.

In case there is interest, here are some rough initial ideas for the API

% map arguments
eqslite:q(DB, "select :name", #{name => "esqlite"}).

% This one would maintain ordering, which may be preferable with
% SQLite, but atom keys could not be used as they are used currently
% for type annotations
eqslite:q(DB, "select :name", [{"name", "esqlite"}]).

% Alternatively they could always have type annotations, to resolve
% the ambiguity of a 2 element tuple
eqslite:q(DB, "select :name", [{text, name, "esqlite"}]).

Thanks,
Louis

enif_alloc_env one msg_env, after use to send msg but not enif_free_env

i see the code found in the 'esqlite3_nif.c' and the 'update_callback' fun use enif_alloc_env to alloc one env to send msg but not enif_free_env. it is seem reource leak

Erlang/OTP 23 /usr/bin/ld: cannot find -lerl_interface

Linking seems to fail with rebar3 defaults on Erlang/OTP 23 as they have removed erl_interface.so from the shipped bundle.

See https://www.erlang.org/news/140

I had to manually work around with a new Makefile. Not sure though if just removing the erl_interface dependency works on older versions of OTP..

Occasional segfaults

I dont have a way of reproducing it yet, but I have experienced segmentation faults on multiple occasions, crashing the whole VM. I'll try write a test to reproduce it.

Here's one:
https://travis-ci.org/arjan/persi/jobs/27609491

It doesnt seem to be specific to R16, also seen it on other versions.

Race condition: Occasional VM crash due to dangling pointer to sqlite3_stmt struct

As mentioned in my final comment on #33, I am still seeing occasional VM crashes, though with new symptoms. The crashes I am seeing now are always segfaults (no assertion failures as in #33), and with a build instrumented to print stack traces, the crashes are always in sqlite3_* API functions.

I believe the following sequence of operations is happening:

1. (scheduler thread) Create DB.
2. (scheduler thread) Prepare a statement.
3. (scheduler thread) Request information about that prepared statement (i.e. column_names). Queues request to command processing thread.
4. (scheduler thread) Thread gets killed.
5. (scheduler thread) Garbage collects statement reference, thus triggering a call to sqlite3_finalize.
6. (command processing thread) Processes the request queued at step 3, thus triggering a call to sqlite3_column_name using a now-defunct pointer to sqlite3_stmt. crash

Library module name 'esqlite3_nif' does not match calling module ...

I've tried several things but I'm getting:

{"init terminating in do_boot",{{badmatch,{error,{bad_lib,"Library module name 'esqlite3_nif' does not match calling module 'fmv1992_blobs'"}}},[{fmv1992_blobs,main,1

It is my first time using a NIF, so I might be missing something obvious. In any case, perhaps the README could have instructions for newbie erlangers?

Thanks!

esqlite return "error = 21"

Hi @mmzeeman

We switched to the latest esqlite version yesterday (we were previously using 0.8.6 for years) and we started getting the following error:

This happens sporadically and under almost no load (not even during concurrent writes).
Do you have any idea what can lead to this?
Many thanks

. Erlang 26.1.2
. rebar 3.22.0 on Erlang/OTP 26 Erts 14.0.2
. OS: macOS Sonoma | Ubuntu 22.04 LTS

Module can't be loaded on Windows

I'm having trouble getting esqlite to compile on Windows 10

I get two different error messages depending on how I try to compile it.
Either

1> erlang:load_nif("esqlite3_nif", 0).
{error,{bad_lib,"Library module name 'esqlite3_nif' does not match calling module 'erl_eval'"}}

Or

 1> erlang:load_nif("./esqlite3_nif", 0).
 {error,{load_failed,"Failed to load NIF library ./esqlite3_nif: 'Unspecified error'"}}

This started when I created a new phoenix app using sqlite as a database.

mix phoenix.new webend --database sqlite

but that failed since compiling esqlite failed. Compiler cl.exe was not found in path but luckily I had Visual Studio installed on my computer so I just added the bin-folder to path. After trying again to compile esqlite using

mix deps.compile esqlite

it failed again. This time it couldn't find some files if I remember right. I installed proper rebar and added that to my path. Again tried to compile and it still failed. Then I started developer command prompt for visual studio and tried to compile esqlite again. This time it worked.

But loading the module will fail with unspecified error or not matching calling module 'erl_eval'.

I tried with following combinations:
cl.exe from C:\Program Files (x86)\Microsoft Visual Studio 14.0\VC\bin
cl.exe from C:\Program Files (x86)\Microsoft Visual Studio 14.0\VC\bin\amd64_x86
cl.exe from C:\Program Files (x86)\Microsoft Visual Studio 14.0\VC\bin\x86_amd64
and several VS2015 * tools command prompts

Compiling esqlite will give lots of warnings but it will always compile. The module just can't be loaded.

I'm using
Erlang/OTP 18 [erts-7.0] [64-bit] [smp:12:12] [async-threads:10]
Eshell V7.0 (abort with ^G)
esqlite 0.2.1
Windows 10 64bit ENG
Visual Studio 2015

Is there a way to get results out of a transaction?

Is there a way to use transactions and get results out of it?

esqlite3:q(
<<"
BEGIN;
-- SELECT data from blobs where md5sum = ?;
-- INSERT INTO blobs_access_record (md5sum) values (?);
SELECT 1;
SELECT 1;
SELECT 1;
COMMIT;
">>,
[],
Db)

Returns []. I would like to get the [1,1,1].

Occasional VM crash due to race condition between deferred prepare and garbage collection

This is admittedly an unlikely condition in real-world usage, but I have observed an occasional BEAM crash in the esqlite NIF code when the following sequence of events occurs in quick succession:

1. A database is created using esqlite:open/4.
2. A statement is prepared using esqlite:prepare/4.
3. The database resource gets garbage collected.

I added some debug logging to the native code which suggests the above sequence can (though doesn't always) expand into the following sequence:

1. (Scheduler thread) Database created. It creates the command-processing thread.
2. (Scheduler thread) esqlite_prepare is called. It queues up a cmd_prepare event.
3. (Scheduler thread) destruct_esqlite_connection is called. It queues up a cmd_stop event and waits for the command-processing thread to stop.
4. (Command processor thread) Receives the cmd_prepare event and calls do_prepare.
5. (Command processor thread) do_prepare calls enif_keep_resource on the database connection resources and subsequently returns a reference to a statement resource.
6. (Command processor thread) Processes cmd_stop event and exits.
7. (Scheduler thread) destruct_esqlite_connection is unblocked and finishes tearing down data structures.
8. (Scheduler thread) destruct_esqlite_statement gets called. It calls enif_release_resource on the database resource.
9. (Scheduler thread) destruct_esqlite_connection gets called a second time for the same resource cited in step #3 above. The data structures were released at step 7 so the pointers are now invalid. Crash with an assertion failure or segfault.

Here's an example from a recent test run:

esqlite_start
  conn = 0000000014902e60
  queue = 0000000014d800b0

esqlite_prepare called on conn 0000000014902e60
do_prepare
  conn = 0000000014902e60
  sql = PRAGMA foreign_keys

destruct_esqlite_connection
  conn = 0000000014902e60
do_prepare calling keep_resource on conn 0000000014902e60
destruct_esqlite_statement calling release_resource on conn 0000000014902e60

destruct_esqlite_connection
  conn = 0000000014902e60
Assertion failed: (queue->message == NULL && "Attempting to send multiple messages."), function queue_send, file c_src/queue.c, line 173.
Abort trap: 6

Note that segfaults and other assertions have been observed as well, presumably because the precise failure mode can vary depending on how the data structures have been altered since being released.

Also: Line numbers may be slightly different from current master branch because I've added instrumentation.

The crash happens about 20-30% of the time if I run the test suite for elixir-sqlite/sqlite_ecto2#29. I'm attempting to derive a simpler test case for it, but haven't succeeded in stripping it down yet. If I do, I'll add that as a comment here.

I may also attempt a fix, but NIFs are relatively new to me.

Timeouts on heavy insert load

I am running into timeouts when trying to bulk insert data.

I am using esqlite3 as a local key/value store. I have a CSV file with a short string key and 1KB of JSON data, about 1M records.

I load the data by making a series of insert calls, e.g.

def insert_records(records, db_path, commit_cycle) do
    chunks = Enum.chunk_every(records, commit_cycle)
    for chunk <- chunks do
        {:ok, db} = :esqlite3.open(db_path)
        {:ok, statement} = :esqlite3.prepare("INSERT OR REPLACE INTO kv_data (key, value) VALUES(?1, ?2);", db)
        :ok = :esqlite3.exec("begin;", db)
        for {key, value} <- chunk, do: insert_row(statement, [key, value])
        :ok = :esqlite3.exec("commit;", db)
        :ok = :esqlite3.close(db)
    end
    :ok
  end

  def insert_row(statement, params), do: insert_row(statement, params, :first, 1)

  def insert_row(statement, params, :first, count) do
    :ok = :esqlite3.bind(statement, params)
    insert_row(statement, params, :esqlite3.step(statement), count)
  end
  def insert_row(statement, params, :"$busy", count) do
    :timer.sleep(10)
    insert_row(statement, params, :esqlite3.step(statement), count + 1)
  end
  def insert_row(_statement, _params, :"$done", count) do
    if count > 1 do
      Lager.debug("sqlite3 busy count: #{count}")
    end
    :ok
  end
  def insert_row(_statement, params, {:error, reason}, _count) do
    Lager.error("esqlite: Error inserting #{inspect params}: #{inspect reason}")
    :ok
  end

As you can see, I am handling the $busy results, but eventually I get a timeout error.
The problem only occurs on servers with spinning HDDs, SSDs are ok.