Hi, List
Probably this is another case of database corrupted. I read the
documents about this topic and think I did not make same mistakes
described in that 'how to corrupt ...' documentation.
I have a testing code, attached in this email, if continuously run it for
20 - 40 hours, the sqlite database will be corrupted. But the code
itself is very simple, it just keeping inserting a lot of rows. After
inserted some long time, it begin to delete some rows and continues to
insert.
There is no multi-threads, no concurrent database operations. Every
instance of the application instance owns exclusively its database file.
The application is running on an ARM Linux system with Yaffs2 filesystem
on NAND flashes.
Below are sample database error:
------------------------------------------------------
sqlite> pragma integrity_check;
*** in database main ***
Multiple uses for byte 752 of page 20506
On tree page 21363 cell 27: invalid page number 16843521
Multiple uses for byte 676 of page 21363
Fragmentation of 8 bytes reported as 0 on page 21363
Multiple uses for byte 392 of page 28676
rowid 518219 missing from index testidx
Error: database disk image is malformed
-----------------------------------------------------
Please have a look at my attached code and be kindly give me a clue on
what are possible causes?
Thanks in advance.
--
woody
I can't go back to yesterday - because I was a different person then.
#define _GNU_SOURCE
#include <string.h>
#include <stdbool.h>
#include <stddef.h>
#include <strings.h>
#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include <limits.h>
#include <unistd.h>
#include <syslog.h>
#include <assert.h>
#include <ctype.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <sqlite3.h>
#define MAX_SQL_LEN 150
#define SQL_CREATE_TESTTBL \
"create table if not exists testtbl(nComID number(2), nMeterID number(2),
nMPID number(2), nData number, nStatus number, nTimeReq number);"
#define SQL_CREATE_TESTIDX \
"create index if not exists testidx on testtbl(nTimeReq asc,nComID
asc,nMeterID asc,nMPID asc);"
#define SQL_BEGIN_TRANS "BEGIN;"
#define SQL_COMMIT_TRANS "COMMIT;"
#define SQL_ROLLBACK_TRANS "ROLLBACK;"
static char *db_fname = "test.db";
static sqlite3 *conn;
static int sql_code;
static char *sql_msg;
static int fill_time;
static int max_rec_age = 2048;
static bool auto_vacuum;
static int cache_size = 0;
static bool use_syslog;
static bool verbose;
static unsigned max_fill_count;
static void mlog(int opt, const char *fmt, ...)
{
if (! verbose && LOG_PRI(opt) >= LOG_DEBUG)
return;
char s[256];
snprintf(s, sizeof(s), "[%s] %s", basename(db_fname), fmt);
fmt = s;
va_list args;
va_start(args, fmt);
if (use_syslog)
vsyslog(opt, fmt, args);
else
vprintf(fmt, args);
va_end(args);
}
static bool sql_exec(const char *sql)
{
mlog(LOG_DEBUG, "sql_exec: %s\n", sql);
while (true) {
sql_code = sqlite3_exec(conn,
sql, NULL, NULL, &sql_msg);
if (sql_code == SQLITE_OK)
return true;
else {
mlog(LOG_NOTICE, "%s: %s\n", sql_msg, sql);
if (sql_msg)
sqlite3_free(sql_msg);
if (sql_code == SQLITE_BUSY || SQLITE_LOCKED)
sleep(1);
else
return false;
}
}
}
static bool sql_select(const char *sql, char ***result)
{
mlog(LOG_DEBUG, "sql_select: %s\n", sql);
while (true) {
sql_code = sqlite3_get_table(conn, sql,
result, NULL, NULL, &sql_msg);
if (sql_code == SQLITE_OK)
return true;
else {
mlog(LOG_NOTICE, "%s: %s\n", sql_msg, sql);
if (sql_msg)
sqlite3_free(sql_msg);
if (*result)
sqlite3_free_table(*result);
*result = NULL;
if (sql_code == SQLITE_BUSY || SQLITE_LOCKED)
sleep(1);
else
return false;
}
}
}
static bool get_min_time(int *time)
{
char **result_tbl;
if (! sql_select("SELECT MIN(nTimeReq) FROM testtbl;", &result_tbl))
return false;
if (! result_tbl[1])
*time = 0;
else
*time = atoi(result_tbl[1]);
sqlite3_free_table(result_tbl);
return true;
}
static bool get_max_time(int *time)
{
char **result_tbl;
if (! sql_select("SELECT MAX(nTimeReq) FROM testtbl;", &result_tbl))
return false;
if (! result_tbl[1])
*time = 0;
else
*time = atoi(result_tbl[1]);
sqlite3_free_table(result_tbl);
return true;
}
static bool init_fill_time(void)
{
return get_max_time(&fill_time);
}
static bool prepare(void)
{
char sql_set_cache[MAX_SQL_LEN];
mlog(LOG_INFO, "prepare database ...\n");
if (auto_vacuum) {
mlog(LOG_INFO, "auto_vacuum = 1\n");
if (! sql_exec("PRAGMA auto_vacuum = 1;"))
return false;
}
if (cache_size > 0) {
mlog(LOG_INFO, "using cache_size = %d\n", cache_size);
snprintf(sql_set_cache, sizeof(sql_set_cache), "PRAGMA cache_size =
%d;", cache_size);
if (! sql_exec(sql_set_cache))
return false;
}
if (! sql_exec(SQL_CREATE_TESTTBL) || ! sql_exec(SQL_CREATE_TESTIDX))
return false;
return init_fill_time();
}
static bool rotate(void)
{
if (max_rec_age <= 0)
return true;
int time0, time1, time2;
if (! get_min_time(&time0) || !get_max_time(&time1))
return false;
if (time1 < time0) {
mlog(LOG_ERR, "invalide time range [%d, %d]\n", time0, time1);
return false;
} else
mlog(LOG_DEBUG, "current time range [%d, %d]\n", time0, time1);
if (time1 - time0 <= max_rec_age * 60)
return true;
mlog(LOG_INFO, "delete nTimeReq < %d + 4\n", time0);
char sql_del[MAX_SQL_LEN];
sprintf(sql_del, "DELETE FROM testtbl WHERE nTimeReq < %d", time0 + 4);
if (! sql_exec(SQL_BEGIN_TRANS))
return false;
if (! sql_exec(sql_del)) {
sql_exec(SQL_ROLLBACK_TRANS);
return false;
}
if (! sql_exec(SQL_COMMIT_TRANS))
return false;
if (! get_min_time(&time2))
return false;
if (time2 < time0 + 4) {
mlog(LOG_ERR, "delete failed! current min: %d\n", time2);
return false;
} else
return true;
}
static bool check_db(void)
{
static time_t last_chk_time = 0;
time_t elapsed = (unsigned) ((signed)time(0) - (signed)last_chk_time);
if (elapsed < 30 * 60)
return true;
int nrow, ncol;
char **result;
if (sqlite3_get_table(conn, "PRAGMA integrity_check;",
&result, &nrow, &ncol, &sql_msg) != SQLITE_OK) {
mlog(LOG_ERR, "integrity check failed\n");
sqlite3_free_table(result);
if (sql_msg)
sqlite3_free(sql_msg);
return false;
}
if (nrow > 0 && strcmp(result[1], "ok") && strcmp(result[1], "OK")
&& strcmp(result[1], "Ok")) {
sqlite3_free_table(result);
mlog(LOG_ERR, "database corrupted!\n");
return false;
} else
mlog(LOG_INFO, "database integrity ok\n");
sqlite3_free_table(result);
last_chk_time = time(0);
return true;
}
static bool fill(void)
{
char sql[MAX_SQL_LEN];
if (! rotate())
return false;
mlog(LOG_DEBUG, "insert with fill_time = %d\n", fill_time);
if (! sql_exec(SQL_BEGIN_TRANS))
return false;
size_t n;
for (n = 0; n < 5; ++n) {
sprintf(sql, "INSERT INTO testtbl VALUES(3,2,%d,596516,0,%d);", n + 1,
fill_time);
if (! sql_exec(sql))
break;
}
if (n < 5) {
mlog(LOG_WARNING, "insert failed\n");
if (! sql_exec(SQL_ROLLBACK_TRANS))
return false;
} else if (! sql_exec(SQL_COMMIT_TRANS))
return false;
while ((sql_code = sqlite3_close(conn)) != SQLITE_OK) {
mlog(LOG_WARNING, "sqlite closing (%d)\n", sql_code);
sleep(1);
}
if ((sql_code = sqlite3_open(db_fname, &conn)) != SQLITE_OK) {
mlog(LOG_ERR, "sqlite open error (%d)\n", sql_code);
return false;
}
return check_db();
}
static bool do_test(void)
{
unsigned cnt = 0;
while ((max_fill_count == 0 || cnt++ < max_fill_count)
&& fill_time++ < INT_MAX
&& fill())
;
if ((max_fill_count == 0 || cnt++ < max_fill_count)
&& fill_time < INT_MAX) {
mlog(LOG_WARNING, "test aborted abnormally\n");
return false;
} else
return true;
}
static void test(void)
{
if (prepare()) {
mlog(LOG_INFO, "do the test, fill_time=%d\n"
, fill_time);
do_test();
mlog(LOG_INFO, "test end, fill_time=%d ...\n"
, fill_time);
}
}
static bool parse_opt(int argc, char **argv)
{
int c;
opterr = 0;
while ((c = getopt(argc, argv, "vusc:a:n:")) != -1)
switch (c) {
case 'v':
verbose = true;
break;
case 'u':
auto_vacuum = true;
break;
case 's':
use_syslog = true;
break;
case 'n':
max_fill_count = atoi(optarg);
break;
case 'a':
max_rec_age = atoi(optarg);
break;
case 'c':
cache_size = atoi(optarg);
break;
case '?':
if (optopt == 'a' || optopt == 'c'|| optopt == 'n')
fprintf(stderr, "option -%c requries an argument.\n",
optopt);
else if (isprint(optopt))
fprintf(stderr, "unknown option -%c.\n", optopt);
else
fprintf(stderr, "unknown option character 0x%x", optopt);
return false;
default:
return false;
}
if (argc - optind > 1) {
fprintf(stderr, "too many positional arguments\n");
return false;
} else if (optind < argc)
db_fname = argv[optind];
return true;
}
int main(int argc, char **argv)
{
if (! parse_opt(argc, argv))
return -1;
mlog(LOG_INFO, "sqlitetest start. max_rec_age = %d, auto_vacuum=%d\n",
max_rec_age, auto_vacuum);
if ((sql_code = sqlite3_open(db_fname, &conn)) == SQLITE_OK) {
test();
sqlite3_close(conn);
} else {
mlog(LOG_ERR, "sqlite3_open = %d (%s)\n", sql_code, sql_msg);
if (sql_msg)
sqlite3_free(sql_msg);
}
mlog(LOG_INFO, "sqlitetest end\n");
return 0;
}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
