Hi Donald&Jay, Thanks for your information. And I don't know how to reach Jay kreibich's email address, so I CC this mail to 'General Discussion of SQLite Database'.
Could you please help me forward to Jay Kreibich if he cannot receive it. For the *easiest* way, I tried by turning off the journal mode in my application, it will not generate journal file any more when delete performed. But I found the speed of update database is EXTREMELY slow( I have about 8000 records to insert into the database). And the second concern is that if I turn off the journal mode, SQLITE will not roll back even if I using transactions, am I right? For the *safest* way, I will try and give feedback to you. Thanks again, Tim -----邮件原件----- 发件人: Griggs, Donald [mailto:donald.gri...@allscripts.com] 发送时间: 2010年3月26日 21:22 收件人: Bravo Shen 主题: FW: [sqlite] sqlite3 error: database or disk is full when commit transaction Below is from Jay Kreibich. I will write you again later, Donald -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich Sent: Thursday, March 25, 2010 1:09 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] sqlite3 error: database or disk is full when commit transaction On Thu, Mar 25, 2010 at 10:56:40PM +0800, Tim scratched on the wall: > > I have a 32MB hard disk. My OS is about 17MB and my application's size > is about 3MB. So there is only 10MB for program data. > > My database is about 4.74MB. When I perform following sql query in a > transaction > > sqlite3_exec(m_sqlite3, "delete from table", 0, 0, &errmsg); > > I got the error "database or disk is full". I guess when do the > transaction, the database was duplicated, and it caused no free disk space. The database is not duplicated, but a journal file is going to be created that needs to hold a copy of every page the delete command touches. If this is the main table in that database, that will be most of the database. This will happen if the delete is inside an explicit transaction or not. That's just the first of your problems, however. Even if the delete command worked, the database file would not get any smaller. To actually recover the disk space you need to VACUUM the database. That does copy the database, plus creating a journal file, so it requires up to 2x the size of the database in freespace-- although in your case the copy would be much smaller, so you might get away with ~1x the free space. The *safest* way to get past the delete is to delete a small number of rows at a time. The journal file will grow roughly in step with the number of deleted rows. So if you delete 10% of the rows at a time (as different transactions) you may be able to get by on more limited space. In the end, the database size will remain the same however. You'll still need to VACUUM to make the database smaller, and you still won't have room to do that. You will have room to add new rows, however. The *easiest* way to get around this is to turn off journaling. The delete should take no extra disk space and the vacuum should only need enough free space to hold the a copy of the new database. Of course, if anything goes wrong or you run out of space anyways, your original database is toast. Make sure you back it up first. http://www.sqlite.org/pragma.html#pragma_journal_mode -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users __________ Information from ESET NOD32 Antivirus, version of virus signature database 4978 (20100326) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 4978 (20100326) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
