[sqlite] sqlite3 corruption (maybe a resend)
Seeking some help/pointers on how one of the production databases at one of our customers got corrupted. The customer started with 3.6 and upgraded to 3.7 on windows. Attached: a) output of pragma quick_check b) pretty print of the first page c) pretty print of the corrupted page I used content in http://www.sqlite.org/fileformat2.html to write the pretty print utility. The (db_pages * page_size) matches the file size shown by ‘ls –l’. Any thoughts/hypothesis much appreciated. Regards --sharad a) OUTPUT OF PRAGMA QUICK_CHECK sqlite> pragma quick_check; *** in database main *** Page 1331646: btreeInitPage() returns error code 11 On tree page 1331638 cell 17: Child page depth differs On tree page 1331647 cell 44: 4 of 4 pages missing from overflow list starting at -2112224253 On tree page 1331647 cell 52: invalid page number 909014121 On tree page 1331647 cell 53: 7 of 7 pages missing from overflow list starting at -39619475 On tree page 1331647 cell 54: invalid page number 1811978616 On tree page 1331647 cell 65: 7 of 7 pages missing from overflow list starting at -39619475 Corruption detected in cell 2 on page 1331647 Corruption detected in cell 39 on page 1331647 Corruption detected in cell 40 on page 1331647 Corruption detected in cell 42 on page 1331647 Corruption detected in cell 43 on page 1331647 Corruption detected in cell 44 on page 1331647 Corruption detected in cell 45 on page 1331647 Corruption detected in cell 46 on page 1331647 Corruption detected in cell 47 on page 1331647 Corruption detected in cell 48 on page 1331647 Corruption detected in cell 49 on page 1331647 Corruption detected in cell 51 on page 1331647 Corruption detected in cell 52 on page 1331647 Corruption detected in cell 53 on page 1331647 Corruption detected in cell 54 on page 1331647 Corruption detected in cell 55 on page 1331647 Corruption detected in cell 56 on page 1331647 Corruption detected in cell 57 on page 1331647 Corruption detected in cell 58 on page 1331647 Corruption detected in cell 59 on page 1331647 Corruption detected in cell 60 on page 1331647 Corruption detected in cell 61 on page 1331647 Corruption detected in cell 63 on page 1331647 Corruption detected in cell 64 on page 1331647 Corruption detected in cell 65 on page 1331647 Multiple uses for byte 88 of page 1331647 On tree page 1331638 cell 18: Child page depth differs b) PRETTY PRINT OF THE FIRST PAGE header = SQLite format 3 page_size = 1024 write_version = 1 read_version = 1 reserved = 0 max_fraction = 64 min_fraction = 32 leaf_fraction = 32 fc_counter = 119344 db_pages = 1392744 freelist_trunk_page = 1392693 freelist_pages = 2 schema_cookie = ea schema_format = 1 default_cache_size = 0 largest_root_btree_page = 0 encoding = 1 user_version = 0 incremental_vaccum = 0 future[0] = 0 future[1] = 0 future[2] = 0 future[3] = 0 future[4] = 0 future[5] = 0 version_valid_for = 119344 sqlite_version = 3007000 btree_type = 5 (interior table node) freeblock_offset = 0 numcells = 7 cellcontent_offset = 989 frag_freebytes = 0 rptr = 36 (0x24) cell[0]=1019 cell[1]=1014 cell[2]=1009 cell[3]=1004 cell[4]=999 cell[5]=994 cell[6]=989 c) PRETTY PRINT OF PAGE 1331647 btree_type = 10 (leaf index node) freeblock_offset = 0 numcells = 66 cellcontent_offset = 199 frag_freebytes = 0 cell[0]=199 cell[1]=223 cell[2]=5207 cell[3]=279 cell[4]=306 cell[5]=327 cell[6]=352 cell[7]=373 cell[8]=397 cell[9]=421 cell[10]=445 cell[11]=469 cell[12]=494 cell[13]=518 cell[14]=542 cell[15]=566 cell[16]=590 cell[17]=614 cell[18]=638 cell[19]=662 cell[20]=686 cell[21]=711 cell[22]=735 cell[23]=759 cell[24]=783 cell[25]=807 cell[26]=831 cell[27]=855 cell[28]=879 cell[29]=903 cell[30]=928 cell[31]=952 cell[32]=976 cell[33]=1000 cell[34]=928 cell[35]=952 cell[36]=976 cell[37]=1000 cell[38]=88 cell[39]=1027 cell[40]=5892 cell[41]=805 cell[42]=1024 cell[43]=64931 cell[44]=21581 cell[45]=12336 cell[46]=12336 cell[47]=12311 cell[48]=1027 cell[49]=9476 cell[50]=253 cell[51]=41840 cell[52]=24438 cell[53]=25970 cell[54]=26982 cell[55]=31022 cell[56]=27759 cell[57]=26368 cell[58]=39288 cell[59]=12055 cell[60]=1027 cell[61]=9476 cell[62]=253 cell[63]=41808 cell[64]=24406 cell[65]=25970 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FW: core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond Take 2
I can't reply in my system, so I create the problem description again.
I miss one source code line "char tempString[1024];"in the last email. The
code dump happened after 4 days' run in a test script not immediately. The
SQLITE statements seem to be ok. Could be a performance issue?
ChingChang
The source code is shown as below,
char tempString[1024];
vector dbStatements;
dbStatements.push_back( "BEGIN TRANSACTION;" );
for ( int x = 0; x < 10; x++ ) {
sprintf( tempString,
"update utilization_table set utilization=%5.2f,sample=%d where
slot='0' and device='cavium' and resource='bus' and sample='%d';",
ntohd(msg->bus_util[x]),
x,
x );
dbStatements.push_back( tempString );
sprintf( tempString,
"update utilization_table set utilization=%5.2f,sample=%d where
slot='0' and device='cavium' and resource='icache' and sample='%d';",
100.00-ntohd(msg->inst_hit_rate[x]), // Convert to misses
x,
x );
dbStatements.push_back( tempString );
sprintf( tempString,
"update utilization_table set utilization=%5.2f,sample=%d where
slot='0' and device='cavium' and resource='dcache' and sample='%d';",
100.00-ntohd(msg->data_hit_rate[x]), // Convert to misses
x,
x );
dbStatements.push_back( tempString );
}
dbStatements.push_back( "COMMIT;" );
// populate the DB
vector::iterator dbStatementsIter;
SqlQuery oper_db(operDatabase, __FILE__, __LINE__);
for ( dbStatementsIter = dbStatements.begin(); dbStatementsIter !=
dbStatements.end(); dbStatementsIter++ ) {
oper_db.execw( *(dbStatementsIter) );
}
dbStatements.clear();
The core dump is shown as below.
#0 0x32e94b04 in raise () from /lib/libc.so.6
#1 0x32e962f4 in abort () from /lib/libc.so.6
#2 0x32e8c2a4 in __assert_fail () from /lib/libc.so.6
#3 0x32ae60cc in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
#4 0x32b4c324 in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
#5 0x32ba12c0 in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
#6 0x32b7926c in sqlite3_step () from /ovn/lib/libsqlite3.mgmt-crd.so
#7 0x32b7a2c4 in sqlite3_exec () from /ovn/lib/libsqlite3.mgmt-crd.so
#8 0x329a9630 in SqlQuery::execw () from /ovn/lib/libPlatform.so
#9 0x329a98e8 in SqlQuery::execw () from /ovn/lib/libPlatform.so
#10 0x10010290 in NpuMessageHandler::processUtilReport (this=, msg=,
nbytes=) at cavium_driver.cpp:1387
#11 0x10012808 in NpuMessageHandler::run (this=0x38be1008) at
cavium_driver.cpp:954
#12 0x328a65b0 in Thread::start_thread () from /ovn/lib/libCommon.mgmt-crd.so
#13 0x3278b5cc in ?? () from /lib/libpthread.so.0
#14 0x32f39b88 in clone () from /lib/libc.so.6
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond
Hello ChingChang,
How big is tempString? What kind of type is it? I wonder if you're
trashing the stack with the sprintf.
What happens if you replace all the sprinfs with some simple inserts
inserted directly into vector?
dbStatements.push_back("INSERT INTO...");
You need to simplify your test till you figure out what's wrong.
C
Tuesday, November 1, 2011, 7:09:09 PM, you wrote:
CH> Do you know why it goes to core dump?
CH> ChingChang
CH> The source code is shown as below,
CH> vector dbStatements;
CH> dbStatements.push_back( "BEGIN TRANSACTION;" );
CH> for ( int x = 0; x < 10; x++ ) {
CH> sprintf( tempString,
CH> "update utilization_table set
CH> utilization=%5.2f,sample=%d where slot='0' and device='cavium' and
CH> resource='bus' and sample='%d';",
CH> ntohd(msg->bus_util[x]),
CH> x,
CH> x );
CH> dbStatements.push_back( tempString );
CH> sprintf( tempString,
CH> "update utilization_table set
CH> utilization=%5.2f,sample=%d where slot='0' and device='cavium' and
CH> resource='icache' and sample='%d';",
CH> 100.00-ntohd(msg->inst_hit_rate[x]), // Convert to misses
CH> x,
CH> x );
CH> dbStatements.push_back( tempString );
CH> sprintf( tempString,
CH> "update utilization_table set
CH> utilization=%5.2f,sample=%d where slot='0' and device='cavium' and
CH> resource='dcache' and sample='%d';",
CH> 100.00-ntohd(msg->data_hit_rate[x]), // Convert to misses
CH> x,
CH> x );
CH> dbStatements.push_back( tempString );
CH> }
CH> dbStatements.push_back( "COMMIT;" );
CH> // populate the DB
CH> vector::iterator dbStatementsIter;
CH> SqlQuery oper_db(operDatabase, __FILE__, __LINE__);
CH> for ( dbStatementsIter = dbStatements.begin(); dbStatementsIter
CH> != dbStatements.end(); dbStatementsIter++ ) {
CH> oper_db.execw( *(dbStatementsIter) );
CH> }
CH> dbStatements.clear();
CH> The core dump is shown as below.
CH> #0 0x32e94b04 in raise () from /lib/libc.so.6
CH> #1 0x32e962f4 in abort () from /lib/libc.so.6
CH> #2 0x32e8c2a4 in __assert_fail () from /lib/libc.so.6
CH> #3 0x32ae60cc in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
CH> #4 0x32b4c324 in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
CH> #5 0x32ba12c0 in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
CH> #6 0x32b7926c in sqlite3_step () from /ovn/lib/libsqlite3.mgmt-crd.so
CH> #7 0x32b7a2c4 in sqlite3_exec () from /ovn/lib/libsqlite3.mgmt-crd.so
CH> #8 0x329a9630 in SqlQuery::execw () from /ovn/lib/libPlatform.so
CH> #9 0x329a98e8 in SqlQuery::execw () from /ovn/lib/libPlatform.so
CH> #10 0x10010290 in NpuMessageHandler::processUtilReport
CH> (this=, msg=,
CH> nbytes=) at cavium_driver.cpp:1387
CH> #11 0x10012808 in NpuMessageHandler::run (this=0x38be1008) at
cavium_driver.cpp:954
CH> #12 0x328a65b0 in Thread::start_thread () from
/ovn/lib/libCommon.mgmt-crd.so
CH> #13 0x3278b5cc in ?? () from /lib/libpthread.so.0
CH> #14 0x32f39b88 in clone () from /lib/libc.so.6
CH> ___
CH> sqlite-users mailing list
CH> sqlite-users@sqlite.org
CH> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Best regards,
Tegmailto:t...@djii.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VFS minor issue with xDelete method return value
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 31/10/11 09:25, Всеволод Новиков wrote: > I am using sqlite 3.7.8. > > My custom VFS xDelete method returned SQLITE_NOTFOUND in a case when > the file to be deleted was not found, and this worked fine for older > versions (probably prior to 3.7.x where the write ahead log was > introduced), particularly for 3.6.10. This was a backwards incompatible change made in SQLite 3.7.8. I did report the issue to the developers before its release, but they took no action and chose not to document it. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk6wj8gACgkQmOOfHg372QR0QgCgkXpLgUMZ2lQIY68VJSKwJTbc EdUAnjE26wC4sYNKIWTm2jtYngmmUr1D =GsrC -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond
On 1 Nov 2011, at 11:09pm, ChingChang Hsiao wrote:
> dbStatements.push_back( "COMMIT;" );
>
> // populate the DB
> vector::iterator dbStatementsIter;
> SqlQuery oper_db(operDatabase, __FILE__, __LINE__);
> for ( dbStatementsIter = dbStatements.begin(); dbStatementsIter !=
> dbStatements.end(); dbStatementsIter++ ) {
>oper_db.execw( *(dbStatementsIter) );
> }
For debugging purposes, please log the value of
dbStatements.length()
before the loop starts, then check to see how many statements are executed
before you get a core dump. It would be very useful to know, for example,
whether your code always crashes at the first 'UPDATE', or at the 'COMMIT'.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] does sqlite3_reset have a performance impact on queries?
On Tue, Nov 1, 2011 at 6:50 PM, Tal Tabakman wrote:
> Yes!, now it flys.
> can you please elaborate ? did originally reset dumped all data to disk ?
> suppose that I open a db for read only, is it recommended to issue the
> "BEGIN" command at the start of analysis ?
>
When you didn't reset the first statement, that held the transaction open,
causing things to run faster.
There is a lot of overhead associated with starting a transaction. It is
best to do multiple operations within the same transaction whenever
possible.
> thanks in advance
> Tal
> On Wed, Nov 2, 2011 at 12:33 AM, Richard Hipp wrote:
>
> > On Tue, Nov 1, 2011 at 6:25 PM, Tal Tabakman
> > wrote:
> >
> > > I have a slowness problem when running sqlite3 based application.
> > > I have a small db (500 K on disk, only 1 rows) and I have a loop
> that
> > > perform 1 selections according to
> > > the primary key of a certain table.
> > > for some reason in takes 15-20 seconds to run the loop below.
> > > by experimentation I found that this issue is related to the use of
> > > sqlite3_reset. i.e, by creating a new statement in every loop iteration
> > > without reseting and reusing it I got good performance (took a
> > > second,needless to say that I want to avoid this since it causes mem
> > > leaks.)
> > > any idea why sqlite3_reset has such a bad performance impact on my
> > program
> > > ?
> > >
> >
> > Please try the change below and let us know how it helps.
> >
> >
> > >
> > > int main() {
> > >sqlite3 * handle;
> > >sqlite3_stmt * m_entrySelectSnumStmt;
> > >sqlite3_open("entries.db",&handle);
> > >std::ostringstream query;
> > >query << "SELECT * FROM entries where SNUM = ? LIMIT 1;";
> > >sqlite3_prepare_v2(handle, query.str().c_str(),
> > > query.str().length()+1, &m_entrySelectSnumStmt, 0);
> > >
> >
> > sqlite3_exec(handle, "BEGIN", 0, 0, 0);
> >
> >
> > >for(int i = 0; i < 1; i ++){
> > > sqlite3_bind_int(m_entrySelectSnumStmt,1,i);
> > > int rc = sqlite3_step(m_entrySelectSnumStmt);
> > > sqlite3_clear_bindings(m_entrySelectSnumStmt);
> > > sqlite3_reset(m_entrySelectSnumStmt);
> > >}
> > >
> >
> > sqlite3_exec(handle, "COMMIT", 0, 0, 0);
> >
> >
> >
> > > }
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond
Do you know why it goes to core dump?
ChingChang
The source code is shown as below,
vector dbStatements;
dbStatements.push_back( "BEGIN TRANSACTION;" );
for ( int x = 0; x < 10; x++ ) {
sprintf( tempString,
"update utilization_table set utilization=%5.2f,sample=%d where
slot='0' and device='cavium' and resource='bus' and sample='%d';",
ntohd(msg->bus_util[x]),
x,
x );
dbStatements.push_back( tempString );
sprintf( tempString,
"update utilization_table set utilization=%5.2f,sample=%d where
slot='0' and device='cavium' and resource='icache' and sample='%d';",
100.00-ntohd(msg->inst_hit_rate[x]), // Convert to misses
x,
x );
dbStatements.push_back( tempString );
sprintf( tempString,
"update utilization_table set utilization=%5.2f,sample=%d where
slot='0' and device='cavium' and resource='dcache' and sample='%d';",
100.00-ntohd(msg->data_hit_rate[x]), // Convert to misses
x,
x );
dbStatements.push_back( tempString );
}
dbStatements.push_back( "COMMIT;" );
// populate the DB
vector::iterator dbStatementsIter;
SqlQuery oper_db(operDatabase, __FILE__, __LINE__);
for ( dbStatementsIter = dbStatements.begin(); dbStatementsIter !=
dbStatements.end(); dbStatementsIter++ ) {
oper_db.execw( *(dbStatementsIter) );
}
dbStatements.clear();
The core dump is shown as below.
#0 0x32e94b04 in raise () from /lib/libc.so.6
#1 0x32e962f4 in abort () from /lib/libc.so.6
#2 0x32e8c2a4 in __assert_fail () from /lib/libc.so.6
#3 0x32ae60cc in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
#4 0x32b4c324 in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
#5 0x32ba12c0 in ?? () from /ovn/lib/libsqlite3.mgmt-crd.so
#6 0x32b7926c in sqlite3_step () from /ovn/lib/libsqlite3.mgmt-crd.so
#7 0x32b7a2c4 in sqlite3_exec () from /ovn/lib/libsqlite3.mgmt-crd.so
#8 0x329a9630 in SqlQuery::execw () from /ovn/lib/libPlatform.so
#9 0x329a98e8 in SqlQuery::execw () from /ovn/lib/libPlatform.so
#10 0x10010290 in NpuMessageHandler::processUtilReport (this=, msg=,
nbytes=) at cavium_driver.cpp:1387
#11 0x10012808 in NpuMessageHandler::run (this=0x38be1008) at
cavium_driver.cpp:954
#12 0x328a65b0 in Thread::start_thread () from /ovn/lib/libCommon.mgmt-crd.so
#13 0x3278b5cc in ?? () from /lib/libpthread.so.0
#14 0x32f39b88 in clone () from /lib/libc.so.6
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] does sqlite3_reset have a performance impact on queries?
Yes!, now it flys.
can you please elaborate ? did originally reset dumped all data to disk ?
suppose that I open a db for read only, is it recommended to issue the
"BEGIN" command at the start of analysis ?
thanks in advance
Tal
On Wed, Nov 2, 2011 at 12:33 AM, Richard Hipp wrote:
> On Tue, Nov 1, 2011 at 6:25 PM, Tal Tabakman
> wrote:
>
> > I have a slowness problem when running sqlite3 based application.
> > I have a small db (500 K on disk, only 1 rows) and I have a loop that
> > perform 1 selections according to
> > the primary key of a certain table.
> > for some reason in takes 15-20 seconds to run the loop below.
> > by experimentation I found that this issue is related to the use of
> > sqlite3_reset. i.e, by creating a new statement in every loop iteration
> > without reseting and reusing it I got good performance (took a
> > second,needless to say that I want to avoid this since it causes mem
> > leaks.)
> > any idea why sqlite3_reset has such a bad performance impact on my
> program
> > ?
> >
>
> Please try the change below and let us know how it helps.
>
>
> >
> > int main() {
> >sqlite3 * handle;
> >sqlite3_stmt * m_entrySelectSnumStmt;
> >sqlite3_open("entries.db",&handle);
> >std::ostringstream query;
> >query << "SELECT * FROM entries where SNUM = ? LIMIT 1;";
> >sqlite3_prepare_v2(handle, query.str().c_str(),
> > query.str().length()+1, &m_entrySelectSnumStmt, 0);
> >
>
> sqlite3_exec(handle, "BEGIN", 0, 0, 0);
>
>
> >for(int i = 0; i < 1; i ++){
> > sqlite3_bind_int(m_entrySelectSnumStmt,1,i);
> > int rc = sqlite3_step(m_entrySelectSnumStmt);
> > sqlite3_clear_bindings(m_entrySelectSnumStmt);
> > sqlite3_reset(m_entrySelectSnumStmt);
> >}
> >
>
> sqlite3_exec(handle, "COMMIT", 0, 0, 0);
>
>
>
> > }
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] does sqlite3_reset have a performance impact on queries?
On Tue, Nov 1, 2011 at 6:25 PM, Tal Tabakman wrote:
> I have a slowness problem when running sqlite3 based application.
> I have a small db (500 K on disk, only 1 rows) and I have a loop that
> perform 1 selections according to
> the primary key of a certain table.
> for some reason in takes 15-20 seconds to run the loop below.
> by experimentation I found that this issue is related to the use of
> sqlite3_reset. i.e, by creating a new statement in every loop iteration
> without reseting and reusing it I got good performance (took a
> second,needless to say that I want to avoid this since it causes mem
> leaks.)
> any idea why sqlite3_reset has such a bad performance impact on my program
> ?
>
Please try the change below and let us know how it helps.
>
> int main() {
>sqlite3 * handle;
>sqlite3_stmt * m_entrySelectSnumStmt;
>sqlite3_open("entries.db",&handle);
>std::ostringstream query;
>query << "SELECT * FROM entries where SNUM = ? LIMIT 1;";
>sqlite3_prepare_v2(handle, query.str().c_str(),
> query.str().length()+1, &m_entrySelectSnumStmt, 0);
>
sqlite3_exec(handle, "BEGIN", 0, 0, 0);
>for(int i = 0; i < 1; i ++){
> sqlite3_bind_int(m_entrySelectSnumStmt,1,i);
> int rc = sqlite3_step(m_entrySelectSnumStmt);
> sqlite3_clear_bindings(m_entrySelectSnumStmt);
> sqlite3_reset(m_entrySelectSnumStmt);
>}
>
sqlite3_exec(handle, "COMMIT", 0, 0, 0);
> }
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] does sqlite3_reset have a performance impact on queries?
I have a slowness problem when running sqlite3 based application.
I have a small db (500 K on disk, only 1 rows) and I have a loop that
perform 1 selections according to
the primary key of a certain table.
for some reason in takes 15-20 seconds to run the loop below.
by experimentation I found that this issue is related to the use of
sqlite3_reset. i.e, by creating a new statement in every loop iteration
without reseting and reusing it I got good performance (took a
second,needless to say that I want to avoid this since it causes mem leaks.)
any idea why sqlite3_reset has such a bad performance impact on my program ?
int main() {
sqlite3 * handle;
sqlite3_stmt * m_entrySelectSnumStmt;
sqlite3_open("entries.db",&handle);
std::ostringstream query;
query << "SELECT * FROM entries where SNUM = ? LIMIT 1;";
sqlite3_prepare_v2(handle, query.str().c_str(),
query.str().length()+1, &m_entrySelectSnumStmt, 0);
for(int i = 0; i < 1; i ++){
sqlite3_bind_int(m_entrySelectSnumStmt,1,i);
int rc = sqlite3_step(m_entrySelectSnumStmt);
sqlite3_clear_bindings(m_entrySelectSnumStmt);
sqlite3_reset(m_entrySelectSnumStmt);
}
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disk I/O Error
On Tue, Nov 1, 2011 at 3:09 PM, Kyle McKay wrote: > "If write() is interrupted by a signal after it successfully writes some > data, it shall return the number of bytes written." > > and it doesn't appear that SQLite currently permits that behavior either > with or without the above patch. > See http://www.sqlite.org/src/artifact/ddda0b1c5a?ln=3104-3108 > -- > D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disk I/O Error
On November 1, 2011 08:48:25 PDT, Richard Hipp wrote: On Tue, Nov 1, 2011 at 10:40 AM, Korey Calmettes >wrote: When we are able to reproduce the problem again, I will run these checks. It's fairly random however consistent. I will e-mail the results to you later today. If you are able, please recompile using the version of SQLite here: http://www.sqlite.org/src/ci/72256634773f?sbs=1 which includes a patch that will retry read() system calls that return fewer bytes than expected. Let us know if this clears the problem. Note that this patch is on a branch and probably won't go onto the trunk unless we hear back positive results from you. It's not just read system calls that are allowed to do that. The POSIX spec. for read is at: http://pubs.opengroup.org/onlinepubs/009695399/functions/read.html and says: "If a read() is interrupted by a signal after it has successfully read some data, it shall return the number of bytes read." which the above patch handles. However, the POSIX spec. for write is at: http://pubs.opengroup.org/onlinepubs/007904975/functions/write.html and it says basically the same thing: "If write() is interrupted by a signal after it successfully writes some data, it shall return the number of bytes written." and it doesn't appear that SQLite currently permits that behavior either with or without the above patch. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disk I/O Error
On 11/02/2011 12:37 AM, Korey Calmettes wrote: I don't think the file is truncated. What is the size of the file on disk? Interesting stuff here when I run pragma integrity_check: /data # sqlite3 test.db "pragma integrity_check;" *** in database main *** Page 480: unable to get the page. error code=522 Page 481: unable to get the page. error code=522 Page 482: unable to get the page. error code=522 On tree page 32 cell 3: Child page depth differs Page 485: unable to get the page. error code=522 On tree page 32 cell 5: Child page depth differs On tree page 32 cell 6: Child page depth differs Page 487: unable to get the page. error code=522 On tree page 32 cell 7: Child page depth differs On tree page 32 cell 8: Child page depth differs Page 493: unable to get the page. error code=522 On tree page 32 cell 13: Child page depth differs On tree page 32 cell 14: Child page depth differs Page 495: unable to get the page. error code=522 On tree page 32 cell 15: Child page depth differs On tree page 32 cell 16: Child page depth differs Page 505: unable to get the page. error code=522 On tree page 45 cell 4: Child page depth differs On tree page 45 cell 5: Child page depth differs Page 421: unable to get the page. error code=522 On tree page 15 cell 1: Child page depth differs Page 422: unable to get the page. error code=522 On tree page 15 cell 3: Child page depth differs Page 424: unable to get the page. error code=522 On tree page 15 cell 4: Child page depth differs On tree page 15 cell 5: Child page depth differs Page 49: unable to get the page. error code=522 Page 33: unable to get the page. error code=522 Page 34: unable to get the page. error code=522 Page 472: unable to get the page. error code=522 Page 473: unable to get the page. error code=522 Page 474: unable to get the page. error code=522 Page 475: unable to get the page. error code=522 Page 476: unable to get the page. error code=522 Page 477: unable to get the page. error code=522 Page 478: unable to get the page. error code=522 Page 479: unable to get the page. error code=522 Error: disk I/O error Also, the page size is 1024. Korey -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: October 31, 2011 2:32 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Disk I/O Error On Mon, Oct 31, 2011 at 5:01 PM, Korey Calmettes wrote: Hello, We are having a random problem with our system that has be puzzled at this point. First a little background. We have an embedded system running an ARM processor and JFFS file system. We are running 3.7.7.1. I e-mailed about a month ago about enabling WAL on our system. Found out that I needed to set SQLITE_SHM_DIRECTORY to our tmpfs file system when compiling as JFFS doesn't support shared mmap files. This has been working fine, however throughout our testing, we have been hitting a problem. It seems that some tables are no longer accessible giving a Disk I/O Error. I logged into the command line interface and replicated the problem. I ran ".log stdout" and here are the results. sqlite> select password from auth; (522) statement aborts at 7: [select password from auth;] Error: disk I/O error However access to other tables will work without any problems. Error code 522 is SQLITE_IOERR_SHORT_READ. It is generated here: http://www.sqlite.org/src/artifact/07acbb3e074e?ln=3012 SQLite was trying to read N bytes and got back M byes where M>0 and M I searched for how to read the log without success. I am hopeful that someone would be able to tell me what's going on. Korey ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Referencing column aliases
On 1 Nov 2011, at 4:44pm, Pete wrote: > Since SQLite already provides an extension to reference column aliases in > WHERE and JOIN clauses, is there any likelihood that it might be further > extended to allow them in the SELECT statement itself? That would require processing the SELECT statement before the SELECT statement was processed. For instance, how would you handle this: SELECT (itemPrice * numItems) AS totalPrice, (totalPrice * exchangeRate) AS myCcyPrice FROM invoiceItems; It would change a SELECT command to something which required a great deal of intelligence and code to work out which parts should be done first. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disk I/O Error
Please copy the database file to a workstation and try "PRAGMA integrity_check" on the work station. This will help us to decide if the problem is with the SQLite code (or JFFS) or a if it is a corrupt database. On Tue, Nov 1, 2011 at 1:37 PM, Korey Calmettes wrote: > I don't think the file is truncated. > > Interesting stuff here when I run pragma integrity_check: > > /data # sqlite3 test.db "pragma integrity_check;" > *** in database main *** > Page 480: unable to get the page. error code=522 > Page 481: unable to get the page. error code=522 > Page 482: unable to get the page. error code=522 > On tree page 32 cell 3: Child page depth differs > Page 485: unable to get the page. error code=522 > On tree page 32 cell 5: Child page depth differs > On tree page 32 cell 6: Child page depth differs > Page 487: unable to get the page. error code=522 > On tree page 32 cell 7: Child page depth differs > On tree page 32 cell 8: Child page depth differs > Page 493: unable to get the page. error code=522 > On tree page 32 cell 13: Child page depth differs > On tree page 32 cell 14: Child page depth differs > Page 495: unable to get the page. error code=522 > On tree page 32 cell 15: Child page depth differs > On tree page 32 cell 16: Child page depth differs > Page 505: unable to get the page. error code=522 > On tree page 45 cell 4: Child page depth differs > On tree page 45 cell 5: Child page depth differs > Page 421: unable to get the page. error code=522 > On tree page 15 cell 1: Child page depth differs > Page 422: unable to get the page. error code=522 > On tree page 15 cell 3: Child page depth differs > Page 424: unable to get the page. error code=522 > On tree page 15 cell 4: Child page depth differs > On tree page 15 cell 5: Child page depth differs > Page 49: unable to get the page. error code=522 > Page 33: unable to get the page. error code=522 > Page 34: unable to get the page. error code=522 > Page 472: unable to get the page. error code=522 > Page 473: unable to get the page. error code=522 > Page 474: unable to get the page. error code=522 > Page 475: unable to get the page. error code=522 > Page 476: unable to get the page. error code=522 > Page 477: unable to get the page. error code=522 > Page 478: unable to get the page. error code=522 > Page 479: unable to get the page. error code=522 > Error: disk I/O error > > Also, the page size is 1024. > > Korey > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp > Sent: October 31, 2011 2:32 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Disk I/O Error > > On Mon, Oct 31, 2011 at 5:01 PM, Korey Calmettes > wrote: > > > Hello, > > > > We are having a random problem with our system that has be puzzled at > > this point. > > > > First a little background. We have an embedded system running an ARM > > processor and JFFS file system. We are running 3.7.7.1. I e-mailed > > about a month ago about enabling WAL on our system. Found out that I > > needed to set SQLITE_SHM_DIRECTORY to our tmpfs file system when > > compiling as JFFS doesn't support shared mmap files. > > > > This has been working fine, however throughout our testing, we have > > been hitting a problem. It seems that some tables are no longer > > accessible giving a Disk I/O Error. I logged into the command line > > interface and replicated the problem. I ran ".log stdout" and here > are the results. > > > > sqlite> select password from auth; > > (522) statement aborts at 7: [select password from auth;] > > Error: disk I/O error > > > > However access to other tables will work without any problems. > > > > Error code 522 is SQLITE_IOERR_SHORT_READ. It is generated here: > > http://www.sqlite.org/src/artifact/07acbb3e074e?ln=3012 > > SQLite was trying to read N bytes and got back M byes where M>0 and M > Perchance is your database file truncated? What happens when you run > "PRAGMA integrity_check" on the database? What is the page size of your > database? > > > > > > > I searched for how to read the log without success. I am hopeful that > > > someone would be able to tell me what's going on. > > > > Korey > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disk I/O Error
I don't think the file is truncated. Interesting stuff here when I run pragma integrity_check: /data # sqlite3 test.db "pragma integrity_check;" *** in database main *** Page 480: unable to get the page. error code=522 Page 481: unable to get the page. error code=522 Page 482: unable to get the page. error code=522 On tree page 32 cell 3: Child page depth differs Page 485: unable to get the page. error code=522 On tree page 32 cell 5: Child page depth differs On tree page 32 cell 6: Child page depth differs Page 487: unable to get the page. error code=522 On tree page 32 cell 7: Child page depth differs On tree page 32 cell 8: Child page depth differs Page 493: unable to get the page. error code=522 On tree page 32 cell 13: Child page depth differs On tree page 32 cell 14: Child page depth differs Page 495: unable to get the page. error code=522 On tree page 32 cell 15: Child page depth differs On tree page 32 cell 16: Child page depth differs Page 505: unable to get the page. error code=522 On tree page 45 cell 4: Child page depth differs On tree page 45 cell 5: Child page depth differs Page 421: unable to get the page. error code=522 On tree page 15 cell 1: Child page depth differs Page 422: unable to get the page. error code=522 On tree page 15 cell 3: Child page depth differs Page 424: unable to get the page. error code=522 On tree page 15 cell 4: Child page depth differs On tree page 15 cell 5: Child page depth differs Page 49: unable to get the page. error code=522 Page 33: unable to get the page. error code=522 Page 34: unable to get the page. error code=522 Page 472: unable to get the page. error code=522 Page 473: unable to get the page. error code=522 Page 474: unable to get the page. error code=522 Page 475: unable to get the page. error code=522 Page 476: unable to get the page. error code=522 Page 477: unable to get the page. error code=522 Page 478: unable to get the page. error code=522 Page 479: unable to get the page. error code=522 Error: disk I/O error Also, the page size is 1024. Korey -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: October 31, 2011 2:32 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Disk I/O Error On Mon, Oct 31, 2011 at 5:01 PM, Korey Calmettes wrote: > Hello, > > We are having a random problem with our system that has be puzzled at > this point. > > First a little background. We have an embedded system running an ARM > processor and JFFS file system. We are running 3.7.7.1. I e-mailed > about a month ago about enabling WAL on our system. Found out that I > needed to set SQLITE_SHM_DIRECTORY to our tmpfs file system when > compiling as JFFS doesn't support shared mmap files. > > This has been working fine, however throughout our testing, we have > been hitting a problem. It seems that some tables are no longer > accessible giving a Disk I/O Error. I logged into the command line > interface and replicated the problem. I ran ".log stdout" and here are the results. > > sqlite> select password from auth; > (522) statement aborts at 7: [select password from auth;] > Error: disk I/O error > > However access to other tables will work without any problems. > Error code 522 is SQLITE_IOERR_SHORT_READ. It is generated here: http://www.sqlite.org/src/artifact/07acbb3e074e?ln=3012 SQLite was trying to read N bytes and got back M byes where M>0 and M > I searched for how to read the log without success. I am hopeful that > someone would be able to tell me what's going on. > > Korey > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Occasional problems with < and >
For .0001 worse-case just mulitply all your values by 1000 and all decimal places will work just as you want and you can compute any difference you want accurately and compare accurately. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Ryan Belcher [ryan.belc...@mma1.com] Sent: Tuesday, November 01, 2011 10:24 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Occasional problems with < and > Thanks to all who responded. I understand the problem now. I also see the wisdom of using integer type as a substitute for not having decimal type. I do have one other question. Are there more downsides to using a tolerance value in my comparisons? It seems to me that either going the integer or tolerance route, I'm committing to a fixed precision for base and thick. If I know that base and thick never use more than .0001 of precision and sqlite uses 13 digits then I should be safe using .001 as a tolerance value, right? Or am I heading for trouble going that route? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Tuesday, November 01, 2011 11:03 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Occasional problems with < and > On 1 Nov 2011, at 2:52pm, Richard Hipp wrote: > This is why you are admonished to never compare floating point numbers for > equality - in any system, not just in SQLite. And in this case, the most rigid and correct solution is to hold those numbers as integers. So given the numbers you supplied were > Id|base|thick > 89|281.04|0.03 > 90|282.09|1.05 > 91|283.11|1.02 > 92|290.08|6.97 my guess is that you should multiply those measurements by 100. Alternatively you can simply expect to lose the precision of the last digit. In other words, if the database holds a number that looks like abc.de and 'e' is a 7 in this case, you can expect that comparisons might interpret this as a 6, 7 or 8 randomly, and plan your application accordingly. Which of the two above solutions you choose depends on the nature of your system. The first gives you technically correct answers but requires reformatting of numbers when they're input and output. The second is simpler and requires less programming but requires users to understand 'slop'. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This communication contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s), please note that any distribution, copying or use of this communication or the information in it is strictly prohibited. If you have received this communication in error, please notify the sender immediately and then destroy any copies of it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Referencing column aliases
Thanks for the information and the VIEW suggestion. Since SQLite already provides an extension to reference column aliases in WHERE and JOIN clauses, is there any likelihood that it might be further extended to allow them in the SELECT statement itself? Thanks, Pete > Message: 14 > Date: Tue, 1 Nov 2011 08:26:17 -0400 > From: "Igor Tandetnik" > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] sqlite-users Digest, Vol 46, Issue 29 > Message-ID: > Content-Type: text/plain; charset="iso-8859-1" > > Pete wrote: > > Thanks. I guess I'd like to confirm just where column aliases can > > be referenced. I think they cannot be referenced within the list of > column > > names in which they are defined, and they can be referenced in any other > > clauses of the SELECT statement, eg WHERE, ORDER BY, GROUP BY, HAVING. Is > > that correct? > > Per SQL standard, column aliases can be referenced in ORDER BY, GROUP BY > and HAVING clauses. As an extension, SQLite also allows them in WHERE and > JOIN ON clauses, but again, such usage is non-standard (though very > convenient at times). Neither the standard nor SQLite implementation allow > referencing aliases in the SELECT clause. > -- > Igor Tandetnik > > > > -- > > Message: 15 > Date: Tue, 1 Nov 2011 12:36:27 + > From: Simon Slavin > To: General Discussion of SQLite Database > Subject: Re: [sqlite] sqlite-users Digest, Vol 46, Issue 29 > Message-ID: <2822a5a5-cce0-4bb2-90a7-7e6177c06...@bigfraud.org> > Content-Type: text/plain; charset=us-ascii > > > On 1 Nov 2011, at 12:26pm, Igor Tandetnik wrote: > > > Per SQL standard, column aliases can be referenced in ORDER BY, GROUP BY > and HAVING clauses. As an extension, SQLite also allows them in WHERE and > JOIN ON clauses, but again, such usage is non-standard (though very > convenient at times). Neither the standard nor SQLite implementation allow > referencing aliases in the SELECT clause. > > Am I right that the most convenient way to do this might be by using a > VIEW ? One could define a VIEW which had a number of columns like > > totalPrice = numItems * itemPrice > > then use this totalPrice column for things like sorting, right ? > > Simon. > > *** > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Occasional problems with < and >
On 1 Nov 2011, at 3:24pm, Ryan Belcher wrote: > I do have one other question. Are there more downsides to using a tolerance > value in my comparisons? It seems to me that either going the integer or > tolerance route, I'm committing to a fixed precision for base and thick. If > I know that base and thick never use more than .0001 of precision and sqlite > uses 13 digits then I should be safe using .001 as a tolerance value, > right? No. Because it's impossible to express the number .001 in binary. Whatever numbers you hold in floating point, you should always be prepared to lose some precision any time you do any maths, including comparisons. I'd recommend that you find out what your measurement precision is and use that as integers, or in your training explain to your users that they should always expect the last shown digit to be a little fuzzy. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disk I/O Error
On Tue, Nov 1, 2011 at 10:40 AM, Korey Calmettes wrote: > When we are able to reproduce the problem again, I will run these > checks. It's fairly random however consistent. I will e-mail the > results to you later today. > If you are able, please recompile using the version of SQLite here: http://www.sqlite.org/src/ci/72256634773f?sbs=1 which includes a patch that will retry read() system calls that return fewer bytes than expected. Let us know if this clears the problem. Note that this patch is on a branch and probably won't go onto the trunk unless we hear back positive results from you. > > Thanks, > > Korey > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp > Sent: October 31, 2011 2:32 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Disk I/O Error > > On Mon, Oct 31, 2011 at 5:01 PM, Korey Calmettes > wrote: > > > Hello, > > > > We are having a random problem with our system that has be puzzled at > > this point. > > > > First a little background. We have an embedded system running an ARM > > processor and JFFS file system. We are running 3.7.7.1. I e-mailed > > about a month ago about enabling WAL on our system. Found out that I > > needed to set SQLITE_SHM_DIRECTORY to our tmpfs file system when > > compiling as JFFS doesn't support shared mmap files. > > > > This has been working fine, however throughout our testing, we have > > been hitting a problem. It seems that some tables are no longer > > accessible giving a Disk I/O Error. I logged into the command line > > interface and replicated the problem. I ran ".log stdout" and here > are the results. > > > > sqlite> select password from auth; > > (522) statement aborts at 7: [select password from auth;] > > Error: disk I/O error > > > > However access to other tables will work without any problems. > > > > Error code 522 is SQLITE_IOERR_SHORT_READ. It is generated here: > > http://www.sqlite.org/src/artifact/07acbb3e074e?ln=3012 > > SQLite was trying to read N bytes and got back M byes where M>0 and M > Perchance is your database file truncated? What happens when you run > "PRAGMA integrity_check" on the database? What is the page size of your > database? > > > > > > > I searched for how to read the log without success. I am hopeful that > > > someone would be able to tell me what's going on. > > > > Korey > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Occasional problems with < and >
Thanks to all who responded. I understand the problem now. I also see the wisdom of using integer type as a substitute for not having decimal type. I do have one other question. Are there more downsides to using a tolerance value in my comparisons? It seems to me that either going the integer or tolerance route, I'm committing to a fixed precision for base and thick. If I know that base and thick never use more than .0001 of precision and sqlite uses 13 digits then I should be safe using .001 as a tolerance value, right? Or am I heading for trouble going that route? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Tuesday, November 01, 2011 11:03 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Occasional problems with < and > On 1 Nov 2011, at 2:52pm, Richard Hipp wrote: > This is why you are admonished to never compare floating point numbers for > equality - in any system, not just in SQLite. And in this case, the most rigid and correct solution is to hold those numbers as integers. So given the numbers you supplied were > Id|base|thick > 89|281.04|0.03 > 90|282.09|1.05 > 91|283.11|1.02 > 92|290.08|6.97 my guess is that you should multiply those measurements by 100. Alternatively you can simply expect to lose the precision of the last digit. In other words, if the database holds a number that looks like abc.de and 'e' is a 7 in this case, you can expect that comparisons might interpret this as a 6, 7 or 8 randomly, and plan your application accordingly. Which of the two above solutions you choose depends on the nature of your system. The first gives you technically correct answers but requires reformatting of numbers when they're input and output. The second is simpler and requires less programming but requires users to understand 'slop'. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This communication contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s), please note that any distribution, copying or use of this communication or the information in it is strictly prohibited. If you have received this communication in error, please notify the sender immediately and then destroy any copies of it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Occasional problems with < and >
On Nov 1, 2011, at 3:34 PM, Ryan Belcher wrote: > For that last result, 290.08 - 6.97 = 283.11; so base - thick is equal to > 283.11 but not less. Is this a bug or something I'm not doing right or don't > understand? As mentioned, precision, precision, precision :P select * FROM test WHERE base > 281.01 AND round( base - thick, 2 ) < 283.11; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disk I/O Error
On Mon, Oct 31, 2011 at 5:44 PM, Richard Hipp wrote: > On Mon, Oct 31, 2011 at 5:40 PM, Pavel Ivanov wrote: > >> > Error code 522 is SQLITE_IOERR_SHORT_READ. It is generated here: >> > >> > http://www.sqlite.org/src/artifact/07acbb3e074e?ln=3012 >> > >> > SQLite was trying to read N bytes and got back M byes where M>0 and M> >> Could it be that N bytes cannot be read atomically and operation was >> interrupted in the middle by some signal? This scenario can explain >> short read even if database is not truncated or corrupted. > > If an interrupt occurs that stops the read before it can complete, then the > read should be tried again. > http://www.sqlite.org/src/artifact/07acbb3e074e?ln=2968 http://linux.die.net/man/2/read "On success, the number of bytes read is returned (zero indicates end of file), and the file position is advanced by this number. It is not an error if this number is smaller than the number of bytes requested; this may happen for example because fewer bytes are actually available right now (maybe because we were close to end-of-file, or because we are reading from a pipe, or from a terminal), or because read() was interrupted by a signal." So the condition "got <0 && errno == EINTR" will be true only when signal came before any data were read. If signal comes when some data already have been read then got will be greater than 0 and errno will be 0, although got will be less than requested. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Occasional problems with < and >
On 1 Nov 2011, at 2:52pm, Richard Hipp wrote: > This is why you are admonished to never compare floating point numbers for > equality - in any system, not just in SQLite. And in this case, the most rigid and correct solution is to hold those numbers as integers. So given the numbers you supplied were > Id|base|thick > 89|281.04|0.03 > 90|282.09|1.05 > 91|283.11|1.02 > 92|290.08|6.97 my guess is that you should multiply those measurements by 100. Alternatively you can simply expect to lose the precision of the last digit. In other words, if the database holds a number that looks like abc.de and 'e' is a 7 in this case, you can expect that comparisons might interpret this as a 6, 7 or 8 randomly, and plan your application accordingly. Which of the two above solutions you choose depends on the nature of your system. The first gives you technically correct answers but requires reformatting of numbers when they're input and output. The second is simpler and requires less programming but requires users to understand 'slop'. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Occasional problems with < and >
Have you heard of rounding errors? If you want exact numbers you need to convert to integers: sqlite> create table test_col (id integer,base real,thick real); sqlite> insert into test_col values(89,281.04,0.03); sqlite> insert into test_col values(90,282.09|1.05); Error: table test_col has 3 columns but 2 values were supplied sqlite> insert into test_col values(90,282.09,1.05); sqlite> insert into test_col values(91,283.11,1.02); sqlite> insert into test_col values(92,290.08,6.97); sqlite> SELECT id,base,thick FROM test_col WHERE base > 281.01 AND base - thick < 283.11; 89|281.04|0.03 90|282.09|1.05 91|283.11|1.02 92|290.08|6.97 sqlite> sqlite> create table test_col2 (id integer,base integer,thick integer); sqlite> insert into test_col values(89,28104,3); sqlite> insert into test_col values(90,28209,105); sqlite> insert into test_col values(91,28311,102); sqlite> insert into test_col values(92,29008,697); sqlite> SELECT id,base/100,thick/100 FROM test_col WHERE base > 28101 AND base - thick < 28311; 89|281.04|0.03 90|282.09|1.05 91|283.11|1.02 s Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Ryan Belcher [ryan.belc...@mma1.com] Sent: Tuesday, November 01, 2011 9:37 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Occasional problems with < and > I should have added that I have database with lots of values like this and I run many queries like this. Sometimes sqlite returns the correct results and sometimes it includes results where the values are equal to the base or base - thick. It probably returns the correct result a little over the half the time. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disk I/O Error
If it helps the discussion any, we are using JFFS file system on Linux 2.6.8.1. We are running uclibc, not sure if that matters or not. And one more thing, we updated from an old version of SQLite that has been in our product (and stable) for years. (I want to say it was 3.3.something.) We didn't have any problems until updating to the latest version and enabling WAL mode. I hope that this helps, Thanks, Korey -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Stephan Beal Sent: November 1, 2011 7:46 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Disk I/O Error On Mon, Oct 31, 2011 at 10:44 PM, Richard Hipp wrote: > If an interrupt occurs that stops the read before it can complete, > then the read should be tried again. > http://www.sqlite.org/src/artifact/07acbb3e074e?ln=2968 This topic came up before a few months ago and someone (don't remember who) pointed out that platforms exist which can return >0 from read() when interrupted. My man page says: POSIX allows a read() that is interrupted after reading some data to return -1 (with errno set to EINTR) or to return the number of bytes already read. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Occasional problems with < and >
On Tue, Nov 1, 2011 at 10:34 AM, Ryan Belcher wrote: > Sometimes queries using < and > will include values that are equal. Here > is an example. > > SELECT id,base,thick FROM test_col WHERE base > 281.01 AND base - thick < > 283.11; > > Id|base|thick > 89|281.04|0.03 > 90|282.09|1.05 > 91|283.11|1.02 > 92|290.08|6.97 > > For that last result, 290.08 - 6.97 = 283.11; so base - thick is equal to > 283.11 but not less. Is this a bug or something I'm not doing right or > don't understand? > Floating-point numbers are approximations. There is no way to represent 290.08 or 6.97 or 283.11 in binary. The closest you can get to these numbers using IEEE doubles is: 290.079998408384271897375583648681640625 6.96975131004248396493494510650634765625 283.11000136424205265939235687255859375 And to compound the difficulty, SQLite does not attempt to get that close, as doing so requires infinite-precision integer math in the worst case and a noticeable performance hit. SQLite, therefore, goes for a rougher approximation and settles for 14 significant digits. Floating point is designed to represent physical measurements (distance, voltage, mass, etc.) and no instrument you have ever seen in your lifetime is able to measure a physical quantity to anything close to 14 significant digits. Even financial quantities are never truly known to 14 significant digits, as 1% interest compounded continuously changes the 13th digit every millisecond or so. This is why you are admonished to never compare floating point numbers for equality - in any system, not just in SQLite. > > > This communication contains information which is confidential and may also > be privileged. It is for the exclusive use of the intended recipient(s). If > you are not the intended recipient(s), please note that any distribution, > copying or use of this communication or the information in it is strictly > prohibited. If you have received this communication in error, please notify > the sender immediately and then destroy any copies of it. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disk I/O Error
On Mon, Oct 31, 2011 at 10:44 PM, Richard Hipp wrote: > If an interrupt occurs that stops the read before it can complete, then the > read should be tried again. > http://www.sqlite.org/src/artifact/07acbb3e074e?ln=2968 This topic came up before a few months ago and someone (don't remember who) pointed out that platforms exist which can return >0 from read() when interrupted. My man page says: POSIX allows a read() that is interrupted after reading some data to return -1 (with errno set to EINTR) or to return the number of bytes already read. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disk I/O Error
When we are able to reproduce the problem again, I will run these checks. It's fairly random however consistent. I will e-mail the results to you later today. Thanks, Korey -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: October 31, 2011 2:32 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Disk I/O Error On Mon, Oct 31, 2011 at 5:01 PM, Korey Calmettes wrote: > Hello, > > We are having a random problem with our system that has be puzzled at > this point. > > First a little background. We have an embedded system running an ARM > processor and JFFS file system. We are running 3.7.7.1. I e-mailed > about a month ago about enabling WAL on our system. Found out that I > needed to set SQLITE_SHM_DIRECTORY to our tmpfs file system when > compiling as JFFS doesn't support shared mmap files. > > This has been working fine, however throughout our testing, we have > been hitting a problem. It seems that some tables are no longer > accessible giving a Disk I/O Error. I logged into the command line > interface and replicated the problem. I ran ".log stdout" and here are the results. > > sqlite> select password from auth; > (522) statement aborts at 7: [select password from auth;] > Error: disk I/O error > > However access to other tables will work without any problems. > Error code 522 is SQLITE_IOERR_SHORT_READ. It is generated here: http://www.sqlite.org/src/artifact/07acbb3e074e?ln=3012 SQLite was trying to read N bytes and got back M byes where M>0 and M > I searched for how to read the log without success. I am hopeful that > someone would be able to tell me what's going on. > > Korey > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Occasional problems with < and >
On Nov 1, 2011, at 3:34 PM, Ryan Belcher wrote: > Is this a bug or something I'm not doing right or don't understand? Check your data type. Make sure to use one which can hold the necessary precision (i.e. real): http://www.sqlite.org/datatype3.html If necessary, cast when appropriate: http://www.sqlite.org/lang_expr.html#castexpr ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Occasional problems with < and >
I should have added that I have database with lots of values like this and I run many queries like this. Sometimes sqlite returns the correct results and sometimes it includes results where the values are equal to the base or base - thick. It probably returns the correct result a little over the half the time. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ryan Belcher Sent: Tuesday, November 01, 2011 10:35 AM To: sqlite-users@sqlite.org Subject: [sqlite] Occasional problems with < and > Sometimes queries using < and > will include values that are equal. Here is an example. SELECT id,base,thick FROM test_col WHERE base > 281.01 AND base - thick < 283.11; Id|base|thick 89|281.04|0.03 90|282.09|1.05 91|283.11|1.02 92|290.08|6.97 For that last result, 290.08 - 6.97 = 283.11; so base - thick is equal to 283.11 but not less. Is this a bug or something I'm not doing right or don't understand? This communication contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s), please note that any distribution, copying or use of this communication or the information in it is strictly prohibited. If you have received this communication in error, please notify the sender immediately and then destroy any copies of it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This communication contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s), please note that any distribution, copying or use of this communication or the information in it is strictly prohibited. If you have received this communication in error, please notify the sender immediately and then destroy any copies of it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Occasional problems with < and >
Sometimes queries using < and > will include values that are equal. Here is an example. SELECT id,base,thick FROM test_col WHERE base > 281.01 AND base - thick < 283.11; Id|base|thick 89|281.04|0.03 90|282.09|1.05 91|283.11|1.02 92|290.08|6.97 For that last result, 290.08 - 6.97 = 283.11; so base - thick is equal to 283.11 but not less. Is this a bug or something I'm not doing right or don't understand? This communication contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s), please note that any distribution, copying or use of this communication or the information in it is strictly prohibited. If you have received this communication in error, please notify the sender immediately and then destroy any copies of it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Default column values conflict with not null option
On Sun, Oct 30, 2011 at 05:08:51PM +0400, ??? scratched on the wall: > Default column values conflict with not null option. By default, yes. The default default value for all columns is NULL. If you add a NOT NULL constraint and do not re-define the default value using a DEFAULT constraint, then you cannot use default values. This is normal for all SQL database systems. -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@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ORDER BY disregarded for GROUP BY
On Fri, Oct 28, 2011 at 06:42:06PM +0200, Tobias Sj??sten scratched on the wall: > I have a table: > > CREATE TABLE t > ( > i INT, > g VARCHAR(1), > v INT > ); > But when I group it by 'g' it completely disregards the ordering: > > > SELECT g,v FROM t GROUP BY g ORDER BY v ASC; > a|3 > b|3 > > Using descending order does not matter, btw. The result is the same. What are you expecting? The output of the GROUP BY (which includes an arbitrary "v" value for each group, since you didn't define a grouping method for "v") is sorted by "v". In this case, 3 == 3, so there isn't any meaning in the order. Remember that GROUP BY is applied *before* ORDER BY. > I'm not sure if this is the expected behavior in SQLite but for me it > certainly wasn't. I was thinking SQLite would act as MySQL and respect > the ordering when grouping. MySQL would likely throw an error with this query. Most DBs will not not let you have "v" in the results without being in the GROUP BY clause and/or using it as a parameter in an aggregate function. -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@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indirect Referencing
On Tue, Nov 01, 2011 at 02:01:30PM +, Black, Michael (IS) scratched on the wall: > Hmmm...how hard would it be allow one to bind the table name with prepare? Generally, "very." Or, rather, it would be fairly straight forward if you're willing to skip the query optimization step and do all look-ups as linear table scans and all joins as nested loops. Much of the query construction, including the use of indexes, join optimizations, constraints, keys, and everything else is based off knowing which specific tables and columns are being accessed. You'd also need to give up most of the query sanity checking, since you wouldn't be able to do things like verify that a named column actually exists in the unspecified table. If you were to do all that work after "binding" the identifier names, you'd more or less be re-preparing the whole statement. So you might as well just re-prepare the statement. Besides, getting back to some comments from before, if you've got a whole series of tables with the same structure, so that you can take advantage of this kind of thing, you likely need to rethink your database design. Yes, it would be a minor convenience from time to time, but generally reuse comes from very simple statements that are easy to simply rebuild. -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@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indirect Referencing
Though you probably COULD merge them all together there are situations where you don't want to. Just like his original question. Imagine you have a bunch of tables of consumer products split by type. Yes, you could stick it all in one humogous database...but... The split allows you to reduce contention so that doing an update doesn't stop everybody and even reads will be faster since they are split. The bind allows you to use just one routine with a pre-prepared statement for speed instead of building your own sql string and having to prepare it each time. Seems like quite an upside to it to me unless I'm missing something (which is quite possible :-) Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org] Sent: Tuesday, November 01, 2011 9:10 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Indirect Referencing On 1 Nov 2011, at 2:01pm, Black, Michael (IS) wrote: > Hmmm...how hard would it be allow one to bind the table name with prepare? If you are in a position to bind the table name, does that not mean you could just merge all the tables together ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indirect Referencing
Boy Howdy ! I've wished for that capability just about every time I've had to generate Dynamic SQL Statements to execute a common query against varying Table Names :) -- kjh Black, Michael (IS) wrote, On 11/01/2011 09:01 AM: Hmmm...how hard would it be allow one to bind the table name with prepare? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Konrad J Hambrick [kon...@payplus.com] Sent: Tuesday, November 01, 2011 8:44 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Indirect Referencing Bertus -- The only way to reference a Variable Table Name would be to create a Dynamic SQL Statement at runtime, prep the Dynamic Statement then exec. This would be true for all implementations of SQL that I am aware of ... -- kjh Stander, Bertus (Pretoria) wrote, On 11/01/2011 05:25 AM: Good day to you all, I am very inexperienced with SQlite and any help will be appreciated. I want to know if I can use indirect referencing within the SQL language as per SQlite standards. I will give an example. If I create a table as illustrated below. CREATE TABLE Testing ( ID INTEGER PRIMARY KEY AUTOINCREMENT, Tbl_Name VARCHAR (45)); The value of 'Tbl_Name' is set to 'My_Table' Will it be possible to read the values in table My_Table using the field 'Tbl_Name'? Select * from Testing.Tbl_Name; The tests I have performed is not working. Any suggestions or work around please? This email has been scanned for viruses and malware, and automatically archived by Mimecast SA (Pty) Ltd, an innovator in Software as a Service (SaaS) for business. Mimecast Unified Email Management UEM) offers email continuity, security, archiving and compliance with all current legislation. To find out more,contact Mimecast. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indirect Referencing
On 1 Nov 2011, at 2:01pm, Black, Michael (IS) wrote: > Hmmm...how hard would it be allow one to bind the table name with prepare? If you are in a position to bind the table name, does that not mean you could just merge all the tables together ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indirect Referencing
Hmmm...how hard would it be allow one to bind the table name with prepare? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Konrad J Hambrick [kon...@payplus.com] Sent: Tuesday, November 01, 2011 8:44 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Indirect Referencing Bertus -- The only way to reference a Variable Table Name would be to create a Dynamic SQL Statement at runtime, prep the Dynamic Statement then exec. This would be true for all implementations of SQL that I am aware of ... -- kjh Stander, Bertus (Pretoria) wrote, On 11/01/2011 05:25 AM: > Good day to you all, > > I am very inexperienced with SQlite and any help will be appreciated. > > I want to know if I can use indirect referencing within the SQL language > as per SQlite standards. I will give an example. > > If I create a table as illustrated below. > > CREATE TABLE Testing ( > >ID INTEGER PRIMARY KEY AUTOINCREMENT, > >Tbl_Name VARCHAR (45)); > > The value of 'Tbl_Name' is set to 'My_Table' > > Will it be possible to read the values in table My_Table using the field > 'Tbl_Name'? > > Select * from Testing.Tbl_Name; > > The tests I have performed is not working. > > Any suggestions or work around please? > > > > > This email has been scanned for viruses and malware, and automatically > archived by Mimecast SA (Pty) Ltd, an innovator in Software as a Service > (SaaS) for business. Mimecast Unified Email Management > UEM) offers email continuity, security, archiving and compliance with all > current legislation. To find out more,contact Mimecast. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indirect Referencing
Bertus -- The only way to reference a Variable Table Name would be to create a Dynamic SQL Statement at runtime, prep the Dynamic Statement then exec. This would be true for all implementations of SQL that I am aware of ... -- kjh Stander, Bertus (Pretoria) wrote, On 11/01/2011 05:25 AM: Good day to you all, I am very inexperienced with SQlite and any help will be appreciated. I want to know if I can use indirect referencing within the SQL language as per SQlite standards. I will give an example. If I create a table as illustrated below. CREATE TABLE Testing ( ID INTEGER PRIMARY KEY AUTOINCREMENT, Tbl_Name VARCHAR (45)); The value of 'Tbl_Name' is set to 'My_Table' Will it be possible to read the values in table My_Table using the field 'Tbl_Name'? Select * from Testing.Tbl_Name; The tests I have performed is not working. Any suggestions or work around please? This email has been scanned for viruses and malware, and automatically archived by Mimecast SA (Pty) Ltd, an innovator in Software as a Service (SaaS) for business. Mimecast Unified Email Management UEM) offers email continuity, security, archiving and compliance with all current legislation. To find out more,contact Mimecast. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indirect Referencing
Thanks Simon, The response I am giving here is to assist others in deciding whether they can use SQlite. I understand Triggers reasonably well as I am using it continuously very successfully. The 1200 tables in the SQlite database has no impact on performance as every transaction executed on a small device like the Trimble is sub-second. The storage space is also no problem as the database is stored on SSD. I do understand the terms "table" and "row" etc quite well, but must admit I need more good references to sites for valuable and RELIABLE information and EXAMPLES of MORE COMPLEX SQL. Please exclude the www.SQlite.org as I am aware of that site. Hopefully I will still receive more comments and suggestions on the possibilities of "Indirect Referencing" within SQlite. Bertus > That is one aspect. I also want to use minimal triggers to identify > certain abnormalities and perform certain processes. This I want to keep > Central and not writing triggers for all 1200 tables. So key to a > solution I was thinking of is to store table names and field names in a > central table and then reference those tables and those fields > identified as problem areas during the capture process. You will not be able to get SQLite to run triggers stored in tables automatically. And any database which includes 1200 tables will probably work slowly and take up a lot of storage space. I'm not sure you properly understand the SQL terms 'table' and 'row'. It may be that the best thing you can do now is read a beginner's book about SQL just so you understand how it works. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email has been scanned for viruses and malware, and automatically archived by Mimecast SA (Pty) Ltd, an innovator in Software as a Service (SaaS) for business. Mimecast Unified Email Management UEM) offers email continuity, security, archiving and compliance with all current legislation. To find out more,contact Mimecast. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE
Thanks, Igor. My original macro did replicate the where in the sub-select. I temporarily got rid of them in my testing because it was so messy and I didn't know how to get the update working. I will give this a shot, and thanks for time and effort. On Tue, Nov 1, 2011 at 7:45 AM, Igor Tandetnik wrote: > Don V Nielsen wrote: > > I need help with a complex UPDATE. I want to update each row in a table, > > calculating an average, and then apply that value back into a column of > the > > same row. Is this possible with Sqlite? Below is code that should work > > with SqlServer; its UPDATE supports a FROM statement. > > > > UPDATE m SET rtwgt = avgrowid > > FROM seg_02_matches as m > > JOIN ( > > SELECT pr3.zip, pr3.crrt, avg(ap.[rowid]) AS avgrowid > > FROM ( > > SELECT pr1.zip, pr1.crrt, pr1.prty, 'WI' AS 'id' FROM pool_WI AS pr1 > > UNION > > SELECT pr2.zip, pr2.crrt, pr2.prty, 'NY' AS 'id' FROM pool_NY AS pr2 > > ) AS pr3 > > INNER JOIN add_priorities AS ap ON ap.prty = pr3.prty AND ap.poolid = > > pr3.id > > GROUP BY pr3.zip, pr3.crrt > > ) as sub ON m.zip = sub.zip AND m.route = sub.crrt > > UPDATE seg_02_matches SET rtwgt = ( > SELECT avg(ap.rowid) > FROM ( >SELECT zip, crrt, prty, 'WI' AS id FROM pool_WI AS pr1 >UNION ALL >SELECT zip, crrt, prty, 'NY' AS id FROM pool_NY AS pr2 > ) AS pr3 > JOIN add_priorities AS ap ON ap.prty = pr3.prty AND ap.poolid = pr3.id > WHERE seg_02_matches.zip = pr3.zip AND seg_02_matches.route = pr3.crrt > ); > > Might work faster if WHERE clause were duplicated inside the subselect. > Try it both ways: > > UPDATE seg_02_matches SET rtwgt = ( > SELECT avg(ap.rowid) > FROM ( >SELECT prty, 'WI' AS id FROM pool_WI AS pr1 >WHERE seg_02_matches.zip = pr1.zip AND seg_02_matches.route = pr1.crrt >UNION ALL >SELECT prty, 'NY' AS id FROM pool_NY AS pr2 >WHERE seg_02_matches.zip = pr2.zip AND seg_02_matches.route = pr2.crrt > ) AS pr3 > JOIN add_priorities AS ap ON ap.prty = pr3.prty AND ap.poolid = pr3.id > ); > > Might be better still if you had a single "pool" table with the explicit > id column, rather than having to manufacture it on the fly with UNION ALL. > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.7.9
SQLite version 3.7.9 is now available on the primary and on the backup websites: http://www.sqlite.org/ http://www2.sqlite.org/ http://www3.sqlite.org/ Version 3.7.9 is a periodic maintenance release. Upgrading from versions 3.7.6.3 and later is optional. Upgrading from prior versions is recommended. Additional information about this release can be found at: http://www.sqlite.org/releaselog/3_7_9.html http://www.sqlite.org/news.html As always, please let me know if you encounter any difficulty with this release. D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Expert
It's one of the best programs I have tested myself. I have even suggested to Bogdan to add forms to make it even a better application for covering GUI needs for complex database like in my case where I have to deal with stock taking and cartridges. I hope he do something about it because honestly is one of the greatest applications I have ever tried on SQLite adminstration; highly recommended by all means. > >From: Don V Nielsen >To: General Discussion of SQLite Database >Sent: Monday, October 31, 2011 11:05 PM >Subject: Re: [sqlite] SQLite Expert > >I use the free version of sqlite expert. I use sqlite for my IO handling. >Having the power of sql available to test values, generate counts, etc... >is indispensable. > >On Sun, Oct 30, 2011 at 2:50 PM, Abair Heart wrote: > >> Hi all, >> >> Searching the archives implies, that "sqlite expert" hasn't been asked >> about. >> >> Has anyone tried it yet? Is the program safe? time-saving? >> >> >> Thanks for any input. >> >> Abair >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >___ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE
Don V Nielsen wrote: > I need help with a complex UPDATE. I want to update each row in a table, > calculating an average, and then apply that value back into a column of the > same row. Is this possible with Sqlite? Below is code that should work > with SqlServer; its UPDATE supports a FROM statement. > > UPDATE m SET rtwgt = avgrowid > FROM seg_02_matches as m > JOIN ( > SELECT pr3.zip, pr3.crrt, avg(ap.[rowid]) AS avgrowid > FROM ( > SELECT pr1.zip, pr1.crrt, pr1.prty, 'WI' AS 'id' FROM pool_WI AS pr1 > UNION > SELECT pr2.zip, pr2.crrt, pr2.prty, 'NY' AS 'id' FROM pool_NY AS pr2 > ) AS pr3 > INNER JOIN add_priorities AS ap ON ap.prty = pr3.prty AND ap.poolid = > pr3.id > GROUP BY pr3.zip, pr3.crrt > ) as sub ON m.zip = sub.zip AND m.route = sub.crrt UPDATE seg_02_matches SET rtwgt = ( SELECT avg(ap.rowid) FROM ( SELECT zip, crrt, prty, 'WI' AS id FROM pool_WI AS pr1 UNION ALL SELECT zip, crrt, prty, 'NY' AS id FROM pool_NY AS pr2 ) AS pr3 JOIN add_priorities AS ap ON ap.prty = pr3.prty AND ap.poolid = pr3.id WHERE seg_02_matches.zip = pr3.zip AND seg_02_matches.route = pr3.crrt ); Might work faster if WHERE clause were duplicated inside the subselect. Try it both ways: UPDATE seg_02_matches SET rtwgt = ( SELECT avg(ap.rowid) FROM ( SELECT prty, 'WI' AS id FROM pool_WI AS pr1 WHERE seg_02_matches.zip = pr1.zip AND seg_02_matches.route = pr1.crrt UNION ALL SELECT prty, 'NY' AS id FROM pool_NY AS pr2 WHERE seg_02_matches.zip = pr2.zip AND seg_02_matches.route = pr2.crrt ) AS pr3 JOIN add_priorities AS ap ON ap.prty = pr3.prty AND ap.poolid = pr3.id ); Might be better still if you had a single "pool" table with the explicit id column, rather than having to manufacture it on the fly with UNION ALL. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 46, Issue 29
On 1 Nov 2011, at 12:26pm, Igor Tandetnik wrote: > Per SQL standard, column aliases can be referenced in ORDER BY, GROUP BY and > HAVING clauses. As an extension, SQLite also allows them in WHERE and JOIN ON > clauses, but again, such usage is non-standard (though very convenient at > times). Neither the standard nor SQLite implementation allow referencing > aliases in the SELECT clause. Am I right that the most convenient way to do this might be by using a VIEW ? One could define a VIEW which had a number of columns like totalPrice = numItems * itemPrice then use this totalPrice column for things like sorting, right ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 46, Issue 29
Pete wrote: > Thanks. I guess I'd like to confirm just where column aliases can > be referenced. I think they cannot be referenced within the list of column > names in which they are defined, and they can be referenced in any other > clauses of the SELECT statement, eg WHERE, ORDER BY, GROUP BY, HAVING. Is > that correct? Per SQL standard, column aliases can be referenced in ORDER BY, GROUP BY and HAVING clauses. As an extension, SQLite also allows them in WHERE and JOIN ON clauses, but again, such usage is non-standard (though very convenient at times). Neither the standard nor SQLite implementation allow referencing aliases in the SELECT clause. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indirect Referencing
On 1 Nov 2011, at 11:44am, Stander, Bertus (Pretoria) wrote: > That is one aspect. I also want to use minimal triggers to identify > certain abnormalities and perform certain processes. This I want to keep > Central and not writing triggers for all 1200 tables. So key to a > solution I was thinking of is to store table names and field names in a > central table and then reference those tables and those fields > identified as problem areas during the capture process. You will not be able to get SQLite to run triggers stored in tables automatically. And any database which includes 1200 tables will probably work slowly and take up a lot of storage space. I'm not sure you properly understand the SQL terms 'table' and 'row'. It may be that the best thing you can do now is read a beginner's book about SQL just so you understand how it works. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] VFS minor issue with xDelete method return value
I am using sqlite 3.7.8. My custom VFS xDelete method returned SQLITE_NOTFOUND in a case when the file to be deleted was not found, and this worked fine for older versions (probably prior to 3.7.x where the write ahead log was introduced), particularly for 3.6.10. But now the sqlite core system asks a VFS to delete the .db-wal file at the start of the opening the database. When the VFS returns SQLITE_NOTFOUND error, the core system refuses opening the database. I've patched my custom VFS to return SQLITE_OK in this case, but IMHO this is a bug, because returning SQLITE_NOTFOUND error is a normal way to report about this particular case - the file requested to be deleted is not found. I've looked briefly into previous logs and noticed that older sqlite subsystems used to check access to the file using xAccess method before trying to delete it. I think this is a most proper way to delete a file also in case of .db-wal file. I think that xDelete should be explained deeply in the documentation. I didn't find any description for xDelete-specific return values in the documentation, and examples are the only source introducing them. Regards, Vsevolod Novikov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indirect Referencing
Thanks for the response Simon.
I am working with field loggers where the field workers are capturing
different assets using a Trimble mobile device.
I have more than 1200 individual tables, each table holding the values
of a unique asset.
Living in South Africa where bandwidth is a myth in some areas and lots
of verifications must happen before we pump the data unnecessarily to a
central point I would like to read only those asset data tables, that
were touched and not all 1200 individual tables just to find some empty
tables. This will assist me in verifying some basic data abnormalities
which could be rectified before data is transferred and then deleted.
That is one aspect. I also want to use minimal triggers to identify
certain abnormalities and perform certain processes. This I want to keep
Central and not writing triggers for all 1200 tables. So key to a
solution I was thinking of is to store table names and field names in a
central table and then reference those tables and those fields
identified as problem areas during the capture process.
I have inherit this system and trying my best to rewrite it as it is not
a normalised system. But as usual business continue and no one is
waiting for the IT guys and there is no end in sight for enhancement
requests! Does it sound familiar?
Bertus
On 1 Nov 2011, at 10:25am, Stander, Bertus (Pretoria) wrote:
> If I create a table as illustrated below.
>
> CREATE TABLE Testing (
>
> ID INTEGER PRIMARY KEY AUTOINCREMENT,
>
> Tbl_Name VARCHAR (45));
In this example you have a TABLE called 'Testing' and a COLUMN called
'Tbl_Name'.
> The value of 'Tbl_Name' is set to 'My_Table'
>
> Will it be possible to read the values in table My_Table using the
field
> 'Tbl_Name'?
>
> Select * from Testing.Tbl_Name;
>
> The tests I have performed is not working.
Good commands for your above example would be
INSERT INTO Testing (Tbl_Name) VALUES ('My_Table');
SELECT * FROM Testing;
SELECT ID,Tbl_Name FROM Testing;
SELECT ID FROM Testing WHERE Tbl_Name = 'My_Table';
However, the names you have picked are a little strange unless you are
storing information about tables. It would be more normal to see
CREATE TABLE My_Table (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
My_Column TEXT);
INSERT INTO My_Table (My_Column) VALUES ('example row');
SELECT * FROM My_Table;
SELECT ID, My_Column FROM My_Table;
SELECT ID FROM My_Table WHERE My_Column = 'example row';
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
This email has been scanned for viruses and malware, and automatically archived
by Mimecast SA (Pty) Ltd, an innovator in Software as a Service (SaaS) for
business. Mimecast Unified Email Management
UEM) offers email continuity, security, archiving and compliance with all
current legislation. To find out more,contact Mimecast.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indirect Referencing
On 1 Nov 2011, at 10:25am, Stander, Bertus (Pretoria) wrote:
> If I create a table as illustrated below.
>
> CREATE TABLE Testing (
>
> ID INTEGER PRIMARY KEY AUTOINCREMENT,
>
> Tbl_Name VARCHAR (45));
In this example you have a TABLE called 'Testing' and a COLUMN called
'Tbl_Name'.
> The value of 'Tbl_Name' is set to 'My_Table'
>
> Will it be possible to read the values in table My_Table using the field
> 'Tbl_Name'?
>
> Select * from Testing.Tbl_Name;
>
> The tests I have performed is not working.
Good commands for your above example would be
INSERT INTO Testing (Tbl_Name) VALUES ('My_Table');
SELECT * FROM Testing;
SELECT ID,Tbl_Name FROM Testing;
SELECT ID FROM Testing WHERE Tbl_Name = 'My_Table';
However, the names you have picked are a little strange unless you are storing
information about tables. It would be more normal to see
CREATE TABLE My_Table (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
My_Column TEXT);
INSERT INTO My_Table (My_Column) VALUES ('example row');
SELECT * FROM My_Table;
SELECT ID, My_Column FROM My_Table;
SELECT ID FROM My_Table WHERE My_Column = 'example row';
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Indirect Referencing
Good day to you all, I am very inexperienced with SQlite and any help will be appreciated. I want to know if I can use indirect referencing within the SQL language as per SQlite standards. I will give an example. If I create a table as illustrated below. CREATE TABLE Testing ( ID INTEGER PRIMARY KEY AUTOINCREMENT, Tbl_Name VARCHAR (45)); The value of 'Tbl_Name' is set to 'My_Table' Will it be possible to read the values in table My_Table using the field 'Tbl_Name'? Select * from Testing.Tbl_Name; The tests I have performed is not working. Any suggestions or work around please? This email has been scanned for viruses and malware, and automatically archived by Mimecast SA (Pty) Ltd, an innovator in Software as a Service (SaaS) for business. Mimecast Unified Email Management UEM) offers email continuity, security, archiving and compliance with all current legislation. To find out more,contact Mimecast. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
