SQLite - error regression in Julia 1.8 vs Julia 1.7

[bkamins]

The source code used is from https://github.com/bkamins/JuliaForDataAnalysis/blob/main/ch08.jl (and all data needed to reproduce is there).

puzzles is a large data frame. The following code starting from https://github.com/bkamins/JuliaForDataAnalysis/blob/40ed58df742537a3335be2aedecb9199ff8668df/ch08.jl#L135 works correctly under Julia 1.7:

using SQLite
db = SQLite.DB("puzzles.db")

SQLite.load!(puzzles, db, "puzzles")

but under Julia 1.8 on Windows I get:

julia> using DataFrames

julia> using CSV

julia> puzzles = CSV.read("puzzles.csv", DataFrame);

julia> using SQLite

julia> db = SQLite.DB("puzzles.db")
SQLite.DB("puzzles.db")

julia> SQLite.load!(puzzles, db, "puzzles")

Please submit a bug report with steps to reproduce this fault, and any error messages that follow (in their entirety). Thank
s.
Exception: EXCEPTION_ACCESS_VIOLATION at 0x7ffee7e595ca -- memcpy at C:\WINDOWS\System32\msvcrt.dll (unknown line)
in expression starting at REPL[7]:1
memcpy at C:\WINDOWS\System32\msvcrt.dll (unknown line)
sqlite3VdbeExec at /workspace/srcdir/sqlite-autoconf-3390400\sqlite3.c:91839
sqlite3Step at /workspace/srcdir/sqlite-autoconf-3390400\sqlite3.c:86740 [inlined]
sqlite3_step at /workspace/srcdir/sqlite-autoconf-3390400\sqlite3.c:86801
sqlite3_step at C:\Users\bogum\.julia\packages\SQLite\oQymI\src\api.jl:139 [inlined]
#30 at C:\Users\bogum\.julia\packages\SQLite\oQymI\src\tables.jl:264
transaction at C:\Users\bogum\.julia\packages\SQLite\oQymI\src\SQLite.jl:573
unknown function (ip: 000000005d138a94)
transaction at C:\Users\bogum\.julia\packages\SQLite\oQymI\src\SQLite.jl:565
#load!#29 at C:\Users\bogum\.julia\packages\SQLite\oQymI\src\tables.jl:254
load! at C:\Users\bogum\.julia\packages\SQLite\oQymI\src\tables.jl:238 [inlined]
load! at C:\Users\bogum\.julia\packages\SQLite\oQymI\src\tables.jl:238
unknown function (ip: 000000005d137bf5)
#load!#28 at C:\Users\bogum\.julia\packages\SQLite\oQymI\src\tables.jl:211
load! at C:\Users\bogum\.julia\packages\SQLite\oQymI\src\tables.jl:206
jl_apply at /cygdrive/c/buildbot/worker/package_win64/build/src\julia.h:1839 [inlined]
do_call at /cygdrive/c/buildbot/worker/package_win64/build/src\interpreter.c:126
eval_value at /cygdrive/c/buildbot/worker/package_win64/build/src\interpreter.c:215
eval_stmt_value at /cygdrive/c/buildbot/worker/package_win64/build/src\interpreter.c:166 [inlined]
eval_body at /cygdrive/c/buildbot/worker/package_win64/build/src\interpreter.c:594
jl_interpret_toplevel_thunk at /cygdrive/c/buildbot/worker/package_win64/build/src\interpreter.c:750
jl_toplevel_eval_flex at /cygdrive/c/buildbot/worker/package_win64/build/src\toplevel.c:906
jl_toplevel_eval_flex at /cygdrive/c/buildbot/worker/package_win64/build/src\toplevel.c:850
ijl_toplevel_eval at /cygdrive/c/buildbot/worker/package_win64/build/src\toplevel.c:915 [inlined]
ijl_toplevel_eval_in at /cygdrive/c/buildbot/worker/package_win64/build/src\toplevel.c:965
eval at .\boot.jl:368 [inlined]
eval_user_input at C:\buildbot\worker\package_win64\build\usr\share\julia\stdlib\v1.8\REPL\src\REPL.jl:151
repl_backend_loop at C:\buildbot\worker\package_win64\build\usr\share\julia\stdlib\v1.8\REPL\src\REPL.jl:247
start_repl_backend at C:\buildbot\worker\package_win64\build\usr\share\julia\stdlib\v1.8\REPL\src\REPL.jl:232
#run_repl#47 at C:\buildbot\worker\package_win64\build\usr\share\julia\stdlib\v1.8\REPL\src\REPL.jl:369
run_repl at C:\buildbot\worker\package_win64\build\usr\share\julia\stdlib\v1.8\REPL\src\REPL.jl:355
jfptr_run_repl_65039.clone_1 at C:\Users\bogum\AppData\Local\Programs\Julia-1.8.2\lib\julia\sys.dll (unknown line)
#967 at .\client.jl:419
jfptr_YY.967_34052.clone_1 at C:\Users\bogum\AppData\Local\Programs\Julia-1.8.2\lib\julia\sys.dll (unknown line)
jl_apply at /cygdrive/c/buildbot/worker/package_win64/build/src\julia.h:1839 [inlined]
jl_f__call_latest at /cygdrive/c/buildbot/worker/package_win64/build/src\builtins.c:774
#invokelatest#2 at .\essentials.jl:729 [inlined]
invokelatest at .\essentials.jl:726 [inlined]
run_main_repl at .\client.jl:404
exec_options at .\client.jl:318
_start at .\client.jl:522
jfptr__start_43806.clone_1 at C:\Users\bogum\AppData\Local\Programs\Julia-1.8.2\lib\julia\sys.dll (unknown line)
jl_apply at /cygdrive/c/buildbot/worker/package_win64/build/src\julia.h:1839 [inlined]
true_main at /cygdrive/c/buildbot/worker/package_win64/build/src\jlapi.c:575
jl_repl_entrypoint at /cygdrive/c/buildbot/worker/package_win64/build/src\jlapi.c:719
mainCRTStartup at /cygdrive/c/buildbot/worker/package_win64/build/cli\loader_exe.c:59
BaseThreadInitThunk at C:\WINDOWS\System32\KERNEL32.DLL (unknown line)
RtlUserThreadStart at C:\WINDOWS\SYSTEM32\ntdll.dll (unknown line)
Allocations: 112066108 (Pool: 112038721; Big: 27387); GC: 33

[bkamins]

I get this error on Win11, on Linux there is no error.

[metab0t]

I play with this example for a while and observe some quirks.
If I run SQLite.load!(@view(puzzles[1500000:2000000, :]), db, "puzzles") directly, it will throw the same error.
If I run SQLite.load!(@view(puzzles[1500000:1600000, :]), db, "puzzles") and change 1500000:1600000 to 1600000:1700000, 1700000:1800000, etc., it will not error.

Update:
The failure is so weird, I can run load! successfully for the first time, then the second try with a new table name fails.

I tried to only load several columns selectively, columns 1:10 is fine but 1:12 fails.
I suspect that this is caused by internal of Julia instead of code of SQLite.jl.

[bkamins]

I get mixed results - sometimes things work, sometimes things fail. Indeed it is hard to identify the pattern.
I will ask on #internals on Slack.

[bkamins]

@vchuravy suggested that the issue is GC and indeed it is.

We pass some data in load! that GC tries to free, but SQLite tries to access. The reason why we have a problem is that GC heuristics changed in 1.8. However, this means that the bug was already in 1.7 (just it did not surface).

CC @quinnj

[metab0t]

@quinnj My understanding is that the bind! method with string argument only records the pointer owned by row so we need to keep it alive. Is it correct?

[bkamins]

@quinnj - this has to be reopened :(.

I have checked on another Win11 machine and we have the following behavior:

Tables.rowtable, Tables.columntable and DataFrame happen to work sometimes (so they do not always fail) but also all throw:

Exception: EXCEPTION_ACCESS_VIOLATION at 0x7ffee7e595ca -- memcpy at C:\WINDOWS\System32\msvcrt.dll (unknown line)
in expression starting at REPL[6]:1
memcpy at C:\WINDOWS\System32\msvcrt.dll (unknown line)
sqlite3VdbeExec at /workspace/srcdir/sqlite-autoconf-3390400\sqlite3.c:91839
sqlite3Step at /workspace/srcdir/sqlite-autoconf-3390400\sqlite3.c:86740 [inlined]
sqlite3_step at /workspace/srcdir/sqlite-autoconf-3390400\sqlite3.c:86801
sqlite3_step at C:\Users\bogum\.julia\packages\SQLite\94qEF\src\capi.jl:720 [inlined]
#32 at C:\Users\bogum\.julia\packages\SQLite\94qEF\src\tables.jl:330
transaction at C:\Users\bogum\.julia\packages\SQLite\94qEF\src\SQLite.jl:654
unknown function (ip: 0000000064287708)
transaction at C:\Users\bogum\.julia\packages\SQLite\94qEF\src\SQLite.jl:646
#load!#31 at C:\Users\bogum\.julia\packages\SQLite\94qEF\src\tables.jl:319
load! at C:\Users\bogum\.julia\packages\SQLite\94qEF\src\tables.jl:286 [inlined]
load! at C:\Users\bogum\.julia\packages\SQLite\94qEF\src\tables.jl:286
unknown function (ip: 000000006428652e)
#load!#30 at C:\Users\bogum\.julia\packages\SQLite\94qEF\src\tables.jl:248

The reason I checked this is that eg. Tables.rowtable should not do any allocations.
Also I modified the source code to use row inside the loop after the call that uses GC.@preserve to make sure that it is not GCed, but this does not help.

However, disabling GC with GC.enable(false) makes the process work always.

Additionally I checked that:

julia> @time SQLite.load!(Tables.rowtable(puzzles), db, "puzzles");
  4.167506 seconds (44.10 M allocations: 1.293 GiB, 5.43% gc time)

which shows that SQLite.load! does a lot of allocations even though iterating Tables.rowtable(puzzles) should not allocate at all and be type stable. (Tables.rowtable(puzzles) call is 2.13M of allocations so we have over 40M allocations in SQLite.load!)

Also I think I identified the root cause of the problem. The schema of the table loaded is:

julia> describe(puzzles, :eltype)
9×2 DataFrame
 Row │ variable         eltype   
     │ Symbol           DataType
─────┼───────────────────────────
   1 │ PuzzleId         String7
   2 │ FEN              String
   3 │ Moves            String
   4 │ Rating           Int64
   5 │ RatingDeviation  Int64
   6 │ Popularity       Int64
   7 │ NbPlays          Int64
   8 │ Themes           String
   9 │ GameUrl          String

Now if I change PuzzleId from String7 to String I do not get an exception. So it seems that the memory access problem is some conversion of String7 to String during loading.

[quinnj]

Well shoot, we're getting bit by:

cconvert(::Type{Ptr{UInt8}}, s::AbstractString) = String(s)
cconvert(::Type{Ptr{Int8}}, s::AbstractString) = String(s)

so our String7 objects are getting converted to String, but not being GC preserved anywhere, so they quickly get cleaned up and the pointers are invalid.

[quinnj]

Ok, so this bites us in the following way:

• we get a row from a table that has InlineStrings
• We call bind! to attach the values to the sqlite stmt for inserting a single row
• for the lifetime of the bind! call, the InlineString is converted to String and GC.preserved
• once the bind! call finishes, however, the converted String is no longer GC.preserved
• We then subsequently call sqlite_step which actually executes the insert

So I see 2 options:

• either we convert inline string to String ourselves and ensure it's GC.preserved until after sqlite3_step
• Define a cconvert method for InlineString that is correct so we can pass inline string directly to sqlite correctly

Obviously the benefit of the 2nd option is avoiding extra allocations, so I'm going to explore that option.

[vchuravy]

Yeah define the cconvert and unsafe_convert yourself. But you likely need to make sure that your InlineStrings are zero terminated.

[quinnj]

Ok, here's part 1: JuliaStrings/InlineStrings.jl#56. But I realized we actually need to do both options I outlined above; i.e. the InlineStrings.jl PR only avoids the unnecessary String allocation for InlineStrings, but won't fix the fundamental issue here which is that we need the cconverted arguments to be GC.preserved all the way until we're done calling sqlite3_step. I have an idea on how to do that.