SQLITE db locking problem

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

SQLITE db locking problem

Mark Wynter
Hi everyone

I’m having issues with SQLITE as the db driver - I encounter this BUSY warning.  Only occurs when patching a particular map, in this case  “temp_nbt_cleaned" which is the largest of the datasets being patched.  If I work with smaller datasets, it works fine.  Pre-patching, SQLITE DB size is only 170MB.

I’ve come across various threads reporting something similar with db locking.  But couldn’t find a resolution.
I’m running grass70 on Centos65, hosted on AWS..  SQLite is version 3.6.23.1 

This is the error message.

GRASS 7.0.0svn (nodeclean):/var/tmp > v.patch -e input=temp_t_cleaned,temp_b_cleaned,temp_nbt_cleaned out=temp_patched
Patching vector map <temp_t_cleaned>...
Patching vector map <temp_b_cleaned>...
Patching vector map <temp_nbt_cleaned>...
WARNING: Busy SQLITE db, already waiting for 10 seconds…
WARNING: Busy SQLITE db, already waiting for 20 seconds…
etc
etc

I encounter no problems patching the maps, including “temp_nbt_cleaned" if I use PG db driver, or DBF driver.

building topology for vector map <temp_patched@PERMANENT>...
Registering primitives...
1860504 primitives registered
9027638 vertices registered
Building areas...
 100%
0 areas built
0 isles built
Attaching islands...
Attaching centroids...
 100%
Number of nodes: 1457611
Number of primitives: 1860504
Number of points: 0
Number of lines: 1860504
Number of boundaries: 0
Number of centroids: 0
Number of areas: 0
Number of isles: 0
Intersections at borders will have to be snapped
Lines common between files will have to be edited
The header information also may have to be edited
v.patch complete. 3 vector maps patched


I’m more than comfortable using PG in the backend, but I find v.out.postgis too slow when exporting the resultant dataset back to postgis (possibly because its simultaneously trying to read and write to the same db). I haven’t tested whether v.out.ogr quicker than v.out.postgis.

Any assistance resolving the SQLite issue would be greatly appreciated.

Kind regards

Mark


_______________________________________________
grass-user mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/grass-user
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE db locking problem

Markus Metz-3
On Sun, Jan 18, 2015 at 3:23 PM, Mark Wynter <[hidden email]> wrote:

> Hi everyone
>
> I’m having issues with SQLITE as the db driver - I encounter this BUSY
> warning.  Only occurs when patching a particular map, in this case
> “temp_nbt_cleaned" which is the largest of the datasets being patched.  If I
> work with smaller datasets, it works fine.  Pre-patching, SQLITE DB size is
> only 170MB.
>
> I’ve come across various threads reporting something similar with db
> locking.  But couldn’t find a resolution.
> I’m running grass70 on Centos65, hosted on AWS..  SQLite is version 3.6.23.1

I thought the official SQLite version for Centos65 (and Centos66) is
3.6.20. Could there be a conflict between the official version and
your version 3.6.23.1?

>
> This is the error message.
>
> GRASS 7.0.0svn (nodeclean):/var/tmp > v.patch -e
> input=temp_t_cleaned,temp_b_cleaned,temp_nbt_cleaned out=temp_patched
> Patching vector map <temp_t_cleaned>...
> Patching vector map <temp_b_cleaned>...
> Patching vector map <temp_nbt_cleaned>...
> WARNING: Busy SQLITE db, already waiting for 10 seconds…
> WARNING: Busy SQLITE db, already waiting for 20 seconds…
> etc
> etc

I can not reproduce the problem on Fedora 20 with SQLite 3.8.7.4 and
Scientific Linux 6.6 with SQLite 3.6.20. Are you using default db
connection settings for SQLite or is the GRASS SQLite database in a
non-standard location?

Markus M

>
> I encounter no problems patching the maps, including “temp_nbt_cleaned" if I
> use PG db driver, or DBF driver.
>
> building topology for vector map <temp_patched@PERMANENT>...
> Registering primitives...
> 1860504 primitives registered
> 9027638 vertices registered
> Building areas...
>  100%
> 0 areas built
> 0 isles built
> Attaching islands...
> Attaching centroids...
>  100%
> Number of nodes: 1457611
> Number of primitives: 1860504
> Number of points: 0
> Number of lines: 1860504
> Number of boundaries: 0
> Number of centroids: 0
> Number of areas: 0
> Number of isles: 0
> Intersections at borders will have to be snapped
> Lines common between files will have to be edited
> The header information also may have to be edited
> v.patch complete. 3 vector maps patched
>
>
> I’m more than comfortable using PG in the backend, but I find v.out.postgis
> too slow when exporting the resultant dataset back to postgis (possibly
> because its simultaneously trying to read and write to the same db). I
> haven’t tested whether v.out.ogr quicker than v.out.postgis.
>
> Any assistance resolving the SQLite issue would be greatly appreciated.
>
> Kind regards
>
> Mark
>
>
> _______________________________________________
> grass-user mailing list
> [hidden email]
> http://lists.osgeo.org/mailman/listinfo/grass-user
_______________________________________________
grass-user mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/grass-user
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE db locking problem

Markus Metz-3
On Tue, Jan 20, 2015 at 10:31 PM, Markus Metz
<[hidden email]> wrote:

> On Sun, Jan 18, 2015 at 3:23 PM, Mark Wynter <[hidden email]> wrote:
>> Hi everyone
>>
>> I’m having issues with SQLITE as the db driver - I encounter this BUSY
>> warning.  Only occurs when patching a particular map, in this case
>> “temp_nbt_cleaned" which is the largest of the datasets being patched.  If I
>> work with smaller datasets, it works fine.  Pre-patching, SQLITE DB size is
>> only 170MB.
>>
>> I’ve come across various threads reporting something similar with db
>> locking.  But couldn’t find a resolution.
>> I’m running grass70 on Centos65, hosted on AWS..  SQLite is version 3.6.23.1
>
> I thought the official SQLite version for Centos65 (and Centos66) is
> 3.6.20. Could there be a conflict between the official version and
> your version 3.6.23.1?
>
>>
>> This is the error message.
>>
>> GRASS 7.0.0svn (nodeclean):/var/tmp > v.patch -e
>> input=temp_t_cleaned,temp_b_cleaned,temp_nbt_cleaned out=temp_patched
>> Patching vector map <temp_t_cleaned>...
>> Patching vector map <temp_b_cleaned>...
>> Patching vector map <temp_nbt_cleaned>...
>> WARNING: Busy SQLITE db, already waiting for 10 seconds…
>> WARNING: Busy SQLITE db, already waiting for 20 seconds…
>> etc
>> etc
>
> I can not reproduce the problem on Fedora 20 with SQLite 3.8.7.4 and
> Scientific Linux 6.6 with SQLite 3.6.20. Are you using default db
> connection settings for SQLite or is the GRASS SQLite database in a
> non-standard location?

I was able to reproduce the problem with larger vector maps.
Apparently SQLite does not like to have the same database opened twice
(you can do multiple operations on the same database, but it is safer
to have the db opened only once).

Fixed in trunk r64277.

Markus M

>
> Markus M
>>
>> I encounter no problems patching the maps, including “temp_nbt_cleaned" if I
>> use PG db driver, or DBF driver.
>>
>> building topology for vector map <temp_patched@PERMANENT>...
>> Registering primitives...
>> 1860504 primitives registered
>> 9027638 vertices registered
>> Building areas...
>>  100%
>> 0 areas built
>> 0 isles built
>> Attaching islands...
>> Attaching centroids...
>>  100%
>> Number of nodes: 1457611
>> Number of primitives: 1860504
>> Number of points: 0
>> Number of lines: 1860504
>> Number of boundaries: 0
>> Number of centroids: 0
>> Number of areas: 0
>> Number of isles: 0
>> Intersections at borders will have to be snapped
>> Lines common between files will have to be edited
>> The header information also may have to be edited
>> v.patch complete. 3 vector maps patched
>>
>>
>> I’m more than comfortable using PG in the backend, but I find v.out.postgis
>> too slow when exporting the resultant dataset back to postgis (possibly
>> because its simultaneously trying to read and write to the same db). I
>> haven’t tested whether v.out.ogr quicker than v.out.postgis.
>>
>> Any assistance resolving the SQLite issue would be greatly appreciated.
>>
>> Kind regards
>>
>> Mark
>>
>>
>> _______________________________________________
>> grass-user mailing list
>> [hidden email]
>> http://lists.osgeo.org/mailman/listinfo/grass-user
_______________________________________________
grass-user mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/grass-user
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE db locking problem

Mark Wynter
Thanks Marcus - am I right in understanding that read and write resulted in the db being opened twice? Was this confined to just v.patch?  Anything a user needs to do differently, apart from update?
_______________________________________________
grass-user mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/grass-user
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE db locking problem

Markus Metz-3
On Thu, Jan 22, 2015 at 9:12 AM, Mark Wynter <[hidden email]> wrote:
> Thanks Marcus - am I right in understanding that read and write resulted in the db being opened twice?
Yes

> Was this confined to just v.patch?

Probably not. All modules modifying attributes and working with more
than one table need to be checked.

>  Anything a user needs to do differently, apart from update?

No, just update v.patch.

SQLite seems to behave different than the other db backends. The
SQLite locking mechanism is annoying, and the fact that the same
SQLite db should not be opened twice is equally annoying. Well, it's a
lite SQL db...

Markus M
_______________________________________________
grass-user mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/grass-user