mmzeeman / esqlite Goto Github PK
View Code? Open in Web Editor NEWErlang NIF for sqlite
License: Apache License 2.0
Erlang NIF for sqlite
License: Apache License 2.0
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:
esqlite
main page.Can't wait to see this new feature included.
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]
.
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?
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
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.
The error routine should add the response code in the error tuple and set a different message for misuse responses.
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
.
esqlite
uses Dirty scheduler
: could this be the problem?Config:
. Erlang (macOS + Ubuntu 22.04 LTS): 26.2.3
. esqlite: 0.8.6
Help appreciated
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.
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:
column_names
). Queues request to command processing thread.sqlite3_finalize
.sqlite3_column_name
using a now-defunct pointer to sqlite3_stmt
. crashI 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)
https://sqlite.org/src/timeline?r=server-process-edition
https://news.ycombinator.com/item?id=17766799
Appears to be a performance improvement.
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 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!
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)
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.
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"
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:
Line 825 in 89366d4
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.
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
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.
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:
esqlite:open/4
.esqlite:prepare/4
.I added some debug logging to the native code which suggests the above sequence can (though doesn't always) expand into the following sequence:
esqlite_prepare
is called. It queues up a cmd_prepare
event.destruct_esqlite_connection
is called. It queues up a cmd_stop
event and waits for the command-processing thread to stop.cmd_prepare
event and calls do_prepare
.do_prepare
calls enif_keep_resource
on the database connection resources and subsequently returns a reference to a statement resource.cmd_stop
event and exits.destruct_esqlite_connection
is unblocked and finishes tearing down data structures.destruct_esqlite_statement
gets called. It calls enif_release_resource
on the database resource.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.
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"...>>}}}]}
Would you please add a new release to hex.pm with the OTP18 fixes? Thank you.
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..
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
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?
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.
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
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!
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.