Here is the reproduction path.
```
sqlite3 db
PRAGMA journal_mode=WAL;
CREATE TABLE t(i INTEGER);
INSERT INTO t VALUES(1);
PRAGMA wal_checkpoint=TRUNCATE;
// check the WAL file size without checkpoint/close sqlite connection
ls -l | grep wal
// print 0 db-wal
BEGIN IMMEDIATE;
SAVEPOINT s1;
IN
I find a bug that exists in `UNION ALL`.
tldr: `UNION ALL` will not merge the data in different schemas in the moment
that one of schema is committed but the another not. BUT, `UNION` will.
Here are the reproduce steps:
Preparation:
1. Prepare a database named "OLD"
1.1 Create a table for "O
I find a bug that exists in `UNION ALL`.
tldr: `UNION ALL` will not merge the data in different schemas in the moment
that one of schema is committed but the another not. BUT, `UNION` will.
Here are the reproduce steps:
Preparation:
1. Prepare a database named "OLD"
1.1 Create a table for "O
I find that a UPDATE statement with WITH CLAUSE always fails, although I use
the syntax as SQLite syntax suggested.
Also, the `lang_with.html` do no show a UPDATE statement with WITH CLAUSE. They
all run in a SELECT statement.
Here is the sample SQL I tried:
```
CREATE TABLE t(i INTEGER);
INSER
I find that a UPDATE statement with WITH CLAUSE always fails, although I use
the syntax as SQLite syntax suggested.
Also, the `lang_with.html` do no show a UPDATE statement with WITH CLAUSE. They
all run in a SELECT statement.
Here is the sample SQL I tried:
```
CREATE TABLE t(i INTEGER);
INSER
t:sqlite-userssqlite-us...@mailinglists.sqlite.org
Date:Monday, Aug 21, 2017 17:25
Subject:Re: [sqlite] PRAGMA table_info could not update schema
sanhua.zh wrote: I find that `PRAGMA table_info(tableName)` will not check the
expired schema which is modified by other sqlite connections. Here is the
sam
I find that `PRAGMA table_info(tableName)` will not check the expired schema
which is modified by other sqlite connections.
1. Open conn 1 and conn 2.
2. Run a SQL to load the schema for conn 2
3. Change the schema using conn 1 by create-table-statement.
4. Get the schema using conn 2 by table_i
Subject:Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !
On 21 Aug 2017, at 3:22am, sanhua.zh sanhua...@foxmail.com wrote: If so, who
or which doc can tell me that which SQL will or will not update the schema ?
Commands with CREATE, DROP, or ALTER update schema.
L, NULL, NULL);
assert(rc==0);
```
Original Message
Sender:Jay kreibich...@kreibi.ch
Recipient:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
Date:Friday, Aug 18, 2017 19:46
Subject:Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !
On Aug 18, 2017, at 4:04 AM, sanhua.
hema ?
Original Message
Sender:Jay kreibich...@kreibi.ch
Recipient:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
Date:Friday, Aug 18, 2017 19:46
Subject:Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !
On Aug 18, 2017, at 4:04 AM, sanhua.zh sanhua...@foxmail.com wrote
I am using SQLite in multi-thread mode, which means that different threads
using different SQLite connection.
And now I find an issue that the results of SQLite C interface returned is
expired while the schema of database is changed.
The following sample runs in different threads, but I force t
Here is a sample.
As for a specific sqlite connection,
1. sql is executed in thread A
2. sql is prepared in thread B
3. Prepared statement in 2. is stepped and finalized in thread C
All 1. 2. 3. steps are run sequentially, which means that the step 2 runs after
step 1 finished and step 3 runs af
When I call `CREATE TABLE test(i INTEGER PRIMARY KEY DESC AUTO INCREMENT)`,
SQLite comes to an error "AUTOINCREMENT is only allowed on an INTEGER PRIMARY
KEY".
But as the document http://www.sqlite.org/lang_createtable.htmlsays, in the
column-constraint, it shows that `PRIMARY DESC AUTOINCREME
Following the code in mem1.c, Why SQLite Use Different Malloc Zone While CPU
Count 1?
static int sqlite3MemInit(void *NotUsed){
#if defined(__APPLE__) !defined(SQLITE_WITHOUT_ZONEMALLOC)
int cpuCount;
size_t len;
if( _sqliteZone_ ){
return SQLITE_OK;
}
len = sizeof(cpuCount);
/* One us
?
On 11/3/16, sanhua.zh sanhua...@foxmail.com wrote: Here is a part of code in
[unixWrite]. (os_unix.h) Is not it written by mmap ? You are looking at older
code that is not longer used. Look at latest sources and you will see that the
code you quote below is enclosed within #if defined
So, it means that this is a bug in old version of SQLite ?
原始邮件
发件人:Dan kennedydanielk1...@gmail.com
收件人:sqlite-userssqlite-us...@mailinglists.sqlite.org
发送时间:2016年11月3日(周四) 18:38
主题:Re: [sqlite] Is msync necessary for SQLite?
On 11/03/2016 04:53 PM, sanhua.zh wrote: Here is a part of code in
SQLite?
On 11/3/16, sanhua.zh sanhua...@foxmail.com wrote: I run in iOS. The mmap is
enable in recent version. So, once a mmap enable, data may lost after os crash
unless msync. But I can’t find anywhere in the source of SQLite call the
msync. Is it a bug? No. Mmap() is only used for reading, not
时间:2016年11月3日(周四) 17:38
主题:Re: [sqlite] Is msync necessary for SQLite?
On 11/03/2016 02:13 PM, sanhua.zh wrote: I notice that SQLite may not call
msync for mmap. Instead, it calls the fsync. As my search, fsync does not
imply the msync. See
this,https://groups.google.com/forum/#!topic
I notice that SQLite may not call msync for mmap. Instead, it calls the fsync.
As my search, fsync does not imply the msync. See
this,https://groups.google.com/forum/#!topic/comp.unix.programmer/pIiaQ6CUKjU
So, is it necessary call a msync for SQLite?
g Table Results in 'no such table' On 21 Oct 2016, at
4:42am, sanhua.zh sanhua...@foxmail.com wrote: I am trying to rename a table by
editing the sqlite_master directly. I do know there are a ‘ALTER TABLE … RENAME
TO …’ syntax to do that. I just want to try it in this way. But I find
qlite] Drop Existing Table Results in 'no such table'
sanhua.zh wrote: I am trying to rename a table by editing the sqlite_master
directly. Don't do this. But I find that the ‘no such table’ error occurs
while I try to drop the renamed table. Only I close the connection or star
:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
发送时间:2016年10月21日(周五) 14:39
主题:Re: [sqlite] Drop Existing Table Results in 'no such table'
On 21 Oct 2016, at 4:42am, sanhua.zh sanhua...@foxmail.com wrote: I am trying
to rename a table by editing the sqlite_master directly. I do
I am trying to rename a table by editing the sqlite_master directly. I do know
there are a ‘ALTER TABLE … RENAME TO …’ syntax to do that. I just want to try
it in this way.
But I find that the ‘no such table’ error occurs while I try to drop the
renamed table.
Only I close the connection or star
hould be no problem
原始邮件
发件人:Simon slavinslav...@bigfraud.org
收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
发送时间:2016年10月10日(周一) 17:03
主题:Re: [sqlite] CREATE TABLE/ALTER TABLE Slow Down When Table CountGrow
On 10 Oct 2016, at 4:52am, sanhua.zh sanhua...@foxmail.com wrot
? -Original
Message- From: sqlite-users
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of sanhua.zh
Sent: Sunday, 9 October, 2016 21:53 To: sqlite-users Subject: [sqlite] CREATE
TABLE/ALTER TABLE Slow Down When Table Count Grow I foundCREATE TABLE/ALTER
TABLE Slow
I foundCREATE TABLE/ALTER TABLE Slow Down When Table Count Grow.
Since those SQLs modify the schema, SQLite use theOP_ParseSchema to update
them, which cause the search of sqlite_master. (SELECT name, rootpage, sql FROM
'%q'.%s WHERE %s ORDER BY rowid)
As we all know, sqlite_master has no index e
Now I found the possible reason why [Rename Table] become slow.
While the one table renamed, the schema need to be updated. But SQLite use the
['SELECT name, rootpage, sql FROM 'main'.sqlite_master WHERE
tbl_name=’tablename' ORDER BY rowid”] to update the schema.
Isn’t it too ugly ? Why we shou
I found that [Rename Table](using ‘ALTER TABLE RENAME TO') is much slower than
[Drop Table]. The cost of [Rename Table] may be twice, even if the table is
empty(which means it has no index, no trigger, no view and no column).
As I known, both [Drop Table] and [Rename Table] just modify the
`sq
QLite?
On 31 Aug 2016, at 9:39am, sanhua.zh sanhua...@foxmail.com wrote: Yes. even on
iDevice, [mmap] can get faster than sequential I/O. Sometime it will be twice
faster. Unless your users have complained about speed, this does not matter.
There is no need to make everything happen as fas
OK, I get your idea.
Thanks for your advice. :)
原始邮件
发件人:Stephan bealsgb...@googlemail.com
收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
发送时间:2016年8月31日(周三) 17:09
主题:Re: [sqlite] Why MMAP return ENOMEM in SQLite?
On Wed, Aug 31, 2016 at 11:03 AM, sanhua.zh sanhua...@foxmail.com
, 2016 at 10:55 AM, sanhua.zh sanhua...@foxmail.com wrote: Yes,
[mmap] can be larger than physical memory. Indeed, my mistake. And what do
you think about the new mapping way I mentioned ? i think it's a "huge can of
worms" - it's asking for more, bigger problems than the p
Wed, Aug 31, 2016 at 10:43 AM, Stephan Beal sgb...@googlemail.com wrote: On
Wed, Aug 31, 2016 at 10:39 AM, sanhua.zh sanhua...@foxmail.com wrote: In my
testcase, I can only [mmap] a db file at most 1.4GB size. But in this new way,
I can map a file at most 3.2GB. The test device is iPhone 6S
map a file at most
3.2GB. The test device is iPhone 6S. 原始邮件 发件人:Simon slavinslav...@bigfraud.org
收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org 发送时间:2016年8月31日(周三)
14:38 主题:Re: [sqlite] Why MMAP return ENOMEM in SQLite? On 31 Aug 2016, at
4:40am, sanhua.zh sanhua...@foxmail.com wr
iling listsqlite-us...@mailinglists.sqlite.org
发送时间:2016年8月31日(周三) 14:38
主题:Re: [sqlite] Why MMAP return ENOMEM in SQLite?
On 31 Aug 2016, at 4:40am, sanhua.zh sanhua...@foxmail.com wrote: Is it caused
by mmap file too big that the address space is not enough? Yes. Under iOS,
memory maps can only
I try to mmap a BIG file which is around 1.8GB size, on iOS.
Then I found that [sqlite3OSFetch] doesn’t work at all, which means the mmap is
disable.
After debug, I find that [osMmap] in [unixRemapFile] return an error. The error
code is 12, named ENOMEM, which means cannot allocate memory.
My
ceat the end of
each page" ?
sanhua.zh wrote: It describles the"Bytes of unused‘reserved' space at the end
of each page”. I think it’s the exact thing what I need. But I have no idea
how to use it. It seems that there is no interface to do this.
sqlite3_test_con
Now I want to add my customize data for each page.
And I find thatIt describles the"Bytes of unused‘reserved' space at the end of
each page”. I think it’s the exact thing what I need.
But I have no idea how to use it. It seems that there is no interface to do
this.
Can any one tell me about the d
Hello,
I found this pagewhich is about SQLite 4. But there are no download page in it
and the most recently update is 2015.08.15. Is SQLite 4 in a developing status?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.s
In the source code of [fcntlSizeHint] in SQLite 3.9.2,
Its comment said that "If the database is already nBytes or larger, this
routine is a no-op.”.
But if SQLITE_MAX_MMAP_SIZE0, there is no judgement about file size.
#if SQLITE_MAX_MMAP_SIZE0
if( pFile-mmapSizeMax0 nBytepFile-mmapSize ){
i
I find that sqlite.org only explains the reference and a
fewobsoletetutorialabout vdbe.
Without a“Getting Started” tutorial, I feel a little hard to trace the source
code in this part, although I have already read the btree part.
Is there any suggestion for theinitiate like me to start up the v
What’s the usage ofSQLITE_ENABLE_ATOMIC_WRITE macro?
I can’t find any document or comment about it.
How can I make sure whether I should open it? Will it reduce the corruption of
SQLite or increase the performance?
___
sqlite-users mailing list
sqlite-
I insert a value 18446744072708321492 as a integer into the table.
In C code,sqlite3_column_type tells me is a type of 2, which is float point
value.
And I usesqlite3_column_double to get it. It returns18446744072708321280, which
is already lost theaccuracy.
Is SQLite not support Uint64 value,
?4?7?(??)?21:35
??:Re: [sqlite] Why SQLite lock 510 bytes for shared lock?
On 4/7/16, sanhua.zh sanhua.zh at foxmail.com wrote: I found the code and the
comment at [unixLock] of os_unix.h is different. The comment say that a shared
lock will lock a random byte from ?shared byte range?(which is
I found the code and the comment at [unixLock] of os_unix.h is different.
The comment say that a shared lock will lock a random byte from ?shared byte
range?(which is 510 bytes length). But the code show that it locks the whole
?shared byte range?.
It makes me confused. Which one does the right t
6:03 AM, sanhua.zh wrote: Recently, I am reading the source code
of SQLite. I found that on OS level, SQLite use file lock to solve
multi-processes problem and use VFS to solve multi-threads problem. But all of
them might failed with racing and SQLite will return a SQLITE_BUSY result code
to
busy-retry but not lock-and-wait?
sanhua.zh wrote: in the unixLock, we can use F_SETLKW instead of F_SETLK, so
that code will return immediatly when file unlocked. We have not need to
sleep- and-retry, which may waste our running time. But then SQLite would have
no control over the waiting
Slavinslavins at bigfraud.org
???:SQLite mailing listsqlite-users at mailinglists.sqlite.org
:2016?4?6?(??)?13:31
??:Re: [sqlite] Why SQLite use busy-retry but not lock-and-wait?
On 6 Apr 2016, at 5:03am, sanhua.zh sanhua.zh at foxmail.com wrote: I found
that on OS level, SQLite use file lock to solve
Recently, I am reading the source code of SQLite.
I found that on OS level, SQLite use file lock to solve multi-processes problem
and use VFS to solve multi-threads problem. But all of them might failed with
racing and SQLite will return a SQLITE_BUSY result code to make it
sleep-and-retry.
I
NULL Data
On 4 Mar 2016, at 8:22am, sanhua.zh sanhua.zh at foxmail.com wrote: 3. I guess
it could be a problem of operating system. I work on iOS, but I have no any
further idea. Almost all of these problems are caused by your program doing one
of these A) Writing its own data into a pointer
I am debugging db corruption. After I get some corrupted db, I found that they
all corrupted by writing null data.
So, I decide to add some check and dump call stackin the source code in order
to find out who corrupts the db.
Here is the code I added in the source code.
int sqlite3CheckNullDa
mailinglists.sqlite.org
:2016?2?25?(??)?17:36
??:Re: [sqlite] Why skip invoking busy handlerwhilepBt-inTransaction!=TRANS_NONE
On 02/25/2016 09:53 AM, sanhua.zh wrote: So can this be understood as, if I
run my code in WAL, I can invoke busy handler even it in TRAN_READ? No. The
busy-handler is not
-inTransaction!=TRANS_NONE
On 02/24/2016 08:32 PM, sanhua.zh wrote: In the source code of SQLite,
btree.c, sqlite3BtreeBeginTrans function, The codedo { /* Call
lockBtree() until either pBt-pPage1 is populated or ** lockBtree() returns
something other than SQLITE_OK. lockBtree() ** may return
In the source code of SQLite, btree.c, sqlite3BtreeBeginTrans function,
The code
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 p
mailinglists.sqlite.org
:2016?2?15?(??)?20:03
??:Re: [sqlite] empty page corrupt
On 15 Feb 2016, at 11:59am, sanhua.zh sanhua.zh at foxmail.com wrote: I did it
on iOS. I already checked the howtocorrupt.html, but I can?t find anything
helpful. This is almost always a result of hardware failure
I did it on iOS.
I already checked the howtocorrupt.html, but I can?t find anything helpful.
???:Clemens Ladischclemens at ladisch.de
???:sqlite-userssqlite-users at mailinglists.sqlite.org
:2016?2?15?(??)?18:42
??:Re: [sqlite] empty page corrupt
sanhua.zh wrote: I find some db
I find some db corrupted and try to find out the reason.
After a simple analysis, I found that many of the db pages are empty, which
means all bits in this page is zero. This is the reason causing sqlite report
db corrupt.
I don?t know why this happen, does someone have the same problem ever?
H
I don?t mean to be rude. BUT, I can?t agree with your opinion, Simon.
1. Not all other codes except SQLITE_OK, SQLITE_ROW, SQLITE_DONE should be
treated as fatal errors.
As an example, SQLITE_BUSY indicates that this op is temporarily failed, but
it can be done later. (Note that sometimes you
] Database Corrupt While Disk Full
On 14 Jan 2016, at 9:58am, sanhua.zh sanhua.zh at foxmail.com wrote: Through
the error code timeline, it shows that much of SQLITE_FULL, SQLITE_IOERR,
SQLITE_CANTOPEN happened before SQLITE_CORRUPT. You should never get to this
situation. The three result codes
On 14 Jan 2016, at 8:44am, sanhua.zh sanhua.zh at foxmail.com wrote: Recently,
my monitoring system showed that the error code SQLITE_FULL and SQLITE_CORRUPT
increasing in same trend. And thousands of users, who?s database is corrupt,
also traped inlow disk free space and their log show that
Recently, my monitoring system showed that the error code SQLITE_FULL and
SQLITE_CORRUPT increasing in same trend. And thousands of users, who?s database
is corrupt, also traped inlow disk free space and their log show that
SQLITE_IOERR, SQLITE_FULL happenedsimultaneously.
I confuse that wheth
iOS/Mac
17 dec 2015, sanhua.zh: I try to use shared cache to optimize my code.
Sincesqlite3_enable_shared_cache is deprecated on iOS/Mac, I
usesqlite3_open_v2 withSQLITE_OPEN_SHAREDCACHE flag to open shared cache mode.
4 threads select is running in my code, while each thread has its own
I try to use shared cache to optimize my code. Sincesqlite3_enable_shared_cache
is deprecated on iOS/Mac, I usesqlite3_open_v2 withSQLITE_OPEN_SHAREDCACHE flag
to open shared cache mode.
4 threads select is running in my code, while each thread has its own sqlite
connection and do the same thing
Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von
sanhua.zh Gesendet: Dienstag, 15. Dezember 2015 10:27 An: SQLite mailing list
Betreff: [sqlite] ??? Why SQLITE_BUSY? I?m very excited that I re-produce the
S
I?m very excited that I re-produce the SQLITE_BUSY code in a simple demo.
Here is my test code,
void showResultCode(int resultCode)
{
if (resultCode!=SQLITE_DONEresultCode!=SQLITE_OKresultCode!=SQLITE_ROW) {
NSLog(@"unexperted result %d", resultCode);
}
}
void SQLiteLog(void* userInfo,
I queue all my db operation into one thread with single sqlite conn. neither
multi-thread nor multi-process operation happened.But some SQLITE_BUSY error
code still be catched.
I can not re-produce this error code indeveloping environment,because it happen
in alow probability. I only catch this
/2/15, sanhua.zh sanhua.zh at foxmail.com wrote: I make a database,
create a table and insert some data. And then delete a section of data
manually using binary editor, Did you really "delete" the data, or did you
simply change the data to have all NULL content? -- D. Richard H
listsqlite-users at mailinglists.sqlite.org
:2015?12?2?(??)?19:42
??:Re: [sqlite] Why Corrupted DB File Cause No Error?
On 2 Dec 2015, at 11:38am, Stephan Beal sgbeal at googlemail.com wrote: On
Wed, Dec 2, 2015 at 11:59 AM, sanhua.zh sanhua.zh at foxmail.com wrote: Why
it does not show error
:2015?12?2?(??)?19:38
??:Re: [sqlite] Why Corrupted DB File Cause No Error?
On Wed, Dec 2, 2015 at 11:59 AM, sanhua.zh sanhua.zh at foxmail.com wrote: Why
it does not show error, some thing like?Error: database disk image is
malformed?? Is it a bug in SQLite? because you didn't corrupt a
I make a database, create a table and insert some data.
And then delete a section of data manually using binary editor, result that I
can?t get any of column but SQLite does not give me a error message.
console log before db file corrupted:
sqlite .open test.db
sqlite select * from testtable;
au
reasons making lower performance.
???:Simon Slavinslavins at bigfraud.org
???:SQLite mailing listsqlite-users at mailinglists.sqlite.org
:2015?11?2?(??)?11:38
??:Re: [sqlite] Why SQLite take lower performanceinmulti-threadSELECTing?
On 2 Nov 2015, at 3:12am, sanhua.zh sanhua.zh at
I change my code to make it clear.
- (double)now
{
struct timeval time;
gettimeofday(time, NULL);
double ms = time.tv_sec+time.tv_usec/100.0;
return ms;
}
- (void)test
{
...
double before = [self now];
sqlite3_exec(handle, "SELECT * FROM testtable", NULL, NULL, NULL);
double after
sqlite-users at mailinglists.sqlite.org
:2015?11?2?(??)?10:45
??:Re: [sqlite] Why SQLite take lower performance inmulti-threadSELECTing?
On 2 Nov 2015, at 2:19am, sanhua.zh sanhua.zh at foxmail.com wrote: it seems
that this mail list will filter my code. I re-send it now. It?s written by
Objective-C
le (threadWait);
//begin
[Ticker tickWithInfo:start];
sqlite3_exec(handle, "SELECT * FROM testtable", NULL, NULL, NULL);
[Ticker stopWithInfo:end];
sqlite3_close(handle);
});
}
}
@end
???:Richard Hippdrh at sqlite.org
???:SQLite mailing lis
Hi, all
I use SQLite in iOS. I found that SQLite will take lower performancein
multi-thread SELECTing.
Here is my test result,
It costs 0.11s to select 100, elements,in 1-thread SELECTing:
But the sameIn 4-thread SELECTing, it costs 0.2s avg.
This test run on iPhone 6s.
You can see that 4-th
Ashttps://www.sqlite.org/wal.html mentioned,
Specialized applications for which the default implementation of shared memory
is unacceptable can devise alternative methods via a customVFS. For example, if
it is known that a particular database will only be accessed by threads within
a single pro
ns at bigfraud.org
???:General Discussion of SQLite Databasesqlite-users at mailinglists.sqlite.org
:2015?10?9?(??)?20:38
??:Re: [sqlite] Is it possible that keep SQLite in normal lockingmodewithout
mmap?
On 9 Oct 2015, at 1:29pm, sanhua.zh sanhua.zh at foxmail.com wrote: 2.
multithread-re
Hello,
I am using SQLite on iOS developing.
Now I need the better performance of multithread-reading-and-writing and the
robustness ofWAL journal mode without mmap.
As I know,WALjournal mode without mmap requires EXCLUSIVE locking mode, which
may prevent multithread-reading-and-writing. But mmap
77 matches
Mail list logo