在iPhone SDK上格式错误的sqlite数据库磁盘映像

I'm having an issue with a new application on the iPhone SDK using SQLite as the DB backend.

我在使用SQLite作为数据库后端的iPhone SDK上的新应用程序时遇到问题。

Occasionally, my app will stop loading data to my UITableViews and after downloading the device DB via the Organizer I can access the SQLite DB via the command line. I can query certain tables fine but not others without getting an "SQL error: database disk image is malformed" error. See a sqlite session below:

偶尔，我的应用程序将停止将数据加载到我的UITableViews，并在通过管理器下载设备数据库后，我可以通过命令行访问SQLite DB。我可以查询某些表，但没有得到“SQL错误：数据库磁盘映像格式错误”错误的其他表。请参阅下面的sqlite会话：

    SQLite version 3.6.17
    Enter ".help" for instructions
    Enter SQL statements terminated with a ";"
    sqlite> select * from user;
    1|cpjolicoeur@gmail.com|cpjolicoeur||4d055e38bb1d3758|image/gif|cartoonme_avatar.gif||Craig|Jolicoeur|1|1
    sqlite> select * from item;
    SQL error: database disk image is malformed
    sqlite>

In this example my user table works fine but my item table is malformed, which corresponds with what I am seeing in my app where the items dont load. The app doesnt crash, the data just doesnt load because of this malformed error.

在这个例子中，我的用户表工作正常，但我的项目表格格式不正确，这与我在我的应用程序中看到的项目不相加。应用程序没有崩溃，数据只是因为这个错误的错误而无法加载。

Any ideas why this is happening? My only thought is that maybe the DB is being corrupted because I am writing to the SQLite DB via a background thread within the app. I download data from a webserver via an NSOperationQueue in a background thread and update the SQLite DB with the data downloaded. Would writing to the DB in a background thread (while potentially reading from the main thread) corrupt the DB, or is it something else?

任何想法为什么会这样？我唯一想到的可能是数据库被损坏，因为我通过应用程序中的后台线程写入SQLite数据库。我通过后台线程中的NSOperationQueue从Web服务器下载数据，并使用下载的数据更新SQLite DB。在后台线程中写入数据库（虽然可能从主线程中读取）会损坏数据库，还是其他什么？

3 个解决方案

#1

You have to be very careful about background threads accessing the database while debugging! This is because when the debugger halts processing (such as at a breakpoint) all threads are paused, including threads that may be in the middle of a database call, somewhere in between a database "open" and a database "close" call.

在调试时，您必须非常小心访问数据库的后台线程！这是因为当调试器暂停处理（例如在断点处）时，所有线程都会暂停，包括可能位于数据库调用中间的线程，介于数据库“open”和数据库“close”调用之间。

If you are halted at a breakpoint, and click the stop sign in Xcode, your app exits immediately. This very often causes errors such as the one you saw, or the "corrupted database" error.

如果您在断点处停止，并单击Xcode中的停止标志，您的应用会立即退出。这经常会导致错误，例如您看到的错误或“损坏的数据库”错误。

There really isn't any solution (because there is no way to modify the behavior of "stop tasks", but I have evolved some techniques to mitigate it: 1. Add code to detect the app entering the background and have your db operations gracefully stop. 2. Never use the stop sign to halt processing while debugging. Instead, when done with a breakpoint then "continue", hit the home button on the simulator or device (which should trigger the code you added in step 1), wait for the app to background, THEN you can stop the run.

确实没有任何解决方案（因为没有办法修改“停止任务”的行为，但我已经发展了一些技术来缓解它：1。添加代码以检测进入后台的应用程序并优雅地执行数据库操作2.在调试时不要使用停止标志停止处理。相反，当完成断点然后“继续”时，点击模拟器或设备上的主页按钮（应触发您在步骤1中添加的代码），等待对于应用程序的背景，那么你可以停止运行。

#2

In my case this had to do with iOS 7: On iOS 7 Core Data now uses the SQLite WAL journaling mode which writes data to a .db-wal file instead of directly to the .db file. In my app, I would copy a prepared .db file into Library/Application Support during an app update. The problem was that an old .db-wal file was still in that directory and I only replaced the .db file. That way, I ended up with a .db file that was out of sync with the old .db-wal file.

在我的情况下，这与iOS 7有关：在iOS 7上，Core Data现在使用SQLite WAL日记模式，该模式将数据写入.db-wal文件而不是直接写入.db文件。在我的应用程序中，我会在应用程序更新期间将准备好的.db文件复制到库/应用程序支持中。问题是旧的.db-wal文件仍然在该目录中，我只替换了.db文件。这样，我最终得到的.db文件与旧的.db-wal文件不同步。

There are two solutions to this problem:

这个问题有两种解决方案：

Make sure the .db, .db-wal and .db-shm files are deleted before you copy your new .db file into place.
在将新的.db文件复制到位之前，请确保删除.db，.db-wal和.db-shm文件。
Go back to the old pre-iOS 7 behavior like so: https://*.com/a/18870738/171933
回到旧的iOS 7之前的行为：https：//*.com/a/18870738/171933

#3

Depends on how SQLite is compiled, it may or may not be thread-safe. If you're using the built-in one, it may not have the compile-time options you're looking for.

取决于如何编译SQLite，它可能是也可能不是线程安全的。如果您使用的是内置的，它可能没有您正在寻找的编译时选项。

For our app, we had to roll our own SQLite to add full text search. Take a look at this page.

对于我们的应用程序，我们必须推出自己的SQLite来添加全文搜索。看看这个页面。

#1