Hello !
I pulled the trigger too soon !
The wall file stop growing at 4MB as advertised.
But in this example there is only one application accessing the database and
only one table with one small record been updated all the time and no active
readers, why it's writing to wal ?
And why not update the same record instead of adding a new one in such
situation ?
I'll try with a bigger record to see what happens.
Cheers !
?
> Thu Mar 24 2016 11:55:11 AM CET from "Domingo Alvarez Duarte"
><sqlite-mail at dev.dadbiz.es> Subject: [sqlite] SQLite with wall enabled
>what's wrong C demo
>
> Hello !
>
> I'm sending here a C program that demonstrates the problem of sqlite3 in
>wall
> mode,
>
> in this simple example of a simulated session management for a web server
> when executing
>
> the wall log file will grow and grow till eat all our disk.
>
> ?
>
> There is something wrong with the program or with sqlite3 ?
>
> Cheers !
>
> ?
>
> #include <stdio.h>
> #include <stdlib.h>
> #include <signal.h>
> #include <unistd.h>
> #include "sqlite3.h"
>
>
> static const char create_sql[] = "create table if not exists sessions(id
>text
> primary key, data text, ip_address, cdate datetime default
>CURRENT_TIMESTAMP,
> mdate datetime);";
> static const char insert_sql[] = "insert or ignore into sessions(id, data,
> ip_address, mdate) values(?,?,?, CURRENT_TIMESTAMP)";
> static const char update_sql[] = "update sessions set data=?,
> mdate=CURRENT_TIMESTAMP where id=?";
> static const char select_sql[] = "select data from sessions where id=? and
> ip_address=?";
>
> static volatile int keepRunning = 1;
>
> void intHandler(int dummy) {
> ??? keepRunning = 0;
> }
>
> int main()
> {
> ??? signal(SIGINT, intHandler);
>
> ??? printf("Starting our busy session management !\n");
> ??? sqlite3 *db;
> ??? int rc = sqlite3_open("sessions.db", &db);
>
> ??? if(rc == SQLITE_OK)
> ??? {
> ??????? sqlite3_stmt *stmt_insert, *stmt_update, *stmt_select;
> ??????? //rc = sqlite3_exec(db, "PRAGMA synchronous = 0;", NULL,
>NULL,
> NULL);
> ??????? rc = sqlite3_exec(db, "PRAGMA journal_mode = WAL;", NULL,
> NULL, NULL);
>
> ??????? //create sessions table
> ??????? rc = sqlite3_exec(db, create_sql, NULL, NULL, NULL);
>
> ??????? //create prepared statements
> ??????? rc = sqlite3_prepare_v2(db, insert_sql,
>sizeof(insert_sql)-1,
> &stmt_insert, NULL);
> ??????? rc = sqlite3_prepare_v2(db, update_sql,
>sizeof(update_sql)-1,
> &stmt_update, NULL);
> ??????? rc = sqlite3_prepare_v2(db, select_sql,
>sizeof(select_sql)-1,
> &stmt_select, NULL);
>
> ??????? const char session_id[] = "ABC123456789";
> ??????? const char ip_address[] = "127.0.0.1";
> ??????? const char data[] = "a kind of initial data";
>
> ??????? //insert initial data
> ??????? rc = sqlite3_bind_text(stmt_insert, 1, session_id,
> sizeof(session_id)-1, NULL);
> ??????? rc = sqlite3_bind_text(stmt_insert, 2, data, sizeof(data)-1,
> NULL);
> ??????? rc = sqlite3_bind_text(stmt_insert, 3, ip_address,
> sizeof(ip_address)-1, NULL);
> ??????? rc = sqlite3_step(stmt_insert);
> ??????? rc = sqlite3_reset(stmt_insert);
>
> ??????? int count = 0;
> ??????? //start our busy long work
> ??????? while(keepRunning)
> ??????? {
> ??????????? printf("Managing session %d\n", ++count);
> ??????????? sleep(1);
> ??????????? //one client arrived, let's get it's saved data
> ??????????? rc = sqlite3_bind_text(stmt_select, 1, session_id,
> sizeof(session_id)-1, NULL);
> ??????????? rc = sqlite3_bind_text(stmt_select, 2, ip_address,
> sizeof(ip_address)-1, NULL);
> ??????????? rc = sqlite3_step(stmt_select);
> ??????????? const unsigned char *saved_data =
> sqlite3_column_text(stmt_select, 0);
> ??????????? rc = sqlite3_reset(stmt_select);
>
> ??????????? sleep(1);
> ??????????? //ok we served our client, let's save it's data
> ??????????? rc = sqlite3_bind_text(stmt_update, 1, saved_data ?
> "ok we have previous data" : data, -1, NULL);
> ??????????? rc = sqlite3_bind_text(stmt_update, 2, session_id,
> sizeof(session_id)-1, NULL);
> ??????????? rc = sqlite3_step(stmt_update);
> ??????????? rc = sqlite3_reset(stmt_update);
> ??????? }
>
> ??????? sqlite3_finalize(stmt_insert);
> ??????? sqlite3_finalize(stmt_update);
> ??????? sqlite3_finalize(stmt_select);
> ??????? sqlite3_close(db);
> ??? }
>
> ??? return 0;
> }
>
>
>
> ?
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
?
