Repository: sql-js/sql.js (language: javascript)

Issue:
Memory issues in 1.9.0
Some of our users are running into memory issues which weren't happening on 1.8.0. I can also confirm that we were able to resolve these issues by downgrading back to 1.8.0.

Some of the console errors when this issue happens:

```Database closed```

```Out of bounds call_indirect```

```Out of bounds memory access (evaluating 'e.apply(null,s)')```

Happens in Safari (Mac), Chrome (Mac & Windows), Edge (Windows), so not an implementation issue, I think.

From some quick Googling it looks like an Emscripten build configuration thing, but I have to admit that I'm absolutely clueless about these things.

If I can do anything more specific to help set up a test case, happy to hear how I can help better.


Discussion / hints:
Which version of sql.js are you using? The wasm one? Do you use features like custom js functions?
May be related to https://github.com/emscripten-core/emscripten/issues/11208 ?

@kripken, do you have an idea where this could come from ? We bumped emscripten from 3.1.20 to 3.1.49

@pieterbeulque , if you could provide a minimal reproduction, that would be great !
`wasm`, yes! Everything is pretty vanilla, no custom JS functions.

I'll try and free up some time to see if I can whip up a minimal reproduction.

Thank for the quick response.
Looks like 3.1.20 to 3.1.49 is over a year of updates, so it's hard to guess what could cause this. My best idea would be some change in how LLVM optimizes undefined behavior. I would recommend bisecting on emscripten revisions to find where this regressed, that is usually the fastest method in situations like this:

https://emscripten.org/docs/contributing/developers_guide.html#bisecting
Yes, we'll need a good repro from @pieterbeulque for that, thanks @kripken !
Do you think there is undefined behavior in sqlite ? I would find that surprising.
+1 sqljs 1.9.0 is making our project run out of memory, pinning the version to 1.8.0 works for us

```
    const initializer = await sql({
      locateFile: (file: string) =>
        `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.8.0/${file}`,
    })
```
@louisjoecodes : a small self contained reproduction would be welcome!
+1. `Error: out of memory` and appears on calling initSqlJs
@ejechev : same. Small reproduction welcome. 
I spent an hour on a minimal repro but we use SQL views very heavily and I'm failing to get it to break in a simpler setup—I'd have to figure out a way to get a snapshot of the database in production that runs out of memory and set up a reproduction with that.

Unfortunately as a solo dev for our company I can't really dedicate more time to this issue, so I'll just be pinning us to 1.8.0 for the time being.

Appreciate the work! 

Thanks for the efforts anyway, @pieterbeulque ! This is appreciated 👍
oh god I was going crazy for not knowing what was wrong, until I compared files with another old application, and the only difference was the wasm file, for the moment I will use version 1.8

thank you for your contributions!
@JuanLongines Please provide a self contained reproduction
> @JuanLongines Please provide a self contained reproduction

Reproduction for me: 

create a new angular project 

```
Angular CLI: 17.0.6
Node: 18.17.0      
Package Manager: npm 10.2.3
OS: win32 x64
```

install dependencies:
`npm install --save @capacitor-community/sqlite`
apparently this version of angular already has the sql.js version 1.9 package declared in package.json

I created a new service to create the connection to the sqlite database, and run` npm serve` and I got the error:

![Untitled](https://github.com/sql-js/sql.js/assets/51804152/e11f991f-3016-41e6-929b-16e70356b46d)
![Untitled](https://github.com/sql-js/sql.js/assets/51804152/8ce3228d-f5ed-479c-8890-94835be1a522)


practically my connection was the same because I have it in other apps, I looked for solutions but I couldn't find anything... until I used winmerge to compare files, there were no differences in the files, only the wasm, in winmerge I only looked at the content in hexa and it was different, I decided to copy the wasm from my old project (ver 1.8) and it worked
similar problem here, popping up "Error: out of memory" for new builds using 1.9.0
I think it's clear by now that there is a general problem with the release. What we need is for someone to take the time to investigate it. 
confirm ver 1.9.0 breaks even the simpliest empty sqlite db creation. See: https://github.com/jepiqueau/jeep-sqlite/issues/33#issuecomment-1864671240
while 1.8.0 works perfect.


also running into issues with 1.9.0, works on 1.8.0

happens when INSERTing data, running on NodeJs
```
RuntimeError: memory access out of bounds
    at wasm://wasm/002b23f6:wasm-function[2098]:0x9584b
    at wasm://wasm/002b23f6:wasm-function[1591]:0x7a0fc
    at wasm://wasm/002b23f6:wasm-function[67]:0x269a
    at wasm://wasm/002b23f6:wasm-function[476]:0x2595b
    at wasm://wasm/002b23f6:wasm-function[358]:0x19489
    at wasm://wasm/002b23f6:wasm-function[984]:0x42ddd
    at wasm://wasm/002b23f6:wasm-function[398]:0x1f3ce
    at wasm://wasm/002b23f6:wasm-function[393]:0x1b14e
    at wasm://wasm/002b23f6:wasm-function[386]:0x1a89a
    at wasm://wasm/002b23f6:wasm-function[136]:0x711c
```
@larshp , are you interested in helping debugging this ?

@kripken suggested bisecting on the emscripten version. It's a little bit of work, but it will help a lot of people.
I wonder if the new release fixes this regression. If any one of the commenters has a more straight-forward way of testing this than me, I'd love to see you try it out.
thanks, looks like I'm getting the same error on 1.10.1

```
RuntimeError: memory access out of bounds
    at wasm://wasm/002badfe:wasm-function[2116]:0x97859
    at wasm://wasm/002badfe:wasm-function[1608]:0x7be53
    at wasm://wasm/002badfe:wasm-function[67]:0x26ae
    at wasm://wasm/002badfe:wasm-function[476]:0x25a87
    at wasm://wasm/002badfe:wasm-function[358]:0x1952b
    at wasm://wasm/002badfe:wasm-function[984]:0x42f60
    at wasm://wasm/002badfe:wasm-function[398]:0x1f510
    at wasm://wasm/002badfe:wasm-function[393]:0x1b293
    at wasm://wasm/002badfe:wasm-function[386]:0x1a9df
    at wasm://wasm/002badfe:wasm-function[136]:0x7130
```







Thanks for verifying @larshp 
minimal reproduction still welcome
here we go, https://github.com/larshp/sqljs-crash

the crash https://github.com/larshp/sqljs-crash/actions/runs/7593311451/job/20683652663 with logic https://github.com/larshp/sqljs-crash/blob/main/src/index.js

and the same code working on 1.8.0 https://github.com/larshp/sqljs-crash/pull/1

in my scenario it looks like its happening when inserting data that is longer than 64k bytes?

I'm doing `require('sql.js/dist/sql-wasm-debug');`, and it seems to work with `require('sql.js');`, but still it should not crash?
That 64K number makes me think this might be related to the stack. Does sql.js pass data of arbitrary size there? If so then it could be the [default stack size changes in 3.1.27](https://github.com/emscripten-core/emscripten/blob/main/ChangeLog.md#3127---112922). If that's it then rebuilding with `-sSTACK_SIZE=5MB` would fix things, as mentioned in the link.

Another way to confirm that is to build with `-sSTACK_OVERFLOW_CHECK=2` or `-sASSERTIONS=2` which will add (expensive) checks for stack overflows.

If stack size is relevant and sql.js uses pthreads then [3.1.30](https://github.com/emscripten-core/emscripten/blob/main/ChangeLog.md#3130---011123) also has a relevant change.
@kripken : yes, sql.js [does put the sql string to execute on the stack](https://github.com/sql-js/sql.js/blob/master/src/api.js#L948). I'll open a pr with the changes you suggest.

Fix patch:
diff --git a/Makefile b/Makefile
index d487e496..ae303266 100644
--- a/Makefile
+++ b/Makefile
@@ -37,7 +37,8 @@ EMFLAGS = \
 	-s EXPORTED_RUNTIME_METHODS=@src/exported_runtime_methods.json \
 	-s SINGLE_FILE=0 \
 	-s NODEJS_CATCH_EXIT=0 \
-	-s NODEJS_CATCH_REJECTION=0
+	-s NODEJS_CATCH_REJECTION=0 \
+	-s STACK_SIZE=5MB
 
 EMFLAGS_ASM = \
 	-s WASM=0
@@ -56,7 +57,7 @@ EMFLAGS_OPTIMIZED= \
 	--closure 1
 
 EMFLAGS_DEBUG = \
-	-s ASSERTIONS=1 \
+	-s ASSERTIONS=2 \
 	-O1
 
 BITCODE_FILES = out/sqlite3.bc out/extension-functions.bc


Test patch:
diff --git a/test/test_long_sql_statement.js b/test/test_long_sql_statement.js
new file mode 100644
index 00000000..3005c087
--- /dev/null
+++ b/test/test_long_sql_statement.js
@@ -0,0 +1,34 @@
+// test for https://github.com/sql-js/sql.js/issues/561
+exports.test = function (sql, assert) {
+    // Create a database
+    var db = new sql.Database();
+    var len = 70000;
+    var many_a = "";
+    for (var i = 0; i < len; i++) many_a += 'a';
+
+    var res = db.exec("select length('" + many_a + "') as len");
+    var expectedResult = [
+        {
+            columns: ['len'],
+            values: [
+                [len]
+            ]
+        }
+    ];
+    assert.deepEqual(res, expectedResult, "length of long string");
+};
+
+if (module == require.main) {
+    const target_file = process.argv[2];
+    const sql_loader = require('./load_sql_lib');
+    sql_loader(target_file).then((sql) => {
+        require('test').run({
+            'test long sql string (issue 561)': function (assert) {
+                exports.test(sql, assert);
+            }
+        });
+    }).catch((e) => {
+        console.error(e);
+        assert.fail(e);
+    });
+}