sqlite disk I/O problem
July 13, 2009 09:34PM
Hi all,
I'm trying to build a sqlite3 helloworld, but I'm having a strange problem.
My helloworld application is built using latest stable libogc and dekitppc rev16.
Compilation goes fine, database is opened ok, but when trying to do a simple select from a table with just one record I get a disk I/O error, and can't find the cause.
I'm using default fat library and sqlite3 lib taken from [wiimp.googlecode.com] and also compiled the source thinking it could be a compatibility problem.
Anyway to make it compile with devkitPPC16 I had to do a quick hack:
comment
int _EXFUN(gettimeofday, (struct timeval *__p, void *__tz));
on
devkitPro\devkitPPC\powerpc-gekko\include\sys\time.h
and adding gettimeofday function you see in the db.c file.
but I don't think the problem can be there (I hope :P).
The example is pretty easy to read, any help is appreciated:

main.c
#include 
#include 
#include 
#include 
#include "db.h"
#include "sqlite3.h"

static void *xfb = NULL;
static GXRModeObj *rmode = NULL;

sqlite3 *db;

//---------------------------------------------------------------------------------
int main(int argc, char **argv) {
//---------------------------------------------------------------------------------
	VIDEO_Init();
	WPAD_Init();
	rmode = VIDEO_GetPreferredMode(NULL);
	xfb = MEM_K0_TO_K1(SYS_AllocateFramebuffer(rmode));
	console_init(xfb,20,20,rmode->fbWidth,rmode->xfbHeight,rmode->fbWidth*VI_DISPLAY_PIX_SZ);
	VIDEO_Configure(rmode);
	VIDEO_SetNextFramebuffer(xfb);
	VIDEO_SetBlack(FALSE);
	VIDEO_Flush();
	VIDEO_WaitVSync();
	if(rmode->viTVMode&VI_NON_INTERLACE) VIDEO_WaitVSync();

	fatInitDefault();
	printf("\x1b[2;0H");
	

	printf("Hello World!");

	DB_Open();
	execQuery("select * from game", db);
	DB_Close();

	while(1) {
		WPAD_ScanPads();
		u32 pressed = WPAD_ButtonsDown(0);
		if ( pressed & WPAD_BUTTON_HOME ) exit(0);
		VIDEO_WaitVSync();
	}

	return 0;
}

db.c
#include "db.h"

extern sqlite3 *db;

//
// HACK
//
// comment int _EXFUN(gettimeofday, (struct timeval *__p, void *__tz));
// in C:\devkitPro\devkitPPC\powerpc-gekko\include\sys\time.h
//
int gettimeofday(struct timeval *tp, struct timezone *tz) {
 
	if (tp != NULL) {
		tp->tv_sec = time(NULL);
		tp->tv_usec = 0;
	}
	if (tz != NULL) {
		tz->tz_minuteswest = 0;
		tz->tz_dsttime = 0;
 
	}
	return 0;
}

int DB_callback(void *NotUsed, int argc, char **argv, char **azColName)
{
	NotUsed=0;
	int i;
	char merlo[255];
	
	for(i=0; i, argv ? argv: "NULL");
 		sprintf(merlo, "%s = %s\n", azColName, argv ? argv: "NULL");
 	}
 
 	//WindowPrompt("OK?", merlo, 0, &okButton);
 	printf("done\n");
 	
 	return 0;
 }
 
 void execQuery(char* command, sqlite3* db)
 {
 	char *zErrMsg = 0;
 	//printf("%s", command);
 	
 	printf("pre\n");
 	int rc = sqlite3_exec(db, command, DB_callback, 0, &zErrMsg);
 		//return;
 	printf("post\n");	
 	if(rc != SQLITE_OK)
 	{
 		//WindowPrompt("execQuery error", zErrMsg, 0, &okButton);
 		printf("execQuery error %s\n", zErrMsg);
         /* This will free zErrMsg if assigned */
 		//if (zErrMsg)
 		//	free(zErrMsg);
 	}
 	else
 		printf("DB QUERY OK\n");
 }
 
 void DB_Open()
 {
 	int rc;
 	
 	rc = sqlite3_open(DB_FILE, &db);
 	
 	if(rc)
 	{
 		printf("cannot open %s\n", (char*)sqlite3_errmsg(db));
 		//WindowPrompt("CANNOT OPEN", (char*)sqlite3_errmsg(db), 0, &okButton);
 		sqlite3_close(db);
 	}
 	else
 		printf("DB OPENED OK\n");
 	//else
 	//	WindowPrompt("OPEN OK", "Database opened!", 0, &okButton);
 }
 
 
 void DB_Close()
 {
 	sqlite3_close(db);
 }
 
db.h
 #ifndef __DB__
 #define __DB__
 
 #include "sqlite3.h"
 #include "time.h"
 #include <sys/time.h>
#include 
#include 
#include 

#define DB_FILE "sd:/wiitdb.db"

int DB_callback(void *NotUsed, int argc, char **argv, char **azColName);
void execQuery(char* command, sqlite3* db);
int gettimeofday(struct timeval *tp, struct timezone *tz);
void DB_Open();
void DB_Close();

#endif

Makefile is the usual one with these modifications
LIBS	:=	-lfat -lwiiuse -lbte -logc -lm -lsqlite3
LIBDIRS	:= $(CURDIR)/libs/sqlite

The dbfile is very small (20K) and contains few tables with 1 rows each, I'll attach if needed.
The dbfile also is opened and used ok using sqllitebrowser.

The output I get is:
Hello World!DB OPENED OK!
pre
post
execQuery error disk I/O error

Any clue? Maybe DrTwox if you are reading this you can help...
Sorry for big post, and thanks in advance.

Scognito

EDIT: The code works on linux (with the obvious changes) since it is not a problem of how I coded it.



Edited 1 time(s). Last edit at 07/13/2009 10:55PM by scognito.
Re: sqlite disk I/O problem
July 13, 2009 11:26PM
scognito,

If you are using sqlite.c and not the library, you will need to add
-DSQLITE_OS_UNIX and -DSQLITE_OS_LIBOGC to the CFLAGS in your Makefile. This will build sqlite3 with the changes I've made.

In your DB_Open() function, the call to open the database should look something like this:
s32 result;
result = sqlite3_open_v2( "sd:/path/to/database.db", &db, SQLITE_OPEN_READONLY, "unix-none" );
OR
result = sqlite3_open_v2( "sd:/path/to/database.db" , &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, "unix-none" );
if ( result != SQLITE_OK ) {
    /* error /*
}

Let me know how it goes!



Edited 1 time(s). Last edit at 07/14/2009 08:38AM by DrTwox.
Re: sqlite disk I/O problem
July 14, 2009 12:43AM
( EDITED, don't forget to look at the end of the post )

Thanks!
At least now I made a step further! (I was trying to sleep when I read the response on the phone and powered on the wii again!)

I'm using the precompiled one, (the one I compiled used your makefile, but didn't had a look inside it :))

Didn't noticed that I pasted half db.c, here it is the complete one: [pastebin.com]
The weird thing is that with the simple example I get DB QUERY OK, while in my project (a "little" bigsize one) I get:

library routine called out of sequence

I read here [www.sqlite.org] but doesn't seem help...

Any cloue? Thanks!

EDIT:
Got it to work (at least it seems so :P): the difference between them seems the path of the db file:

working: SD:/mydb.db
not working: SD:/path/mydb.db

I'm very tired and maybe I did something wrong, but at least I'm happy it worked! (using sqlite3_open_v2( DB_DST_NAME, &db, SQLITE_OPEN_READONLY, "unix-none" ) as I need it only as read-only).


Thanks DrTwox!



Edited 1 time(s). Last edit at 07/14/2009 01:10AM by scognito.
Re: sqlite disk I/O problem
July 18, 2009 09:31PM
Ok, everything works, but seems I'm having a bit of memory trashing.
This is what I use:

void getNameFromId(char* id, char* title){
	
	char q[300];
	sprintf(q, "select id, version, region, \
			    from table \
                         where id = '%s'", id);
	execQuery(q, db);	
}

void execQuery(char* command, sqlite3* db)
{
	char *zErrMsg = 0;

	int rc = sqlite3_exec(db, command, DB_callback, 0, &zErrMsg);

	if(rc != SQLITE_OK)
	{
            /* This crash if I use one of the free */
		//if (zErrMsg)
			//free(zErrMsg);
			//sqlite3_free(zErrMsg);
	}
}

int DB_callback(void *NotUsed, int argc, char **argv, char **azColName)
{
	NotUsed=0;
	int i;
	
	sprintf(cGame.id, argv[0]);
	sprintf(cGame.version, argv[1]);
	sprintf(cGame.region, argv[2]);
	
	return 0;
}

Using mallinfo I get about 2K of memory used each query I do...
Is it normal?
Thanks!
Re: sqlite disk I/O problem
July 19, 2009 12:01AM
I'm not familiar with the mallinfo function - I learn something new every day!
Have you tried the same code on Linux to compare the memory usage? Perhaps this is more of a question for a sqlite3 mailing list; I simply did the port, and the internal workings of the library are a mystery to me :)

Anyway, this part here...
if(rc != SQLITE_OK)
        {
            /* This crash if I use one of the free */
                //if (zErrMsg)
                        //free(zErrMsg);
                        //sqlite3_free(zErrMsg);
        }
...crashes because you use free and then sqlite3_free on the same pointer. Just use sqlite3_free. You should print the error too; perhaps that will be a clue to the memory usage.

Out of curiosity; what are you working on?
Re: sqlite disk I/O problem
July 19, 2009 12:52AM
Thanks DrTwox, but the problem is that it crashes if I use one of the free above...
Probably there is something that doesn't work correctly on the free in sqlcode?
Offtopic
As for the project, probably no one will help me after I say the project I'm working on, but who cares, I'm coding that only for the fun of that. It's one of the 1000 usb loaders, but I use that for learning new things, in this case I'm using the db for storing information about games.
I'm not using that for piracy, since I spend the few hours of free time I have coding rather than playing warez games.
Hoping this will not lead people to not helping me :P
/ Offtopic
Re: sqlite disk I/O problem
July 19, 2009 05:41AM
Have you printed the error anyway, too see what the sqlite error is?
Re: sqlite disk I/O problem
July 19, 2009 11:49AM
I just get a code dump, anyway I'm trying on linux to see if I get this memory leak and if the free works.
I'll post results soon, thanks.
Problem using sqlite (was Re: sqlite disk I/O problem)
July 19, 2009 01:14PM
The leak seems still there, so or I'm doing something *really* lame, or the is something else I didn't catch about sqlite.
Test is run on linux, and the code is available here: [pastebin.com]
I used both frees for the zErrMsg but the result is the same (no crash, but memory leak still there)

This is the result
DB OPENED OK
RZDP01 PAL The Legend Of Zelda: Twilight Princess
all: 1188 KB used: 1138 KB notused: 49 KB canfree: 38KB

RMGP01 PAL Super Mario Galaxy
all: 1188 KB used: 1141 KB notused: 46 KB canfree: 35KB

RSPP01 PAL Wii Sports
all: 1188 KB used: 1144 KB notused: 43 KB canfree: 30KB

REXP01 PAL Excite Truck
all: 1188 KB used: 1144 KB notused: 43 KB canfree: 30KB

RMCP01 PAL Mario Kart Wii
all: 1188 KB used: 1148 KB notused: 39 KB canfree: 30KB

As you can see the mem used grows as I didn't free'd some resource...(field 'used')
Should I really ask to sqlite forum or there is something I did wrong?
Thanks in advance.



Edited 1 time(s). Last edit at 07/19/2009 01:15PM by scognito.
Re: sqlite disk I/O problem
July 19, 2009 02:31PM
Quote
scognito

	char *zErrMsg = 0;

	int rc = sqlite3_exec(db, command, DB_callback, 0, &zErrMsg);

	if(rc != SQLITE_OK)
	{
            /* This crash if I use one of the free */
		//if (zErrMsg)
			//free(zErrMsg);
			//sqlite3_free(zErrMsg);
	}

I do not see why sqlite3_free(zErrMsg) fails, but free(zErrMsg) can't work.



Edited 1 time(s). Last edit at 07/19/2009 02:31PM by daniel_c_w.
Re: sqlite disk I/O problem
July 19, 2009 02:47PM
Quote
daniel_c_w
I do not see why sqlite3_free(zErrMsg) fails, but free(zErrMsg) can't work.
I don't know why them crashes...anyway why the free can't work? Just curious.
Re: sqlite disk I/O problem
July 19, 2009 03:01PM
free() is not a part of the c language, but of the c-libraray.
how free() and malloc() are implemented is dependent on the platform.

In other words: free() may work in a way that can't do anything with the pointer returned from sqlite's function.
(for example: malloc() and free() may organize a table of free memory, and sqlite's functions can not know anything about that table, and therefore can't keep it up to date)



Anyway: I should rephrase my statemnet:
Quote

but free(zErrMsg) can't work
to: but free(zErrMsg) will most likely not work
Re: sqlite disk I/O problem
July 19, 2009 03:20PM
Ah ok now I understand, thanks.
Do you know anyway if you think it's normal I get the memory leak (almost) each time I execute a query?
I posted the code on pastebin.

Thanks!
Re: sqlite disk I/O problem
July 19, 2009 09:18PM
Using valgrind on the text I get:

==2987== ERROR SUMMARY: 0 errors from 0 contexts (suppressed: 11 from 1)
==2987== malloc/free: in use at exit: 0 bytes in 0 blocks.
==2987== malloc/free: 1,387 allocs, 1,387 frees, 64,390 bytes allocated.
==2987== For counts of detected errors, rerun with: -v
==2987== All heap blocks were freed -- no leaks are possible.

Seems I don't have memory leaks, wonder why mallinfo tells me memory is allocated then...
Re: sqlite disk I/O problem
July 19, 2009 11:53PM
Re: sqlite disk I/O problem
July 20, 2009 12:13AM
Quote
scognito
Do you know anyway if you think it's normal I get the memory leak (almost) each time I execute a query!
If know next to nothing about sqlite, and nothing about mallinfo.
As I saw your code, I did some googling on sqlite and the code you posted here looks fine to me.
(didn't study the pastebin code)

If I would have to take a wild guess:
cache memory from the database.
If you do a lot more queries, does the memusage reach a peak?




Edited 1 time(s). Last edit at 07/20/2009 12:13AM by daniel_c_w.
Re: sqlite disk I/O problem
July 22, 2009 07:28AM
Any news?
Re: sqlite disk I/O problem
July 23, 2009 10:12AM
Didn't have much time to testing these days...
Anyway last thing I did was read a bit of "Dynamic Memory Allocation In SQLite" and I used the function:

sqlite3_status(verb, ¤t, &highwater, resetflag); (http://www.sqlite.org/c3ref/status.html)

where verb is SQLITE_STATUS_MEMORY_USED, and reset flag is 1.

I noticed that each query increase the value of "current", but executing the same query with same values does not.
So seems that the query result is cached, maybe this is the reason why seems I get these "leaks".

Anyway since query are _really_ simple ("select a from table where x = y (1 record)") and I don't want to waste space, I'm searching a solution to not cache query results OR limit upperbound memory.

I found [www.sqlite.org] but didn't understand very well, also the integer passed as parameter what means? KB, Kb, bytes?
Re: sqlite disk I/O problem
July 23, 2009 10:34AM
sqlite3_soft_heap_limit takes the bytes to use as the heap size; though it will use more than this if required to successfully execute a command; hence why it's called a 'soft limit'. If you want to force a strict upper memory limit look at the section called "Zero-malloc memory allocator" in the previous link, though you will have to compile sqlite3.c from source to enable it.
Re: sqlite disk I/O problem
July 23, 2009 11:10AM
Yeah I don't want a strict memory limit, a soft one is good, I want to have an upperbound of of 100K is ok.
I'll test with a lower value to see how "soft" it is.
The drawback is that the executable is 500KB bigger, but that's fine, sqlite rocks :)
Sorry, only registered users may post in this forum.

Click here to login