Re: [sqlite] database disk image is malformed
On Mon, Nov 11, 2019 at 05:37:37PM +, Simon Slavin wrote: > On 11 Nov 2019, at 5:13pm, Jukka Marin wrote: > > > The main process first opens the databases and checks that their > > version matches that of the software and if not, the databases are > > closed and initialized by running a script. > > > > After closing the databases, main process forks the children and > > all processes (including main process) open the databases and use > > their own connections. > > > > What I was trying to ask was this: If any of the children dies > > (a bug in the code), main process will restart the child. At > > this point, the main process has the databases open, so the new > > child receives the connections as well. What should I do now? > > Okay, that gives us enough information to work with. > > The conservative way to do it is to have the main process close the > connection before forking and open it again. Then, of course, the child > processes make their own connections. > > But I don't think that's necessary. A child process can have access to the > main process' database connection but ignore it. So I think the main process > can fork without closing its connection. Then each child can never use that > one but instead make its own. Okay, that's what I was hoping for. > Of course, every one of these connections needs to set a timeout. And every > call to the SQLite3 library needs to check its result code and make sure it > is getting SQLITE_OK (or, for queries, SQLITE_DONE etc.). Yes, I'm doing all this, > > Should the child close the databases before opening them again? > > Will this close the databases for the main process as well? > > As you suspected, closing the connection releases both memory structures and > file handles. Anything that tries to use that connection will then fail > because it has no idea what it's talking to. Ok. > What puzzles me is this: you're getting "database malformed" and nothing > you've described justifies this. Assuming that this isn't just one old > database which is genuinely corrupt, but that you are using a fresh uncorrupt > database each time, you seem to have a genuine bug in your code. That was happening before I changed my code to open the databases in the children. Originally, only main process opened the databases and the children "inherited" the connections. I was wondering if this was okay, but I didn't see a warning in the SQLite docs, so.. I never found the "how to corrupt your database" manual on my own ;-) I came back to the mailing list when I noticed the above "problem" of a child dying and needing to respawn it while the main process already has the databases open. So I guess it's safe now that all the children open the databases by themselves. Thanks to all who responded! -jm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database disk image is malformed
On Mon, Nov 11, 2019 at 05:03:25PM +, Simon Slavin wrote: > On 11 Nov 2019, at 1:42pm, Jukka Marin wrote: > > > Or does the main process need to close all databases, then fork, then > > reopen the databases? > > Which processes access the databases ? The main process ? Its children ? > Are they all using the same connection ? > Are they all trying to use the same connection at the same time ? All processes access the databases. No, I changed the code so that every process opens the databases separately, so they use their own connections (at random times, so probably simultaneously). The main process first opens the databases and checks that their version matches that of the software and if not, the databases are closed and initialized by running a script. After closing the databases, main process forks the children and all processes (including main process) open the databases and use their own connections. What I was trying to ask was this: If any of the children dies (a bug in the code), main process will restart the child. At this point, the main process has the databases open, so the new child receives the connections as well. What should I do now? Should the child close the databases before opening them again? Will this close the databases for the main process as well? (One way is to stop using the databases in the main process, so they are not passed to children, but this would be a major change in the code.) -jm ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database disk image is malformed
On Fri, Nov 08, 2019 at 09:57:25AM +0200, Jukka Marin wrote: > On Thu, Nov 07, 2019 at 09:26:46AM -0800, Shawn Wagner wrote: > > This line stood out: > > > > > The main process opens the databases and then forks the other processes > > which can then perform database operations using the already opened > > databases. > > > > From > > https://sqlite.org/howtocorrupt.html#_carrying_an_open_database_connection_across_a_fork_ > > : > > > > > Do not open an SQLite database connection, then fork(), then try to use > > that database connection in the child process. All kinds of locking > > problems will result and you can easily end up with a corrupt database. > > SQLite is not designed to support that kind of behavior. Any database > > connection that is used in a child process must be opened in the child > > process, not inherited from the parent. > > > > In this kind of situation, I usually use pthread_atfork() callbacks to > > automate closing databases and then re-opening them in the parent and child. > > Okay, thanks! I suspected it could be something like this, but couldn't > find anything in the SQLite docs. In some situations, my main process will have the databases opened before it needs to fork a new child (this happens only if a child dies and has to be restarted). If the child process immediately closes its copies of the databases and then reopens them, will it be safe? Or does the main process need to close all databases, then fork, then reopen the databases? Thanks again! Jukka Marin ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database disk image is malformed
On Thu, Nov 07, 2019 at 09:26:46AM -0800, Shawn Wagner wrote: > This line stood out: > > > The main process opens the databases and then forks the other processes > which can then perform database operations using the already opened > databases. > > From > https://sqlite.org/howtocorrupt.html#_carrying_an_open_database_connection_across_a_fork_ > : > > > Do not open an SQLite database connection, then fork(), then try to use > that database connection in the child process. All kinds of locking > problems will result and you can easily end up with a corrupt database. > SQLite is not designed to support that kind of behavior. Any database > connection that is used in a child process must be opened in the child > process, not inherited from the parent. > > In this kind of situation, I usually use pthread_atfork() callbacks to > automate closing databases and then re-opening them in the parent and child. Okay, thanks! I suspected it could be something like this, but couldn't find anything in the SQLite docs. I'll change my code and see what happens :-) Thanks again for the quick reply! Jukka Marin ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] database disk image is malformed
Dear List, I'm developing software which keeps parameters and real-time data in SQLite databases on a x86_64/linux system. I am getting "database disk image is malformed" errors from SQLite when using select. Some select operations succeed, some fail. This happens on multiple systems. I would like to know if I'm doing something that is known not to work with SQLite. Background information: The software runs on a xen virtual machine. The database files are on a separate disk (actually, a separate partition of the host linux) which is directly mounted on the virtual machine (not using NFS) and uses ext4 filesystem. The whole system runs off a single SSD disk. SQLite version is 3.28.0 and it is currently built and statically linked against the application binary. The databases are originally created by a script and the sqlite3 command. "pragma journal_mode=wal;" is set for all databases. The software consists of multiple processes. The main process opens the databases and then forks the other processes which can then perform database operations using the already opened databases. No locking is done in the processes, they rely on the SQLite internal mechanisms. sqlite3_busy_timeout is set to 2500 for all databases. The processes mostly read (select) data from the databases, but also occasionally add new rows or update existing rows. Is this system with multiple processes accessing the databases safe with SQLite? If it is, does anyone have ideas of what could be corrupting the database files? These systems are running 24/7, but of course, it _may_ be possible that someone has disconnected the power supply without shutting down the system first and causing errors in the database files. Is there something I could do to (try to) prevent database corruption if power is lost? (In software - a UPS is a hardware solution, of course). If the database is corrupted, is there any way of recovering it? Maybe I should use sqlite3 to dump and recreate the database (of course, some or all of the data might get lost)? Experienced SQLite users, please share your ideas - thank you! :-) Jukka Marin (a beginner with SQLite) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
