No, I said I used another tool to fill the table with random data, which is why I couldn’t submit it to Feedback. All the testing is in Xojo. But I didn’t try using encryption. Do you think that’s a contributing factor?
The SQLite code base that we use is slightly different than the public domain version in that we have a license for their own encryption engine. There are differences from time to time which is why Im so adamant that we are comparing apples to apples here.
- It works on Windows (said Robin in the Feedback report)
- If it were some kind of threshold error (notice point 1 proves it isn’t), that shouldn’t crash, it should return an error (said all programmers everywhere)
What more convincing is needed? The fact that it crashes on Mac and Linux, but not on Windows, seems like pretty clear evidence that this must be a problem with the way the library is compiled for Mac and Linux (since probably these platforms use the same library). I said in the OP that I had checked the SQLite docs for limits on returned query size and it looks like there is none. Others have tested and showed there doesn’t seem to be a limit. And even if there is a limit, if that limit is overstepped, that shouldn’t result in a crash, it should return an error.
[quote]I’ve just done a bunch of tests and have made a some observations based on the project on the submitted feedback case:
- The size of the result sets that crash on my machine are right around 1GB. That is, 13107199 records with a size of ~81 bytes per record.
- I did a test and inserted the same data into Max1 and Max2 as was put in Points and the results were nearly identical.
It seems to me that sqlite may have a 1GB memory limit on returned recordsets, although I can find no documentation to that effect.
FWIW, You can get around this by doing multiple queries using the LIMIT and OFFSET directives to return groups of records at a time, so instead of:
SELECT * FROM S67You would use:
SELECT * FROM S67 LIMIT 10000000 OFFSET 0;and increase the offset by the number of records you requested the previous time.
SELECT * FROM S67 LIMIT 10000000 OFFSET 10000000;SELECT * FROM S67 LIMIT 10000000 OFFSET 20000000;I appreciate that., and I’m aware of this possibility, but the method doesn’t provide one recordset, so it doesn’t solve the problem. For obvious reasons, the recordset object doesn’t allow merging several objects into one, so the workaround doesn’t do what SELECT * without LIMIT and OFFSET does.
This got my thinking. maybe it is the way VARCHAR columns are allocated in the library when there is no character limit passed.
EDIT: Nope. I added character limits on all the VARCHAR columns. It didn’t change anything. Crashed with the same report. 
Jay, can you please remove the 3rd party tool from your project and add the project to the Feedback report? Or can you at least share your table structure so we can run a similar test?
Unknow stuff:
a. number of TABLE(s)
b. number of Column in the (each) TABLE(s)
c. size of the data base file
and apparently, the db machine (code) is not Xojos one (unsure).
That makes many things we do not know.
BTW: is theres other (non data base file, say import data from .txt or .csv or .JASON or .XML ?) file involved ?
[quote=427832:@Aaron Hunt]This got my thinking. maybe it is the way VARCHAR columns are allocated in the library when there is no character limit passed.
EDIT: Nope. I added character limits on all the VARCHAR columns. It didn’t change anything. Crashed with the same report. :/[/quote]
Well you wouldn’t expect it to make any difference. There are no VARCHARs in SQLite.
Have you looked here already?
Of course SQLite handles varchar.
[quote=427843:@Tim Streater]Have you looked here already?
https://www.sqlite.org/limits.html[/quote]
Yes, as I said already in the OP and also repeated above.
I didn’t say it doesn’t handle them. They are treated as TEXT, and any length limit is ignored.
Oh, I see now what you meant, sorry. And thanks, I didn’t know that.
It’s reported working on Windows, crashes on Mac and Linux. Could it be any more bleeding obvious that this is a bug in the .dylib? So I’m looking for clues anywhere, to try to help Xojo fix this.
There are no third party tools in my project. I used a third party tool (externally) to FILL the test table with random data. My test project is not going to be of any help with an empty table, and I’m not going to try uploading a multi-GB db to Feedback (it probably wouldn’t take it anyway). Your feedback case stated that it includes a method for filling your test table with data. Mine doesn’t.
I already described the structure of my table above, but here is it in SQL form to be clear:
CREATE TABLE BigTable (
id INTEGER PRIMARY KEY AUTOINCREMENT,
col1 TEXT,
col2 TEXT,
col3 TEXT
);
Like I said, simple table. I filled it with over 28 million records of varying sizes (10 to 1500 bytes) and have yet to get the Xojo app to crash on Mac or Windows. The test app is as simple as it gets - open the db file (no pragmas or anything else) and Select * into a recordset. That’s it.
So is my simple app an equal comparison, or do I have to encrypt the db to match the parameters of the test?
No, the DB is not encrypted.
Please try changing your table to this, fill only col1 with data, and see if it crashes.
CREATE TABLE BigTable (
col1 varchar,
col2 boolean,
col3 integer,
col4 varchar,
col5 varchar,
col6 varchar,
col7 varchar,
ID integer NOT NULL PRIMARY KEY
);
Another good reference page is:
https://www.sqlite.org/datatype3.html
Personally I only ever use the TEXT, INTEGER, REAL, NULL, and BLOB datatypes.
Somewhere else to ask might be the SQLite Users Mailing List (see http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users). The SQLite developers hang out there and may have some comments on what you are seeing.
The Feedback case has been marked by William Yu as Fixed and Closed. I’m grateful that the Xojo team has attended to this promptly, and I’m waiting for a link to download the fixed library.
Just FYI: I looked into this a bit and found that SQLite only returns one result row at a time. So I suppose the Xojo API loops through the results and that way, creates a result set (recordset) to return. I guess the problem cropped up there rather than in SQLite itself.
You are a prerelease tester so youll be able to try this as soon as we start testing for 2019r1.
Greg, can you give us some details of what the problem was? Just curious, because I used the table structure that Aaron listed and it did crash for me. But I also found several scenarios where it did and did not crash. I’ve got a hunch it has something to do with the number of columns in combination with the number of rows.
Please also look into <https://xojo.com/issue/55123>: include symbols dylibs for Xojo’s internal plugins
When symbols are included in dylibs, you’d better get a stack trace pointing to the problem.
[quote=428000:@Christian Schmitz]Please also look into <https://xojo.com/issue/55123>: include symbols dylibs for Xojo’s internal plugins
When symbols are included in dylibs, you’d better get a stack trace pointing to the problem.[/quote]
We have them here. When you guys submit crash reports, we symbolicate them.