Re: [sqlite] sqlite on freertos/fatfs : SQLITE_NOTADB during create table
Thanks ! The problem was that lseek of fastfs behaves differently from the posix specs. If you do a fatfs lseek to a place beyond the actual file size and the file is opened for writing, he will automatically increase the file size which is not OK according to posix. (lseek is called by de read function to read at an offset) And the next time you do xFileSize() you will get a non zero result, resulting in the not a valid database file error. The strange thing is that even though the file size is increased in the SW structures maintained by fatfs, the larger file is never actually written to disk so i did not notice it when checking the file system. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dan Kennedy Sent: zaterdag 28 juli 2018 17:59 To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] sqlite on freertos/fatfs : SQLITE_NOTADB during create table On 07/28/2018 10:51 PM, Bram Peeters wrote: > Ah maybe that is also interesting to know: i put a breakpoint on the write > convenience wrapper in sqlite, it is never called. > So it is not that there is a problem with the write function . > It is that there is never an attempt to write anything to the file before it > is being read... > Which is why i started looking at who might be responsible for writing > this, but it is a bit hard to debug why something does not happen if > you are not familiar with the 211k lines of code :) > Suggest checking the xFileSize() implementation. Is it setting the output variable to 0 when the file is 0 bytes in size on disk? I think if the file on disk is zero bytes in size, this bit: sqlite3PagerPagecount(pBt->pPager, ); should set nPageFile to 0. Not 1. Dan. > > Regards > Bram > > > From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on > behalf of Bram Peeters [bram.peet...@dekimo.com] > Sent: Saturday, July 28, 2018 17:45 > To: SQLite mailing list > Subject: Re: [sqlite] sqlite on freertos/fatfs : SQLITE_NOTADB during > create table > >> An empty file and a file filled with zeros are two different things > The file is empty/has size 0 in the file system. > > So the filesystem interface indeed returns SQLITE_IOERR_SHORT_READ. > > But the page1 pointer (this is not in the file, but in the structures managed > by sqlite) points to a buffer in memory with all 0's (probably cos it is > malloced somewhere and it does not get filled with contents of the file). > > Regards, > Bram > > > > > > > From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on > behalf of Clemens Ladisch [clem...@ladisch.de] > Sent: Saturday, July 28, 2018 15:07 > To: sqlite-users@mailinglists.sqlite.org > Subject: Re: [sqlite] sqlite on freertos/fatfs : SQLITE_NOTADB during > create table > > Bram Peeters wrote: >> He reads a page from the file in sqlite3PagerSharedLock, but the file is >> still 0 so the page is all zeros. > > An empty file and a file filled with zeros are two different things. > > Does the file system return SQLITE_IOERR_SHORT_READ? > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite on freertos/fatfs : SQLITE_NOTADB during create table
On 07/28/2018 10:51 PM, Bram Peeters wrote: Ah maybe that is also interesting to know: i put a breakpoint on the write convenience wrapper in sqlite, it is never called. So it is not that there is a problem with the write function . It is that there is never an attempt to write anything to the file before it is being read... Which is why i started looking at who might be responsible for writing this, but it is a bit hard to debug why something does not happen if you are not familiar with the 211k lines of code :) Suggest checking the xFileSize() implementation. Is it setting the output variable to 0 when the file is 0 bytes in size on disk? I think if the file on disk is zero bytes in size, this bit: sqlite3PagerPagecount(pBt->pPager, ); should set nPageFile to 0. Not 1. Dan. Regards Bram From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Bram Peeters [bram.peet...@dekimo.com] Sent: Saturday, July 28, 2018 17:45 To: SQLite mailing list Subject: Re: [sqlite] sqlite on freertos/fatfs : SQLITE_NOTADB during create table An empty file and a file filled with zeros are two different things The file is empty/has size 0 in the file system. So the filesystem interface indeed returns SQLITE_IOERR_SHORT_READ. But the page1 pointer (this is not in the file, but in the structures managed by sqlite) points to a buffer in memory with all 0's (probably cos it is malloced somewhere and it does not get filled with contents of the file). Regards, Bram From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Clemens Ladisch [clem...@ladisch.de] Sent: Saturday, July 28, 2018 15:07 To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] sqlite on freertos/fatfs : SQLITE_NOTADB during create table Bram Peeters wrote: He reads a page from the file in sqlite3PagerSharedLock, but the file is still 0 so the page is all zeros. An empty file and a file filled with zeros are two different things. Does the file system return SQLITE_IOERR_SHORT_READ? Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite on freertos/fatfs : SQLITE_NOTADB during create table
Ah maybe that is also interesting to know: i put a breakpoint on the write convenience wrapper in sqlite, it is never called. So it is not that there is a problem with the write function . It is that there is never an attempt to write anything to the file before it is being read... Which is why i started looking at who might be responsible for writing this, but it is a bit hard to debug why something does not happen if you are not familiar with the 211k lines of code :) Regards Bram From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Bram Peeters [bram.peet...@dekimo.com] Sent: Saturday, July 28, 2018 17:45 To: SQLite mailing list Subject: Re: [sqlite] sqlite on freertos/fatfs : SQLITE_NOTADB during create table >An empty file and a file filled with zeros are two different things The file is empty/has size 0 in the file system. So the filesystem interface indeed returns SQLITE_IOERR_SHORT_READ. But the page1 pointer (this is not in the file, but in the structures managed by sqlite) points to a buffer in memory with all 0's (probably cos it is malloced somewhere and it does not get filled with contents of the file). Regards, Bram From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Clemens Ladisch [clem...@ladisch.de] Sent: Saturday, July 28, 2018 15:07 To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] sqlite on freertos/fatfs : SQLITE_NOTADB during create table Bram Peeters wrote: > He reads a page from the file in sqlite3PagerSharedLock, but the file is > still 0 so the page is all zeros. An empty file and a file filled with zeros are two different things. Does the file system return SQLITE_IOERR_SHORT_READ? Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite on freertos/fatfs : SQLITE_NOTADB during create table
>An empty file and a file filled with zeros are two different things The file is empty/has size 0 in the file system. So the filesystem interface indeed returns SQLITE_IOERR_SHORT_READ. But the page1 pointer (this is not in the file, but in the structures managed by sqlite) points to a buffer in memory with all 0's (probably cos it is malloced somewhere and it does not get filled with contents of the file). Regards, Bram From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Clemens Ladisch [clem...@ladisch.de] Sent: Saturday, July 28, 2018 15:07 To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] sqlite on freertos/fatfs : SQLITE_NOTADB during create table Bram Peeters wrote: > He reads a page from the file in sqlite3PagerSharedLock, but the file is > still 0 so the page is all zeros. An empty file and a file filled with zeros are two different things. Does the file system return SQLITE_IOERR_SHORT_READ? Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite on freertos/fatfs : SQLITE_NOTADB during create table
Bram Peeters wrote: > He reads a page from the file in sqlite3PagerSharedLock, but the file is > still 0 so the page is all zeros. An empty file and a file filled with zeros are two different things. Does the file system return SQLITE_IOERR_SHORT_READ? Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite on freertos/fatfs : SQLITE_NOTADB during create table
Hi, I am trying to get sqlite working on a freertos/fatfs based STM32 embedded system. I started from the 3.24.0 amalgamation an did the steps in https://www.sqlite.org/custombuild.html. The VFS is based on the https://www.sqlite.org/src/doc/trunk/src/test_demovfs.c code (though I did implement the truncate method). Compilation flags are: SQLITE_MUTEX_APPDEF=1 SQLITE_OS_OTHER=1 SQLITE_OMIT_WAL=1 SQLITE_TEMP_STORE=3 SQLITE_ENABLE_8_3_NAMES=2 I am running into the problem that when I try to create a table using sql ="CREATE TABLE Cars(Id INT, Name TEXT, Price INT);" ;/* Create SQL statement */ rc = sqlite3_exec(db, sql, NULL, NULL, );/* Execute SQL statement */ (full sample code below) I get a SQLITE_NOTADB. Searching the mailing list archive there was a similar post a few years ago (https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg96723.html), but in that case it was a problem with the journal file it seems (https://stackoverflow.com/questions/35738578/sqlite-porting-on-freertos-with-stm32). In my case demoOpen is called only once for the main database and that one is in read/write mode so I am encountering a different problem it seems. Looking with a debugger the stack trace when it goes wrong is: sqlite3_exec sqlite3_step sqlite3Step sqlite3VdbeExec sqlite3BtreeBeginTrans lockBtree He reads a page from the file in sqlite3PagerSharedLock, but the file is still 0 so the page is all zeros. pPage1->aData points to a buffer with all 0's sqlite3PagerPagecount(pBt->pPager, ); //sets nPageFile to 1 if( nPage==0 || memcmp(24+(u8*)pPage1->aData, 92+(u8*)pPage1->aData,4)!=0 ){ nPage = nPageFile; } // changes nPage from 0 to 1 if( (pBt->db->flags & SQLITE_ResetDatabase)!=0 ){ nPage = 0; } // Since pBt->db->flags = 0x00048060 and SQLITE_ResetDatabase = 0x0200 , this keeps nPage as 1 So it triggers the SQLITE_NOTADB case if( nPage>0 ) { u32 pageSize; u32 usableSize; u8 *page1 = pPage1->aData; rc = SQLITE_NOTADB; /* EVIDENCE-OF: R-43737-3 Every valid SQLite database file begins ** with the following 16 bytes (in hex): 53 51 4c 69 74 65 20 66 6f 72 6d ** 61 74 20 33 00. */ if( memcmp(page1, zMagicHeader, 16)!=0 ) { goto page1_init_failed; } So I have been trying to figure out how the magic header should end up in the database file. I does not seem to happen when the database opened in the sqlite3_open_v2 call. That just opens/creates the file in RW mode, but does not yet write any data to it (behaviour on a windows PC seems to same so I guess this is OK) Writing the magic header seems to be done in a function static int newDatabase(BtShared *pBt) And I am assuming this function should be called from sqlite3BtreeBeginTrans in a part of the code that looks like: pBt->btsFlags &= ~BTS_INITIALLY_EMPTY; if( pBt->nPage==0 ) pBt->btsFlags |= BTS_INITIALLY_EMPTY; do { /* Call lockBtree() until either pBt->pPage1 is populated or ** lockBtree() returns something other than SQLITE_OK. lockBtree() ** may return SQLITE_OK but leave pBt->pPage1 set to 0 if after ** reading page 1 it discovers that the page-size of the database ** file is not pBt->pageSize. In this case lockBtree() will update ** pBt->pageSize to the page-size of the file on disk. */ while( pBt->pPage1==0 && SQLITE_OK==(rc = lockBtree(pBt)) ); if( rc==SQLITE_OK && wrflag ){ if( (pBt->btsFlags & BTS_READ_ONLY)!=0 ){ rc = SQLITE_READONLY; }else{ rc = sqlite3PagerBegin(pBt->pPager,wrflag>1,sqlite3TempInMemory(p->db)); if( rc==SQLITE_OK ){ rc = newDatabase(pBt); // <---HERE } } } if( rc!=SQLITE_OK ){ unlockBtreeIfUnused(pBt); } }while( (rc&0xFF)==SQLITE_BUSY && pBt->inTransaction==TRANS_NONE && btreeInvokeBusyHandler(pBt) ); but if I analyse code execution this statement is never executed in the cases I end up in sqlite3BtreeBeginTrans [1] First time I am there is with stack trace: sqlite3_exec sqlite3_prepare_v2 sqlite3LockAndPrepare sqlite3Prepare sqlite3RunParser sqlite3Parser yy_reduce sqlite3StartTable sqlite3ReadSchema sqlite3Init sqlite3InitOne sqlite3BtreeBeginTrans But in sqlite3InitOne, it is called with sqlite3BtreeBeginTrans(pDb->pBt, 0); so wrflag is 0, so the newDatabase part is not executed. [2] I get there a second time with stack trace: sqlite3_exec sqlite3_prepare_v2 sqlite3LockAndPrepare sqlite3Prepare sqlite3RunParser sqlite3Parser yy_reduce sqlite3StartTable sqlite3ReadSchema sqlite3Init sqlite3InitOne sqlite3_exec sqlite3_step sqlite3Step sqlite3VdbeExec sqlite3BtreeBeginTrans(pBt, pOp->p2); //pOp->p2 = 0 again (pOp->opcode = 0x02) So since pOp->p2 is 0, again wrflag is 0, so the newDatabase part is not executed. And since if( p->inTrans==TRANS_WRITE ||