3

I am running a Python script that inserts data into a SQLite database every minute; there are millions of rows in the database.

Is it possible that once the database grows to a certain size, it becomes corrupt (or inefficient) such that no more data can be inserted?

For example, when I FTP'd into my remote server today, I noticed that the database was 590,052,352 bytes in size. However, when I refreshed a few minutes later, the database hadn't grown in size (which it should have).

When looking at the 'last modified' timestamp of the database, I realised that no data had been inserted for more than 40 hours. Consequently, I had a look at the error logs, but there was nothing in there to indicate any issues.

I then renamed the database from data.db to data-1521564403.db and created a fresh new database named data.db. After doing this, the python script was able to insert data into this new database once again, without any issues.

I had noticed a similar issue last week. In that instance, the database reached a size of 632 MB, after which it began adding errors to the error log, saying duplicate data was being inserted into a field that had a UNIQUE constraint. However, I know that this could not have been possible because the python script makes sure to check that no duplicate data is being inserted. So I followed the same procedure of renaming the database and then creating a new one, and the issue was resolved, data could be inserted once again.

So my question is, do SQLite databases stop working as they should (or become inefficient) after they reach a certain size or contain a certain number of rows?

Thanks!

badcoder
  • 3,624
  • 5
  • 32
  • 33
  • No; [testing](http://www.sqlite.org/testing.html) ensures that there are no such bugs. It is, however, possible for buggy file systems to corrupt the file, [especially](http://www.sqlite.org/howtocorrupt.html) if you're using a network file system or virtual machine. Run [PRAGMA integrity_check](http://www.sqlite.org/pragma.html#pragma_integrity_check) on the bad DB file. – CL. Mar 20 '18 at 17:45
  • Thanks @CL. I am running the script on a Digital Ocean droplet, so based on your comment on virtual machines, does that mean the actual droplet could be corrupting the file? – badcoder Mar 20 '18 at 17:54
  • Does Digital Ocean guarantee that its file system works correctly, especially regarding locking and fsync? – CL. Mar 20 '18 at 17:58
  • Did you ever resolve this issue? I've actually encountered this as well and had raised a Question before finding this one. – John Go-Soco Aug 29 '19 at 09:01
  • My related question: https://stackoverflow.com/questions/57523341/sqlite3-disk-i-o-error-encountered-after-a-while-but-worked-after-using-copy-of – John Go-Soco Aug 29 '19 at 09:01

1 Answers1

-1
 reasons due to which SQLite database moves to non-accessible state:

File Overwriting Operations -> Overwriting of SQLite files is possible as these files are ordinary disk files, not any way is there to defend the database from such actions.

  • Running File Descriptor: Closing and reopening a file Decryptor may overwrite the thread managed on SQLite log files, in this case the data should go on to the Server gets replaced and it shows corruption afterwards
  • Restore While Transaction Is In Running Mode: Backup processes running at the backend of system may try to create backup of SQLite files while the transaction is not completed. The backup will save some logs of previous state and some logs of earlier transactions, at the end transactions id will not match and user will see the corruption issue.
  • Hot Journal Deletion: For recovery of database SQLite use auxiliary journal files with the same name of the database, only the suffix -journal or -wal gets added. During SQLite database repair, if the Server will not find journal file due to renaming, deletion, or file move then there will be no hope for automatic database recovery.

File Locking Issues: For coordinating concurrent processes, SQLite uses locking database facility and the reason behind it is avoiding changes done by two different processes for a single database that explores data corruption.

  • Missing Lock Details: File systems those are treated as reliable sources for locking databases may have some un-resolvable bugs along with and when any database thread is locked but such kind of system then chances of data corruption increase.
  • Posix Advisory Lock Cancellation: This facility is important to be settled into all UNIX system running SQLite for default locking, The Close () system call oftentimes interrupt this action and it further generates SQLite thread corruption. The main cause behind this kind of corruption may be linking of SQLite database multiple copies Contradiction Of Two Locking Protocols: SQLite3 vfs and SQLite3 open v2(), these two protocols are well known for SQLite database handling, in a system working with SQLite with both of these protocols then contradiction will surely take place
  • Rename Or Unlink Database: Once you rename a file of SQLite database that is actually not present at the same location then, it may a result of unlink or rename the SQLite database or file, it is also not a good practice as it will be responsible to corrupt SQLite database

Numerous Links For A File: If you have linked up a SQLite database to multiple links with network connectivity then, it will again show corruption situations. - Database Syncing Failure: If sync command of SQLite Server playing the role of I/O barrier instead of a true sync, then any failure may come across to you that further rollback actions, you can say it violation of ACID property. In this case database will be in resistant mode.

  • Disk Drives Not Honoring Sync Requests: for out-of-order disk requests SQLite does not have any built-in method, Commit roll back may affect the durability of database but, it does not corrupt the database actually. To defense the database against failed sync operation, users should use SQLite WAL mode.
  • PRAGMAs For Restricting Data Syncing: By setting PRAGMA synchronous setting to the OFF state, all sync operations are omitted. This setting helps to improve the performance of SQLite databases but unfortunately it shows corruption as OS reorders all the actions.

Flash And Disk Drive Failures: The changes in content of hard drives and disk drives may be quite annoying for SQLite users.

  • Unsafe Flash Memory Controllers: Converting or writing database content from one file format to other while your hard drive is not power protected is another known cause for generating interruption into SQLite databases.
  • No More Data Storage Capability: When disk has no space for data storage and you are trying to write something into it then SQLite corruption may take place.

Memory corruption, other problems in operating systems, and SQLite bugs are also enlisted within the crucial causes due to which SQLite files and databases move to inaccessible mode.

VICTOR BASU
  • 7
  • 1