Sqlite Database is Locked


#1

Hello,

I hope for some help. My SqLite Database- File ist stored in /home/pi/.
Everything is working fine working with node-red-node-sqlite 0.3.6. But when I try to write the copied (same file) to my network-Drive I can only read from it. By writing I get the "Database is Locked" Error. I changed the SQLite-Node and linked it to the new path(my Network-Drive; /mnt/NVR/e). The path is full accessable within node-red. I can delete and rename the Database (and other files) when I use the EXEC-node. So it shouldn't be a permission-problem (/mnt/NVR/e is full accessable for 'everyone'.
I hope i can get any ideas to solve this.

Thank you in advance

Greetings Roland


#2

Is any other application accessing the database? Including any command line utility you may be using?


#3

No, I double checked that. And it's simply impossible: I copied the .db file to /mnt/NVR/e (so trhere was no file before so no other application could access the (not existing) database.
After copying, the node cannot write (database is locked). But I can access it with the commandline.


#4

Well that is another process accessing it so it's not impossible. Make sure you don't try to access with command line at the same time as node-red.
Assuming it is not that, however, can you write to it with the command line?


#5

it is impossible. I tried to write data to the database via commandline AFTER I failed to write with nodered. At this point no application 'knew' that there is a database (execpt one nodered sqlite-node that says it was locked). one other sqlite-node is linked to a database in /home/pi/ and works perfectly. but I need it to get it work when the database is' stored in /mnt/NVR/e'.

summary:

  1. copied the database to path /mnt/NVR/e (database exists now in the path /mnt/NVR/e.
  2. try to write with nodered => 'database is locked'
  3. try with commandline ==> success

tried it all with other subdirectories. =>same

in local it works fine.


#6

Sorry, no idea in that case.

Are you wanting to do simultaneous access to the db on a shared drive? If so then it is generally considered that sqlite doesn't work well in that situation.


#7

Instead of copying the database, have you tried doing a back/restore from CLI sqlite?
or search Google for some ideas?


#8

Are you sure nothing else is holding the "database" open ? like the read... ?


#9

@dceejay: i bet my life on it!!
again: how can any application a database open, when I seconds before I want to access it with nodered, (write) is created by copying it in a folder. before it doesn't exist. I tried it on several subfolders.


#10

Can you write a file from node-red to that folder? Try with the File Out node.


#11

Thx Colin! I found something interesting.
I can write to that folder using 'node-red-contrib-fs' with the node 'file delete'. And I can do a test with 'file-access' (read/write). Both successful.
But with the node 'File-Lister' I can't even read that folder ("Error processing folder listing").
home/pi/ can be listed to any subfolder in that path (with the same node).

Is it possibble that some nodes have different permissions?
strange


#12

Sorry, I am out of ideas.


#13

One more thing to try. What do these commands show

ls -al /mnt/NVR
ls -al /mnt/NVR/e

Copy/paste the results here.
Also copy/paste the result of

node-red-stop
node-red-start

Also export the sqlite node and paste it here.


#14

Colin, thanks for the effort:
here we go:

pi@raspberrypi:/mnt/NVR $ ls -al /mnt/NVR
insgesamt 48
drwxrwxrwx 3 root root 4096 Jun 16 2017 .
drwxrwxrwx 5 root root 4096 Feb 13 09:49 ..
drwxrwxrwx 2 root root 40960 Feb 13 19:18 e

pi@raspberrypi:/mnt/NVR $ ls -al /mnt/NVR/e
insgesamt 40582342
drwxrwxrwx 2 root root 40960 Feb 13 19:18 .
drwxrwxrwx 3 root root 4096 Jun 16 2017 ..
-rwxrwxrwx 1 root root 3592 Jun 26 2017 Acer wecken.xml
-rwxrwxrwx 1 root root 5632 Nov 6 13:59 Alexa.4tw
-rwxrwxrwx 1 root root 4608 Nov 6 12:21 Alexa.4tw.pre_850
-rwxrwxrwx 1 root root 5632 Nov 6 12:37 Alexa.4tw.prev
-rwxrwxrwx 1 root root 15471 Feb 3 19:33 .alexa.devicelist.json
-rwxrwxrwx 1 root root 33647 Feb 3 19:32 .alexa.login
-rwxrwxrwx 1 root root 5582 Nov 6 11:59 Alexa_Login.mrf
-rwxrwxrwx 1 root root 3900 Nov 6 15:05 alexa.rec
-rwxrwxrwx 1 root root 794 Mai 21 2017 amzon_AWS-codes.txt
drwxrwxrwx 2 root root 0 Aug 30 2017 Application Files
-rwxrwxrwx 1 root root 3174012 Nov 6 12:09 AutoHotkey_1.1.30.00_setup.exe
drwxrwxrwx 2 root root 0 Feb 5 18:09 be960225447221f0fc3e4dd7f1f319
drwxrwxrwx 2 root root 0 Mär 7 2017 BlueIris
-rwxrwxrwx 1 root root 9216 Aug 30 2017 CatAway.exe
-rwxrwxrwx 1 root root 11 Jan 25 07:00 Cataway.txt
drwxrwxrwx 2 root root 0 Jun 14 2017 CD-Rom
-rwxrwxrwx 1 root root 45260 Sep 22 11:29 config_Rollladen_U13_Build20100_201811 (1).dat
-rwxrwxrwx 1 root root 65536 Sep 22 11:28 config_Rollladen_U13_Build20100_201811.dat
-rwxrwxrwx 1 root root 5660 Aug 30 2017 ConsoleApplication4.application
-rwxrwxrwx 1 root root 20626 Feb 13 18:03 cookies.txt
-rwxrwxrwx 1 root root 1126 Okt 4 13:21 Cube_Status.txt
-rwxrwxrwx 1 root root 4264558592 Jan 12 2017 Debian 8.6.0 (32bit).vdi
-rwxrwxrwx 1 root root 331350016 Mai 23 2017 debian-8.8.0-i386-netinst.iso
-rwxrwxrwx 1 root root 642977280 Mär 12 2017 debian_exp.ova
drwxrwxrwx 2 root root 0 Feb 4 21:54 f
drwxrwxrwx 2 root root 0 Feb 4 21:46 Fhem
-rwxrwxrwx 1 root root 320096 Nov 2 21:43 Firefox Installer (1).exe
-rwxrwxrwx 1 root root 320096 Nov 2 21:29 Firefox Installer.exe
drwxrwxrwx 2 root root 0 Sep 13 19:04 FRITZ!fax_3.07.04 Installation
-rwxrwxrwx 1 root root 18089776 Sep 13 19:07 FRITZ!fax_3.07.04 Installation.zip
-rwxrwxrwx 1 root root 199 Mär 19 2018 ic_add_black_48px.svg
-rwxrwxrwx 1 root root 181 Mär 19 2018 ic_remove_black_24px.svg
drwxrwxrwx 2 root root 0 Jun 2 2017 Kiwi-Syslog-Server-9.6.1-Freeware
-rwxrwxrwx 1 root root 8891166 Feb 8 07:57 koko-lambda-upload.zip
drwxrwxrwx 2 root root 0 Nov 6 12:06 MacroDollar-0.6.1
-rwxrwxrwx 1 root root 117718 Nov 6 12:05 MacroDollar-0.6.1.zip
drwxrwxrwx 2 root root 0 Sep 13 09:02 MAXBuddy-r9.16.2-win
-rwxrwxrwx 1 root root 52240998 Sep 10 15:18 MAXBuddy-r9.16.2-win.zip
-rwxrwxrwx 1 root root 98925112 Sep 10 15:09 max_home_automation_setup_2_10.exe
-rwxrwxrwx 1 root root 2542720 Nov 6 11:48 mouserecordersetup.exe
-rwxrwxrwx 1 root root 7034976 Nov 6 12:12 mtw_free.exe
drwxrwxrwx 2 root root 0 Jan 25 08:27 Musik
-rwxrwxrwx 1 root root 17182720 Feb 5 18:04 mysql-installer-web-community-8.0.15.0.msi
-rwxrwxrwx 1 root root 1426720 Feb 5 18:06 NDP47-KB3186500-Web.exe
-rwxrwxrwx 1 root root 3638 Jun 25 2017 OMV wecken.xml
drwxrwxrwx 2 root root 0 Jun 14 2017 prtg
-rwxrwxrwx 1 root root 164879976 Jun 2 2017 prtg.zip
drwxrwxrwx 2 root root 0 Mär 16 2018 raps
-rwxrwxrwx 1 root root 31914983424 Jun 4 2017 raspberrypi-dd-backup-20170604-220201.img
drwxrwxrwx 2 root root 0 Feb 13 00:43 Rasp_Fhem_Backup
-rwxrwxrwx 1 root root 4025483264 Mai 15 2017 Rasp.img
drwxrwxrwx 2 root root 0 Mär 8 2017 $RECYCLE.BIN
-rwxrwxrwx 1 root root 771512 Aug 30 2017 setup.exe
drwxrwxrwx 2 root root 0 Okt 3 14:36 SharedData
drwxrwxrwx 2 root root 0 Mär 8 2017 Sicherung_HDD0_20170308_1611
drwxrwxrwx 2 root root 0 Mai 4 2017 Sicherung_HDD1_20170504_0954
drwxrwxrwx 2 root root 0 Jan 2 2018 Sicherung_HDD1_20180102_1512
-rwxrwxrwx 1 root root 61440 Feb 13 10:06 SmartH.db
-rwxrwxrwx 1 root root 2375 Mai 21 2017 sonofF_FEHEN.txt
-rwxrwxrwx 1 root root 9216 Sep 28 2017 Sonoff_Reboot.exe
drwxrwxrwx 2 root root 0 Feb 3 10:32 sqliteadmin
drwxrwxrwx 2 root root 0 Feb 13 06:53 StH
drwxrwxrwx 2 root root 0 Jul 16 2016 System Volume Information
-rwxrwxrwx 1 root root 6369 Feb 2 20:46 test
-rwxrwxrwx 1 root root 6369 Feb 2 20:46 test.xls
-rwxrwxrwx 1 root root 33792 Nov 6 13:54 tinytask_162.exe
-rwxrwxrwx 1 root root 102 Feb 5 18:01 tinytask_162.ini
drwxrwxrwx 2 root root 0 Mai 14 2017 Visual Studio 2008
drwxrwxrwx 2 root root 0 Mai 14 2017 Visual Studio 2010
drwxrwxrwx 2 root root 0 Jun 26 2017 WOL2
-rwxrwxrwx 1 root root 50176 Jan 4 2005 WolCmd.exe
-rwxrwxrwx 1 root root 140184 Jun 25 2017 wol.exe
-rwxrwxrwx 1 root root 361199 Okt 29 2017 xbmc-gamepass-master.zip

node-red-stop
pi@raspberrypi:/mnt/NVR $ node-red-stop

Stop Node-RED

Use   node-red-start   to start Node-RED again


node-red-stop

pi@raspberrypi:/mnt/NVR $ node-red-start

Start Node-RED

Once Node-RED has started, point a browser at http://192.168.0.248:1880
On Pi Node-RED works better with the Firefox or Chrome browser

Use   node-red-stop                          to stop Node-RED
Use   node-red-start                         to start Node-RED again
Use   node-red-log                           to view the recent log output
Use   sudo systemctl enable nodered.service  to autostart Node-RED at every boot
Use   sudo systemctl disable nodered.service to disable autostart on boot

To find more nodes and example flows - go to http://flows.nodered.org

Starting as a systemd service.
Started Node-RED graphical event wiring tool.
13 Feb 19:35:12 - [info]
Welcome to Node-RED
===================
13 Feb 19:35:12 - [info] Node-RED version: v0.19.4
13 Feb 19:35:12 - [info] Node.js  version: v8.12.0
13 Feb 19:35:12 - [info] Linux 4.9.35-v7+ arm LE
13 Feb 19:35:13 - [info] Loading palette nodes
13 Feb 19:35:25 - [info] Dashboard version 2.13.0 started at /ui
13 Feb 19:35:26 - [warn] ------------------------------------------------------
13 Feb 19:35:26 - [warn] [node-red-contrib-xiaomi-devices/xiaomi-ht] Type already registered
13 Feb 19:35:26 - [warn] [node-red-contrib-xiaomi-devices/xiaomi-magnet] Type already registered
13 Feb 19:35:26 - [warn] [node-red-contrib-xiaomi-devices/xiaomi-motion] Type already registered
13 Feb 19:35:26 - [warn] [node-red-contrib-xiaomi-devices/xiaomi-switch] Type already registered
13 Feb 19:35:26 - [warn] [node-red-contrib-xiaomi-devices/xiaomi-socket] Type already registered
13 Feb 19:35:26 - [warn] [node-red-contrib-xiaomi-devices/xiaomi-socket-wifi] Type already registered
13 Feb 19:35:26 - [warn] [node-red-contrib-xiaomi-devices/xiaomi-configurator] Type already registered
13 Feb 19:35:26 - [warn] [node-red-contrib-snowboy/snowboy] Error: libcblas.so.3: cannot open shared object file: No such file or directory (line:5)
13 Feb 19:35:26 - [warn] ------------------------------------------------------
13 Feb 19:35:26 - [info] Settings file  : /home/pi/.node-red/settings.js
13 Feb 19:35:26 - [info] Context store  : 'default' [module=memory]
13 Feb 19:35:26 - [info] User directory : /home/pi/.node-red
13 Feb 19:35:26 - [warn] Projects disabled : set editorTheme.projects.enabled=true to enable
13 Feb 19:35:26 - [info] Flows file     : /home/pi/.node-red/flows_raspberrypi.json
13 Feb 19:35:26 - [info] Server now running at http://127.0.0.1:1880/
13 Feb 19:35:26 - [warn]
---------------------------------------------------------------------
Your flow credentials file is encrypted using a system-generated key.
If the system-generated key is lost for any reason, your credentials
file will not be recoverable, you will have to delete it and re-enter
your credentials.
You should set your own key using the 'credentialSecret' option in
your settings file. Node-RED will then re-encrypt your credentials
file using your chosen key the next time you deploy a change.
---------------------------------------------------------------------
13 Feb 19:35:26 - [info] Starting flows
13 Feb 19:35:27 - [info] [maxcube-server:877c6ca6.d771b] undefined
13 Feb 19:35:29 - [info] [fritzbox-callmonitor:266e9be6.127eb4] Connecting to fritzbox...
13 Feb 19:35:29 - [info] Started flows
13 Feb 19:35:30 - [info] [sqlitedb:9596ebaa.b1646] opened /home/pi/SmartH.db ok
13 Feb 19:35:30 - [info] [sqlitedb:9596ebaa.b1646] opened /home/pi/SmartH.db ok
13 Feb 19:35:30 - [info] [sqlitedb:9596ebaa.b1646] opened /home/pi/SmartH.db ok
13 Feb 19:35:30 - [info] [sqlitedb:9596ebaa.b1646] opened /home/pi/SmartH.db ok
13 Feb 19:35:30 - [info] [sqlitedb:94a0d278.049a18] opened /mnt/blue/SmartH.db ok
13 Feb 19:35:31 - [info] [fritzbox-callmonitor:266e9be6.127eb4] Connected to fritzbox
13 Feb 19:35:31 - [info] [mqtt-broker:44384cc2.2368f4] Connected to broker: mqtt://192.168.0.249:1883
13 Feb 19:35:31 - [info] [mqtt-broker:localhost] Connected to broker: mqtt://localhost:1883
13 Feb 19:35:35 - [info] [exec:bfb8276f.105be] error:Error: Command failed:  sh /alexa_remote_control.sh -d " 21.10" -e speak:'Gäste-WC-Heizung ist auf undefined Grad eingestellt! Die aktuelle Temperatur beträgt 21 Grad. Der Thermostat ist zu undefined prozent geöffnet. Die Luftfeuchtigkeit beträgt 37 Prozent!'
13 Feb 19:35:39 - [info] [maxcube out:e4f625bc.295a88] {"duty_cycle":0,"free_memory_slots":50}


I hope you can find something. thanx











#15

At least part of your issue is that all of those files and folders are owned by root:root. Since Node-RED is most likely not being run by user root (or a user in group root), you may hit some issues.

Maybe try changing ownership to pi:users instead?


#16

Well, lets start from the begining.
Probably nothing to do with the problem, but you should sort it first anyway, just in case. You seem to have installed two conflicting xiaomi nodes. Remove whichever one you are not using and make sure those warnings go away.
Next, you seem to be opening multiple connections to /home/pi/SmartH.db
and one to /mnt/blue/SmartH.db. I guess this means you have multiple config nodes instead of re-using the same one in the sqlite nodes. You should sort it so you only use one config node for each database (I think).
Did you realise you are using two databases with the same name, one on the pi and one on the share?
Finally did it occur to you to wonder why I asked for an ls of /mnt/NVR/e? It was to see the permissions on the database, but it seems it is not there at all, it is in /mnt/blue, so that is the folder that is of interest. If you do this again then I don't need to see all the files, just that one.
I suggest you clear up the known problems first then see what happens.


#17

Personally as this is in a pi I would start again.

Export your flows and email them to your self.
Then start with a new install, install the nodes you use and you shouldn’t have an issue with permissions

You can then reimport your flows


#18

I think the permissions issues are on a shared drive, not on the pi. But actually he/she isn't even using that folder so I don't think it is relevant.


#19

first:
I´ll fix the conflicting xaomi nodes and see.

yes, there is now a config-node pointing to /mnt/blue. I made an other share pointing to the same destination. I wanted to be sure that there is realy no permission issue caused by the host. But didn't solve the problem. A few hours before that sqlite-node was configured linking to /mnt/NVR/e. This is the node I triy to get to work to store my data.
The /home/pi/SmartH.db-node is the current working sqlite-node where I store my data as long as I can't get the other to work.

/mnt/blue was just a testing thing. In the meantime I reconfigured it to
/mnt/NVR/e again an unmounted /mnt/blue . So you saw the permissions of the right folder .
Sorry, I wasn`t aware that the node was still configured to mnt/blue.
To be clear, I'm using this folder: mnt/NVR/e

So when there is a permission issue, why can I write to the shared drive with some nodes and, with other nodes I can't? With file-lister-node I can't even read.

I have full access to the share with user pi via commandline. I could show with copy/paste.

@ukmoose

To reinstall I think a total remove of node-red and a new install should do.

so I remove node-red, delete all folders in .node-red and reinstall?

Thank you all.


#20

No point reinstalling Node-RED on its own. The problem is probably outside of Node-RED. It would appear that you’ve tried so many things, sometimes it is better to take a breath and start again.