SQLite3数据库或磁盘已满/数据库磁盘映像格式不正确

我的数据库大约有25MB，我已经验证了访问它的用户名和文件权限在几个月内没有变化。我遇到了一个问题，查询失败是由于“数据库或磁盘已满”，然后有时“数据库磁盘映像是畸形”的问题。

除非我读错了，否则我的磁盘根本就没有满(这是一个 Ubuntu 服务器，9.10，如果有区别的话)

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

作为一个测试，我刚刚做了一个动作，添加了一个新的记录，它的罚款。我想知道是否有一系列行动失败了。但是，在插入(并验证它是否存在)之后，数据库磁盘上的字节数没有改变(既不向上也不向下)。

使用命令行实用程序会导致类似下面这样的结果，这将导致引人注目的失败:)

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 max 值的文章，目前为止，我的数据库中没有任何值接近它们。

然后我看了一下我的日常备份，我发现数据库备份在3-4天内没有改变文件大小——非常奇怪。我恢复了数据库的一个备份副本，在它没有改变文件大小之前，仍然会出现奇怪的问题。

我认为我将不得不(1)从一个旧的备份中恢复，(2)重新运行我的 Rails 迁移来修复。

157240 次浏览
小开

I have seen this happen when the database gets corrupted, have you tried cloning it into a new one ?

Safley copy a s SQLite db

Safely copy a SQLite database

It's trivially easy to copy a SQLite database. It's less trivial to do this in a way that won't corrupt it. Here's how:

shell$ sqlite3 some.db
sqlite> begin immediate;
<press CTRL+Z>
shell$ cp some.db some.db.backup
shell$ exit
sqlite> rollback;

This will give you a nice clean backup that's sure to be in a proper state, since writing to the database half-way through your copying process is impossible.

小开
最佳答案

A few things to consider:

  • 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.

  • 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 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?

小开

To repair a corrupt database you can use the sqlite3 commandline utility. Type in the following commands in a shell after setting the environment variables:

cd $DATABASE_LOCATION
echo '.dump'|sqlite3 $DB_NAME|sqlite3 repaired_$DB_NAME
mv $DB_NAME corrupt_$DB_NAME
mv repaired_$DB_NAME $DB_NAME

This code helped me recover a SQLite database I use as a persistent store for Core Data and which produced the following error upon save:

Could not save: NSError 259 in Domain NSCocoaErrorDomain { NSFilePath = mydata.db NSUnderlyingException = Fatal error. The database at mydata.db is corrupted. SQLite error code:11, 'database disk image is malformed' }

小开

I use the following script for repairing malformed sqlite files:

#!/bin/bash


cat <( sqlite3 "$1" .dump | grep "^ROLLBACK" -v ) <( echo "COMMIT;" ) | sqlite3 "fix_$1"

Most of the time when a sqlite database is malformed it is still possible to make a dump. This dump is basically a lot of SQL statements that rebuild the database.

Some rows might be missing from the dump (probably becasue they are corrupted). If this is the case the INSERT statements of the missing rows will be replaced with some comments and the script will end with a ROLLBACK TRANSACTION.

So what we do here is we make the dump (malformed rows are excluded) and we replace the ROLLBACK with a COMMIT so that the entire dump script will be committed in stead of rolled back.

This method saved my life a couple of 100 times already \o/

小开

To avoid getting "database or disk is full" in the first place, try this if you have lots of RAM:

sqlite> pragma temp_store = 2;

That tells SQLite to put temp files in memory. (The "database or disk is full" message does not mean either that the database is full or that the disk is full! It means the temp directory is full.) I have 256G of RAM but only 2G of /tmp, so this works great for me. The more RAM you have, the bigger db files you can work with.

If you haven't got a lot of ram, try this:

sqlite> pragma temp_store = 1;
sqlite> pragma temp_store_directory = '/directory/with/lots/of/space';

temp_store_directory is deprecated (which is silly, since temp_store is not deprecated and requires temp_store_directory), so be wary of using this in code.

小开

while using Google App Engine, i had this problem. For some reason i did following since then Google App Engine was never starting.

$ echo '' > /tmp/appengine.apprtc.root/*.db

To fix it i required to do manually:

$ sqlite3 datastore.db
sqlite> begin immediate;
<press CTRL+Z>
$ cp datastore.db logs.db

And then run the Google App Engine with flag: 

$ dev_appserver.py --clear_datastore --clear_search_index

after that it finally worked.

小开

During app development I found that the messages come from the frequent and massive INSERT and UPDATE operations. Make sure to INSERT and UPDATE multiple rows or data in one single operation.

var updateStatementString : String! = ""


for item in cardids {
let newstring = "UPDATE "+TABLE_NAME+" SET pendingImages = '\(pendingImage)\' WHERE cardId = '\(item)\';"
updateStatementString.append(newstring)
}


print(updateStatementString)


let results = dbManager.sharedInstance.update(updateStatementString: updateStatementString)


return Int64(results)
小开

Cloning the current database from the sqlite3 commandline worked for me. 

.open /path/to/database/corrupted_database.sqlite3
.clone /path/to/database/new_database.sqlite3

In the Django setting file change the database name

DATABASES = {
'default': {
'ENGINE': 'django.db.backends.sqlite3',
'NAME': os.path.join(BASE_DIR, 'new_database.sqlite3'),
}}
小开

I had "database disk image is malformed" trying to read DB with object definitions created with later lib version and not supported by the current one.

I suppose there can be many examples of this, in my case it was GENERATED ALWAYS AS column - it was causing "database disk image is malformed" error with lib version 3.24.0. Updating it to 3.37.0 which supports this feature - has fixed this issue.


提交答案