sqlite on freertos/fatfs : SQLITE_NOTADB during create table

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite on freertos/fatfs : SQLITE_NOTADB during create table

Bram Peeters
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, &zErrMsg);/* 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@.../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, &nPageFile); //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   = 0x02000000 , 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-39999 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 || (p->inTrans==TRANS_READ && !wrflag) ){
    goto trans_begun;
  }
is true (p->inTrans = 0x1 (= TRANS_READ))  we make the jump over it already in the beginning.

[3] Then the 3th time is the one where I get the error.
sqlite3_exec
sqlite3_step
sqlite3Step
sqlite3VdbeExec
sqlite3BtreeBeginTrans

The call is sqlite3BtreeBeginTrans(pBt, pOp->p2); with pOp->p2 = 1, pOp->opcode = 0x02
But now we get the error already in the lockBtree part of
while( pBt->pPage1==0 && SQLITE_OK==(rc = lockBtree(pBt)) );
So again newDatabase will not be called because that happens only afterwards.


The code I am using to test is the same as in the original thread I already refered to:
{
        sqlite3 *db = NULL;
        char *zErrMsg = 0;
        int rc;
        char *sql;

        l_nIntermediateResult = e_RETURNVALUE_Success;

        if ( e_RETURNVALUE_Success == l_nIntermediateResult)
        {
            //mmm this is also done by sqlite3_os_init but it does not harm to do it twice i think
            rc = sqlite3_vfs_register(sqlite3_demovfs(), 1); /* Register a VFS */
            if(rc != SQLITE_OK)
            {
                LOGGER_LOG_FATAL(LOG_CLASS, "Could not register sqlite vfs: %d", rc);
                l_nIntermediateResult = e_RETURNVALUE_Failure;
            }
        }

        if ( e_RETURNVALUE_Success == l_nIntermediateResult)
        {
            rc = sqlite3_open_v2("testsql.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE , "demo" );/* Create a SQL table */
            if( rc )
            {
                LOGGER_LOG_FATAL(LOG_CLASS, "Could not create table: %d", rc);
                l_nIntermediateResult = e_RETURNVALUE_Failure;
            }
        }
#if 0 // I disabled disabling the journal but it makes no difference
        if ( e_RETURNVALUE_Success == l_nIntermediateResult)
        {
            sql = "PRAGMA journal_mode=OFF";/* Create SQL statement */
            rc = sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);/* Execute SQL statement */
            if( rc != SQLITE_OK )
            {
                sqlite3_free(zErrMsg);
                LOGGER_LOG_FATAL(LOG_CLASS, "Could not execute journal mode: %d", rc);
                l_nIntermediateResult = e_RETURNVALUE_Failure;

            }
        }
#endif

        if ( e_RETURNVALUE_Success == l_nIntermediateResult)
        {
            sql ="CREATE TABLE Cars(Id INT, Name TEXT, Price INT);" ;/* Create SQL statement */
            rc = sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);/* Execute SQL statement */
            if( rc != SQLITE_OK )
            {
                sqlite3_free(zErrMsg);

                LOGGER_LOG_FATAL(LOG_CLASS, "Could not execute create table: %d", rc);
                l_nIntermediateResult = e_RETURNVALUE_Failure;
            }
        }

        if ( e_RETURNVALUE_Success == l_nIntermediateResult)
        {
            sql = "INSERT INTO Cars VALUES(1, 'Audi', 52642);"/* Create SQL statement */
                    "INSERT INTO Cars VALUES(2, 'Skoda', 9000);";
            rc = sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);/* Execute SQL statement */
            if( rc != SQLITE_OK )
            {
                sqlite3_free(zErrMsg);

                LOGGER_LOG_FATAL(LOG_CLASS, "Could not execute insert: %d", rc);
                l_nIntermediateResult = e_RETURNVALUE_Failure;
            }
        }


        if ( e_RETURNVALUE_Success == l_nIntermediateResult )
        {
            sql = "SELECT * from Cars";/* Create SQL statement */
            const char* data = "Callback function called";
            rc = sqlite3_exec(db, sql, lclSqlCallback, (void *)data, &zErrMsg);/* Execute SQL statement */
            if( rc != SQLITE_OK )
            {
                sqlite3_free(zErrMsg);

                LOGGER_LOG_FATAL(LOG_CLASS, "Could not execute select: %d", rc);
                l_nIntermediateResult = e_RETURNVALUE_Failure;
            }
        }

        if ( e_RETURNVALUE_Success == l_nIntermediateResult )
        {
            sqlite3_close(db);
        }

    }



Any suggestions about what could be going wrong (or when this magic header should be written )

Thank you
Bram Peeters


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite on freertos/fatfs : SQLITE_NOTADB during create table

Clemens Ladisch
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite on freertos/fatfs : SQLITE_NOTADB during create table

Bram Peeters
>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 [[hidden email]] on behalf of Clemens Ladisch [[hidden email]]
Sent: Saturday, July 28, 2018 15:07
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite on freertos/fatfs : SQLITE_NOTADB during create table

Bram Peeters
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 [[hidden email]] on behalf of Bram Peeters [[hidden email]]
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 [[hidden email]] on behalf of Clemens Ladisch [[hidden email]]
Sent: Saturday, July 28, 2018 15:07
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite on freertos/fatfs : SQLITE_NOTADB during create table

Dan Kennedy-4
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, &nPageFile);

should set nPageFile to 0. Not 1.

Dan.




>
> Regards
> Bram
>
> ________________________________________
> From: sqlite-users [[hidden email]] on behalf of Bram Peeters [[hidden email]]
> 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 [[hidden email]] on behalf of Clemens Ladisch [[hidden email]]
> Sent: Saturday, July 28, 2018 15:07
> To: [hidden email]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite on freertos/fatfs : SQLITE_NOTADB during create table

Bram Peeters
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:[hidden email]] On Behalf Of Dan Kennedy
Sent: zaterdag 28 juli 2018 17:59
To: [hidden email]
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, &nPageFile);

should set nPageFile to 0. Not 1.

Dan.




>
> Regards
> Bram
>
> ________________________________________
> From: sqlite-users [[hidden email]] on
> behalf of Bram Peeters [[hidden email]]
> 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 [[hidden email]] on
> behalf of Clemens Ladisch [[hidden email]]
> Sent: Saturday, July 28, 2018 15:07
> To: [hidden email]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users