SQLiteDatabase.dylib hard crash on SELECT *

  1. ‹ Older
  2. last year

    Jay M

    10 Mar 2019 Testers, Xojo Pro NC, USA

    @Greg OLone But you stated that you were using tools other than Xojo for your tests. I’m trying to get to the bottom of the Aaron’s problem in Xojo. Your information is helpful in that it shows that it’s possible with SQLite, but there’s obviously something different about our plugin.

    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?

  3. Greg O

    10 Mar 2019 Xojo Inc scout.galaxy.barn

    @Jay M 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 I’m so adamant that we are comparing apples to apples here.

  4. Aaron H

    11 Mar 2019 Testers Europe (Germany)
    Edited last year

    @Greg OLone I'm still not convinced.

    1. It works on Windows (said Robin in the Feedback report)
    2. 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.

    I've just done a bunch of tests and have made a some observations based on the project on the submitted feedback case:

    1. 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.
    2. 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 S67

    You 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.

  5. Aaron H

    11 Mar 2019 Testers Europe (Germany)
    Edited last year

    @Jay M As I stated before, it must have something to do with the structure of his table ...

    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. :/

  6. Aaron H

    11 Mar 2019 Testers Europe (Germany)
    Edited last year

    @Jay M As I stated before, it must have something to do with the structure of his table ...

    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?

  7. Emile S

    11 Mar 2019 Europe (France, Strasbourg)

    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 Xojo’s one (unsure).

    That makes many things we do not know.

    BTW: is there’s other (non data base file, say import data from .txt or .csv or .JASON or .XML ?) file involved ?

  8. Tim S

    11 Mar 2019 Testers Canterbury, UK

    @Aaron H 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. :/

    Well you wouldn't expect it to make any difference. There are no VARCHARs in SQLite.

    Have you looked here already?

    https://www.sqlite.org/limits.html

  9. Aaron H

    11 Mar 2019 Testers Europe (Germany)

    @Tim S Well you wouldn't expect it to make any difference. There are no VARCHARs in SQLite.

    Of course SQLite handles varchar.

    @Tim S Have you looked here already?
    https://www.sqlite.org/limits.html

    Yes, as I said already in the OP and also repeated above.

  10. Tim S

    11 Mar 2019 Testers Canterbury, UK

    @Aaron H Of course SQLite handles varchar.

    I didn't say it doesn't handle them. They are treated as TEXT, and any length limit is ignored.

  11. Aaron H

    11 Mar 2019 Testers Europe (Germany)
    Edited last year

    @Tim S 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.

  12. Jay M

    11 Mar 2019 Testers, Xojo Pro NC, USA

    @Aaron H 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?

    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.

  13. Jay M

    11 Mar 2019 Testers, Xojo Pro NC, USA
    Edited last year

    @Greg OLone 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 I’m so adamant that we are comparing apples to apples here.

    So is my simple app an equal comparison, or do I have to encrypt the db to match the parameters of the test?

  14. Aaron H

    11 Mar 2019 Testers Europe (Germany)

    @Jay M 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
    );
  15. Tim S

    11 Mar 2019 Testers Canterbury, UK

    @Aaron H Oh, I see now what you meant, sorry. And thanks, I didn't know that.

    Another good reference page is:

    https://www.sqlite.org/datatype3.html

    Personally I only ever use the TEXT, INTEGER, REAL, NULL, and BLOB datatypes.

    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.

    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.

  16. Aaron H

    11 Mar 2019 Testers Europe (Germany)

    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.

  17. Tim S

    11 Mar 2019 Testers Canterbury, UK

    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.

  18. Greg O

    11 Mar 2019 Xojo Inc scout.galaxy.barn

    @Aaron H 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.

    You are a prerelease tester so you’ll be able to try this as soon as we start testing for 2019r1.

  19. Jay M

    11 Mar 2019 Testers, Xojo Pro NC, USA

    @Aaron H The Feedback case has been marked by William Yu as Fixed and Closed.

    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.

  20. Christian S

    12 Mar 2019 Testers, Xojo Pro, XDC Speakers, Third Party Store Germany

    Please also look into Feedback Case #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.

  21. Greg O

    12 Mar 2019 Xojo Inc scout.galaxy.barn

    @ChristianSchmitz Please also look into Feedback Case #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.

    We have them here. When you guys submit crash reports, we symbolicate them.

  22. Newer ›

or Sign Up to reply!