Sqlite SQLITE_MISUSE error in node.js script

Question

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

Query error: error: SQLITE_MISUSE: unknown error

I read that SQLITE_MISUSE occurs when the SQLITE API is used slowly. Could you help me because I cannot find something wrong in this code.

EDIT. I made changes to the code to get rid of the race problem.

The SQLITE_MISUSE violation message still occurs, but the problem with the disappearing tables has disappeared. In my requests there was a race.

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 initDB.sql request file is as follows:

 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) ); 

Answer 1

You have a race condition; perhaps your last request (whose callback closes the connection) will complete before one of the previous requests completes, and this, of course, will require an earlier request. You need to rework the code so that the last request is completed, and not the last request to start, closes the connection (for example, sets the counter for the number of requests and each request reduces it when it ends). One that reduces it to zero closes the connection).

You can also see the serialize method available for database objects. Right now, your initialization requests are independent of each other, but if you start using foreign key constraints, you will have problems if the reference table has not been created yet, so you will need to force the execution order.

Answer 2

 var fs = require("fs"); var sqlite3 = require("sqlite3").verbose(); var db = new sqlite3.Database('json.sqlite3'); var file = process.env.CLOUD_DIR + "json.sqlite3"; var exists = fs.existsSync(file); var jsonfile = { key: "myvalue" }; fs.readFile('demo.json', function(err, data) { if (err) { return console.error(err); } console.log("File content: " + data.toString()); // now save to db if (!exists) { console.log("Creating DB file."); fs.openSync(file, "w"); } db.serialize(function() { if (!exists) { db.run("CREATE TABLE Stuff (thing TEXT)"); } var jsonString = escape(JSON.stringify(data)); db.transaction(function(tx) { tx.executeSql('INSERT OR REPLACE INTO Stuff(md5, json, expires) VALUES("'+hash+'", "'+jsonString+'", "'+expireStamp+'") '); }, function(tx, error) { console.log(JSON.stringify(tx.message)); console.log(JSON.stringify(error)); }, function() { }); }); }); db.close(); 

I have the same problem