Dblite does not return query a second time in Linux

[jansmolders86]

Hej there,

I've been working a fair amount with this module and I really love it. Thank you for making it!

I have a strange issue though I would like to hear your take on this:

Currently I run a query to check if my database has any entries available. (eg SELECT * FROM table ORDER BY album asc) If this query returns an error or null, I kick off a separate process to fetch items to fill up the database. So far so good.

Once the separate scraping process has completed its task, I rerun the previous query to check if the database is filled with the desired entries. This is where things go wrong in Linux only. Windows works just fine.

In Linux, the function wrapping the query is being called (checked with console logs), but the query itself never returns anything. Not an error, nor any results.

If I stop the node process and restart it again, the initial check is done and of course since the separate scraping process has already filled the database, the query returns the result.

So what is happening here? Why doesn't the query do anything the second time I run it?

To see the code check here: https://github.com/jansmolders86/mediacenterjs/blob/master/apps/music/music-functions.js

My attempts to fix this so far have been:

• Added timeouts in case it is some sort of async problem
• Adding "db.close()" on several places but this only results in errors.

I'm kind of at a loss and I really hope you can shed some light on this problem.

I'm using sqlite3 version 3.7.9

Thank you again for your hard work!

Jan

[WebReflection]

my question is: which version of dblite are you using? there was a similar issue fixed not so long ago, it was about bad handling of empty tables or errors putting dblite in a weird state.

Also, any basic example able to reproduce would be appreciated, as well as more details on node version and Linux version, thanks

[jansmolders86]

Thanks for taking the time to help me out!

Here are the answers to your questions:

• DBlite version 0.4.2
• Node version 0.10.28

To create a simple example is a bit harder. Though if it would help to resolve this issue I will definitely try to create one. Of course, You can always check out the project as a whole to see the problem in action. Although, with your busy schedule I can image that is not doable. Hopefully the answers given above can indicate what the issue might be. Thanks again!

[WebReflection]

So here the thing … I've been dealing with databases for a while and having queries that silently fail is a "welcome to hell" idea but since you are not the first one that asks me for dirty behavior I've decided to put a boolean property called .ignoreErrors that is false by default

Long story short, if you use the right queries to check if the table exists, otherwise creates it, and if there is data, otherwise populate it, you should never need this kind of dirty behavior.

But if you do, then you can explicitly set the database as unsafe via .ignoreErrors that will mark the db not busy when an error occurred … otherwise I rather stick with the current behavior which is:

do not bloody do any extra thing if there was an error since most likely I've done something wrong and db might get corrupted

which is my way to deal with DBs.

Please let me know if version 0.5.0 is reasonably usable for your case which should be similar to the one shown in this test.

[jansmolders86]

Thank you very much Andrea for taking the time to add this option. I completely agree with you that this new feature should not be needed in the first place. I try to stick to your workflow, and I did so with my project. A database is create if there isn't one to begin with. The mechanism that is currently faulty in Linux only has two states concerning the database. IE, no data or data. So on paper there is no need for the ignore error flag. Nevertheless I tried it but the result remains the same in my case I'm afraid.

Is there a way to find out what is happening? Some way to print the errors if there are any? Thanks again for your help.

[WebReflection]

Trying to follow your situation here …

A database is create if there isn't one to begin with

this is not a concern of dblite, in the meaning that if your sqlite client handles this so the problem is not here … or is it?

The mechanism that is currently faulty in Linux only has two states concerning the database. IE, no data or data.

SQLite has a syntax to create tables if these are not there yet … I hope you are using such syntax … e.g.

db.query('CREATE TABLE IF NOT EXISTS tname (id INTEGER PRIMARY KEY, data TEXT)')

Accordingly, you don't need to pass through errors in order to understand if a table is there.

If it is, there is a test that passes when there is no data available so a table without data should not fail at all.

It's hard to understand our code without seeing it, or your specific case without reading queries … but what I see here are two options, in order to know if you have data or not:

1. db.lastRowID("table_name", function(rowid){if(rowid)…data else no-data})
2. assuming there's a generic row without a primary id SELECT COUNT(*) FROM table_name LIMIT 1 and then verify that function (err, data){ if (data.length) {… data …} else { … no data ...}}

Passing through these steps you should never need or find your self needing to generate an error …. way cleaner, much better, IMO.

If this does still not work then I really need you to write down a test that fails in Linux only or we are stuck :-/

[WebReflection]

just adding notes for future similar problems … you can also query the db in order to know if a table is there via:

db.query(
  'SELECT name FROM sqlite_master WHERE type=? AND name=?',
  ['table', 'your_table_name'],
  function (err, rows) {
    if (!rows.length) {
      db.query('CREATE TABLE your_table_name (id INTEGER PRIMARY KEY)');
    }
    db.query( … go on with the logic … );
  }
);

I might get this in as utility beside .lastRowID like .tableExists(tbname, callback(exists)) … will think about it.

[jansmolders86]

Hej Andrea,

I've been doing some digging to figure out why this problem occurs. I hate to waist your time so I've been trying different approaches and running tests.

I found out the problem is not Linux only. The problem occurs when I have more than 100 arrays I've checked and then written to the database.

I believe the following issue may be occurring:

I'm doing a lot of queries sequentially. Naturally, all these queries are first sent to the memory and executed, before writing the results to the database file. Could it be I'm sending such a large amount of queries in a very short time period to the memory, it can't properly write all the data back to the database itself. Eventually freezing in an error-less state. It would explain the lack of errors and the fact it is only occurring after a certain amount of files.

Thanks again for all you help!

Jan

[WebReflection]

The problem occurs when I have more than 100 arrays

do I read this correctly ? not 100 items but 100 arrays ? I wonder why you need to hold on memory all that data since you have a database which aim is to simplify this ensuring no data loss if something goes wrong.

Anyway, too much data could result in:

1. not enough space on the disk, remember sqlite duplicates/ghosts the db file behind the scene so it might take twice the amount of bytes when many operations are done without having a change to normalize and save in the original file the data
2. if your ram is full I don't know if that could cause troubles with the spawn instance
3. I really need to be able to reproduce bugs or it's just a blind guess …

Last, but not least, have you actually tried to flag the db as "unsafe" ?

Or even better, have you tried to make the operation less greedy splitting queries a part?

Have you tried to use a transaction before sending the huge amount of data?

 db.on('error', console.log.bind(console));
 db.query('BEGIN TRANSACTION');
// assuming data is an Array of arrays
while(data.length) {
  db.query('INSERT INTO table VALUES(null, ?, ?)', data.shift());
}
db.query('COMMIT');

[jansmolders86]

I've figured out what the issue was! And I must apologize because it didn't have anything to do with this module. The reason I got stuck in a error-less, response-less state was because I spawned the process that did all the heavy database lifting. This of course, filled up the buffer of the spawned process pretty quick and got things stuck. Sorry for wasting your time, I really appreciate all the help you have given me!

Best of luck! Jan