sqlite disk I/O problem

Here I'm again, with a new problem this time :)
After spending *days* in trying to figure out why my code didn't work, I decided to ask a friend to test my dol.
The scenario is simple: insert 3 rows in a table, and each row contains about 40 fields.
I'm using prepared statement and (tried also without) BEGIN / END transaction.
On my card, after 3 insert (no error reported by sqlite) the file created is so corrupted that I can't even transfer to my pc, so I had to remove it.
It "worked" if I removed the bind_text for 2 fields and it's very strange (I made 200 test to test if the variable was null and so on...) seems "magic" and anyway I need these 2 parameters.
So I sent the "not working" dol to him, and he told me it worked...so he sent me back the newly created db.
The only difference is that my card is formatted with FAT32, while his one is just FAT.
So I formatted my card to FAT and guess what...it worked.
That's *very* odd since I need FAT32 formatteed card, and anyway I want to understand why FAT worked and FAT32 not.
Also I tried other vfs modules in the sqlite3_openv2 function but only "unix-none" (the one I use) and "unix-dotfile" can be used, and both give me the same result: file corrupted on FAT32.
Using null as last parameter of sqlite3_open_v2 gives me the "topic opener" I/O error.
Any hint? I can also compile sqlite3 if necerssary...

Thanks!

Is your libfat up to date?

Should be one of the latest (if not the latest), anyway I use that version without any problem in other projects (FAT32).
Could it be related in how the sqlite3 is compiled?

Dr Twox, I've compiled sqlite 3.6.16 with your patches if you want, as I hoped it was a library issue, but it didn't.
Also tried with standard libfat provided with libogc...no luck.
I don't know what else to do :(

I probably won't have time to look into this until the weekend, but you could have a look in the functions unixWrite and seekAndWrite and see what you can discover.

I'll have a look at it.
Did you use prepared statement in your code?
Can't find wiimp sources and I'm curious to see some part of the code.
You can send it to me at my nick at gmail.
Thanks!

Edit: the latest fat I tried is libfat 1.0.4 (used also a custom svn one)

What do you think about the corruption of the file? Could it be a FAT problem or a SQLite one?
I guess it is difficult for an application to corrupt the filesystem, so I'm thinking it is a libfat issue, but damn, I don't think no one find this bug on FAT).



Edited 1 time(s). Last edit at 08/04/2009 09:03PM by scognito.

Here it is a sample to try, it is a stripped down version of my project.
It simply create a db, insert a record, close the db. Pretty easy. But doesn't work.
It creates a corrupted file without giving an error.
Maybe drtwox can have a look in it, it is just an insert.
The curious thing is that if you remove the bind_text 6 and 7, it will work.

There is bug somewhere: fat / sqlite / my code.

I tend to exclude my code as I double checked it and didn't find anything...but I hope to be wrong.

[www.mediafire.com]

Really thanks for help.

I've looked at db.c in your code, and I can't see anything wrong at all; but I'm no sqlite expert :P
WiiMP hasn't suppored database creation in some time - it's too slow for what I need it to do, so I wrote a PC app to handle the creation. I dug out some old test code I wrote way back when I did the port, and it has the same issue as you are having too; i.e. corrupt database with more than a couple rows. I really think it's something to do with the unixWrite and seekAndWrite functions, and perhaps the SQLITE_DEFAULT_SECTOR_SIZE define. Hopefully I'll have some time on Sunday to look into it and see what I can find; but I'm really not much of a programmer!

Your help is really appreciated, thanks a lot!

I think the problem may be in the journal file; if I run a simple loop to insert 1024 strings into a table, no problem. If I 'wrap' the inserts into a BEGIN/COMMIT transaction the database will corrupt. Try disable transactions in your db.c for now and see what happens. Will investigate more later...

 83     /* Disabling transactions slows down db creation, but at least it works! */
 84     int i;
 85 //  sqlite3_exec( db, "BEGIN;", NULL, 0, &errorMsg ); //disabled
 86     for ( i = 0; i < 1024; ++i ) {
 87         char command[ 64 ];
 88         snprintf( command, 64, "INSERT INTO test (string) VALUES (\"string %d\")", i );
 89         result = sqlite3_exec( db, command, NULL, 0, &errorMsg );
 90         if ( result != SQLITE_OK ) {
 91             printf( "%s:%s\n", __FUNCTION__, errorMsg );
 92             sqlite3_free( errorMsg );
 93             quit();
 94         
 95     }
 96 //  sqlite3_exec( db, "COMMIT;", NULL, 0, &errorMsg ); //disabled
 97 }

It was one of the thing I tried, but didn't had luck :(
You could try it on my code and see that it doesn't work (at least for me).
I don't really know if this problem can be' solved, I wonder if disabling commit works to you.
Are you sure it is a sqlite problem and not a fat one?

Thanks for testing anyway.

What version of devkitPPC are you using? I had to remove your gettimeofday() functions as it clashes with the existing function in devkitPRO/devkitPPC/powerpc-gekko/include/sys/time.h... otherwise the app compiled and ran fine; no corrupt database on a FAT32 formatted card.

sqlite> .schema
CREATE TABLE game ( id TEXT, version TEXT, region TEXT, title TEXT, synopsis TEXT, title_EN TEXT, synopsis_EN TEXT, localeCnt NUMBER, locale_1 TEXT, locale_2 TEXT, locale_3 TEXT, developer TEXT, publisher TEXT, publisherfromid TEXT, year TEXT, month TEXT, day TEXT, genre TEXT, genresplit_1 TEXT, genresplit_2 TEXT, genresplit_3 TEXT, rating TEXT, ratingtype TEXT, ratingvalue TEXT, ratingdescriptors_1 TEXT, ratingdescriptors_2 TEXT, ratingdescriptors_3 TEXT, ratingvalueCERO TEXT, ratingvalueESRB TEXT, ratingvaluePEGI TEXT, wifiplayers TEXT, wififeatures_1 TEXT, wififeatures_2 TEXT, wififeatures_3 TEXT, wificnt TEXT, players TEXT, accessories_1 TEXT, accessories_2 TEXT, accessories_3 TEXT, accessories_4 TEXT, accessories_5 TEXT, accessoriesreq_1 TEXT, accessoriesreq_2 TEXT, accessoriesreq_3 TEXT, accessoriesreq_4 TEXT, accessoriesreq_5 TEXT, crc TEXT, md5 TEXT, sha1 TEXT);

sqlite> select * from game;
RUUP01||PAL|Animal Crossing: Let's Go to the City|If life were an endless vacation, what would you do? Go fishing, collect shells or watch fireworks with friends? Build a snowman, exchange presents with family or decorate your house for the holidays? Take a trip to the city, go on a shopping spree or visit friends from all over the globe? In Animal Crossing: Let's Go to the City, life moves at a relaxed pace, but the world brims with endless possibilities. There?s always something new to do. In the living, breathing world of Animal Crossing: Let's Go to the City days and seasons pass in real time, so there?s always something to discover. Catch fireflies in the summer, go trick-or-treating on Halloween or hunt for eggs on Bunny Day. If you?re in the mood for something a little faster paced, take a bus to a new urban city area that?s unique to Animal Crossing: Let's Go to the City. There you can catch a show at the theatre or check out the sales at Gracie?s boutique. But if you don?t show your face back home for too long, your neighbours will miss you.|Animal Crossing: Let's Go to the City|If life were an endless vacation, what would you do? Go fishing, collect shells or watch fireworks with friends? Build a snowman, exchange presents with family or decorate your house for the holidays? Take a trip to the city, go on a shopping spree or visit friends from all over the globe? In Animal Crossing: Let's Go to the City, life moves at a relaxed pace, but the world brims with endless possibilities. There?s always something new to do. In the living, breathing world of Animal Crossing: Let's Go to the City days and seasons pass in real time, so there?s always something to discover. Catch fireflies in the summer, go trick-or-treating on Halloween or hunt for eggs on Bunny Day. If you?re in the mood for something a little faster paced, take a bus to a new urban city area that?s unique to Animal Crossing: Let's Go to the City. There you can catch a show at the theatre or check out the sales at Gracie?s boutique. But if you don?t show your face back home for too long, your neighbours will miss you.|1||EN|FR|Nintendo EAD|Nintendo||2008|12|5|life simulation||Life simulation|||PEGI|3||||A|E|3|4||Download|Nintendods|3|1||wiispeak|nintendods||||wiimote|nunchuk|||ecdbce00|68039075bd476d0f0bd5d31d1e474f76|e406847a2350a9d188817dfae75d3a5e89485116

I'm using devkitppc r17 iirc and latest stable one libogc because I had this problem too [forums.devkitpro.org]:
What libraries are you using?

EDIT: since I had 5 minutes free I did some test:

added

#elif SQLITE_OS_LIBOGC
  time_t t;
  time(&t);
  *prNow = t/86400.0 + 2440587.5;

in sqlite3.c, removed the call of gettimeofday function from my code, re-added int _EXFUN(gettimeofday, (struct timeval *__p, void *__tz)); in devkitPro\devkitPPC\powerpc-gekko\include\sys\time.h

Using the generated library I didn't have luck still, even disabling the transaction code from template.c :(



Edited 1 time(s). Last edit at 08/08/2009 08:36PM by scognito.

I have devkitPPC r17 and svn libogc (updated yesterday) and your example code compiles fine (once gettimeofday is removed) and generates a non-corrupt database... I really don't know what the problem is. The last thing I can suggest is you instruct sqlite to create the journal file in memory, not on disk:

/* Include this just after the call to sqlite3_open_v2 in db.c
sqlite3_exec( db, "PRAGMA journal_mode=MEMORY;", NULL, 0, &errorMsg );

The problem is that gettimeofday is declared in devkitppc directory (also confirmed here: [forums.devkitpro.org]), but anyway I've updated libogc/libfat to svn and reinstalled devkitpro r17, but had the same error (I've commented the gettimeofday from my db.c and db.h):

C:\wiidev\sqlitexample2>make
linking ... sqlitexample2.elf
c:/devkitpro/devkitppc/bin/../lib/gcc/powerpc-gekko/4.2.4/../../../../powerpc-gekko/lib\libc.a(lib_a-sysgettod.o): In function `gettimeofday':
(.text+0x20): undefined reference to `_gettimeofday_r'
collect2: ld returned 1 exit status
make[1]: *** [/c/wiidev/sqlitexample2/sqlitexample2.elf] Error 1
make: *** [build] Error 2

Are you sure you are not using devkitppc r16?
Also I tried to add the pragma with no luck.
Maybe I missed something, can you also post the code and the binary somewhere?

Thanks for help as usual.



Edited 1 time(s). Last edit at 08/09/2009 01:28PM by scognito.

Drtwox, any news? :)

Yes, I was using r16; oops! I rolled back my wiidev directory (from svn) a while ago and didn't realise I'd gone back to r16 from r17.

Anyway, I've spent some time trying to debug/test the journal code in sqlite... but have made no progress at all in fixing the problem. My only suggestion remaining is you move the database creation code out of the app, and provide a 'pre made' database with each release, and allow users to update the 'pre made' database from a website.

I tried to separate the db creation code and the insert code in 2 separate apps.
The first app creates the db ok.
The second seems to insert ok but the db is corrupted, as usual.
The problem is that the application should insert records in the db for each game a user has, so I can't provide a db with all games released.
Still don't understand why with R16 it work.

I've downloaded devkitppc-r16, libogc 1.7, libfat20081205, and your sqlite wii lib, gettimeofday() removed from my app.
Got a code dump.
Since I want to run it as separate dol, now I want to compile and run like you did.
What else I'm missing? If it is ok can you send me back the sources since I modified them a lot?
Thanks!

The source + compiled app + working database is here