Sqlite SQLITE_MISUSE error in node.js script

Question

When I launch code with this query, sometimes I can see all the tables listed, sometimes only one and I get always this error:

Query Error: Error: SQLITE_MISUSE: unknown error

I've read that SQLITE_MISUSE occurs when SQLITE API is used unproperly. Could you help me, because I can't find whats wrong in this code.

EDIT. I've made changes to the code to get rid of the race issue.

The message with SQLITE_MISUSE wrror still occurs, however the vanishing tables issue is gone. Race in my queries was the case.

Here is the code.

var sqlite3 = require("node-sqlite3");
var fs = require('fs');

var query_count;
var init = function (response) {
    var db = new sqlite3.Database("test.db", function() {

        fs.readFile('./assets/sql/initDB.sql', function(err,data){
            if(err) {
                console.error("Could not open file: %s", err);
                return;
            }

            var query = data.toString('utf8');
            queries = query.split(";");

            db.serialize(function() {
                query_count = queries.length;
                for(var i=0; i<queries.length; i++) {
                    queries[i] = queries[i].replace("\r\n","");
                    db.run(queries[i], function(error) {
                        if(error) {
                            console.log("Query Error: "+error);
                        }

                        query_count--;

                        if( query_count <= 0 ) {
                            db.close();
                            listAllTables(response);
                        }
                    });
                }
            });
        });
    });
};

function listAllTables(response) {
    var db = new sqlite3.Database("./assets/sql/test.db", function () {
        db.all("SELECT name FROM sqlite_master WHERE type = 'table'", function (error, records) {
            for(var record in records) {
                response.write(record+": "+records[record]+"\n");
                for(var prop in records[record]) {
                    response.write("\t"+prop+": "+records[record][prop]+"\n");
                }
            }

            response.end();
            db.close();
        });
    });
}

exports.load_customers = function(response) {
    init(response);
};

The query file initDB.sql is like this:

CREATE TABLE IF NOT EXISTS TemporaryAuthTokens (
  authToken TEXT PRIMARY KEY NOT NULL UNIQUE,
  expireDate NUMERIC NOT NULL);

CREATE  TABLE IF NOT EXISTS User (
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE  ,
  login TEXT NOT NULL ,
  pass TEXT NOT NULL ,
  creationDate NUMERIC NOT NULL ,
  authToken TEXT NULL REFERENCES TemporaryAuthTokens(authToken)
  );

I can add, that error is caused only by code in init function (I've tried init and SELECT query load_customers separatly). — Mark Richwood
– Mark Richwood, Commented Aug 27, 2012 at 9:51

ebohlman · Accepted Answer · 2012-08-27 09:58:01Z

13

You've got a race condition; it's possible that your last query (whose callback closes the connection) will finish before one of the earlier queries did, and that will, needless to say, hose the earlier query. You need to rework your code so that the last query to finish, rather than the last query to start, closes the connection (e.g set a counter to the number of queries and have each query decrement it when it finishes. The one that decrements it to zero closes the connection).

You might also want to look at the serialize method that's available on database objects. Right now your initialization queries are all independent of each other, but if you started using foreign-key constraints you'd have trouble if the referenced table hadn't been created yet, so you'd need to force the order of execution.

edited Aug 27, 2012 at 9:58

answered Aug 27, 2012 at 9:52

ebohlman

15k5 gold badges35 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mark Richwood Over a year ago

Thanks for reply. I've changed my database close condition due to your advice and also added serialize mode. Unfortunently, error still occurs, but it's message changed slightly from "uknown error" to "not an error": Query Error: Error: SQLITE_MISUSE: not an error

ebohlman Over a year ago

Please edit your question to reflect your new code. Are the tables created correctly?

Mark Richwood Over a year ago

I've edited code in question message. When I wrote about error still occuring, I've thought about the SQLITE_MISUSE message. Tables vanishing bug is fixed.

ebohlman Over a year ago

Change your error handler to print out which query is causing the error (you'll have to use some immediate-function trickery to create a new closure, because when the callback executes i will have changed from what it was when the query was started).

Mark Richwood Over a year ago

I've finnaly got it. When I split my queries by ";" I've forgot that at the end of file there is ";", so the last element of array with queries was empty string. SQLITE_MISUSE was called, because it appears that SQLite doesnt like empty strings as queries... All in all, thanks ebonhlman for your help!

|

Umesh Kadam · Accepted Answer · 2020-05-12 11:43:36Z

Use Promises.

Here I get data from one table and use this data to create a statement for inserting in another table.

serialize() do run 1-by-1 but I wanted the response from one query to be used in another. If I put 2nd query in callback of 1st then it gives the SQLITE_MISUSE error

db.serialize(()=>{

    // QUERY 1 (SELECT) - Get data 
    let promiseGetFoo = new Promise((resolve, reject) => {
      db.all("SELECT * FROM foo", (err, rows) => {
        if (err) {
          console.log(err);
          reject(err);
        } else {
          resolve(rows);
        }
      });
    });

    // QUERY 2 (INSERT) - Use data from QUERY 1
    promiseGetFoo.then((res) => {
      let stmt = (res) => { 
        // code to create INSERT statement 
      }      
      db.run(stmt, (err) => {
        if(err) console.log(err);
        else console.log(">>> Insert DONE");
        closeDb();
      });
    });

});

let closeDb = () => {
    db.close() ;
}

Collectives™ on Stack Overflow

Sqlite SQLITE_MISUSE error in node.js script

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related