On Aug 10, 2010, at 6:03 PM, Yoni Londner wrote:
> Hi,
> Yes, it explains allot.
> But, according to you, the following program should work.
> I don't use transactions, shared cache or threads.
> Just run the checkpoint from another connection.
> And still - WAL file is getting bigger and bigger without limit.
> If I do the checkpoint with the same connection, everything work
> perfectly.
Interesting. Perhaps the call to sqlite3_wal_checkpoint()
is not working because the second connection never really
connects to the database (because connection usually happens
as part of the first SQL statement run).
Are things any different if you change the sqlite3_wal_checkpoint()
to sqlite3_exec(conn, "PRAGMA wal_checkpoint", 0, 0, 0)?
Dan.
> #include "sqlite3.h"
> #include "stdio.h"
> #include "stdlib.h"
> #include "fcntl.h"
>
> static void sql_exec(sqlite3 *conn, char *query)
> {
> char *err;
> if (sqlite3_exec(conn, query, NULL, 0, &err))
> {
> printf("sqlite: failed exec %s. err: %s\n", query, err);
> exit(1);
> }
> }
>
> static sqlite3 *sql_open_conn(void)
> {
> sqlite3 *conn;
> if (sqlite3_open_v2("test.db", &conn, SQLITE_OPEN_READWRITE, NULL))
> {
> printf("sqlite3_open_v2 failed\n");
> exit(1);
> }
> return conn;
> }
>
> static int do_checkpoint()
> {
> sqlite3 *conn;
> conn = sql_open_conn();
> if (sqlite3_wal_checkpoint(conn, NULL))
> {
> printf("sqlite3_wal_autocheckpoint failed\n");
> exit(1);
> }
> sqlite3_close(conn);
> }
>
> int main(int argc, char **argv)
> {
> char *err_msg = NULL;
> pthread_t thread;
> int fd, i;
> sqlite3 *conn;
> if (unlink("test.db") || unlink("test.db-wal"))
> {
> printf("failed unlink test.db\n");
> exit(1);
> }
> fd = open("test.db", O_CREAT|O_RDWR, 0666);
> if (fd<0)
> {
> printf("could not open test.db\n");
> exit(1);
> }
> close(fd);
> conn = sql_open_conn();
> sql_exec(conn, "PRAGMA journal_mode=WAL");
> sql_exec(conn, "PRAGMA synchronous=normal");
> sql_exec(conn, "PRAGMA temp_store=memory");
> sql_exec(conn, "PRAGMA wal_autocheckpoint=-1");
> sql_exec(conn, "create table tbl1 (one varchar(20), two
> varchar(20))");
> while (1)
> {
> if (!(i++%1000))
> do_checkpoint();
> sql_exec(conn, "INSERT INTO tbl1 values('aaaaaaaaaaaaaaaaaaa', "
> "'bbbbbbbbbbbbbbbbbbb')");
> }
> sqlite3_close(conn);
> return 0;
> }
>
> On Tue, Aug 10, 2010 at 1:52 PM, Dan Kennedy <danielk1...@gmail.com>
> wrote:
>
>>
>>
>>> Hi,
>>> I just wanted to add that I changed the program a little bit to not
>>> use
>>> transactions and threads, and I still get the same problem (huge WAL
>>> file).
>>> All I do is endless loop of insert, and every X insert, I perform a
>>> checkpoint on another sqlite connection (but in the same thread).
>>> It only worked if I do the checkpoint on the same connection.
>>
>> You cannot run a checkpoint from within a transaction. If
>> you are in shared-cache mode, this means you cannot run a
>> checkpoint while any connection to the same database has
>> an open transaction.
>>
>> Does that explain anything?
>>
>> Dan.
>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
