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
Re: [sqlite] Backup and integrity check questions
> Rowan Worth Thu, 26 Jul 2018 22:02:50 -0700 > > On 26 July 2018 at 05:56, Rune Torgersen wrote: > > > The databases have been opened with two connections (one for reads, one > > for writes), and use the following options: > > sqlite3_busy_timeout(mDbConn, 500); > > sqlite3_exec(mDbConn, "PRAGMA locking_mode = EXCLUSIVE;", 0, 0, 0); > > > > Surely this effectively reduces your number of connections to one? No, both connections are from within the same application, and have seemed to work just fine for about 10 years now... > > PRAGMA quick_check is faster, but not as exhaustive as integrity_check. > > There's more efficient ways to copy a DB than the backup api, but they > involve either downtime as Simon said, or some risk in correctly managing > locks. The application has uptime sometimes measured in years, so closing it down to backups are not feasible. > It's not clear whether you have another process calling sqlite3_backup_* or > that is done by the application itself. The latter can be more efficient as > sqlite will not have to restart the backup if the application updates the > DB (any writes to a DB are automatically propagated to in-progress backups > within the same process). backup is done from a separate thread within same process. Same with the integrity check Rune Torgersen Innovative Systems LLC ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create VIEW passing despite invalid column name specified
On Thursday, 26 Jul 2018 7:58 PM -0400, Richard Hipp wrote: > On 7/26/18, Tomasz Kot wrote: >> Hello, >> >> Beneath sql shall throw an error on CREATE VIEW statement (as invalid >> column is specified), but it passes (SQLite 3.23.1). > > The error is deferred until you try to use the view. The reason for > deferring the error is that the problem might be corrected by > subsequent DDL statements prior to using the view. And I will add that I've found this behaviour quite useful when upgrading to a new database schema. -- Will ___ 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 ||
