SQLite3数据库或磁盘已满/数据库磁盘映像格式不正确 [英] SQLite3 database or disk is full / the database disk image is malformed
问题描述
我的数据库大约是25 MB，我已经验证了访问它的用户名以及文件权限在几个月内没有变化。我有一个问题，因为数据库或磁盘已满，有时数据库磁盘映像格式错误问题，查询失败。
My database is about 25 MB, and I've verified that the username accessing it, as well as the file permissions haven't changed in months. I'm having a problem where queries are failing due to a "database or disk is full" and then sometimes "database disk image is malformed" issue.
除非我'读这个错误，我的磁盘没有任何接近完整（这是一个Ubuntu服务器，9.10，如果有任何区别）
Unless I'm reading this wrong, my disk isn't anywhere near full (this is an Ubuntu server, 9.10, if it makes any difference)
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda1 19610300 2389596 16224560 13% /
udev 10240 128 10112 2% /dev
none 254136 0 254136 0% /dev/shm
none 254136 36 254100 1% /var/run
none 254136 0 254136 0% /var/lock
none 254136 0 254136 0% /lib/init/rw
作为测试，我刚刚做了一个添加新记录的动作，没关系。我正在努力确定是否有一组具体的操作失败。但是，在插入（并验证它在那里）之后，数据库的磁盘上的字节数没有改变（既不是向上也不是down）。
As a test I just did an action that added a new record, and it's fine. I'm trying to fiqure out if there's a specific set of actions that are failing. However, after the insert (and verifying that it's there) the number of bytes on disk for the database has not changed (neither up nor down).
使用命令行实用程序会产生类似于以下内容的情况，这是无法令人惊叹的：）
Using the command line utility results in something like the following, which is failing spectacularly :)
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma integrity_check;
*** in database main ***
On tree page 2 cell 0: 2nd reference to page 26416
On tree page 2 cell 1: 2nd reference to page 26417
On tree page 2 cell 2: 2nd reference to page 26434
On tree page 2 cell 3: 2nd reference to page 26449
On tree page 2 cell 4: 2nd reference to page 26464
On tree page 2 cell 5: 2nd reference to page 26358
On tree page 2 cell 6: 2nd reference to page 26494
On tree page 2 cell 7: Child page depth differs
On tree page 2 cell 8: 2nd reference to page 26190
On tree page 2 cell 8: Child page depth differs
... etc., etc. ...
有什么想法我应该在下一个？表中的最大行数是否有问题？我在SQLite3的最大值上做了一些阅读，而且我的数据库中没有任何东西与我们可以告诉他们有任何关系。
Any ideas on where I should be looking next? Is there a problem with the maximum number of rows in a table or something? I did some reading on SQLite3 max values, and nothing in my database is anything close to them as far as I can tell.
然后我看了我的每日备份，我看到数据库备份文件大小没有改变3-4天 - 很奇怪。我在文件大小不变的时候恢复了数据库的备份副本，并且仍然会出现奇怪的问题。
I then took a look at my daily backups, and I see that the database backup hasn't changed in file size for 3-4 days - very strange. I restored a backup copy of the database from before the time it was not changing in file size, and still getting strange issues.
我想我要去必须（1）从较旧的备份恢复，（2）重新运行我的Rails迁移以修复。
I'm thinking I'm going to have to (1) restore from an older backup, and (2) re-run my Rails migrations to fix.
推荐答案
很少有事情要考虑：
-
SQLite3数据库文件大致增加了DB页面大小的倍数，除非你使用
VACUUM。如果删除某些行，则释放的空间将被内部标记，并在后续插入中重新使用。因此，插入通常不会导致后台数据库文件的大小发生更改。
SQLite3 DB files grow roughly in multiples of the DB page size and do not shrink unless you use
VACUUM. If you delete some rows, the freed space is marked internally and reused in later inserts. Therefore an insert will often not cause a change in the size of the backing DB file.
不应将传统的备份工具用于SQLite（或任何其他数据库） ，因为它们并没有考虑到DB状态信息，这对确保数据库不受影响至关重要。特别是，在插入事务中间复制DB文件是灾难的秘诀...
You should not use traditional backup tools for SQLite (or any other database, for that matter), since they do not take into account the DB state information that is critical to ensure an uncorrupted database. Especially, copying the DB files in the middle of an insert transaction is a recipe for disaster...
SQLite3有一个 API 专门用于备份或复制正在使用的数据库。
SQLite3 has an API specifically for backing-up or copying databases that are in use.
是的，您的数据库文件似乎已损坏。它可能是硬件/文件系统错误。或者你在复制它们的时候呢？或者可能恢复了未正确使用的备份？
And yes, it does seem that your DB files are corrupted. It could be a hardware/filesystem error. Or perhaps you copied them while they were in use? Or maybe restored a backup that was not properly taken?
这篇关于SQLite3数据库或磁盘已满/数据库磁盘映像格式不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
