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
作为测试，我刚刚做了一个添加新记录的操作，没问题.我试图弄清楚是否有一组特定的操作失败.但是，在插入(并验证它在那里)之后，数据库磁盘上的字节数没有改变(既不增加也不减少).
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 文件大致以 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(或任何其他数据库，就此而言)使用传统的备份工具，因为它们没有考虑数据库状态信息，这对于确保数据库未损坏至关重要.特别是，在插入事务的中间复制数据库文件是灾难的秘诀......
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屋！
