[sqlite] ROLLBACK TO stmt within committed transaction cause write operation to the WAL file.

[sanhua.zh]

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;
INSERT INTO t VALUES(2);
ROLLBACK TO SAVEPOINT s1;
COMMIT;


SELECT * FROM t;
// print 1


// check the WAL file size without checkpoint/close sqlite connection
ls -l | grep wal
// print 4152 db-wal
```


As you can see, the insertion inside the transaction is not effective, which is 
as expected.
The strange thing is that the size of WAL file is changed, which means that 
SOMETHING appended into the end of WAL file.
BUT why? We should have nothing to write.
Is it a bug?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UNION ALL bug in Multi-threading

[sanhua.zh]

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 "OLD": `CREATE TABLE old(i INTEGER)`
1.2 Insert values into "OLD" to make it large enough: `INSERT INTO old 
VALUES(?1)`
2. Prepare a database named "NEW"
2.1 Create a table for "NEW": `CREATE TABLE new(i INTEGER)`


Migration:
For thread 1:
1. Attach "OLD" to "NEW" as "oldSchema": `ATTACH OLD AS oldSchema`
2. Migrate data from "OLD" to "NEW" in same transaction. Note that they should 
be executed with same handle using ATTACH mentioned in 1.
2.1 `BEGIN IMMEDIATE`
2.1 Select one of the row from "OLD": `SELECT i FROM oldSchema.old`
2.2 Insert the row into "NEW": `INSERT INTO main.new VALUES(?1)`
2.3 Delete the row from "OLD": `DELETE FROM oldSchema.old WHERE i == ?1`
2.4 `COMMIT`


For thread 2-N:
1. Create a view that union two tables: `CREATE TEMP VIEW v AS SELECT i FROM 
oldSchema.old UNION ALL SELECT i FROM main.new`
2. Select one of the value from view: `SELECT i FROM temp.v ORDER BY i LIMIT 1 
OFFSET ?1`.
Here is the strange result:
As an example, if the values of 0-999 is inserted into "OLD", then value N 
should be selected as expected at offset N.
But in these kind of steps, it will not.


It can be a little bit hard to reproduce due to the multi-threading. BUT if it 
sleeps for a while when committing, it will be much easier to reproduce:
// vdbeCommit method of vdbeaux.c
for(i=0; rc==SQLITE_OK  idb-nDb; i++){
 Btree *pBt = db-aDb[i].pBt;
 sqlite3_sleep(10); // additional sleep here
 if( pBt ){
  rc = sqlite3BtreeCommitPhaseOne(pBt, 0);
 }
}


It seems that the bug happens when one of the schema is committed but the 
another one is not.
On the other handle, if `UNION ALL` is changed to `UNION` while creating view, 
the bug will not happen too.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UNION ALL bug in Multi-threading

[sanhua.zh]

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 "OLD": `CREATE TABLE old(i INTEGER)`
1.2 Insert values into "OLD" to make it large enough: `INSERT INTO old 
VALUES(?1)`
2. Prepare a database named "NEW"
2.1 Create a table for "NEW": `CREATE TABLE new(i INTEGER)`


Migration:
For thread 1:
1. Attach "OLD" to "NEW" as "oldSchema": `ATTACH OLD AS oldSchema`
2. Migrate data from "OLD" to "NEW" in same transaction. Note that they should 
be executed with same handle using ATTACH mentioned in 1.
2.1 `BEGIN IMMEDIATE`
2.1 Select one of the row from "OLD": `SELECT i FROM oldSchema.old`
2.2 Insert the row into "NEW": `INSERT INTO main.new VALUES(?1)`
2.3 Delete the row from "OLD": `DELETE FROM oldSchema.old WHERE i == ?1`
2.4 `COMMIT`


For thread 2-N:
1. Create a view that union two tables: `CREATE TEMP VIEW v AS SELECT i FROM 
oldSchema.old UNION ALL SELECT i FROM main.new`
2. Select one of the value from view: `SELECT i FROM temp.v ORDER BY i LIMIT 1 
OFFSET ?1`.
Here is the strange result:
As an example, if the values of 0-999 is inserted into "OLD", then value N 
should be selected as expected at offset N.
But in these kind of steps, it will not.


It can be a little bit hard to reproduce due to the multi-threading. BUT if it 
sleeps for a while when committing, it will be much easier to reproduce:
// vdbeCommit method of vdbeaux.c
for(i=0; rc==SQLITE_OK  idb-nDb; i++){
 Btree *pBt = db-aDb[i].pBt;
 sqlite3_sleep(10); // additional sleep here
 if( pBt ){
  rc = sqlite3BtreeCommitPhaseOne(pBt, 0);
 }
}


It seems that the bug happens when one of the schema is committed but the 
another one is not.
On the other handle, if `UNION ALL` is changed to `UNION` while creating view, 
the bug will not happen too.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to use WITH CLAUSE in a UPDATE statement?

[sanhua.zh]

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);
INSERT INTO t VALUES(1);
WITH cte AS(SELECT i FROM t)
UPDATE cte SET i = 0; // Failed with "Error: no such table: cte"
```


Note that the sample WITH CLAUSE make no sense. It's just for testing. But 
still failed.
Did I use it in a wrong way? What's the suggesting rule?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to use WITH CLAUSE in a UPDATE statement?

[sanhua.zh]

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);
INSERT INTO t VALUES(1);
WITH cte AS(SELECT i FROM t)
UPDATE cte SET i = 0; // Failed with "Error: no such table: cte"
```


Note that the sample WITH CLAUSE make no sense. It's just for testing. But 
still failed.
Did I use it in a wrong way? What's the suggesting rule?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA table_info could not update schema

[sanhua.zh]

It's great idea for reproducing with two command-line shells.
Of cource, it can be reproduced with shells and here is the test code.


Note that you should really care the order for running code.


```
Command-line shell 1:
$ sqlite3
SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite .open testschema


Command-line Shell 2:
$ sqlite3
SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite .open testschema
sqlite SELECT * FROM sqlite_master;


Command-line shell 1:
sqlite CREATE TABLE test1 (i INTEGER);
sqlite PRAGMA table_info(test1);
0|i|INTEGER|0||0


Command-line shell 2:
sqlite PRAGMA table_info(test1); // test1 is already created but can't be 
reached.
sqlite SELECT * FROM sqlite_master;
table|test1|test1|2|CREATE TABLE test1 (i INTEGER)
sqlite PRAGMA table_info(test1);
0|i|INTEGER|0||0
```








Original Message
Sender:Clemens ladischclem...@ladisch.de
Recipient: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 
sample code: That code is incomplete and buggy. (Heed the compiler warnings!) 
Anyway, I can reproduce this with two command-line shells: 1 2 
 == select * from sqlite_master; 
create table t(x); pragma table_info(t); 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] PRAGMA table_info could not update schema

[sanhua.zh]

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_info-pragma-statement.


As a result, step 4 could not get any things since the schema is expired but 
table_info-pragma-statement do not update it.
How to solve this problem ? Am I using SQLite in a wrong way ?


Here is the sample code:
```
sqlite3_config(SQLITE_CONFIG_MULTITHREAD);  
sqlite3* conn1;
rc = sqlite3_open(path.UTF8String, conn1);  
sqlite3* conn2;
rc = sqlite3_open(path.UTF8String, conn2);
assert(rc==0);
{
  //load schema
  rc = sqlite3_exec(conn2, "SELECT * FROM sqlite_master", nullptr, nullptr, 
nullptr);
  printf("rc %d\n", rc);
}
rc = sqlite3_exec(conn1, "CREATE TABLE test1 (i INTEGER)", NULL, NULL, NULL);
rc = sqlite3_exec(conn1, "INSERT INTO test1 VALUES(1)", NULL, NULL, NULL);
assert(rc==0);
sqlite3_stmt* stmt;
rc = sqlite3_prepare_v2(conn2, "PRAGMA table_info(test1)", -1, stmt, nullptr);
assert(rc==0);
while (YES) {
  int rc = sqlite3_step(stmt);
  if (rc!=SQLITE_ROW) {
break;
  }
  for (int i = 0; i  sqlite3_column_count(stmt); i++) {
switch (sqlite3_column_type(stmt, i)) {
  case SQLITE_TEXT:
printf("%d %s\n", i, sqlite3_column_text(stmt, i));
break;
  case SQLITE_INTEGER:
printf("%d %d\n", i, sqlite3_column_int(stmt, i));
break;
  default:
   printf("other\n");
break;
}
  }
}
sqlite3_finalize(stmt);
```
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

[sanhua.zh]

And which commands will check whether the schema is expired.
As far as I know, SELECT command will but PRAGMA table_info will not do this.


Original Message
Sender:Simon slavinslav...@bigfraud.org
Recipient:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
Date:Monday, Aug 21, 2017 11:47
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. Simon. 
___ 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's Results Are Expired While Schema Is Changed !

[sanhua.zh]

Hello Jay, here is information update.


I find 2 new things.
1. This issue happens even two conns running in same thread.
2. And it is more important ! This issue only happens in WAL mode !


As I know, databases with journal mode use a file change counter to update 
schema while databases with WAL mode use wal-index to do this.
I'm using macOS with the system builtin sqlite framwork with 3.8.10.2. I think 
it should be a bug in wal-index.


Here is the very simple code to reproduce this problem.


```
sqlite3_config(SQLITE_CONFIG_MULTITHREAD);


sqlite3* handle1;
rc = sqlite3_open(path, handle1);
assert(rc==0);
//  rc = sqlite3_exec(handle1, "PRAGMA journal_mode=WAL", NULL, NULL, NULL);
//  assert(rc==0);


sqlite3* handle2;
rc = sqlite3_open(path, handle2);
assert(rc==0);
//  rc = sqlite3_exec(handle2, "PRAGMA journal_mode=WAL", NULL, NULL, NULL);
//  assert(rc==0);


rc = sqlite3_exec(handle1, "CREATE TABLE test1 (i INTEGER)", NULL, NULL, NULL);
assert(rc==0);
//The sql below will return an error with 'no such table: test1' in WAL, but 
succeeds in journal mode.
rc = sqlite3_exec(handle2, "SELECT * FROM test1", NULL, 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.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 them to 
runsequentially.Thread 1:  1. Conn A: Open, PRAGMA journal_mode=WAL  Thread 
2:  2.ConnB: Open, PRAGMA journal_mode=WAL  Thread 1:  3.ConnA: CREATE TABLE 
sample (i INTEGER);  Thread 2:  4.ConnB: PRAGMA table_info('sample')
Firstly, both thread 1 and 2 do initialization for their own conn, which is to 
read to schema into memory.  Then, Conn A creates a table with Conn A.  
Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and it 
returns nothing.  The same thing could happen if I change the step 4 to 
`sqlite3_table_column_metadata` or some other interfaces.I do know the 
reason should be the expired in-memory-schema. But I find no docs about which 
interface will or will not update the schema and what should I do while I call 
a non-update-schema interface ? See the bottom of the sqlite3_prepare*() docs: 
https://www.sqlite.org/c3ref/prepare.html And the SQLITE_SCHEMA docs: 
https://www.sqlite.org/rescode.html#schema As the docs say, make sure you’re 
using sqlite3_prepare*_v2() or _v3(). If a statement is prepared with these 
newer versions, it will handle most expiration situations automatically by 
re-preparing the statement. Generally speaking, if you do get an SQLITE_SCHEMA 
error, you need to rollback the current transaction, re-prepare the statements, 
and try again. -j -- Jay A. Kreibich  J A Y @ K R E I B I.C H  "Intelligence is 
like underwear: it is important that you have it, but showing it to the wrong 
people has the tendency to make them feel uncomfortable." -- Angela Johnson 
___ 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's Results Are Expired While Schema Is Changed !

[sanhua.zh]

Firstly, I use `PRAGMA table_info('sample')` in my sample case, which means 
that it also calls `sqlite3_prepare_v2` but do not re-prepare. Maybe it does 
not contain the specific OP checking the schema.


Secondly, it's hard to know when the schema is changed in multi-conns 
implementation. So as telling them to re-prepare.


So, As your word "comthis situation would be a problem" said, is it that I 
should not use the non-update-schema-operation in multi-conns implementation ?
If so, who or which doc can tell me that which SQL will or will not update the 
schema ?


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 them to 
runsequentially.Thread 1:  1. Conn A: Open, PRAGMA journal_mode=WAL  Thread 
2:  2.ConnB: Open, PRAGMA journal_mode=WAL  Thread 1:  3.ConnA: CREATE TABLE 
sample (i INTEGER);  Thread 2:  4.ConnB: PRAGMA table_info('sample')
Firstly, both thread 1 and 2 do initialization for their own conn, which is to 
read to schema into memory.  Then, Conn A creates a table with Conn A.  
Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and it 
returns nothing.  The same thing could happen if I change the step 4 to 
`sqlite3_table_column_metadata` or some other interfaces.I do know the 
reason should be the expired in-memory-schema. But I find no docs about which 
interface will or will not update the schema and what should I do while I call 
a non-update-schema interface ? See the bottom of the sqlite3_prepare*() docs: 
https://www.sqlite.org/c3ref/prepare.html And the SQLITE_SCHEMA docs: 
https://www.sqlite.org/rescode.html#schema As the docs say, make sure you’re 
using sqlite3_prepare*_v2() or _v3(). If a statement is prepared with these 
newer versions, it will handle most expiration situations automatically by 
re-preparing the statement. Generally speaking, if you do get an SQLITE_SCHEMA 
error, you need to rollback the current transaction, re-prepare the statements, 
and try again. -j -- Jay A. Kreibich  J A Y @ K R E I B I.C H  "Intelligence is 
like underwear: it is important that you have it, but showing it to the wrong 
people has the tendency to make them feel uncomfortable." -- Angela Johnson 
___ 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] SQLite's Results Are Expired While Schema Is Changed !

[sanhua.zh]

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 them to 
runsequentially.


Thread 1:
1. Conn A: Open, PRAGMA journal_mode=WAL
Thread 2:
2.ConnB: Open, PRAGMA journal_mode=WAL
Thread 1:
3.ConnA: CREATE TABLE sample (i INTEGER);
Thread 2:
4.ConnB: PRAGMA table_info('sample')


Firstly, both thread 1 and 2 do initialization for their own conn, which is to 
read to schema into memory.
Then, Conn A creates a table with Conn A.
Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and it 
returns nothing.
The same thing could happen if I change the step 4 to 
`sqlite3_table_column_metadata` or some other interfaces.


I do know the reason should be the expired in-memory-schema. But I find no docs 
about which interface will or will not update the schema and what should I do 
while I call a non-update-schema interface ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is it safe to use same sqlite connection sequentially between threads ?

[sanhua.zh]

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 after step 2 finished theoretically .
Also, I can make sure the memory order between threads.


Then, is it a safe way to use sqlite connection ? If not, what make it 
impossible?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] A Descending Auto Increment Key

[sanhua.zh]

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 AUTOINCREMENT` is a valid syntax.


Why the original SQL failed? Is that really not supported?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why SQLite Use Different Malloc Zone While CPU Count > 1?

[sanhua.zh]

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 usually wants to use hw.acctivecpu for MT decisions, but not here */
 sysctlbyname("hw.ncpu", cpuCount, len, NULL, 0);
 if( cpuCount1 ){
  /* defer MT decisions to system malloc */
  _sqliteZone_ = malloc_default_zone();
 }else{
  /* only 1 core, use our own zone to contention over global locks,
  ** e.g. we have our own dedicated locks */
  bool success;
  malloc_zone_t* newzone = malloc_create_zone(4096, 0);
  malloc_set_zone_name(newzone, "Sqlite_Heap");
  do{
   success = OSAtomicCompareAndSwapPtrBarrier(NULL, newzone,
(void * volatile *)_sqliteZone_);
  }while(!_sqliteZone_);
  if( !success ){
   /* somebody registered a zone first */
   malloc_destroy_zone(newzone);
  }
 }
#endif
 UNUSED_PARAMETER(NotUsed);
 return SQLITE_OK;
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is msync necessary for SQLite?

[sanhua.zh]

So, if I am already using an old version before 3.11 and enable the mmap, the 
data may lost after os crash. It’s that right?


原始邮件
发件人:Richard hipp...@sqlite.org
收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
发送时间:2016年11月3日(周四) 21:21
主题:Re: [sqlite] Is msync necessary for SQLite?


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(SQLITE_MMAP_READWRITE) ... 
#endif So writing to mmapped memory only happens if you compile with the 
(undocumented, unsupported, and untested) -DSQLITE_MMAP_READWRITE option.
#if SQLITE_MAX_MMAP_SIZE0  /* Deal with as much of this write request as 
possible by transfering  ** data from the memory mapping using memcpy(). */  
if( offsetpFile-mmapSize ){  if( offset+amt = pFile-mmapSize ){  int size = -1; 
 struct stat buf; /* Used to hold return values of fstat() */  if( 
!osFstat(pFile-h, buf) ){  size = (int)buf.st_size;  }  memcpy(((u8 
*)(pFile-pMapRegion))[offset], pBuf, amt);  return SQLITE_OK;  }else{  int 
nCopy = (int)(pFile-mmapSize - offset);  memcpy(((u8 
*)(pFile-pMapRegion))[offset], pBuf, nCopy);  pBuf = ((u8 *)pBuf)[nCopy];  amt 
-= nCopy;  offset += nCopy;  }  }  #endif  原始邮件  发件人:Richard 
hipp...@sqlite.org  收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org 
 发送时间:2016年11月3日(周四) 17:49  主题:Re: [sqlite] Is msync necessary for 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 writing.  All 
changes are implemented by calling write() and fsync(). -- D. Richard  Hipp 
d...@sqlite.org ___  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  -- D. 
Richard Hipp d...@sqlite.org ___ 
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] Is msync necessary for SQLite?

[sanhua.zh]

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 
[unixWrite]. (os_unix.h)  Is not it written by mmap ? It changed to avoid 
writing via mmap for version 3.10.0. http://www.sqlite.org/src/info/67c5d3c646 
And I don't think mmap() was used on IOS until 3.11.0. 
http://www.sqlite.org/src/info/e9a51d2a580daa0f Dan.#if 
SQLITE_MAX_MMAP_SIZE0  /* Deal with as much of this write request as possible 
by transfering  ** data from the memory mapping using memcpy(). */  if( 
offsetpFile-mmapSize ){  if( offset+amt = pFile-mmapSize ){  int size = -1;  
struct stat buf; /* Used to hold return values of fstat() */  if( 
!osFstat(pFile-h, buf) ){  size = (int)buf.st_size;  }  memcpy(((u8 
*)(pFile-pMapRegion))[offset], pBuf, amt);  return SQLITE_OK;  }else{  int 
nCopy = (int)(pFile-mmapSize - offset);  memcpy(((u8 
*)(pFile-pMapRegion))[offset], pBuf, nCopy);  pBuf = ((u8 *)pBuf)[nCopy];  amt 
-= nCopy;  offset += nCopy;  }  }  #endif  原始邮件  发件人:Richard 
hipp...@sqlite.org  收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org 
 发送时间:2016年11月3日(周四) 17:49  主题:Re: [sqlite] Is msync necessary for 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 writing. All changes are 
implemented by calling write() and fsync(). -- D. Richard Hipp d...@sqlite.org 
___ 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] Is msync necessary for SQLite?

[sanhua.zh]

Here is a part of code in [unixWrite]. (os_unix.h)
Is not it written by mmap ?


#if SQLITE_MAX_MMAP_SIZE0
 /* Deal with as much of this write request as possible by transfering
 ** data from the memory mapping using memcpy(). */
 if( offsetpFile-mmapSize ){
  if( offset+amt = pFile-mmapSize ){
   int size = -1;
   struct stat buf;   /* Used to hold return values of fstat() */
   if( !osFstat(pFile-h, buf) ){
size = (int)buf.st_size;
   }
   memcpy(((u8 *)(pFile-pMapRegion))[offset], pBuf, amt);
   return SQLITE_OK;
  }else{
   int nCopy = (int)(pFile-mmapSize - offset);
   memcpy(((u8 *)(pFile-pMapRegion))[offset], pBuf, nCopy);
   pBuf = ((u8 *)pBuf)[nCopy];
   amt -= nCopy;
   offset += nCopy;
  }
 }
#endif




原始邮件
发件人:Richard hipp...@sqlite.org
收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
发送时间:2016年11月3日(周四) 17:49
主题:Re: [sqlite] Is msync necessary for 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 writing. All 
changes are implemented by calling write() and fsync(). -- D. Richard Hipp 
d...@sqlite.org ___ 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] Is msync necessary for SQLite?

[sanhua.zh]

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?


原始邮件
发件人:Dan kennedydanielk1...@gmail.com
收件人:sqlite-userssqlite-us...@mailinglists.sqlite.org
发送时间: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/comp.unix.programmer/pIiaQ6CUKjU   
 So, is it necessary call a msync for SQLite? Probably not in the default 
configuration, at any rate. SQLite mmap-mode only works if the system has a 
unified page-cache. Which is why it is disabled on some platforms (OpenBSD and 
others IIRC). Also, unless SQLITE_MMAP_READWRITE is defined at compile time, 
SQLite uses write() calls to modify to the db file, even though it reads the 
file by memory mapping it. Dan. ___ 
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] Is msync necessary for SQLite?

[sanhua.zh]

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?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop Existing Table Results in 'no such table'

[sanhua.zh]

Hello Simon,
I try it again and I find that I can update the memory of connection itself by 
changing any other connections' schema_version.
This kind of behavior make me confused. Since it can tell other connections’ 
that the schema is changed, why can’t it tell to itself ?




原始邮件
发件人:sanhua.zhsanhua...@foxmail.com
收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
发送时间:2016年10月21日(周五) 14:48
主题:Re: [sqlite] Drop Existing Table Results in 'no such table'


You are changing the details saved on disk but not the copy in memory. Since 
reopen a new connection is a ugly way, is there any other way to update the 
memory ? I know that sqlite3InitCallback may update it, but I can’t find a way 
to invoke it. 原始邮件 发件人:Simon slavinslav...@bigfraud.org 收件人: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 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. SQLite does 
not look at the sqlite_master table before executing each command. It has a 
copy of the database schema in the memory allocated to the connection. You are 
changing the details saved on disk but not the copy in memory. Only I close the 
connection or start a new connection, the renamed table can be dropped. That 
makes sense. Simon. ___ 
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] Drop Existing Table Results in 'no such table'

[sanhua.zh]

Don't do this.
I don’t think so. It is written on the sqlite.com. So it should be a tricky way 
but not a wrong way.
All we need to do is testing enough.




原始邮件
发件人:Clemens ladischclem...@ladisch.de
收件人:sqlite-userssqlite-us...@mailinglists.sqlite.org
发送时间:2016年10月21日(周五) 14:40
主题:Re: [sqlite] 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 start a 
new  connection, the renamed table can be dropped. Re-opening the correction is 
the only way to make the changes visible. (I did not include the word 
"supported" in the previous sentence because there is nothing supported about 
the writable_schema pragma.) 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] Drop Existing Table Results in 'no such table'

[sanhua.zh]

You are changing the details saved on disk but not the copy in memory.
Since reopen a new connection is a ugly way, is there any other way to update 
the memory ?
I know that sqlite3InitCallback may update it, but I can’t find a way to invoke 
it.




原始邮件
发件人:Simon slavinslav...@bigfraud.org
收件人: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 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. SQLite does not look at the sqlite_master table before executing 
each command. It has a copy of the database schema in the memory allocated to 
the connection. You are changing the details saved on disk but not the copy in 
memory.  Only I close the connection or start a new connection, the renamed 
table can be dropped. That makes sense. Simon. 
___ 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] Drop Existing Table Results in 'no such table'

[sanhua.zh]

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 start a new connection, the renamed table can be 
dropped. It seems that the memory inside this connection out of date and 
'PRAGMA schema_version=X+1’ do not update it.
I use theprocedure wrote at the bottom 
ofhttps://www.sqlite.org/lang_altertable.html. It may be a tricky way, but it 
may not be wrong. Does any one know about this?


Here is my test code:

SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite .open test
sqlite CREATE TABLE A (i int);
sqlite select * from sqlite_master;
table|A|A|2|CREATE TABLE A (i int)
sqlite BEGIN
sqlite PRAGMA schema_version;
1
sqlite PRAGMA writable_schema=ON;
sqlite UPDATE sqlite_master set name='B', tbl_name='B', sql='CREATE TABLE B (i 
int)' where name='A';
sqlite PRAGMA schema_version=2;
sqlite PRAGMA writable_schema=OFF;
sqlite COMMIT;
sqlite DROP TABLE B;
Error: no such table: B
sqlite .exit
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CREATE TABLE/ALTER TABLE Slow Down When Table CountGrow

[sanhua.zh]

I’m altering the all table's names, which takes a long time to finish.
But now, I use ‘PRAGMA writable_schema=ON’ to alter them, which is written 
athttps://www.sqlite.org/lang_altertable.html
Although it is easy to corrupt the database, but I do it careful enough, and 
testing adequate, it should 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 wrote:  I 
foundCREATE TABLE/ALTER TABLE Slow Down When Table Count Grow. Yes. SQLite 
needs to search through the (hashed) list of tables every time you give it a 
command that uses tables. The more tables there are, the longer it has to 
search -- each time it gets a command. The more tables there are, the more 
indexes there are, and the longer SQLite has to spend searching through the 
indexes too. The more tables there are, the more space is wasted on space 
allocated to each of these tables and indexes which goes unused. Are you 
creating new tables inside your application ? Do these new tables have the same 
columns as one-another (or nearly the same columns) ? If so, then it is far 
more efficient to create one table with one extra column for what used to be 
the tablename. SQLite can easily handle a table with billions of rows and it 
will far faster to do that than searching through ten thousand tables. Simon. 
___ 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] CREATE TABLE/ALTER TABLE Slow Down When Table CountGrow

[sanhua.zh]

more than 1 tables/indexes


原始邮件
发件人:Keith medcalfkmedc...@dessus.com
收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
发送时间:2016年10月10日(周一) 12:48
主题:Re: [sqlite] CREATE TABLE/ALTER TABLE Slow Down When Table CountGrow


How many system objects do you have that this is a problem?  -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 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 except rowid. So this search 
 may slow down when the table count grow, since it cause a full-table-  search. 
   Is there anyway to solve this problem?  
___  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] CREATE TABLE/ALTER TABLE Slow Down When Table Count Grow

[sanhua.zh]

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 except rowid. So this search may 
slow down when the table count grow, since it cause a full-table-search.


Is there anyway to solve this problem?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 回复： The Performance Between [Drop Table] And [Rename Table] ?

[sanhua.zh]

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 should select the whole [sqlite_master] to update 
ONLY ONE altered table ?




原始邮件
发件人:sanhua.zhsanhua...@foxmail.com
收件人:sqlite-userssqlite-us...@mailinglists.sqlite.org
发送时间:2016年10月9日(周日) 17:59
主题:[sqlite] The Performance Between [Drop Table] And [Rename Table] ?


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 `sqlite_master` 
when the table is empty. But in my testcase, [Rename Table] is much more 
slower. Does anyone know the reason ? Here is my test code. Result: drop table 
total cost 4705633 alter total cost 13172092 Code: #import sqlite3.h #import 
sys/time.h #define EXIT_IF_FAILED(rc) if (rc!=SQLITE_OK) {printf("%d failed at 
%d\n", rc, __LINE__); exit(0);} #define TABLE_COUNT 1 static uint64_t now() 
{ #define MICROSECOND_PER_SECOND 100 struct timeval cur; gettimeofday(cur, 
NULL); uint64_t time = cur.tv_sec*MICROSECOND_PER_SECOND+cur.tv_usec; return 
time; } void preCreateTable(sqlite3* db) { int rc = SQLITE_OK; rc = 
sqlite3_exec(db, "BEGIN", NULL, NULL, NULL); EXIT_IF_FAILED(rc); for (int i = 
0; i TABLE_COUNT; i++) { NSString* sql = [NSString stringWithFormat:@"CREATE 
TABLE test%d (name TEXT)", i]; rc = sqlite3_exec(db, sql.UTF8String, NULL, 
NULL, NULL); EXIT_IF_FAILED(rc); } rc = sqlite3_exec(db, "COMMIT", NULL, NULL, 
NULL); EXIT_IF_FAILED(rc); } void config(sqlite3* db) { sqlite3_exec(db, 
"PRAGMA journal_mode=WAL;", NULL, NULL, NULL); sqlite3_exec(db, "PRAGMA 
synchronous=FULL;", NULL, NULL, NULL); } int main(int argc, const char * 
argv[]) { const char* testDropTablePath = 
"/Users/sanhuazhang/Desktop/testDropTablePath"; const char* testAlterTablePath 
= "/Users/sanhuazhang/Desktop/testAlterTablePath"; //test 'drop table' { 
sqlite3* db; int rc = sqlite3_open(testDropTablePath, db); EXIT_IF_FAILED(rc); 
config(db); preCreateTable(db); uint64_t before = now(); rc = sqlite3_exec(db, 
"BEGIN", NULL, NULL, NULL); EXIT_IF_FAILED(rc); for (int i = 0; i TABLE_COUNT; 
i++) { NSString* sql = [NSString stringWithFormat:@"DROP TABLE test%d", i]; rc 
= sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL); EXIT_IF_FAILED(rc); } rc 
= sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); EXIT_IF_FAILED(rc); uint64_t 
after = now(); printf("drop table total cost %llu\n", after-before); 
sqlite3_close(db); } //test 'alter table' { sqlite3* db; int rc = 
sqlite3_open(testAlterTablePath, db); EXIT_IF_FAILED(rc); config(db); 
preCreateTable(db); uint64_t before = now(); rc = sqlite3_exec(db, "BEGIN", 
NULL, NULL, NULL); EXIT_IF_FAILED(rc); for (int i = 0; i TABLE_COUNT; i++) { 
NSString* sql = [NSString stringWithFormat:@"ALTER TABLE test%d RENAME TO 
re%d", i, i]; rc = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL); 
EXIT_IF_FAILED(rc); } rc = sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); 
EXIT_IF_FAILED(rc); uint64_t after = now(); printf("alter total cost %llu\n", 
after-before); sqlite3_close(db); } return 0; } 
___ 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] The Performance Between [Drop Table] And [Rename Table] ?

[sanhua.zh]

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 
`sqlite_master` when the table is empty. But in my testcase, [Rename Table] is 
much more slower. Does anyone know the reason ?
Here is my test code.


Result:
drop table total cost 4705633
alter total cost 13172092


Code:
#import sqlite3.h
#import sys/time.h


#define EXIT_IF_FAILED(rc) if (rc!=SQLITE_OK) {printf("%d failed at %d\n", rc, 
__LINE__); exit(0);}
#define TABLE_COUNT 1


static uint64_t now()
{
#define MICROSECOND_PER_SECOND 100
  struct timeval cur;
  gettimeofday(cur, NULL);
  uint64_t time = cur.tv_sec*MICROSECOND_PER_SECOND+cur.tv_usec;
  return time;
}


void preCreateTable(sqlite3* db)
{
  int rc = SQLITE_OK;
  rc = sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);
  EXIT_IF_FAILED(rc);
  for (int i = 0; i  TABLE_COUNT; i++) {
NSString* sql = [NSString stringWithFormat:@"CREATE TABLE test%d (name 
TEXT)", i];
rc = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
EXIT_IF_FAILED(rc);
  }
  rc = sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
  EXIT_IF_FAILED(rc);
}


void config(sqlite3* db)
{
  sqlite3_exec(db, "PRAGMA journal_mode=WAL;", NULL, NULL, NULL);
  sqlite3_exec(db, "PRAGMA synchronous=FULL;", NULL, NULL, NULL);
}


int main(int argc, const char * argv[]) {
 
  const char* testDropTablePath = 
"/Users/sanhuazhang/Desktop/testDropTablePath";
  const char* testAlterTablePath = 
"/Users/sanhuazhang/Desktop/testAlterTablePath";
 
  //test 'drop table'
  {
sqlite3* db;
int rc = sqlite3_open(testDropTablePath, db);
EXIT_IF_FAILED(rc);
config(db);
preCreateTable(db);
uint64_t before = now();
rc = sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);
EXIT_IF_FAILED(rc);
for (int i = 0; i  TABLE_COUNT; i++) {
  NSString* sql = [NSString stringWithFormat:@"DROP TABLE test%d", i];
  rc = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
  EXIT_IF_FAILED(rc);
}
rc = sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
EXIT_IF_FAILED(rc);
uint64_t after = now(); 
printf("drop table total cost %llu\n", after-before);
sqlite3_close(db);
  }
 
  //test 'alter table'
  {
sqlite3* db;
int rc = sqlite3_open(testAlterTablePath, db);
EXIT_IF_FAILED(rc);
config(db);
preCreateTable(db);
uint64_t before = now();
rc = sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);
EXIT_IF_FAILED(rc);
for (int i = 0; i  TABLE_COUNT; i++) {
  NSString* sql = [NSString stringWithFormat:@"ALTER TABLE test%d RENAME TO 
re%d", i, i];
  rc = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
  EXIT_IF_FAILED(rc);
}
rc = sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
EXIT_IF_FAILED(rc);
uint64_t after = now(); 
printf("alter total cost %llu\n", after-before);
sqlite3_close(db);
  }


 
  return 0;
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why MMAP return ENOMEM in SQLite?

[sanhua.zh]

Unless your users have complained about speed, this does not matter. There is 
no need to make everything happen as fast /as possible/. Try using just 
standard SQLite and find out if it is fast /enough/. Then you don't have to 
spend lots of programming time learning tiny little details about one small 
piece of a software library.
I have my monitor system to check the lag and delay. I can make sure that I 
must make it faster.



PRAGMA mmap_size=0x7fff
I am using SQLite for a long time, and be familiar with the most of the source 
code. So you have no need to worry about the basic things.


BTW, what do you think if I mapseparatly instead of the whole db file, which is 
the way I mentioned before ?




原始邮件
发件人:Simon slavinslav...@bigfraud.org
收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
发送时间:2016年8月31日(周三) 18:04
主题:Re: [sqlite] Why MMAP return ENOMEM in SQLite?


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 fast /as possible/. Try using 
just standard SQLite and find out if it is fast /enough/. Then you don't have 
to spend lots of programming time learning tiny little details about one small 
piece of a software library.  Simon Won't the standard SQLite API do the job 
well enough ?  I did use the standard SQLite API. It maps the whole db file 
into address space. It does this only if you have told it to. According to 
https://www.sqlite.org/mmap.html "To activate memory-mapped I/O, use the 
mmap_size pragma and set the mmap_size to some large number" So do not do this 
and SQLite will stop trying to memory map the file. But it is possible that you 
are using a SQLite library which has a default memory map size. Can you execute 
the command "PRAGMA mmap_size" and tell what it outputs ? If the result is not 
zero or blank, you can disable memory mapping using the command "PRAGMA 
mmap_size=0" after you have opened the database. Please try this and see 
whether your application still crashes. Simon. 
___ 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] Why MMAP return ENOMEM in SQLite?

[sanhua.zh]

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 wrote:  Why 
do you think it will corrupt the database?  Can you give me more explainations 
or examples?  It's only my intuition - i don't have a concrete example. sqlite 
and ios are "well-oiled machines." They do their jobs and they do it well. If 
you start interfering with that, trying to take over or abuse their 
responsibilities because you think you can do it better, you will _eventually_ 
run into problems. In my experience, the chances of a back-fire when trying to 
push software beyond what it's designed to do are very high. You explicitly 
want to add complexity to an already complex system. Additional complexity 
almost always comes with a higher bug rate. A telephone is _not_ a 
high-performance computing platform, but a _convenience_ platform. Whether a db 
operation takes 10ms or 800ms should, for such platforms, be irrelevant. i 
_suspect_ that you are overestimating the impact of your perceived performance 
problem on the end users. But that's all just my opinion based on experience - 
i have no facts or statistics to back it up. Maybe it will work well for you. 
-- - stephan beal http://wanderinghorse.net/home/stephan/ "Freedom is 
sloppy. But since tyranny's the only guaranteed byproduct of those who insist 
on a perfect world, freedom will have to do." -- Bigby Wolf 
___ 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] Why MMAP return ENOMEM in SQLite?

[sanhua.zh]

Why do you think it will corrupt the database?
Can you give me more explainations or examples?




原始邮件
发件人:Stephan bealsgb...@googlemail.com
收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
发送时间:2016年8月31日(周三) 16:57
主题:Re: [sqlite] Why MMAP return ENOMEM in SQLite?


On Wed, Aug 31, 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 perceived performance 
problems you have right now. The chances that something breaks with that 
approach is, i suspect, very high. You will eventually corrupt a database and 
then write back to the list to ask why that approach corrupted it. -- - 
stephan beal http://wanderinghorse.net/home/stephan/ "Freedom is sloppy. But 
since tyranny's the only guaranteed byproduct of those who insist on a perfect 
world, freedom will have to do." -- Bigby Wolf 
___ 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] Why MMAP return ENOMEM in SQLite?

[sanhua.zh]

Yes, [mmap] can be larger than physical memory.
And what do you think about the new mapping way I mentioned ?




原始邮件
发件人:Stephan bealsgb...@googlemail.com
收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
发送时间:2016年8月31日(周三) 16:53
主题:Re: [sqlite] Why MMAP return ENOMEM in SQLite?


On 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.According to 
google, the iPhone 6s only has 2GB of RAM, so you can't  memmap 3.2G.  
Correction: you can map larger than physical memory, up to limits set by the OS 
environment. In your case, see: 
http://stackoverflow.com/questions/9184773/is-there-a-practical-limit-on-the-number-of-memory-mapped-files-in-ios
  In any case, as Simone said, _other apps_ require memory of their own as  
well.  Correction 2: Simon, not Simone (my roommate's name, so i type the 'e' 
out of habit) -- - stephan beal http://wanderinghorse.net/home/stephan/ 
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those 
who insist on a perfect world, freedom will have to do." -- Bigby Wolf 
___ 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] Why MMAP return ENOMEM in SQLite?

[sanhua.zh]

Thanks for your answer. Simon Why are you trying to do memory-mapping on a 
portable device ? Is it for speed ? Do you absolutely need it ? Yes. even on 
iDevice, [mmap] can get faster than sequential I/O. Sometimes it will be twice 
faster. Simon Won't the standard SQLite API do the job well enough ? I did use 
the standard SQLite API. It maps the whole db file into address space. When the 
db file is too big, the [mmap] will failed and turn back to sequential I/O 
method. I have another way to solve this problem. The address space might 
be“scttered”. There is not enough space to fit a BIG file. But there are a lot 
of fragment space to fit multiple small regions. So I can map multiple regions 
of file, instead of mapping the whole db file. For example, mapping 256 
4MB-regions, instead of mapping the whole 1GB file. This solution should modify 
the [unixRemapFile] method in the source code of SQLite, also, I should remap 
the exact region into the pMapRegion variable. 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.




原始邮件
发件人:sanhua.zhsanhua...@foxmail.com
收件人:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
发送时间:2016年8月31日(周三) 16:39
主题:Re: [sqlite] Why MMAP return ENOMEM in SQLite?


Thanks for your answer. Simon Why are you trying to do memory-mapping on a 
portable device ? Is it for speed ? Do you absolutely need it ? Yes. even on 
iDevice, [mmap] can get faster than sequential I/O. Sometime it will be twice 
faster. Simon Won't the standard SQLite API do the job well enough ? I did use 
the standard SQLite API. It maps the whole db file into address space. When the 
db file is too big, the [mmap] will failed and back tosequential I/O method. I 
have another way to solve this problem. The address space might be“scttered”. 
There is not enough space to fit a BIG file. But there are a lot of fragment 
space to fit multiple small region. So I can map multiple regions of file, 
instead of mapping the whole db file. For example, mapping 128 4MB-regions, 
instead of mapping the whole 1GB file. This solution should modify the 
[unixRemapFile] method in the source code of SQLite, also, I should remap the 
exact region into the pMapRegion variable. 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. 原始邮件 发件人: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 wrote: Is it caused by mmap file too 
big that the address space is not enough? Yes. Under iOS, memory maps can only 
be as big as the available physical memory. You're working on a tiny 
multi-tasking device and memory is at a premium. You can't afford to hog 
memory. Nor can you do data-handling so fast that it runs down the battery or 
overheats the device. Also, iDevices before the iPhone 5S in 2013 ran a 32-bit 
version of iOS, limiting them to 4GB of memory per App. If yes, then how can I 
get the allowed mmap file size? This figure would be useless since available 
physical memory varies depending on things your application cannot prevent. For 
instance, you may find out how much free memory there is, but then there may be 
in incoming phonecall. iOS will then allocate some of that memory to the 
process dealing with the phonecall. And, of course you cannot do anything to 
interfere with a phonecall because that is more important than an App. Why are 
you trying to do memory-mapping on a portable device ? Is it for speed ? Do you 
absolutely need it ? Won't the standard SQLite API do the job well enough ? 
Simon. ___ 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] Why MMAP return ENOMEM in SQLite?

[sanhua.zh]

Thanks for your answer.


Simon Why are you trying to do memory-mapping on a portable device ? Is it for 
speed ? Do you absolutely need it ? 
Yes. even on iDevice, [mmap] can get faster than sequential I/O. Sometime it 
will be twice faster.


Simon Won't the standard SQLite API do the job well enough ?
I did use the standard SQLite API. It maps the whole db file into address 
space. When the db file is too big, the [mmap] will failed and back 
tosequential I/O method.


I have another way to solve this problem.
The address space might be“scttered”. There is not enough space to fit a BIG 
file. But there are a lot of fragment space to fit multiple small region.
So I can map multiple regions of file, instead of mapping the whole db file. 
For example, mapping 128 4MB-regions, instead of mapping the whole 1GB file.
This solution should modify the [unixRemapFile] method in the source code of 
SQLite, also, I should remap the exact region into the pMapRegion variable.
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.




原始邮件
发件人: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 wrote:  Is it caused 
by mmap file too big that the address space is not enough? Yes. Under iOS, 
memory maps can only be as big as the available physical memory. You're working 
on a tiny multi-tasking device and memory is at a premium. You can't afford to 
hog memory. Nor can you do data-handling so fast that it runs down the battery 
or overheats the device. Also, iDevices before the iPhone 5S in 2013 ran a 
32-bit version of iOS, limiting them to 4GB of memory per App.  If yes, then 
how can I get the allowed mmap file size? This figure would be useless since 
available physical memory varies depending on things your application cannot 
prevent. For instance, you may find out how much free memory there is, but then 
there may be in incoming phonecall. iOS will then allocate some of that memory 
to the process dealing with the phonecall. And, of course you cannot do 
anything to interfere with a phonecall because that is more important than an 
App. Why are you trying to do memory-mapping on a portable device ? Is it for 
speed ? Do you absolutely need it ? Won't the standard SQLite API do the job 
well enough ? Simon. ___ 
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] Why MMAP return ENOMEM in SQLite?

[sanhua.zh]

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 question is:
Is it caused by mmap file too big that the address space is not enough?
If yes, then how can I get the allowed mmap file size?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to customize "Bytes of unused 'reserved' spaceat the end of each page" ?

[sanhua.zh]

sqlite3_test_control() is an interface for testing. Is it safe to use it in 
released product?


原始邮件
发件人:Clemens ladischclem...@ladisch.de
收件人:sqlite-userssqlite-us...@mailinglists.sqlite.org
发送时间:2016年8月16日(周二) 18:09
主题:Re: [sqlite] How to customize "Bytes of unused 'reserved' spaceat 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_control(), or a hex editor: 
http://stackoverflow.com/questions/38201975/how-to-create-database-with-reserved-bytes
 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] How to customize "Bytes of unused 'reserved' space at the end of each page" ?

[sanhua.zh]

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 details?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite 4

[sanhua.zh]

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.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Maybe A Bug in [fcntlSizeHint] function of SQLite?

[sanhua.zh]

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 ){
  int rc;
  if( pFile-szChunk=0 ){
   if( robust_ftruncate(pFile-h, nByte) ){
storeLastErrno(pFile, errno);
return unixLogError(SQLITE_IOERR_TRUNCATE, "ftruncate", pFile-zPath);
   }
  }


  rc = unixMapfile(pFile, nByte);
  return rc;
 }
#endif


The routinue [robust_ftruncate] will run and the file will be cut off if 
nBytefileSize.
This routine may (or may not) cause some problem in SQLite. But for someone 
hacking the source code of SQLite(What I’m doing), it may be a trap.


/*
** This function is called to handle the SQLITE_FCNTL_SIZE_HINT
** file-control operation. Enlarge the database to nBytes in size
** (rounded up to the next chunk-size). If the database is already
** nBytes or larger, this routine is a no-op.
*/
static int fcntlSizeHint(unixFile *pFile, i64 nByte){
 if( pFile-szChunk0 ){
  i64 nSize;  /* Required file size */
  struct stat buf;   /* Used to hold return values of fstat() */
 
  if( osFstat(pFile-h, buf) ){
   return SQLITE_IOERR_FSTAT;
  }


  nSize = ((nByte+pFile-szChunk-1) / pFile-szChunk) * pFile-szChunk;
  if( nSize(i64)buf.st_size ){


#if defined(HAVE_POSIX_FALLOCATE)  HAVE_POSIX_FALLOCATE
   /* The code below is handling the return value of osFallocate()
   ** correctly. posix_fallocate() is defined to "returns zero on success,
   ** or an error number on failure". See the manpage for details. */
   int err;
   do{
err = osFallocate(pFile-h, buf.st_size, nSize-buf.st_size);
   }while( err==EINTR );
   if( err ) return SQLITE_IOERR_WRITE;
#else
   /* If the OS does not have posix_fallocate(), fake it. Write a
   ** single byte to the last byte in each block that falls entirely
   ** within the extended region. Then, if required, a single byte
   ** at offset (nSize-1), to set the size of the file correctly.
   ** This is a similar technique to that used by glibc on systems
   ** that do not have a real fallocate() call.
   */
   int nBlk = buf.st_blksize; /* File-system block size */
   int nWrite = 0;   /* Number of bytes written by seekAndWrite */
   i64 iWrite; /* Next offset to write to */


   iWrite = ((buf.st_size + 2*nBlk - 1)/nBlk)*nBlk-1;
   assert( iWrite=buf.st_size );
   assert( (iWrite/nBlk)==((buf.st_size+nBlk-1)/nBlk) );
   assert( ((iWrite+1)%nBlk)==0 );
   for(/*no-op*/; iWritenSize; iWrite+=nBlk ){
nWrite = seekAndWrite(pFile, iWrite, "", 1);
if( nWrite!=1 ) return SQLITE_IOERR_WRITE;
   }
   if( nWrite==0 || (nSize%nBlk) ){
nWrite = seekAndWrite(pFile, nSize-1, "", 1);
if( nWrite!=1 ) return SQLITE_IOERR_WRITE;
   }
#endif
  }
 }


#if SQLITE_MAX_MMAP_SIZE0
 if( pFile-mmapSizeMax0  nBytepFile-mmapSize ){
  int rc;
  if( pFile-szChunk=0 ){
   if( robust_ftruncate(pFile-h, nByte) ){
storeLastErrno(pFile, errno);
return unixLogError(SQLITE_IOERR_TRUNCATE, "ftruncate", pFile-zPath);
   }
  }


  rc = unixMapfile(pFile, nByte);
  return rc;
 }
#endif


 return SQLITE_OK;
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to study the Vdbe in SQLite?

[sanhua.zh]

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 vdbe part?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] The usage of SQLITE_ENABLE_ATOMIC_WRITE?

[sanhua.zh]

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-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Is Not Support Uint64?

[sanhua.zh]

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, which bigger than the max of Int64?

[sqlite] Why SQLite lock 510 bytes for shared lock?

[sanhua.zh]

Good job!
But I think lock a random byte from ?shared byterange? is enough to implement 
the shared file lock. Locking whole 510 bytes may take a lower performance. Why 
do you do so?



???:Richard Hippdrh at sqlite.org
???:SQLite mailing listsqlite-users at mailinglists.sqlite.org
:2016?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 510 bytes 
length). But the code show that it locks the whole  ?shared byte range?. The 
comment is legacy from the locking protocol used by Windows95, which lacks the 
ability to do a shared file lock. I'll fix the comment. -- D. Richard Hipp drh 
at sqlite.org ___ sqlite-users 
mailing list sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why SQLite lock 510 bytes for shared lock?

[sanhua.zh]

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 thing?


The comment in os_unix.h
 ** To obtain a SHARED lock, a read-lock is obtained on the 'pending
 ** byte'. If this is successful, a random byte from the 'shared byte
 ** range' is read-locked and the lock on the 'pending byte' released.


The code in os_unix.h
  /* Now get the read-lock */
  lock.l_start = SHARED_FIRST;
  lock.l_len = SHARED_SIZE;
  if( unixFileLock(pFile, lock) ){
   tErrno = errno;
   rc = sqliteErrorFromPosixError(tErrno, SQLITE_IOERR_LOCK);
  }

[sqlite] Why SQLite use busy-retry but not lock-and-wait?

[sanhua.zh]

Thanks for your answering.
Actually, I am not trying to do lock-and-wait on file lock level. In my app, 
all operation is done on single process but different threads. I just want to 
simulate this using thread lock. So it may not cost too much time to do 
thread-switching. And thread lock have a timeout.
I will do some testing to check whether it isefficient. Thanks for 
youransweringagain.





???:R Smithrsmith at rsweb.co.za
???:sqlite-userssqlite-users at mailinglists.sqlite.org
:2016?4?6?(??)?15:45
??:Re: [sqlite] Why SQLite use busy-retry but not lock-and-wait?


On 2016/04/06 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 make it sleep-and-retry.I get confused of this code. Why SQLite use 
lock-and-wait? For example, 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.So I think it 
might be a kind oftrick, but I don?t why SQLite do so. I already find out the 
www.sqlite.org, but it tells nothing about this. Does anybody know why SQLite 
design it so? SQLite is platform independent - i.e. it works on all systems and 
has to make do with what is available everywhere. F_SETLKW is not available on 
all platforms and can't be a standard way for SQLite to interface. You can 
however make your own VFS for SQLite that implements it on Unix systems, if you 
like. This is quite easy. Does F_SETLKW have a timeout? what must SQLite do if 
the file still doesn't become available after a long time? How would this be 
better than the current way? You probably know this already, but the 
lock-checking mechanism is quite efficient, and a locked file gets re-checked 
(if a timeout is specified) very quick, then successively longer and longer 
intervals until the timeout expires. I doubt you will see a great increase in 
lock-situation performance using the file-lock wait cycle - but if you do make 
that VFS, perhaps we could do some testing of the hypothesis. Cheers, Ryan 
___ sqlite-users mailing list 
sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why SQLite use busy-retry but not lock-and-wait?

[sanhua.zh]

SQLite solves well with deadlock. I don?t think it will let it wait infinitely. 
Can you give me an example how they could be a deadlock.



???:Clemens Ladischclemens at ladisch.de
???:sqlite-userssqlite-users at mailinglists.sqlite.org
:2016?4?6?(??)?14:35
??:Re: [sqlite] Why SQLite use 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 time. It would never do to wait infinitely long in 
case of a deadlock. Regards, Clemens 
___ sqlite-users mailing list 
sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why SQLite use busy-retry but not lock-and-wait?

[sanhua.zh]

You don?t understand what I mean.


busy_timeout is also not the best solution. It can not know the exactly time 
when other handles unhold the lock.


But lock and wait until other handles unhold the lock, it will restart 
immediatly. I just wonder why SQLite do so.





???:Simon 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 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. 
Use this call https://www.sqlite.org/c3ref/busy_timeout.html To tell SQLite to 
handle backoff-and-retry itself. Simon. 
___ sqlite-users mailing list 
sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why SQLite use busy-retry but not lock-and-wait?

[sanhua.zh]

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 get confused of this code. Why SQLite use lock-and-wait? For example, 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.


So I think it might be a kind oftrick, but I don?t why SQLite do so. I already 
find out the www.sqlite.org, but it tells nothing about this. Does anybody know 
why SQLite design it so?

[sqlite] SQLite Corruption By Writing NULL Data

[sanhua.zh]

I use the C API.
I think it is not possible to get the hole call stack if continue using the 
released SQLite pointer.



???:Simon Slavinslavins at bigfraud.org
???:SQLite mailing listsqlite-users at mailinglists.sqlite.org
:2016?3?4?(??)?17:50
??:Re: [sqlite] SQLite Corruption By Writing 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 made by SQLite B) Releasing a 
SQLite pointer and then continuing to use it Which API are you using to call 
SQLite ? Are you calling the SQLite API using C commands ? Or are you using 
another language or another API ? How are you including SQLite in your project 
? Are you calling a library you supply ? Are you calling a library already 
present in your programming language ? Or are you including the 'sqlite.c' and 
'sqlite.h' files in your project ? Simon. 
___ sqlite-users mailing list 
sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Corruption By Writing NULL Data

[sanhua.zh]

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 sqlite3CheckNullData(const unsigned char* data, const int length)
{
  const size_t* s = (const size_t*)data;
  const unsigned char* d = (const unsigned char*)data;
  int n = length/sizeof(size_t);
  int i;
  for (i = 0; i  n; i++) {
if (s[i]!=0) {
  return 0;
}
  }
  for (i = i*sizeof(size_t); ilength; i++) {
if (d[i]!=0) {
  return 0;
}
  }
  return 1;
}
static int unixWrite(
 sqlite3_file *id,
 const void *pBuf,
 int amt,
 sqlite3_int64 offset
){
 unixFile *pFile = (unixFile*)id;
 if (amt0sqlite3CheckNullData(pBuf, amt)) {
  SQLITE_KNOWN_ERROR(SQLITE_CORRUPT, "writing null data into %s from %d length 
%d", unixGetFilename(pFile-zPath), offset, amt);
 }
...
}

The code is simple. I check the data whether is all null in 
[sqlite3CheckNullData], and add a macro [SQLITE_KNOWN_ERROR], which is defined 
as [sqlite_log], to throw this error outside SQLite. Outside SQLite, I dump the 
call stack of all thread, and I got this:

0x195774000 + 113628   objc_msgSend (in libobjc.dylib) + 28
0x1000f8000 + 7781724   _ZL9LogSQLitePviPKc,WCDataBase.mm,line 81
0x1000f8000 + 2836888   sqlite3_vlog,printf.c,line 1023
0x1000f8000 + 2778664   sqlite3KnownError,main.c,line 3192
0x1000f8000 + 2554560   unixWrite,os_unix.c,line 3335
0x1000f8000 + 2821984   sqlite3WalCheckpoint,wal.c,line 1798
0x1000f8000 + 2819864   sqlite3WalClose,wal.c,line 1914
0x1000f8000 + 2529964   sqlite3PagerClose,pager.c,line 3995
0x1000f8000 + 2574152   sqlite3BtreeClose,btree.c,line 2516
0x1000f8000 + 277   sqlite3LeaveMutexAndCloseZombie,main.c,line 
10834297741736

0x1000f8000 + 2774220   sqlite3Close,main.c,line 1026


This is the only thread operating database. All other call stack of threads 
make no sense.
You can see the SQLite checkpointing. That is the reason why my database 
corrupt. And I have no idea how this happened even I checking the source code.


Here is some of my conclusion:
1. This checking null data also work for writing into WAL file, but there is no 
report that WAL is been written by null data.
2.Some rogue file descriptor may write the null data into WAL file. But, I have 
several db with the same problem. It?s a rare event that the rogue writter only 
write the null data into the WAL, not all other db files or normal files.
3. I guess it could be a problem of operating system. I work on iOS, but I have 
no any further idea.
4. It would happened in normal knee. But it could easily happen when the disk 
free space is low. I also haveno any further idea about this.


So, this is my confusion:
1. Does anyone have any idea about this?
2. What can I do to reserve this type of corruption?


Note that if a page of sqlite_master is been rewritten by null data, the 
[.dump] shell command will not work to repair the database.

[sqlite] Why skip invoking busy handlerwhilepBt->inTransaction!=TRANS_NONE

[sanhua.zh]

No. I don?t mean [SQLite] will invoke the busy-handler.
I mean I can modify the source code here and let it invoke busy-handler in WAL.
Because it will not result in a deadlock in WAL.


So can I do this?



???:Dan Kennedydanielk1977 at gmail.com
???:sqlite-userssqlite-users at 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 invoked when trying to upgrade from a read to a write 
transaction. Regardless of journal mode. Dan.  ???:Dan 
Kennedydanielk1977 at gmail.com  ???:sqlite-userssqlite-users at 
mailinglists.sqlite.org  :2016?2?24?(??)?23:52  ??:Re: [sqlite] Why skip 
invoking busy handler whilepBt-inTransaction!=TRANS_NONEOn 02/24/2016 08:32 
PM, sanhua.zh wrote: 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 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,wrflag1,sqlite3TempInMemory(p-db)); if( 
rc==SQLITE_OK ){ rc = newDatabase(pBt); } } } if( rc!=SQLITE_OK ){ 
unlockBtreeIfUnused(pBt); } }while( (rc0xFF)==SQLITE_BUSY 
pBt-inTransaction==TRANS_NONE btreeInvokeBusyHandler(pBt) ); You can see 
pBt-inTransaction==TRANS_NONE is one of the condition that invoke busy handler. 
There is a simple way to simulate a situation that does not invoke busy 
handler: 1. begin a transaction without ?IMMEDIATE? and ?EXCLUSIVE? 2. run a 
read operation, like ?SELECT?. This will let pBt-inTransaction be TRANS_READ 3. 
run a write operation, which will invoke sqlite3BtreeBeginTrans again. And if 
it becomes SQLITE_BUSY, then btreeInvokeBusyHandler will be skiped and no retry 
will happen. So it?s the question I confused. Why SQLite skip invoking busy 
handler while it's in TRANS (either read or write) ? Assuming you're not using 
wal-mode, it's because the two processes will be waiting for each other. The 
transaction opened in step 1 cannot be committed until the read-only 
transaction started in step 2 has ended. So if you did invoke the busy-handler 
in step 3, the two processes would each be waiting for the other to give up. 
Not much point to that. In wal-mode it's a little different. The transaction 
opened in step 1 could be committed, but attempting to open the 
write-transaction in step 3 following that would fail with 
SQLITE_BUSY_SNAPSHOT. Dan. ___ 
sqlite-users mailing list sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 
___ sqlite-users mailing list 
sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users  
___  sqlite-users mailing list  
sqlite-users at mailinglists.sqlite.org  
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 
___ sqlite-users mailing list 
sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why skip invoking busy handler whilepBt->inTransaction!=TRANS_NONE

[sanhua.zh]

So can this be understood as, if I run my code in WAL, I can invoke busy 
handler even it in TRAN_READ?



???:Dan Kennedydanielk1977 at gmail.com
???:sqlite-userssqlite-users at mailinglists.sqlite.org
:2016?2?24?(??)?23:52
??:Re: [sqlite] Why skip invoking busy handler 
whilepBt-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 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,wrflag1,sqlite3TempInMemory(p-db));  if( 
rc==SQLITE_OK ){  rc = newDatabase(pBt);  }  }  }   if( rc!=SQLITE_OK ){  
unlockBtreeIfUnused(pBt);  }  }while( (rc0xFF)==SQLITE_BUSY 
pBt-inTransaction==TRANS_NONE  btreeInvokeBusyHandler(pBt) );  You can see 
pBt-inTransaction==TRANS_NONE is one of the condition that invoke busy handler. 
 There is a simple way to simulate a situation that does not invoke busy 
handler:  1. begin a transaction without ?IMMEDIATE? and ?EXCLUSIVE?  2. run a 
read operation, like ?SELECT?. This will let pBt-inTransaction be TRANS_READ  
3. run a write operation, which will invoke sqlite3BtreeBeginTrans again. And 
if it becomes SQLITE_BUSY, then btreeInvokeBusyHandler will be skiped and no 
retry will happen.So it?s the question I confused. Why SQLite skip invoking 
busy handler while it's in TRANS (either read or write) ? Assuming you're not 
using wal-mode, it's because the two processes will be waiting for each other. 
The transaction opened in step 1 cannot be committed until the read-only 
transaction started in step 2 has ended. So if you did invoke the busy-handler 
in step 3, the two processes would each be waiting for the other to give up. 
Not much point to that. In wal-mode it's a little different. The transaction 
opened in step 1 could be committed, but attempting to open the 
write-transaction in step 3 following that would fail with 
SQLITE_BUSY_SNAPSHOT. Dan.  ___  
sqlite-users mailing list  sqlite-users at mailinglists.sqlite.org  
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 
___ sqlite-users mailing list 
sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why skip invoking busy handler while pBt->inTransaction!=TRANS_NONE

[sanhua.zh]

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 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,wrflag1,sqlite3TempInMemory(p-db));
if( rc==SQLITE_OK ){
 rc = newDatabase(pBt);
}
   }
  }

  if( rc!=SQLITE_OK ){
   unlockBtreeIfUnused(pBt);
  }
 }while( (rc0xFF)==SQLITE_BUSY  pBt-inTransaction==TRANS_NONE 
 btreeInvokeBusyHandler(pBt) );




You can see pBt-inTransaction==TRANS_NONE is one of the condition that invoke 
busy handler.
There is a simple way to simulate a situation that does not invoke busy handler:
1. begin a transaction without ?IMMEDIATE? and ?EXCLUSIVE?
2. run a read operation, like ?SELECT?. This will let pBt-inTransaction be 
TRANS_READ
3. run a write operation, which will invoke sqlite3BtreeBeginTrans again. And 
if it becomes SQLITE_BUSY, then btreeInvokeBusyHandler will be skiped and no 
retry will happen.


So it?s the question I confused. Why SQLite skip invoking busy handler while 
it's in TRANS (either read or write) ?

[sqlite] empty page corrupt

[sanhua.zh]

Yeah, but I don?t know how to debug it. I follow the howtocorrupt.html 
suggestion but nothing helpful.
I write in Objective-C. all sqlite relative operation isencapsulated by 
Objective-C and thread mutex.



???:Simon Slavinslavins at bigfraud.org
???:SQLite mailing listsqlite-users at 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 or programming 
error. SQLite does not corrupt databases that way. What language are you 
programming in ? Simon. ___ 
sqlite-users mailing list sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] empty page corrupt

[sanhua.zh]

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 corrupted and try to find out the reason. Are 
you using a network file system or virtual machine? What type of disk? 
http://www.sqlite.org/howtocorrupt.html Regards, Clemens 
___ sqlite-users mailing list 
sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] empty page corrupt

[sanhua.zh]

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?


Here is some info,
1. this db is in WAL mode
2. 4% cause corrupt ( from the online data)
3. this db is used for fts
4. I think all db relative operation is locked well

[sqlite] Database Corrupt While Disk Full

[sanhua.zh]

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 should not retry forever.)
  Another example, while SQLITE_FULL,manually cleaning your pragram cache to 
make some free space might be a better way than crash your app.


2. Quit while get error is also not a great enough idea. Because not all 
pragram is a command line tool. It can be a user-oriented application instead 
of a developer-oriented tool. Users don?t wish to meet a crash.


So, find out how SQLITE_FULL leading to SQLITE_CORRUPT, and fix it or avoid it 
(if it can?t be fixed) might be the better solution.


If you don?t know or you don?t want to teach us the detail of it, I might try 
my best to do it. :)



???:Simon Slavinslavins at bigfraud.org
???:SQLite mailing listsqlite-users at mailinglists.sqlite.org
:2016?1?14?(??)?20:39
??:Re: [sqlite] Database Corrupt While Disk Full


 On 14 Jan 2016, at 11:55am, Brice Andr? brice.andre at ams-solutions.be wrote: 
  I am a little worried with your last post because, I must admint that I do  
not take special measures in my application when such errors occur to  
guarantee that no more DB access are performed.   I have thus the following 
questions : If you're writing a proper long-term program, check the result 
codes returned by every SQLite API call. In most cases all you need to do is 
check to see that the result is SQLITE_OK. Hope this helps.  1. What would be 
the proper reaction to SQLITE_FULL, SQLITE_IOERR,  SQLITE_CANTOPEN ? Perform a 
sqlite3_close ? Just exit the program as is  without touching the database file 
descriptor ? And once the program has  left, can we perform a sqlite3_open ? At 
this point there is no need to execute any other sqlite3_ calls. In fact, if 
you do execute them they'll probably just return another error message. Just 
print an error message and quit. When the SQLite API is next used to open the 
file it will know that the file was not closed properly and automatically try 
to reconstruct an uncorrupted version. This is done without informing the 
program that anything was wrong. So your question is why should you not try to 
do this immediately ? The answer is that without the user taking action it's 
probably going to fail. The original crash might have reported SQLITE_FULL or 
some other other condition which the user needs to fix before reopening the 
database. Trying to recover an uncorrupted database while the disk is still 
full will probably just lead to another crash, so there's no point and, at 
worst, a possibility of corrupting your database or some other file associated 
with the program. Another possibility is that the error is something like 
SQLITE_PROTOCOL, which suggests that you have faulty hardware or faulty file 
system drivers. Again, immediately reopening the file and continuing may just 
result in another crash. Worse, it might fatally corrupt the database because 
the fault in the storage system caused something to overwrite the database 
file. Again, the correct thing to do here is not to retry, but instead to 
report the problem to the user and get them to work the fault then decide for 
themself whether they want to carry on using the program.  2. What is the 
exhaustive list of error codes that shall be considered  as fatal ? On this 
page you will see a full list of all error codes: 
https://www.sqlite.org/rescode.html Near the top you can see that it lists 
SQLITE_OK (0), SQLITE_ROW (100), and SQLITE_DONE (101) as the only codes which 
do not represent errors. All other codes (including ones you can't find 
documentation for) are fatal errors which prevent you from working with the 
database. So don't test for codes which represent specific errors, instead 
check for SQLITE_OK and other expected codes then crash for everything else. 
This is what good SQLite programs do. A normal program should just present a 
useful error message and quit, perhaps something like "Quitting because a 
SQLite operation returned error 24.". You might want to generate specific 
messages for things like a full hard disk but most people don't bother, because 
there's little chance that the underlying problem is really that the user 
filled up the disk. Simon. ___ 
sqlite-users mailing list sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database Corrupt While Disk Full

[sanhua.zh]

OK.
Manual crash might be a kind of solution.


BTW, how does SQLITE_FULL finally result in SQLITE_CORRUPT? How does it happen 
in detail?



???:Simon Slavinslavins at bigfraud.org
???:SQLite mailing listsqlite-users at mailinglists.sqlite.org
:2016?1?14?(??)?18:41
??:Re: [sqlite] 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 SQLITE_FULL, SQLITE_IOERR, SQLITE_CANTOPEN 
are fatal errors. Your program should crash there, it must not try to continue 
using the database. Simon. ___ 
sqlite-users mailing list sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database Corrupt While Disk Full

[sanhua.zh]

Thanks for your answer.


Through the error code timeline, it shows that much of SQLITE_FULL, 
SQLITE_IOERR, SQLITE_CANTOPEN happened before SQLITE_CORRUPT. Database might be 
in an obscure state while disk is full, then it corrupt in some unknown reason.


As you said, disk full might corrupt the database. Can you teach me a little 
more in detail ? I haven?t found the relative code in SQLite source code.



???:Simon Slavinslavins at bigfraud.org
???:SQLite mailing listsqlite-users at mailinglists.sqlite.org
:2016?1?14?(??)?17:46
??:Re: [sqlite] Database Corrupt While Disk Full


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 SQLITE_IOERR, 
SQLITE_FULL happened simultaneously. Once you have received SQLITE_CORRUPT 
once, the database is corrupt and you should no longer use it. Later commands 
may return SQLITE_OK but that just means that they didn't read the corrupt part 
of the database. Somewhere else in the database file it is still corrupt.  I 
confuse that whether disk full will corrupt the database (may be indirectyly). 
Not always ("will"), but sometimes (might). It depends on what SQLite is doing 
when it finds no spare space. However, if this happens 1) The program returned 
SQLITE_IOERR or SQLITE_FULL then crashed 2) you deleted some files to make some 
free space 3) you restart the program 4) the program works without returning 
any other SQLite error SQLite has returned the database to an uncorrupted state 
by rolling back the last transaction before the crash. This cannot happen if 
the error is instead SQLITE_CORRUPT. That result always means that the database 
is corrupt in a way that SQLite will not automatically fix. Simon. 
___ sqlite-users mailing list 
sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database Corrupt While Disk Full

[sanhua.zh]

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 whether disk full willcorrupt the database (may be indirectyly).


Background:
This is happened in iOS. The OS will automatically clean the disk cache to make 
some spacewhile disk free space is low. But disk may still be full while all 
disk cache has been cleaned.

[sqlite] {Spam?} SQLite take lower performance while usingshared cache on iOS/Mac

[sanhua.zh]

Here is the test result for selecting 100,000 items in original test case.


shared cache mode
2015-12-20 21:24:58.714 Test[1126:11609] cost 2.173480
2015-12-20 21:24:58.714 Test[1126:11610] cost 2.173449
2015-12-20 21:24:58.714 Test[1126:11608] cost 2.173768
2015-12-20 21:24:58.714 Test[1126:11611] cost 2.173169


without shared cache mode
2015-12-20 21:28:49.647 Test[1286:13077] cost 0.028914
2015-12-20 21:28:49.647 Test[1286:13078] cost 0.028914
2015-12-20 21:28:49.647 Test[1286:13079] cost 0.028964
2015-12-20 21:28:49.647 Test[1286:13076] cost 0.028958


May be your python code does not run the same thing as mine, I am poor in 
python so that I could not figure it out.





???:E.Pasmapasma10 at concepts.nl
???:SQLite mailing listsqlite-users at mailinglists.sqlite.org
:2015?12?18?(??)?18:29
??:Re: [sqlite] {Spam?} SQLite take lower performance while usingshared cache 
on 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 sqlite 
connection and do the same thing - select all 10 item  from ?test? table.  
  for (int i = 0; i 100; i++) {  .."insert into test values(%d);"..  
... Hello, A rude question: is there any chance that the test in shared cache 
mode was taken with 1.000.000 rows instead of 100.000? Because I have quite 
different results: regular mode: 6.2 seconds cache sharing: 6.35 seconds Thanks 
for posting the program source. I had to use a Python program by lack of 
objective C. I'll include the source too, see below. Another thing: I found 
that sqlite3_open takes significant time when connecting to a shared cache that 
is in use. Therefore the Python test measures the overall elapsed time. Python 
offers a thread.join method to know exactly when a thread is finished. Tnanks, 
E.Pasma import random, os, sys, time, threading, subprocess, socket, socket 
import sqlite3 as sqlite TESTDB='larry.db' SHARED=0 SIZE=2000 print('cache 
sharing', SHARED, 'cache size', SIZE) sqlite.enable_shared_cache(SHARED) def 
connect(): con= sqlite.Connection (TESTDB, isolation_level=None, 
check_same_thread=0) con.execute ("pragma cache_size=%i"%(SIZE,)) return con 
def e_str (e): " format exception as string " return "%s: %s" % 
(e.__class__.__name__, e) class Testthread (threading.Thread): """ execute 
query in a thread """ def __init__ (self, qq, con = None): self.con = con 
self.qq = qq self.out = "thr%i.out" % id (self) open (self.out, 'w').close () 
os.remove (self.out) threading.Thread.__init__ ( self, target=self.__target, ) 
def start (self): threading.Thread.start (self) for retry in range (10): 
time.sleep (2 ** retry * .05) if os.access (self.out, os.R_OK): break else: 
print("Testthread: spoolfile does not appear") time.sleep (.10) # for the SQL 
to start def __target (self): if not self.con: self.con = connect () f = open 
(self.out, 'w') try: try: for q in self.qq.split (';'): for i in 
self.con.execute(q).fetchall(): f.write (str(i)+'\n') except Exception as e: 
f.write (e_str (e) + '\n') finally: f.close() self.con.close () def join (self, 
timeout=None): if timeout is not None: threading.Thread.join (self, timeout) 
else: timeout = 7.5 # respond to keyboard interrupts while self.isAlive (): 
threading.Thread.join (self, timeout) return self def get_result (self): try: 
return open (self.out, 'r').read ().strip () except IOError as e: return None 
def main (): # create some test data ###try: os.remove (TESTDB) ###except 
OSError: pass t0=time.time() con = connect () cur = con.cursor () cur.execute 
("begin") try: cur.execute ("create table test (id integer)") except 
sqlite.OperationalError: pass else: for i in range (100): cur.execute 
("insert into test(id) values(:1)",(i,)) cur.execute ("end") cur.close () 
con.close () print("na insert", time.time()-t0) qq="SELECT COUNT(*) FROM test 
WHERE +id-1" qq+=';'+qq tthh=[] for i in range(4): con = None ###connect() 
tthh.append (Testthread (qq, con)) print("na splits", time.time()-t0) for th in 
tthh: th.start () print("na start", time.time()-t0) for th in tthh: res=th.join 
().get_result().split('\n') assert res[0]=='(100,)', res[0] 
print(time.time()-t0, 'seconds') if __name__ == "__main__": main () 
___ sqlite-users mailing list 
sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite take lower performance while using shared cache on iOS/Mac

[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 sqlite 
connection and do the same thing - select all 10 item from ?test? table.
Strange thing happened.
Each thread ends up within 0.09 seconds without SQLITE_OPEN_SHAREDCACHE, but 
with SQLITE_OPEN_SHAREDCACHE ends up in 14 second, which is much slower.
As sqlite.org said, shared cache mode can reduce the memory and IO, which leads 
to better performance.
Any one can tell me whether I write the wrong code or using shared cache mode 
in an incorrect scene ?


Here is my code mixed by C and Objective-C, but it will not stop your reading 
and understanding:


#import Foundation/Foundation.h
#import sqlite3.h
#import sys/time.h


double now()
{
  timeval now;
  gettimeofday(now, nullptr);
  return now.tv_sec+now.tv_usec/100.0;
}


void showResultCode(int resultCode)
{
  if (resultCode!=SQLITE_DONEresultCode!=SQLITE_OKresultCode!=SQLITE_ROW) {
NSLog(@"unexperted result %d", resultCode);
  }
}


void SQLiteLog(void* userInfo, int ret, const char* msg)
{
  NSLog(@"ret=%d, msg=%s", ret, msg);
}


void write(const char* path)
{
  sqlite3* handle;


  showResultCode(sqlite3_open(path, handle));


  showResultCode(sqlite3_exec(handle, "PRAGMA synchronous=FULL", nullptr, 
nullptr, nullptr));
  showResultCode(sqlite3_exec(handle, "PRAGMA journal_mode=WAL", nullptr, 
nullptr, nullptr));
  showResultCode(sqlite3_exec(handle, "drop table if exists test;", nullptr, 
nullptr, nullptr));
  showResultCode(sqlite3_exec(handle, "create table if not exists test(id 
integer);", nullptr, nullptr, nullptr));


  sqlite3_stmt* stmt = nullptr;
  showResultCode(sqlite3_exec(handle, "BEGIN", nullptr, nullptr, nullptr));
  for (int i = 0; i  100; i++) {
showResultCode(sqlite3_prepare(handle, [NSString stringWithFormat:@"insert 
into test values(%d);", i].UTF8String, -1, stmt, nullptr));
showResultCode(sqlite3_step(stmt));
showResultCode(sqlite3_finalize(stmt));
  }
  showResultCode(sqlite3_exec(handle, "COMMIT", nullptr, nullptr, nullptr));
  showResultCode(sqlite3_close(handle));
}




void read(const char* path)
{
  sqlite3* handle;


  showResultCode(sqlite3_open_v2(path, handle, SQLITE_OPEN_READWRITE | 
SQLITE_OPEN_SHAREDCACHE, nullptr));
//  showResultCode(sqlite3_open_v2(path, handle, SQLITE_OPEN_READWRITE, 
nullptr));
  sqlite3_stmt* stmt;
  showResultCode(sqlite3_prepare(handle, "select * from test;", -1, stmt, 
nullptr));


  double start = now();
  int integer = 0;
  while (sqlite3_step(stmt)!=SQLITE_DONE) {
integer = sqlite3_column_int(stmt, 0);
  }
  NSLog(@"%d", integer);
  showResultCode(sqlite3_finalize(stmt));
  double end = now();
  NSLog(@"cost %f", end-start);


  showResultCode(sqlite3_close(handle));
}


int main(int argc, char * argv[])
{
  sqlite3_config(SQLITE_CONFIG_LOG, SQLiteLog, NULL);
  sqlite3_config(SQLITE_CONFIG_MULTITHREAD);


  const char* path = "/Users/sanhuazhang/Desktop/test.db";


  write(path);
  for (int i = 0; i  4; i++) {
dispatch_queue_t queue = dispatch_queue_create([NSString 
stringWithFormat:@"queue%d", i].UTF8String, DISPATCH_QUEUE_CONCURRENT);
dispatch_async(queue, ^{
  read(path);
});
  }


  sleep(1);
  return 1;
}

[sqlite] 回复： Why SQLITE_BUSY?

[sanhua.zh]

oh, sorry, I make this mistake.


Another question is that if ?sqlite3_prepare? fail, do I need to 
?sqlite3_finalize? the stmt. Here is the sample code,


int ret = sqlite3_prepare(handle, ?some sql?, stmt, ?);
if (ret==SQLITE_OK) {
//step
sqlite3_finalize(stmt);
}else {
//log error
//should I finalize stmt here?
//sqlite3_finalize(stmt);
}





???:Hick Gunterhick at scigames.at
???:'SQLite mailing list'sqlite-users at mailinglists.sqlite.org
:2015?12?15?(??)?17:42
??:Re: [sqlite] ??? Why SQLITE_BUSY?


It looks like you have unfinalized statements in your transaction. You are 
preparing statements inside the loop, but finalizing only 1 (the last) 
statement. And attempting to commit even before finalizing only the last 
statement. So sqlite3_close() is complaining about improper call sequence, not 
interference from any other process/thread. -Urspr?ngliche 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 
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, 
int ret, const char* msg) { NSLog(@"ret=%d, msg=%s", ret, msg); } void 
write(const char* path) { int code = SQLITE_OK; sqlite3* handle; 
showResultCode(sqlite3_open(path, handle)); sqlite3_exec(handle, "PRAGMA 
journal_mode=WAL", nullptr, nullptr, nullptr); sqlite3_exec(handle, "create 
table test(id integer);", nullptr, nullptr, nullptr); sqlite3_stmt* stmt = 
nullptr; showResultCode(sqlite3_exec(handle, "BEGIN IMMEDIATE", nullptr, 
nullptr, nullptr)); for (int i = 0; i 2; i++) { 
showResultCode(sqlite3_prepare(handle, [NSString stringWithFormat:@"insert into 
test values(%d);", i].UTF8String, -1, stmt, nullptr)); 
showResultCode(sqlite3_step(stmt)); } showResultCode(sqlite3_exec(handle, 
"COMMIT", nullptr, nullptr, nullptr)); showResultCode(sqlite3_finalize(stmt)); 
showResultCode(sqlite3_close(handle)); } int main(int argc, char * argv[]) { 
sqlite3_config(SQLITE_CONFIG_LOG, SQLiteLog, NULL); const char* path = 
"/Users/sanhuazhang/Desktop/test.db"; write(path); return 1; } The console 
result is ?unexperted result 5?, which indicates SQLITE_BUSY. It happens at 
code?sqlite3_close?.one of the strange things is that?SQLiteLog? print nothing. 
AndYou can see that I only write some data using transaction. How could 
SQLITE_BUSY happened while sqlite.org said that?The SQLITE_BUSY result code 
indicates that the database file could not be written (or in some cases read) 
because of concurrent activity by some other database connection.?. So, as a 
conclusion, I confuse that why the result code of?sqlite3_close" is SQLITE_BUSY 
and is it possible that SQLITE_BUSY returned by other function in this 
situation(single process,single thread,single connection).  
???:sanhua.zhsanhua.zh at foxmail.com ???:sqlite-userssqlite-users at 
mailinglists.sqlite.org :2015?12?14?(??)?17:21 ??:[sqlite] Why SQLITE_BUSY? 
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 error 
report online. So how did it happen? I guess that, when WAL reach the 
checkpoint, sqlite will write the data back to original db file in background 
thread. So writing will be busy at this time. But I?m not sure. I hope that you 
will not stint your criticism ___ 
sqlite-users mailing list sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 
___ sqlite-users mailing list 
sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 
___ Gunter Hick Software Engineer 
Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, 
A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This 
communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your 
cooperati

[sqlite] 回复： Why SQLITE_BUSY?

[sanhua.zh]

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, int ret, const char* msg)
{
  NSLog(@"ret=%d, msg=%s", ret, msg);
}


void write(const char* path)
{
  int code = SQLITE_OK;
  sqlite3* handle;


  showResultCode(sqlite3_open(path, handle));


  sqlite3_exec(handle, "PRAGMA journal_mode=WAL", nullptr, nullptr, nullptr);
  sqlite3_exec(handle, "create table test(id integer);", nullptr, nullptr, 
nullptr);


  sqlite3_stmt* stmt = nullptr;
  showResultCode(sqlite3_exec(handle, "BEGIN IMMEDIATE", nullptr, nullptr, 
nullptr));
  for (int i = 0; i  2; i++) {
showResultCode(sqlite3_prepare(handle, [NSString stringWithFormat:@"insert 
into test values(%d);", i].UTF8String, -1, stmt, nullptr));
showResultCode(sqlite3_step(stmt));
  }
  showResultCode(sqlite3_exec(handle, "COMMIT", nullptr, nullptr, nullptr));
  showResultCode(sqlite3_finalize(stmt));
  showResultCode(sqlite3_close(handle));
}


int main(int argc, char * argv[])
{
  sqlite3_config(SQLITE_CONFIG_LOG, SQLiteLog, NULL);


  const char* path = "/Users/sanhuazhang/Desktop/test.db";
  write(path);
  return 1;
}


The console result is ?unexperted result 5?, which indicates SQLITE_BUSY. It 
happens at code?sqlite3_close?.one of the strange things is that?SQLiteLog? 
print nothing.
AndYou can see that I only write some data using transaction. How could 
SQLITE_BUSY happened while sqlite.org said that?The SQLITE_BUSY result code 
indicates that the database file could not be written (or in some cases read) 
because of concurrent activity by some other database connection.?.


So, as a conclusion, I confuse that why the result code of?sqlite3_close" is 
SQLITE_BUSY and is it possible that SQLITE_BUSY returned by other function in 
this situation(single process,single thread,single connection).



???:sanhua.zhsanhua.zh at foxmail.com
???:sqlite-userssqlite-users at mailinglists.sqlite.org
:2015?12?14?(??)?17:21
??:[sqlite] Why SQLITE_BUSY?


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 error 
report online. So how did it happen? I guess that, when WAL reach the 
checkpoint, sqlite will write the data back to original db file in background 
thread. So writing will be busy at this time. But I?m not sure. I hope that you 
will not stint your criticism ___ 
sqlite-users mailing list sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why SQLITE_BUSY?

[sanhua.zh]

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 error report online.


So how did it happen?
I guess that, when WAL reach the checkpoint, sqlite will write the data back to 
original db file in background thread. So writing will be busy at this time. 
But I?m not sure.
I hope that you will not stint your criticism

[sqlite] Why Corrupted DB File Cause No Error?

[sanhua.zh]

I try both.
Depending on the data deleting or rewriting, I get 2 empty column, or just 
empty(no column).



???:Richard Hippdrh at sqlite.org
???:SQLite mailing listsqlite-users at mailinglists.sqlite.org
:2015?12?2?(??)?21:31
??:Re: [sqlite] Why Corrupted DB File Cause No Error?


On 12/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 Hipp drh at 
sqlite.org ___ sqlite-users mailing 
list sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why Corrupted DB File Cause No Error?

[sanhua.zh]

I guess so too.
But, It can be happen in real scene.
A part of data may be lost by a low chance accident in the application 
lifecycle. It would be very hard to find out this problem and fix it because of 
causing no error.





???:Simon Slavinslavins at bigfraud.org
???:SQLite mailing 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, some thing like?Error: database disk image is  
malformed?? Is it a bug in SQLite?   because you didn't corrupt a part it 
actually read. Looking at the transcription of the sqlite3.exe sessions 
included in the post, it would appear that the corruption did change the 
result. Therefore the corruption was in a part it actually read. Simon. 
___ sqlite-users mailing list 
sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why Corrupted DB File Cause No Error?

[sanhua.zh]

Thanks for your answer.


But you can see my example, I should get 2 column from testtable, but I get 
none. It means that db is already reading the corrupted part but get nothing.



???:Stephan Bealsgbeal at googlemail.com
???:SQLite mailing listsqlite-users at mailinglists.sqlite.org
: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 part it 
actually read. Imagine if you have a 20GB db and you expect it to report such 
errors when you open the db. It would have to read the whole db to figure that 
out, slowing sqlite to a crawl. Since most dbs are not corrupt, it would be 
slowest for the average case and faster for the error case (since it must stop 
reading on the first error). -- - stephan beal 
http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is 
sloppy. But since tyranny's the only guaranteed byproduct of those who insist 
on a perfect world, freedom will have to do." -- Bigby Wolf 
___ sqlite-users mailing list 
sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why Corrupted DB File Cause No Error?

[sanhua.zh]

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;
autotestmember1|1449050967|0|0|0||
newsapp|1449049422|2|0|0||
sqlite .exit;


console logafter db file corrupted:
sqlite .opentest.db
sqlite select * fromtesttable;
||
||
||
sqlite .exit;


or


sqlite .opentest.db
sqlite select * fromtesttable;
sqlite .exit;


Why it does not show error, some thing like?Error: database disk image is 
malformed?? Is it a bug in SQLite?

[sqlite] Why SQLite take lower performanceinmulti-threadSELECTing?

[sanhua.zh]

I thought it might be storage contention, too.
BUT, as the documentation of SQLite said, in ?DELETE? mode, SELECTing do read 
for disk only.
And I check the source code of SQLite, it uses unix file lock implement mutex.
Will unix file lock keep one reading at one time ? If not, it might be other 
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 foxmail.com wrote:  I change 
my code to make it clear. That is must more easy for me to understand. I can 
think of no other explanation for your problem than storage contention. 
Multiple threads allow lots of processing at the same time but they do not 
allow four things to talk to your disk at the same time. Your disk interface 
can service only one thread at a time. Your threads are having a contest to 
read and write the same part of hard disk, and only one can do this at one 
time. The other three do nothing while that one is keeping the disk busy. So 
making four threads -- or forty threads -- is not going to help. Sorry. Simon. 
___ sqlite-users mailing list 
sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why SQLite take lower performance inmulti-threadSELECTing?

[sanhua.zh]

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 = [self now];


sqlite3_close(handle);


double cost = after-before;
NSLog(@"cost %f begin %f after %f", cost, before, after);
}




1-thread result,
2015-11-02 11:10:04.670 TestSQLite[1663:591258] cost 0.105395 begin 
1446433804.564858 after 1446433804.670253


4-threads result,
2015-11-02 11:10:41.484 TestSQLite[1666:591636] cost 0.150516 begin 
1446433841.333576 after 1446433841.484092
2015-11-02 11:10:41.540 TestSQLite[1666:591635] cost 0.199100 begin 
1446433841.341357 after 1446433841.540457
2015-11-02 11:10:41.540 TestSQLite[1666:591637] cost 0.188056 begin 
1446433841.351582 after 1446433841.539638
2015-11-02 11:10:41.564 TestSQLite[1666:591638] cost 0.166090 begin 
1446433841.397650 after 1446433841.563740





???:Simon Slavinslavins at bigfraud.org
???:SQLite mailing listsqlite-users at mailinglists.sqlite.org
:2015?11?2?(??)?10:47
??:Re: [sqlite] Why SQLite take lower performance inmulti-threadSELECTing?


On 2 Nov 2015, at 2:45am, Simon Slavin slavins at bigfraud.org wrote:  There's 
nothing wrong with your code. Well actually there is (you don't test the values 
returned by all the SQLite calls) but that will not affect what we're 
discussing. Okay, I see how you do this now: you use the log trap to do it. I 
don't know whether that works properly but it should not affect timing either 
way. Simon. ___ sqlite-users 
mailing list sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why SQLite take lower performance inmulti-threadSELECTing?

[sanhua.zh]

I don?t test the returned values because it?s just a test case. And ?sqliteLog' 
function will tell me if it failed.


I compare the same thing in ?DELETE? mode just now.


1-thread result,
2015-11-02 10:53:21.179 TestSQLite[1647:586781]
0: 1446432801.075409, info: NSThread: 0x15de373a0{number = 2, name = (null)} 
start
1: 1446432801.179614, cost 0.104205, info: NSThread: 0x15de373a0{number = 2, 
name = (null)} end


4-threads result,
2015-11-02 10:52:26.908 TestSQLite[1644:586341]
0: 1446432746.741779, info: NSThread: 0x147d4c7b0{number = 5, name = (null)} 
start
1: 1446432746.908137, cost 0.166358, info: NSThread: 0x147d4c7b0{number = 5, 
name = (null)} end
2015-11-02 10:52:26.923 TestSQLite[1644:586346]
0: 1446432746.746527, info: NSThread: 0x147e05230{number = 4, name = (null)} 
start
1: 1446432746.923394, cost 0.176867, info: NSThread: 0x147e05230{number = 4, 
name = (null)} end
2015-11-02 10:52:26.952 TestSQLite[1644:586342]
0: 1446432746.783269, info: NSThread: 0x147e822c0{number = 2, name = (null)} 
start
1: 1446432746.952698, cost 0.169429, info: NSThread: 0x147e822c0{number = 2, 
name = (null)} end
2015-11-02 10:52:26.965 TestSQLite[1644:586343]
0: 1446432746.804017, info: NSThread: 0x147d65b30{number = 3, name = (null)} 
start
1: 1446432746.965503, cost 0.161486, info: NSThread: 0x147d65b30{number = 3, 
name = (null)} end


"4-threads" still take 60-70% slower.





???:Simon Slavinslavins at bigfraud.org
???:SQLite mailing listsqlite-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 and C, but it?s quite simple to understand. There's nothing wrong 
with your code. Well actually there is (you don't test the values returned by 
all the SQLite calls) but that will not affect what we're discussing. Please 
compare times taken by your program when you use 'WAL' mode vs. using 'DELETE' 
mode. Simon. ___ sqlite-users 
mailing list sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why SQLite take lower performance in multi-threadSELECTing?

[sanhua.zh]

it seems that this mail list will filter my code. I re-send it now.
It?s written by Objective-C and C, but it?s quite simple to understand.


void sqliteLog(void* userInfo, int retCode, const char* text)
{
  if (retCode != SQLITE_OK) {
NSLog(@"SQLITE FAILED errCode=%d, errMsg=%s", retCode, text);
  }
}


@implementation TestCase {
  NSString* _path;
  NSUInteger _threadCount;
}


- (id)init
{
  if (self = [super init]) {
NSString* document = 
NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, 
YES)[0];
_path = [document stringByAppendingPathComponent:@"test.sqlite"];


_threadCount = 4;
  }
  return self;
}


- (void)clear
{
  //remove existing DB file
  NSFileManager* fm = [NSFileManager defaultManager];
  NSArray* paths = @[_path, [_path stringByAppendingString:@"-shm"], [_path 
stringByAppendingString:@"-wal"], [_path stringByAppendingString:@"-journal"]];
  for (NSString* path in paths) {
if ([fm fileExistsAtPath:path]) {
  NSLog(@"file exists %@", path);
  NSError* error;
  if (![fm removeItemAtPath:path error:error]) {
NSLog(@"remove error %@", error);
exit(0);
  }
}
  }
}


- (void)prepare
{
  [self clear];


  sqlite3_config(SQLITE_CONFIG_LOG, sqliteLog, NULL);
  sqlite3_config(SQLITE_CONFIG_MULTITHREAD);


  sqlite3* handle;
  sqlite3_open(_path.UTF8String, handle);
  //pragma
  sqlite3_exec(handle, "PRAGMA LOCKING_MODE=NORMAL;", NULL, NULL, NULL);
  sqlite3_exec(handle, "PRAGMA JOURNAL_MODE=WAL;", NULL, NULL, NULL);
  sqlite3_exec(handle, "PRAGMA SYNCHRONOUS=FULL;", NULL, NULL, NULL);
  //create table
  sqlite3_exec(handle, "CREATE TABLE testtable (id INT PRIMARY KEY);", NULL, 
NULL, NULL);
  //pre insert
  sqlite3_exec(handle, "BEGIN", NULL, NULL, NULL);
  sqlite3_stmt* stmt;
  sqlite3_prepare_v2(handle, "INSERT INTO testtable (id) VALUES (?);", -1, 
stmt, NULL);
  for (int i = 0; i  100; i++) {
sqlite3_bind_int(stmt, 1, i);
sqlite3_step(stmt);
sqlite3_reset(stmt);
  }
  sqlite3_exec(handle, "COMMIT", NULL, NULL, NULL);
  sqlite3_finalize(stmt);


  sqlite3_close(handle);
}


- (void)test
{
  [self prepare];


  NSRecursiveLock* lock = [[NSRecursiveLock alloc] init];
  __block NSUInteger threadWait = _threadCount;
  for (int i = 0; i  _threadCount; i++) {
NSString* threadname = [NSString stringWithFormat:@"test_thread_%d", i];
dispatch_async(dispatch_queue_create(threadname.UTF8String, 
DISPATCH_QUEUE_CONCURRENT), ^{
  NSString* start = [NSString stringWithFormat:@"%@ start", [NSThread 
currentThread]];
  NSString* end = [NSString stringWithFormat:@"%@ end", [NSThread 
currentThread]];


  sqlite3* handle;
  sqlite3_open(_path.UTF8String, handle);


  //wait for all thread
  [lock lock];
  threadWait--;
  [lock unlock];
  while (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 listsqlite-users at mailinglists.sqlite.org
:2015?11?1?(??)?03:45
??:Re: [sqlite] Why SQLite take lower performance in multi-threadSELECTing?


On 10/30/15, sanhua.zh sanhua.zh at foxmail.com wrote:  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-thread is take 
almost twice slower than 1-thread.  I know multi-thread might costs the system 
resource, but this result is much  slower than what I excepted.  Is there some 
race condition in SQLite or I write the wrong code ?  You *might* be doing 
something wrong. It is hard to say without seeing your code. But the 2x 
slowdown might be entirely due to thread contention and mutexing. Remember that 
the flash memory filesystem on iOS has finite bandwidth, and all the threads in 
the world will not increase that bandwidth. The extra threads just cause extra 
overhead which ends up slowing everything down. -- D. Richard Hipp drh at 
sqlite.org ___ sqlite-users mailing 
list sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why SQLite take lower performance in multi-thread SELECTing?

[sanhua.zh]

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-thread is take almost twice slower than 1-thread.
I know multi-thread might costs the system resource, but this result is much 
slower than what I excepted.
Is there some race condition in SQLite or I write the wrong code ?


Source Code for this test case is in the attachment.

[sqlite] How to customize my own wal-index using heap memory?

[sanhua.zh]

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 process, the wal-index can be implemented using heap memory instead of 
true shared memory.


I may customize my ownxShmMap, xShmLock, xShmBarrier, and xShmUnmap methods to 
implement thewal-index using heap memory. And all these methods are in 
sqlite3_io_methods struct, BUT is there any C interface to set up those?

[sqlite] Is it possible that keep SQLite in normal lockingmodewithout mmap?

[sanhua.zh]

It?s a mistake ofexpression.
I mean,
1. readconcurrently indifferent threads.
or
2. write in one thread andread inother threads.
noconcurrently writing happened.


Sorry about this. I come from a not-English-speaking country, it's hard for me 
to express myself.



???:Simon Slavinslavins 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-reading-and-writing, not serial It is not possible for two 
different operations, whether different threads or different processes, to 
write to a database at the same time. No matter what system you use this just 
isn't possible. To prove this to yourself, imagine that you had a table with a 
UNIQUE index, and that two threads/processes tried to write rows with the same 
key. The correct way for the API to respond would be to allow one to write its 
row, and to return an error to the other. This cannot happen if the system 
accepts both write requests at the same time. Writing to a database /must/ be 
serial. There is a work-around for this which involve writing new rows by 
appending the data to a text file (entirely outside SQL) and having another 
process read its way through the text file updating the database file. 
Appending to a text file is faster than updating a SQLite database. There are 
other work-arounds too. But they all involve writing new data to some place 
which is not the live database. Simon. 
___ sqlite-users mailing list 
sqlite-users at mailinglists.sqlite.org 
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is it possible that keep SQLite in normal locking modewithout mmap?

[sanhua.zh]

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 may increase 
theprobability of database corruption. While my app has a huge number of 
users(more than ten-million), I have to consider more about robustness.
see also:
http://www.sqlite.org/wal.html#noshm
http://www.sqlite.org/pragma.html#pragma_locking_mode


As a conclusion, I wonder, in SQLite, whether is possible for
1. WAL mode
2. multithread-reading-and-writing, not serial
3. without mmap
If yes, can you tell me a general idea for implementing this. No matter it 
needs to modify the source code or how difficult it is.


Thank you for your time and I appreciate your reply.