Domoticz: Temporary database files on RAM disk not possible since SQLite 3.31.1

[PierreLevres]

Domoticz database opening wil give errors if the temporary database files are on RAM disk (using symbolic links), see forum post https://www.domoticz.com/forum/viewtopic.php?f=6&t=31906. How "Moving Log and Temporary Files To a RAM Drive" in 2020.1 version?
The following link creation in the domoticz start up does no longer work:
ln -sf /var/log/domoticz.db-shm /home/pi/domoticz
ln -sf /var/log/domoticz.db-wal /home/pi/domoticz

Since the temporary WAL and SHM files now need to be in the database directory, PI's on SD-cards running Domoticz have major issues, since this increases the write-actions on the SD-card enormously, thus generating heavy wear on the SD-card.
Please recompile with SQLite flags so that symbolic links are followed.

[kofec]

maybe you can use option :
-dbase file_path (for example /opt/domoticz/domoticz.db)
-userdata file_path (for example /opt/domoticz)
And symblic link will not be needed

[gizmocuz]

@plips-nl , i have been using domoticz from day 1 on a raspberry pi with a sd card and have not experienced such issue in the last 6 years !
So there is no BIG issue ... there is a big issue when using wrong scripts that adds for instance a log message every second to the database (yes i have seen that happening)
As Kofec says, youhave to use the -dbase option if you want to store the database on a different location

[rwaaren]

maybe you can use option :
-dbase file_path (for example /opt/domoticz/domoticz.db)
-userdata file_path (for example /opt/domoticz)
And symblic link will not be needed

Thx for this comment.
Could you please also have a look at the wiki page setting up RAM drive and update it to explain in small steps howto set it up properly ?

Would be very welcomed as it could help many other domoticz users !

[Smanar]

@kofec but if you use theses command line
All databases will be in Ramdisk ?
So after a power off you loose all, included, the config ?

[gizmocuz]

@Smanar , ramdisk ? Why not mound your NAS to /media/mynas and use the -dbase option to point your database to this location ?

[Smanar]

Because like the majority of users I m using a Raspberry and with an SD card because It's something possible on old version without problem.

[gizmocuz]

Great! So am I... as mentioned a few posts above!

[Smanar]

Yep, I will do same.
But objectively, according to setting or configuration, I can imagine it can be a problem.

[gizmocuz]

@Smanar , why is this ? Who says it is a problem ? Just make sure you use a class 10 sd card, this IS important

[turin400]

In my case I am not updating to the new versions of domoticz because I have 20 DG-R8X sensors that send data every 60 seconds, having every 3 seconds more or less a write operation in the one user variable that I decode in lua. I use an SDSDQAF-008G industrial sd, but I think there are a lot of writes for an sd.
I have thought to move all the db to a ramdisk and modify domoticz.sh adding rsync to copy domoticz.db to the sd when starting and stopping the service, also by time every 5 minutes. I think it would be a good option.

[gizmocuz]

That's not a lot of data writes, but you could use the -dbase option to locate your database anywhere you like

[michapr]

@gizmocuz
Hi,
can you please tell us why it is a problem to recompile next version without the "SQLITE_OPEN_NOFOLLOW" flag in sqlite open connection?
It would solve it for all users who like to move the *.db-wal and *.db-shm files to RAM-disk ...
(BTW: I also think it is a good idea if you have a large number of sensors / events)

Thanks,
Michael

[rwaaren]

What about this as a reason why?

SQLite normally stores all content in a single disk file. However, while performing a transaction, information necessary to recover the database following a crash or power failure is stored in auxiliary journal files. Such journal files are described as "hot". The journal files have the same name as the original database file with the addition of -journal or -wal suffix.

SQLite must see the journal files in order to recover from a crash or power failure. If the hot journal files are moved, deleted, or renamed after a crash or power failure, then automatic recovery will not work and the database may go corrupt.

[michapr]

thanks, ok.
If database may go to corrupt if files are missing - it is a real reason.
My understanding was that only the latest transactions will be missing in such a case.

I haven't found this information before, thanks again.
Maybe would be fine to add it in Wiki as comment (with source) for better understanding for all the other users ;)

[gizmocuz]

@michapr , probably we can do this, but we did not add any special compile flag, or 'SQLITE_OPEN_NOFOLLOW' flag ... did you have a look in the code . cmake projects ?

Why don't you use the -dbase option and relocate your database to a NAS drive ?

And for the record (again) as oldest Domoticz user, still using a raspberry pi, with SD card, still without any issues... (I did had a few with a raspberry pi 1/2 with a bad sd card and power supply, lesson learned, Class 10 SD card, 2.5A power supply)

I do have quite a few sensors

[michapr]

@gizmocuz , if there is really a problem with corrupted database after lost db-wal/db-shm files it does not make sense to compile it without the flag (it is set by default, if I understood right). Will be only another additional problem for users.
I didn't know about this database recovery problem before. Only have seen the comments in forum about the "moving to RAM disk issue", that's why was asking.

BTW: I also have used since 6 years the same SD-card on my main system (until Buster now ;) ) without problems. Only on my development unit had the problem - and on one system for heating control with eBus - but here was the logging the reason (forgot the RAM.disk ... learned...).

I move only any logging and temp files to RAM-disk - and all is fine.
And I have 194 active devices, most of them sensors (Z-Wave, 433MHz, virtual from other systems)...

NAS can be an option, but I do not like it to have a dependence on another system - as backup the NAS is ok...
In this time I try to use a USB-SSD - looks fine until now.

[Hennyr]

I am using Domoticz for years and had, untill Buster, no issues. I made now a 2 days routine in Crontab to restart Domoticz to solve the memory issue. I am working on SDcard and my ramdisks are nog so big as ..wal-file can grow.

[gizmocuz]

@michapr , we did not change any flags. So you do want an additional flag ?
Please make a PR for this

[michapr]

@gizmocuz , no, all is fine as it is.
As I wrote above, if there is really a problem with corrupted database after lost db-wal/db-shm files it does not make sense to compile it without the flag.

I would add this in Wiki - and maybe in forum as answer, why not move these files into RAM-disk.
@rwaaren - maybe you could do it with the link to source of this information?

THANKS!
Michael

[rwaaren]

I would add this in Wiki - and maybe in forum as answer, why not move these files into RAM-disk.
@rwaaren - maybe you could do it with the link to source of this information?

On April 21st I added

including a link to the source on the wiki page
I also posted this text and where I found the information in the forum topic

[michapr]

@rwaaren THANKS!