SQLite version 3.8.5 beta

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

SQLite version 3.8.5 beta

Richard Hipp-3
Please download beta snapshots of SQLite 3.8.5 from
http://www.sqlite.org/download.html and test them in your applications.  We
hope to release SQLite version 3.8.5 within the next few weeks.

See http://www.sqlite.org/draft/releaselog/3_8_5.html for the current list
of changes in 3.8.5.

Please report any problems to this mailing list and/or directly to me.

Thank you for your help.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
jic
Reply | Threaded
Open this post in threaded view
|

Re: SQLite version 3.8.5 beta

jic

"Richard Hipp" wrote...

> Please download beta snapshots of SQLite 3.8.5 from
> http://www.sqlite.org/download.html and test them in your applications.
> We
> hope to release SQLite version 3.8.5 within the next few weeks.
>
> See http://www.sqlite.org/draft/releaselog/3_8_5.html for the current list
> of changes in 3.8.5.
>
> Please report any problems to this mailing list and/or directly to me.

Hi Dr. Hipp.

I am having problem using the pre-built DLL:
http://www.sqlite.org/snapshot/sqlite-dll-win32-x86-201405262205.zip

I am getting this error:

ProgName.exe Entry Point Not Found
The procedure entry point FlsAlloc cound not be located in the dynamic Link
library KERNEL32.dll

The problem is happening in a Windows XP PC, so I am wondering: have you
stopped support for WinXP?  By the way, the same program works in Windows 7.
Thoughts?  Thanks.

josé

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

Re: SQLite version 3.8.5 beta

Török Edwin
In reply to this post by Richard Hipp-3
On 05/27/2014 02:24 AM, Richard Hipp wrote:
> Please download beta snapshots of SQLite 3.8.5 from
> http://www.sqlite.org/download.html and test them in your applications.  We
> hope to release SQLite version 3.8.5 within the next few weeks.
>
> See http://www.sqlite.org/draft/releaselog/3_8_5.html for the current list
> of changes in 3.8.5.
>
> Please report any problems to this mailing list and/or directly to me.

SQLite 3.8.4.3 still has the SQLITE_PROTOCOL bug with WAL databases [1],
and I don't see anything about that in the changes list.
Is it still a know bug in 3.8.5?

I'm confused on whats the status of that bug.
When I reported the rng fork issue to the ML it got fixed really quickly
(thanks for that!), but when I asked [1] about the SQLITE_PROTOCOL
thats been known since 3 years [2] it got ignored.
Tried opening a ticket, and that one got deleted.

Does it mean the bug is WONTFIX? If so what workaround should applications use?
Do you need a testcase to reproduce it?
Did the bug just got lost between all the mail?

[1] https://www.mail-archive.com/sqlite-users%40sqlite.org/msg82471.html
[2] https://www.mail-archive.com/sqlite-users%40sqlite.org/msg57092.html

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

Re: SQLite version 3.8.5 beta

Jan Nijtmans
In reply to this post by jic
2014-05-27 5:57 GMT+02:00 jose isaias cabrera <[hidden email]>:
> "Richard Hipp" wrote...
....
>> Please report any problems to this mailing list and/or directly to me.

> I am having problem using the pre-built DLL:
> http://www.sqlite.org/snapshot/sqlite-dll-win32-x86-201405262205.zip
>
> I am getting this error:
>
> ProgName.exe Entry Point Not Found
> The procedure entry point FlsAlloc cound not be located in the dynamic Link
> library KERNEL32.dll

The entry point FlsAlloc doesn't occur anywhere in the sqlite3.c source
code, it is generated by Visual Studio. Previous sqlite3.dll versions
were generated with MinGW (version 3.17, I guess) that's why
they worked fine on Windows XP.

If you are interested to try an sqlite3.dll built by the latest
MinGW (version 4.0.3), you can have a look here:
    <https://sourceforge.net/projects/cyqlite/files/>
This project is still in the startup phase, but I'm trying to fill the
gap Microsoft is leaving behind......

Thanks!

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

Re: SQLite version 3.8.5 beta

Filip Navara-3
Visual Studio 2013 Update 1 and newer allows explicitly to target Windows
XP, but it has to be compiled with correct runtime library.

Best regards,
Filip Navara


On Tue, May 27, 2014 at 11:33 AM, Jan Nijtmans <[hidden email]>wrote:

> 2014-05-27 5:57 GMT+02:00 jose isaias cabrera <[hidden email]>:
> > "Richard Hipp" wrote...
> ....
> >> Please report any problems to this mailing list and/or directly to me.
>
> > I am having problem using the pre-built DLL:
> > http://www.sqlite.org/snapshot/sqlite-dll-win32-x86-201405262205.zip
> >
> > I am getting this error:
> >
> > ProgName.exe Entry Point Not Found
> > The procedure entry point FlsAlloc cound not be located in the dynamic
> Link
> > library KERNEL32.dll
>
> The entry point FlsAlloc doesn't occur anywhere in the sqlite3.c source
> code, it is generated by Visual Studio. Previous sqlite3.dll versions
> were generated with MinGW (version 3.17, I guess) that's why
> they worked fine on Windows XP.
>
> If you are interested to try an sqlite3.dll built by the latest
> MinGW (version 4.0.3), you can have a look here:
>     <https://sourceforge.net/projects/cyqlite/files/>
> This project is still in the startup phase, but I'm trying to fill the
> gap Microsoft is leaving behind......
>
> Thanks!
>
> Regards,
>        Jan Nijtmans
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite version 3.8.5 beta

Richard Hipp-3
In reply to this post by Török Edwin
On Tue, May 27, 2014 at 4:18 AM, Török Edwin <[hidden email]>wrote:

> On 05/27/2014 02:24 AM, Richard Hipp wrote:
> > Please download beta snapshots of SQLite 3.8.5 from
> > http://www.sqlite.org/download.html and test them in your applications.
>  We
> > hope to release SQLite version 3.8.5 within the next few weeks.
> >
> > See http://www.sqlite.org/draft/releaselog/3_8_5.html for the current
> list
> > of changes in 3.8.5.
> >
> > Please report any problems to this mailing list and/or directly to me.
>
> SQLite 3.8.4.3 still has the SQLITE_PROTOCOL bug with WAL databases [1],
> and I don't see anything about that in the changes list.
> Is it still a know bug in 3.8.5?
>
> I'm confused on whats the status of that bug.
> When I reported the rng fork issue to the ML it got fixed really quickly
> (thanks for that!), but when I asked [1] about the SQLITE_PROTOCOL
> thats been known since 3 years [2] it got ignored.
> Tried opening a ticket, and that one got deleted.
>

Nothing has changed with SQLITE_PROTOCOL since 3.7.6.

In big bold letters at the top of the ticket entry form, it says:  "Tickets
for issues that have not been previously discussed on the mailing list will
usually be deleted without comment."

If you are getting PROTOCOL errors, then the thing to do is to describe the
circumstances under which the errors occur so that we can begin to try to
understand what is going wrong.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite version 3.8.5 beta

Török Edwin
On 05/27/2014 01:40 PM, Richard Hipp wrote:

> On Tue, May 27, 2014 at 4:18 AM, Török Edwin <[hidden email]>wrote:
>
>> On 05/27/2014 02:24 AM, Richard Hipp wrote:
>>> Please download beta snapshots of SQLite 3.8.5 from
>>> http://www.sqlite.org/download.html and test them in your applications.
>>  We
>>> hope to release SQLite version 3.8.5 within the next few weeks.
>>>
>>> See http://www.sqlite.org/draft/releaselog/3_8_5.html for the current
>> list
>>> of changes in 3.8.5.
>>>
>>> Please report any problems to this mailing list and/or directly to me.
>>
>> SQLite 3.8.4.3 still has the SQLITE_PROTOCOL bug with WAL databases [1],
>> and I don't see anything about that in the changes list.
>> Is it still a know bug in 3.8.5?
>>
>> I'm confused on whats the status of that bug.
>> When I reported the rng fork issue to the ML it got fixed really quickly
>> (thanks for that!), but when I asked [1] about the SQLITE_PROTOCOL
>> thats been known since 3 years [2] it got ignored.
>> Tried opening a ticket, and that one got deleted.
>>
>
> Nothing has changed with SQLITE_PROTOCOL since 3.7.6.
>
> In big bold letters at the top of the ticket entry form, it says:  "Tickets
> for issues that have not been previously discussed on the mailing list will
> usually be deleted without comment."

OK my bad, I thought the ML discussion I found in the archives from 3 years ago
counted as 'discussed'.

>
> If you are getting PROTOCOL errors, then the thing to do is to describe the
> circumstances under which the errors occur so that we can begin to try to
> understand what is going wrong.

The circumstances are very similar to those described in this commit (in a different application though):
https://github.com/NixOS/nix/commit/d05bf044441dbf8e000036d545df9689bdec0b72

I'll create a testcase that illustrates my problem and get back to you.

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

Re: SQLite version 3.8.5 beta

Jan Nijtmans
In reply to this post by Richard Hipp-3
2014-05-27 1:24 GMT+02:00 Richard Hipp <[hidden email]>:
> Please report any problems to this mailing list and/or directly to me.

$ gcc -DSQLITE_USE_FCNTL_TRACE -c sqlite3.c
sqlite3.c: In function ‘sqlite3VdbeExec’:
sqlite3.c:73742:37: error: expected expression before ‘==’ token
       if( MASKBIT(i) & p->btreeMask)==0 ) continue;
                                     ^
sqlite3.c:73742:41: error: expected statement before ‘)’ token
       if( MASKBIT(i) & p->btreeMask)==0 ) continue;

Bisecting points to this commit:
    <http://www.sqlite.org/src/info/87bf60637e>

Regards,
        Jan Nijtmans
Index: src/vdbe.c
==================================================================
--- src/vdbe.c
+++ src/vdbe.c
@@ -6278,11 +6278,11 @@
 #ifdef SQLITE_USE_FCNTL_TRACE
   zTrace = (pOp->p4.z ? pOp->p4.z : p->zSql);
   if( zTrace ){
     int i;
     for(i=0; i<db->nDb; i++){
-      if( MASKBIT(i) & p->btreeMask)==0 ) continue;
+      if( (MASKBIT(i) & p->btreeMask)==0 ) continue;
       sqlite3_file_control(db, db->aDb[i].zName, SQLITE_FCNTL_TRACE, zTrace);
     }
   }
 #endif /* SQLITE_USE_FCNTL_TRACE */
 #ifdef SQLITE_DEBUG
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite version 3.8.5 beta

jose isaias cabrera
In reply to this post by Jan Nijtmans

"Jan Nijtmans" wrote...


> 2014-05-27 5:57 GMT+02:00 jose isaias cabrera <[hidden email]>:
>> "Richard Hipp" wrote...
> ....
>>> Please report any problems to this mailing list and/or directly to me.
>
>> I am having problem using the pre-built DLL:
>> http://www.sqlite.org/snapshot/sqlite-dll-win32-x86-201405262205.zip
>>
>> I am getting this error:
>>
>> ProgName.exe Entry Point Not Found
>> The procedure entry point FlsAlloc cound not be located in the dynamic
>> Link
>> library KERNEL32.dll
>
> The entry point FlsAlloc doesn't occur anywhere in the sqlite3.c source
> code, it is generated by Visual Studio. Previous sqlite3.dll versions
> were generated with MinGW (version 3.17, I guess) that's why
> they worked fine on Windows XP.
Hmmmm... I am running the original DLL created for 3.8.4.3 on the WinXP and
it works fine, so it was not a change as far as v3.17 and before, but just
this new DLL.  I am wondering if it is also because it is a pre-release and
the "released" version will work fine.

> If you are interested to try an sqlite3.dll built by the latest
> MinGW (version 4.0.3), you can have a look here:
>    <https://sourceforge.net/projects/cyqlite/files/>
> This project is still in the startup phase, but I'm trying to fill the
> gap Microsoft is leaving behind......
Yeah, this one works.  Thanks.  I try this as one as I will need to have
support for XP for 1 or 2 years (some of the machines we used here are still
XP.)  Will these work with Vista and Server 2003 also?  Never mind, I can
give that a try later.  Thanks for this effort.  It is appreciated as much
as sqlite. :-)

Thanks.

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

Re: SQLite version 3.8.5 beta

jic
In reply to this post by Richard Hipp-3
"Jan Nijtmans" wrote...


> 2014-05-27 5:57 GMT+02:00 jose isaias cabrera <[hidden email]>:
>> "Richard Hipp" wrote...
> ....
>>> Please report any problems to this mailing list and/or directly to me.
>
>> I am having problem using the pre-built DLL:
>> http://www.sqlite.org/snapshot/sqlite-dll-win32-x86-201405262205.zip
>>
>> I am getting this error:
>>
>> ProgName.exe Entry Point Not Found
>> The procedure entry point FlsAlloc cound not be located in the dynamic
>> Link
>> library KERNEL32.dll
>
> The entry point FlsAlloc doesn't occur anywhere in the sqlite3.c source
> code, it is generated by Visual Studio. Previous sqlite3.dll versions
> were generated with MinGW (version 3.17, I guess) that's why
> they worked fine on Windows XP.
Hmmmm... I am running the original DLL created for 3.8.4.3 on the WinXP and
it works fine, so it was not a change as far as v3.17 and before, but just
this new DLL.  I am wondering if it is also because it is a pre-release and
the "released" version will work fine.

> If you are interested to try an sqlite3.dll built by the latest
> MinGW (version 4.0.3), you can have a look here:
>    <https://sourceforge.net/projects/cyqlite/files/>
> This project is still in the startup phase, but I'm trying to fill the
> gap Microsoft is leaving behind......
Yeah, this one works.  Thanks.  I try this as one as I will need to have
support for XP for 1 or 2 years (some of the machines we used here are still
XP.)  Will these work with Vista and Server 2003 also?  Never mind, I can
give that a try later.  Thanks for this effort.  It is appreciated as much
as sqlite. :-)

Thanks.

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

Re: SQLite version 3.8.5 beta

Richard Hipp-3
On Tue, May 27, 2014 at 10:39 AM, jose isaias cabrera <[hidden email]
> wrote:

> "Jan Nijtmans" wrote...
>
>
>  2014-05-27 5:57 GMT+02:00 jose isaias cabrera <[hidden email]>:
>>
>>> "Richard Hipp" wrote...
>>>
>> ....
>>
>>> Please report any problems to this mailing list and/or directly to me.
>>>>
>>>
>>  I am having problem using the pre-built DLL:
>>> http://www.sqlite.org/snapshot/sqlite-dll-win32-x86-201405262205.zip
>>>
>>> I am getting this error:
>>>
>>> ProgName.exe Entry Point Not Found
>>> The procedure entry point FlsAlloc cound not be located in the dynamic
>>> Link
>>> library KERNEL32.dll
>>>
>>
>> The entry point FlsAlloc doesn't occur anywhere in the sqlite3.c source
>> code, it is generated by Visual Studio. Previous sqlite3.dll versions
>> were generated with MinGW (version 3.17, I guess) that's why
>> they worked fine on Windows XP.
>>
> Hmmmm... I am running the original DLL created for 3.8.4.3 on the WinXP and
> it works fine, so it was not a change as far as v3.17 and before, but just
> this new DLL.  I am wondering if it is also because it is a pre-release and
> the "released" version will work fine.


It is probably because Dan usually builds the release DLLs using mingw
(32-bit) but I built those pre-release DLLs using MSVC 2012.


>
>
>  If you are interested to try an sqlite3.dll built by the latest
>> MinGW (version 4.0.3), you can have a look here:
>>    <https://sourceforge.net/projects/cyqlite/files/>
>> This project is still in the startup phase, but I'm trying to fill the
>> gap Microsoft is leaving behind......
>>
> Yeah, this one works.  Thanks.  I try this as one as I will need to have
> support for XP for 1 or 2 years (some of the machines we used here are
> still
> XP.)  Will these work with Vista and Server 2003 also?  Never mind, I can
> give that a try later.  Thanks for this effort.  It is appreciated as much
> as sqlite. :-)
>
> Thanks.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: SQLite version 3.8.5 beta

jic
"Richard Hipp" wrote...

> On Tue, May 27, 2014 at 10:39 AM, jose isaias cabrera
> <[hidden email]
>> wrote:
>
>> "Jan Nijtmans" wrote...
>>
>>
>>  2014-05-27 5:57 GMT+02:00 jose isaias cabrera <[hidden email]>:
>>>
>>>> "Richard Hipp" wrote...
>>>>
>>> ....
>>>
>>>> Please report any problems to this mailing list and/or directly to me.
>>>>>
>>>>
>>>  I am having problem using the pre-built DLL:
>>>> http://www.sqlite.org/snapshot/sqlite-dll-win32-x86-201405262205.zip
>>>>
>>>> I am getting this error:
>>>>
>>>> ProgName.exe Entry Point Not Found
>>>> The procedure entry point FlsAlloc cound not be located in the dynamic
>>>> Link
>>>> library KERNEL32.dll
>>>>
>>>
>>> The entry point FlsAlloc doesn't occur anywhere in the sqlite3.c source
>>> code, it is generated by Visual Studio. Previous sqlite3.dll versions
>>> were generated with MinGW (version 3.17, I guess) that's why
>>> they worked fine on Windows XP.
>>>
>> Hmmmm... I am running the original DLL created for 3.8.4.3 on the WinXP
>> and
>> it works fine, so it was not a change as far as v3.17 and before, but
>> just
>> this new DLL.  I am wondering if it is also because it is a pre-release
>> and
>> the "released" version will work fine.
>
>
> It is probably because Dan usually builds the release DLLs using mingw
> (32-bit) but I built those pre-release DLLs using MSVC 2012.

That's good news. I'd rather have "one place" for support. However, it's
good to know that there are alternatives such as Jan's.  Thanks both for
your great effort in providing solutions to us little people. :-)

>>  If you are interested to try an sqlite3.dll built by the latest
>>> MinGW (version 4.0.3), you can have a look here:
>>>    <https://sourceforge.net/projects/cyqlite/files/>
>>> This project is still in the startup phase, but I'm trying to fill the
>>> gap Microsoft is leaving behind......
>>>
>> Yeah, this one works.  Thanks.  I try this as one as I will need to have
>> support for XP for 1 or 2 years (some of the machines we used here are
>> still
>> XP.)  Will these work with Vista and Server 2003 also?  Never mind, I can
>> give that a try later.  Thanks for this effort.  It is appreciated as
>> much
>> as sqlite. :-)
>>
>> Thanks.

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

Re: SQLite version 3.8.5 beta

Keith Medcalf
In reply to this post by Jan Nijtmans
>If you are interested to try an sqlite3.dll built by the latest
>MinGW (version 4.0.3), you can have a look here:

I believe the latest MinGW gcc version is
gcc version 4.8.1 (GCC)




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

Re: SQLite version 3.8.5 beta

Török Edwin
In reply to this post by Török Edwin
On 05/27/2014 01:49 PM, Török Edwin wrote:

> On 05/27/2014 01:40 PM, Richard Hipp wrote:
>>> SQLite 3.8.4.3 still has the SQLITE_PROTOCOL bug with WAL databases [1],
>>> and I don't see anything about that in the changes list.
>>> Is it still a know bug in 3.8.5?
>>>
>>> I'm confused on whats the status of that bug.
>>> When I reported the rng fork issue to the ML it got fixed really quickly
>>> (thanks for that!), but when I asked [1] about the SQLITE_PROTOCOL
>>> thats been known since 3 years [2] it got ignored.
>>> Tried opening a ticket, and that one got deleted.
>>>
>>
>> Nothing has changed with SQLITE_PROTOCOL since 3.7.6.
>>
>> If you are getting PROTOCOL errors, then the thing to do is to describe the
>> circumstances under which the errors occur so that we can begin to try to
>> understand what is going wrong.
>
> The circumstances are very similar to those described in this commit (in a different application though):
> https://github.com/NixOS/nix/commit/d05bf044441dbf8e000036d545df9689bdec0b72
>
> I'll create a testcase that illustrates my problem and get back to you.

Hi,

I made a little progress on the testcase: now I have a machine where SQLITE_PROTOCOL triggers at least once if I import large amounts of data overnight.
So I can test patches, but unfortunately I wasn't able to create a small self-contained testcase yet. I added some debugging code to sqlite3.c to know which WAL_RETRYs trigger (it collects them all and prints them when cnt>100):
http://gitweb.skylable.com/gitweb/?p=sx.git;a=blob_plain;f=3rdparty/libsqlite3/sqlite3.c;hb=HEAD

And this is what I got in my logs:

walTryBeginRead: WAL_RETRY exceeded
walTryBeginRead WAL_RETRY locs: 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49
000 49000 49000 49000 49000 49000 49000 49000 49000
walTryBeginRead WAL_RETRY locs: 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49
000 49000 49000 49000 49000 49000 49000 49000 49000
walTryBeginRead WAL_RETRY locs: 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49
000 49000 49000 49000 49000 49000 49000 49000 49000
walTryBeginRead WAL_RETRY locs: 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000
statement aborts at 33: [INSERT INTO topush (block, size, node) VALUES (:b, :s, :n)] locking protocol

So it looks like it triggers here all the time:
    rc = walLockShared(pWal, WAL_READ_LOCK(mxI));
    if( rc ){
      pWal->retry_locs[cnt] = __LINE__;// added for debugging
      return rc==SQLITE_BUSY ? WAL_RETRY : rc;
    }

Database size:
-rw-r--r-- 1 nobody nogroup  29M Jun  6 07:50 /mnt/spare/sx/xfers.db
-rw-r--r-- 1 nobody nogroup 224K Jun  6 07:50 /mnt/spare/sx/xfers.db-shm
-rw-r--r-- 1 nobody nogroup  25M Jun  6 07:50 /mnt/spare/sx/xfers.db-wal

Database schema:
CREATE TABLE hashfs (key TEXT NOT NULL PRIMARY KEY, value TEXT NOT NULL);
CREATE TABLE topush (id INTEGER NOT NULL PRIMARY KEY, block BLOB(20) NOT NULL, size INTEGER NOT NULL, node BLOB(16) NOT NULL, sched_time TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f')), expiry_time TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now', '900 seconds')), UNIQUE (block, size, node));
CREATE INDEX topush_sched ON topush(sched_time ASC, expiry_time);

WAL mode:
PRAGMA synchronous=NORMAL
PRAGMA journal_mode=WAL

busy timeout: 20s
there is also a WAL hook that does a passive checkpoint at 10000 pages, and a restart checkpoint at 20000 pages
(and a passive checkpoint at 5000 pages when idle), however I've been getting these sqlite_protocol errors
even when using sqlite's default checkpoints.

On another machine:
[3299] walTryBeginRead: WAL_RETRY exceeded
[3299] walTryBeginRead WAL_RETRY locs: 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000
[3350] walTryBeginRead: WAL_RETRY exceeded
[3299] walTryBeginRead WAL_RETRY locs: 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000
[3350] walTryBeginRead WAL_RETRY locs: 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000
[3299] walTryBeginRead WAL_RETRY locs: 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000
[3350] walTryBeginRead WAL_RETRY locs: 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000
[3299] walTryBeginRead WAL_RETRY locs: 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000
[3299] statement aborts at 14: [SELECT blockno FROM blocks WHERE hash = :hash] locking protocol
[3350] walTryBeginRead WAL_RETRY locs: 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000
[3350] walTryBeginRead WAL_RETRY locs: 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000
[3350] statement aborts at 14: [SELECT blockno FROM blocks WHERE hash = :hash] locking protocol

Database size:
-rw-r--r-- 1 nobody nogroup 13M Jun  5 10:10 /mnt/spare/sx/hm00000008.db
-rw-r--r-- 1 nobody nogroup 96K Jun  6 06:25 /mnt/spare/sx/hm00000008.db-shm
-rw-r--r-- 1 nobody nogroup 11M Jun  6 06:25 /mnt/spare/sx/hm00000008.db-wal

Database schema:
CREATE TABLE hashfs (key TEXT NOT NULL PRIMARY KEY, value TEXT NOT NULL);
CREATE TABLE blocks (hash BLOB(20) NOT NULL PRIMARY KEY, blockno INTEGER NOT NULL);
CREATE TABLE avail (blocknumber INTEGER NOT NULL PRIMARY KEY ASC);

WAL mode:
PRAGMA synchronous=NORMAL
PRAGMA journal_mode=WAL

same busy timeout/wal hook
AFAICT there were no checkpoint running during those WAL_RETRY errors.

Some info about these two machines:
machine1: Linux 3.2.0-4-amd64 #1 SMP Debian 3.2.51-1 x86_64
machine2: Linux 3.2.0-4-amd64 #1 SMP Debian 3.2.54-2 x86_64

gcc version 4.7.2 (Debian 4.7.2-5)
sqlite 3.8.4.3 amalgamation, compile flags: -DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_THREADSAFE=0  -DHAVE_POSIX_FALLOCATE=1 -DHAVE_USLEEP=1 -g -O2
sqlite3_sourceid(): 2014-04-03 16:53:12 a611fa96c4a848614efe899130359c9f6fb889c3
sqlite3_compileoption_get(): OMIT_LOAD_EXTENSION SYSTEM_MALLOC THREADSAFE=0

The database is on a HDD:
/dev/mapper/vg0-indian01--spare on /mnt/spare type ext4 (rw,relatime,user_xattr,barrier=1,data=ordered)
vg0 uses /dev/md1 as a physical volume, which is a RAID1 of 2 Seagate ST2000NM0033-9ZM175 drives.

Server memory usage now (unfortunately I don't have the exact values from when the WAL_RETRY happened):
KiB Mem:  49560152 total, 49065808 used,   494344 free,  9312792 buffers
KiB Swap: 16777212 total,  2888208 used, 13889004 free, 16245816 cached

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

SQLITE_PROTOCOL errors (was: Re: SQLite version 3.8.5 beta)

Stelios Bounanos
In reply to this post by Richard Hipp-3
> On Tue, 27 May 2014 06:40:08 -0400, Richard Hipp <[hidden email]> said:

> Nothing has changed with SQLITE_PROTOCOL since 3.7.6.

> In big bold letters at the top of the ticket entry form, it says:  "Tickets
> for issues that have not been previously discussed on the mailing list will
> usually be deleted without comment."

> If you are getting PROTOCOL errors, then the thing to do is to describe the
> circumstances under which the errors occur so that we can begin to try to
> understand what is going wrong.

We occasionally see PROTOCOL errors in one of our applications, a somewhat
atypical caching proxy that uses SQLite (3.7.7.1) to index its cached data.
I will try to describe the most pertinent details.  Apologies for the very
long email.

The application runs on dedicated mid to high-end Linux (modified RHEL5)
servers with several (dozen) disks.  There is an SQLite cache-index
database per disk, on an xfs filesystem, accessed concurrently by a writer
thread and several (usually 5) reader threads, all with their own database
handles.  Write requests are dispatched to the writer thread, read requests
are sent to the readers in round-robin fashion.

All databases are configured with 16K pages and WAL journalling.  We have
our own busy handler, which increments some counters (not involving
locking) but is otherwise identical to sqliteDefaultBusyCallback.  The busy
timeout is normally set to 1000ms.

We also have our own WAL commit hook, whose behaviour is different to
SQLite's.  When the number of WAL pages is less than 1000, the callback
returns immediately.  Between 1000 and 1500 pages, it tries a PASSIVE
checkpoint.  Above 1500 pages it tries a RESTART checkpoint.  Any
SQLITE_BUSY errors returned by a RESTART checkpoint attempt are ignored up
to a yet higher threshold of uncheckpointed pages.

Because the disks containing the cache-index databases may be physically
ejected at any time, and because SQLite's -shm WAL index is mmapped, and,
further, because our application is not prepared to handle SIGBUS, we
arrange for the -shm files to be stored outside the filesystems that
contain the database files themselves on non-removable storage.


Most PROTOCOL errors we see occur in the lab, under stress tests, on
hardware that tends to have slow I/O, large disks and relatively little
RAM, with the application taking several dozen major page faults per
second.  There are long I/O queues and lots of churn in the page cache.
The PROTOCOL errors occur very infrequently, perhaps ~15 times in several
million successfully executed statements.

The query that tends to fail with SQLITE_PROTOCOL most often is a SELECT
that implements the main cache look-up -- unsurprisingly, the query that
gets run most often.  With SQLite's global logging enabled, whenever we
have observed this error, the log message has looked like so:

  sqlite message (15): statement aborts at 29: [SELECT a1, a2, a3, a4, a5,
  a6, a7, a8 FROM table_a WHERE (a3=? AND a2=?) ORDER BY a1 ASC] locking protocol

The statement always aborts at the same place (probably not surprising, see
the explain output below).  Less often, a PROTOCOL error occurs while
executing one of the write queries.  Here is an example log message for the
query that stores data:

  sqlite message (15): statement aborts at 64: [INSERT OR REPLACE INTO
  table_b (b1, b2, b3, b4, b5, b6) VALUES (?,?,?,?,?,?)] locking protocol

The same comment applies to this query w.r.t. where it aborts.


A particularly vexing aspect of this problem is that it occasionally causes
our application to enter a state in which, for one or more databases,
RESTART checkpoints always fail with SQLITE_BUSY and the WAL keeps growing.
Whenever this has come about, our logs have shown that a read and a write
query, usually the two mentioned above, have previously failed with
SQLITE_PROTOCOL at about the same time (well, in the same second).  (We can
tell that both queries executed on the same db from some additional error
logging that I have omitted).

Because this is highly correlated to the PROTOCOL errors and because at
this point we are fairly certain that we always reset statements, roll
transactions back and so on when we handle errors, there is the remote
possibility that there is a SQLite bug hiding in this area.  This has
prompted us to try and tweak our application's behaviour to stop the
PROTOCOL errors from occurring.

So far it seems that PROTOCOL errors are caused by high concurrency,
specifically by contention for the WAL reader locks.  We can make the
errors go away by decreasing the number of readers to (e.g.) 2 -- but this
may have some still-unquantified performance impact on our application.

We can also make them go away by rebuilding SQLite with a bigger
(e.g. doubled) SQLITE_SHM_NLOCK and keep the same number of threads.  In
either case we don't yet consider our tests conclusive -- and in any case
our understanding of the code in wal.c is highly incomplete to say the
least :-)  But we'd very much like to get to the bottom of this.


That's all there is for now.  I'm sure there are important bits I've left
out.  Please feel free to ask.


Explain output for the queries mentioned above.

explain SELECT a1, a2, a3, a4, a5, a6, a7, a8
FROM table_a WHERE (a3=? AND a2=?) ORDER BY a1 ASC

  addr  opcode         p1    p2    p3    p4             p5  comment
  ----  -------------  ----  ----  ----  -------------  --  -------------
  0     Trace          0     0     0                    00
  1     Noop           0     0     0                    00
  2     Variable       1     1     0                    00
  3     Variable       2     2     0                    00
  4     Goto           0     29    0                    00
  5     OpenRead       0     10    0     9              00
  6     OpenRead       2     11    0     keyinfo(3,BINARY,BINARY)  00
  7     SCopy          1     3     0                    00
  8     IsNull         3     26    0                    00
  9     SCopy          2     4     0                    00
  10    IsNull         4     26    0                    00
  11    Affinity       3     2     0     aa             00
  12    SeekGe         2     26    3     2              00
  13    IdxGE          2     26    3     2              01
  14    IdxRowid       2     5     0                    00
  15    Seek           0     5     0                    00
  16    IdxRowid       2     6     0                    00
  17    Column         2     1     7                    00
  18    Column         2     0     8                    00
  19    Column         0     3     9                    00
  20    Column         0     4     10                   00
  21    Column         0     5     11                   00
  22    Column         0     7     12                   00
  23    Column         0     8     13                   00
  24    ResultRow      6     8     0                    00
  25    Next           2     13    0                    00
  26    Close          0     0     0                    00
  27    Close          2     0     0                    00
  28    Halt           0     0     0                    00
  29    Transaction    0     0     0                    00
  30    VerifyCookie   0     10    0                    00
  31    TableLock      0     10    0     table_a        00
  32    Goto           0     5     0                    00

explain INSERT OR REPLACE INTO table_b (b1, b2, b3, b4, b5, b6)
VALUES (?,?,?,?,?,?)

  addr  opcode         p1    p2    p3    p4             p5  comment
  ----  -------------  ----  ----  ----  -------------  --  -------------
  0     Trace          0     0     0                    00
  1     Goto           0     64    0                    00
  2     OpenWrite      0     6     0     6              00
  3     OpenWrite      1     9     0     keyinfo(1,BINARY)  00
  4     OpenWrite      2     8     0     keyinfo(2,BINARY,BINARY)  00
  5     OpenWrite      3     7     0     keyinfo(2,BINARY,BINARY)  00
  6     NewRowid       0     4     0                    00
  7     Variable       1     5     0                    00
  8     Variable       2     6     0                    00
  9     Variable       3     7     0                    00
  10    Variable       4     8     0                    00
  11    Variable       5     9     0                    00
  12    Variable       6     10    0                    00
  13    SCopy          10    11    0                    00
  14    SCopy          4     12    0                    00
  15    MakeRecord     11    2     1     bb             00
  16    SCopy          4     13    0                    00
  17    IsUnique       1     31    13    11             00
  18    NotExists      0     31    13                   00
  19    Rowid          0     12    0                    00
  20    Column         0     5     11                   00
  21    IdxDelete      1     11    2                    00
  22    Rowid          0     16    0                    00
  23    Column         0     0     14                   00
  24    Column         0     3     15                   00
  25    IdxDelete      2     14    3                    00
  26    Rowid          0     16    0                    00
  27    Column         0     0     14                   00
  28    Column         0     1     15                   00
  29    IdxDelete      3     14    3                    00
  30    Delete         0     0     0                    00
  31    SCopy          5     14    0                    00
  32    SCopy          8     15    0                    00
  33    SCopy          4     16    0                    00
  34    MakeRecord     14    3     2     ddb            00
  35    SCopy          5     14    0                    00
  36    SCopy          6     15    0                    00
  37    SCopy          4     16    0                    00
  38    MakeRecord     14    3     3     ddb            00
  39    SCopy          4     13    0                    00
  40    IsUnique       3     54    13    14             00
  41    NotExists      0     54    13                   00
  42    Rowid          0     15    0                    00
  43    Column         0     5     14                   00
  44    IdxDelete      1     14    2                    00
  45    Rowid          0     19    0                    00
  46    Column         0     0     17                   00
  47    Column         0     3     18                   00
  48    IdxDelete      2     17    3                    00
  49    Rowid          0     19    0                    00
  50    Column         0     0     17                   00
  51    Column         0     1     18                   00
  52    IdxDelete      3     17    3                    00
  53    Delete         0     0     0                    00
  54    IdxInsert      3     3     0                    00
  55    IdxInsert      2     2     0                    00
  56    IdxInsert      1     1     0                    00
  57    MakeRecord     5     6     13    ddddbb         00
  58    Insert         0     13    4     table_b        0b
  59    Close          0     0     0                    00
  60    Close          1     0     0                    00
  61    Close          2     0     0                    00
  62    Close          3     0     0                    00
  63    Halt           0     0     0                    00
  64    Transaction    0     1     0                    00
  65    VerifyCookie   0     10    0                    00
  66    TableLock      0     6     1     table_b        00
  67    Goto           0     2     0                    00


Rgds,

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

SQLITE_PROTOCOL errors

Török Edwin
In reply to this post by Török Edwin
On 06/06/2014 11:37 AM, Török Edwin wrote:

> On 05/27/2014 01:49 PM, Török Edwin wrote:
>> On 05/27/2014 01:40 PM, Richard Hipp wrote:
>>> If you are getting PROTOCOL errors, then the thing to do is to describe the
>>> circumstances under which the errors occur so that we can begin to try to
>>> understand what is going wrong.
>>
>> The circumstances are very similar to those described in this commit (in a different application though):
>> https://github.com/NixOS/nix/commit/d05bf044441dbf8e000036d545df9689bdec0b72
>>
>> I'll create a testcase that illustrates my problem and get back to you.
>
> Hi,
>
> I made a little progress on the testcase: now I have a machine where SQLITE_PROTOCOL triggers at least once if I import large amounts of data overnight.
> So I can test patches, but unfortunately I wasn't able to create a small self-contained testcase yet. I added some debugging code to sqlite3.c to know which WAL_RETRYs trigger (it collects them all and prints them when cnt>100):
> http://gitweb.skylable.com/gitweb/?p=sx.git;a=blob_plain;f=3rdparty/libsqlite3/sqlite3.c;hb=HEAD
>
> And this is what I got in my logs:
>
> walTryBeginRead: WAL_RETRY exceeded
> walTryBeginRead WAL_RETRY locs: 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49
> 000 49000 49000 49000 49000 49000 49000 49000 49000
> walTryBeginRead WAL_RETRY locs: 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49
> 000 49000 49000 49000 49000 49000 49000 49000 49000
> walTryBeginRead WAL_RETRY locs: 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49
> 000 49000 49000 49000 49000 49000 49000 49000 49000
> walTryBeginRead WAL_RETRY locs: 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000 49000
> statement aborts at 33: [INSERT INTO topush (block, size, node) VALUES (:b, :s, :n)] locking protocol
>
> So it looks like it triggers here all the time:
>     rc = walLockShared(pWal, WAL_READ_LOCK(mxI));
>     if( rc ){
>       pWal->retry_locs[cnt] = __LINE__;// added for debugging
>       return rc==SQLITE_BUSY ? WAL_RETRY : rc;
>     }
>

Do you need more information to fix the PROTOCOL errors?

Is it safe to retry the query if I get a PROTOCOL error? (only SQlite3 itself is using the DB/WAL files, and all SQlite3 versions match)

Best regards,
--Edwin
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users