Re: GeoPackage deadlocks (Andrea Peri)

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

Re: GeoPackage deadlocks (Andrea Peri)

Paul Wittle-3
Hi Andrea and Enrico,

Thank you for providing an answer even if it was not what I was hoping to hear; it was the conclusion I'd reached myself.

Has this issue been considered given the official move from shapefile to geopackage as the default format?

My understanding was that shapefiles could be used over a network and whilst multiple people editing was dangerous it did not cause deadlocks.

Combined with the issues relating to MapInfo tab files over a network I've got into some hot water over my attempt to get QGIS rolled out in our organisation now. I'm going to have to think carefully about my next steps as it can be difficult to educate large numbers of staff and the two issues mean that people using QGIS incorrectly can cause pretty big issues.

To summarise,
 - You open a geopackage and save to the network; someone else comes along and opens it in their QGIS and everything looks okay until the PCs deadlock...ICT help calls and data corruption may occur.
 - You open a MapInfo tab file from the network and it looks fine (accept that it my draw in a user projection). MapInfo user receive errors but this is unknown to the QGIS user...ICT help calls result.

Whilst I'm glad the forums have helped to diagnose both behaviours I would personally say that the issues pose a bit of a threat to software adoption by larger companies and institutions that may be using Windows networks and potentially migrating from MapInfo.

In both cases my gut feel is that the best solution might be to look into use detection. If GDAL (I assume) can be improved to detect that either file type is already open then it might be possible to simply ban a second user from opening the file at the same time. This might frustrate some users but most importantly it would make the application safer from an ICT perspective.

These are of course just my personal opinion from my particular use case so please don't be offended by them if you disagree but I'd be really happy to hear how others are approaching the issues and/or opposing views?

Many thanks,
Paul

Message: 4
Date: Thu, 26 Sep 2019 11:10:45 +0200
From: Andrea Peri <[hidden email]>
To: Enrico Fiore <[hidden email]>
Cc: qgis-user <[hidden email]>
Subject: Re: [Qgis-user] GeoPackage deadlocks
Message-ID:
        <[hidden email]>
Content-Type: text/plain; charset="utf-8"

Hi,
Geopackage is an Exchange format. Is not affordable in a multiuser data entry environment.
This require a dbms like postgres + postfis.

Regards,
Andrea.


Il gio 26 set 2019, 10:18 Enrico Fiore <[hidden email]> ha scritto:

> Hi,
>
> Is anyone else getting occasional deadlocks when using GeoPackages in
> the Long Term Release of QGIS?
>
> We are using them over a Windows network so there are multiple users
> trying to access the same GeoPackage files. It all seems fine and two
> or more can open the file to look at but when someone edits it you
> sometimes hit a deadlock where none of the QGIS applications will shut down.
>
> I have the same issue and the same scenario. Multiple users that
> trying to access a the same GeoPackage file located in a server causes QGIS deadlock.
>
> The issue rises also if no geopackage layer are in editing mode, but
> simply loaded in one QGIS view.
>
> There is a solution for this behaviour or is this a bug?
>
> Regards,
>
>
> Enrico
> _______________________________________________
> Qgis-user mailing list
> [hidden email]
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20190926/10ada3cf/attachment-0001.html>

------------------------------

This e-mail and any files transmitted with it are intended solely for the use of the individual or entity to whom they are addressed. It may contain unclassified but sensitive or protectively marked material and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All traffic may be subject to recording and/or monitoring in accordance with relevant legislation. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Dorset Council. Dorset Council does not accept service of documents by fax or other electronic means. Virus checking: Whilst all reasonable steps have been taken to ensure that this electronic communication and its attachments whether encoded, encrypted or otherwise supplied are free from computer viruses, Dorset Council accepts no liability in respect of any loss, cost, damage or expense suffered as a result of accessing this message or any of its attachments. For information on how Dorset Council processes your information, please see www.dorsetcouncil.gov.uk/416433
_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks (Andrea Peri)

Alessandro Pasotti-2

On Fri, Sep 27, 2019 at 9:13 AM Paul Wittle <[hidden email]> wrote:
Hi Andrea and Enrico,

Thank you for providing an answer even if it was not what I was hoping to hear; it was the conclusion I'd reached myself.

Has this issue been considered given the official move from shapefile to geopackage as the default format?

My understanding was that shapefiles could be used over a network and whilst multiple people editing was dangerous it did not cause deadlocks.

Combined with the issues relating to MapInfo tab files over a network I've got into some hot water over my attempt to get QGIS rolled out in our organisation now. I'm going to have to think carefully about my next steps as it can be difficult to educate large numbers of staff and the two issues mean that people using QGIS incorrectly can cause pretty big issues.

To summarise,
 - You open a geopackage and save to the network; someone else comes along and opens it in their QGIS and everything looks okay until the PCs deadlock...ICT help calls and data corruption may occur.
 - You open a MapInfo tab file from the network and it looks fine (accept that it my draw in a user projection). MapInfo user receive errors but this is unknown to the QGIS user...ICT help calls result.

Whilst I'm glad the forums have helped to diagnose both behaviours I would personally say that the issues pose a bit of a threat to software adoption by larger companies and institutions that may be using Windows networks and potentially migrating from MapInfo.

In both cases my gut feel is that the best solution might be to look into use detection. If GDAL (I assume) can be improved to detect that either file type is already open then it might be possible to simply ban a second user from opening the file at the same time. This might frustrate some users but most importantly it would make the application safer from an ICT perspective.

These are of course just my personal opinion from my particular use case so please don't be offended by them if you disagree but I'd be really happy to hear how others are approaching the issues and/or opposing views?

Many thanks,
Paul


Hi Paul,

A deadlock is a QGIS bug (and quite a serious one).

Can you please file an issue on https://github.com/qgis/QGIS/issues ?
Please check if it's not there, and if it is, you can add your comments to the existing issue.

Thanks

--
Alessandro Pasotti
w3:   www.itopen.it

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks (Andrea Peri)

Andrea Peri
In reply to this post by Paul Wittle-3

>In both cases my gut feel is that the best solution might 
>be to look into use detection. If GDAL (I assume) can be 
>improved to detect that either file type is already open 
>then it might be possible to simply ban a second user 
>from opening the file at the same time. This might 
>frustrate some users but most importantly it would 
>make the application safer from an ICT perspective.

Hi, afaik, internet and http is a sessionless protocol so i guess is quite impossible to gdal understand that an user (another user) is using a file.
To have this kind of communication is use a messaging queue manager like jboss.

This is my personal opinion.
Regards,
Andrea.



Il ven 27 set 2019, 09:13 Paul Wittle <[hidden email]> ha scritto:
Hi Andrea and Enrico,

Thank you for providing an answer even if it was not what I was hoping to hear; it was the conclusion I'd reached myself.

Has this issue been considered given the official move from shapefile to geopackage as the default format?

My understanding was that shapefiles could be used over a network and whilst multiple people editing was dangerous it did not cause deadlocks.

Combined with the issues relating to MapInfo tab files over a network I've got into some hot water over my attempt to get QGIS rolled out in our organisation now. I'm going to have to think carefully about my next steps as it can be difficult to educate large numbers of staff and the two issues mean that people using QGIS incorrectly can cause pretty big issues.

To summarise,
 - You open a geopackage and save to the network; someone else comes along and opens it in their QGIS and everything looks okay until the PCs deadlock...ICT help calls and data corruption may occur.
 - You open a MapInfo tab file from the network and it looks fine (accept that it my draw in a user projection). MapInfo user receive errors but this is unknown to the QGIS user...ICT help calls result.

Whilst I'm glad the forums have helped to diagnose both behaviours I would personally say that the issues pose a bit of a threat to software adoption by larger companies and institutions that may be using Windows networks and potentially migrating from MapInfo.

In both cases my gut feel is that the best solution might be to look into use detection. If GDAL (I assume) can be improved to detect that either file type is already open then it might be possible to simply ban a second user from opening the file at the same time. This might frustrate some users but most importantly it would make the application safer from an ICT perspective.

These are of course just my personal opinion from my particular use case so please don't be offended by them if you disagree but I'd be really happy to hear how others are approaching the issues and/or opposing views?

Many thanks,
Paul

Message: 4
Date: Thu, 26 Sep 2019 11:10:45 +0200
From: Andrea Peri <[hidden email]>
To: Enrico Fiore <[hidden email]>
Cc: qgis-user <[hidden email]>
Subject: Re: [Qgis-user] GeoPackage deadlocks
Message-ID:
        <[hidden email]>
Content-Type: text/plain; charset="utf-8"

Hi,
Geopackage is an Exchange format. Is not affordable in a multiuser data entry environment.
This require a dbms like postgres + postfis.

Regards,
Andrea.


Il gio 26 set 2019, 10:18 Enrico Fiore <[hidden email]> ha scritto:

> Hi,
>
> Is anyone else getting occasional deadlocks when using GeoPackages in
> the Long Term Release of QGIS?
>
> We are using them over a Windows network so there are multiple users
> trying to access the same GeoPackage files. It all seems fine and two
> or more can open the file to look at but when someone edits it you
> sometimes hit a deadlock where none of the QGIS applications will shut down.
>
> I have the same issue and the same scenario. Multiple users that
> trying to access a the same GeoPackage file located in a server causes QGIS deadlock.
>
> The issue rises also if no geopackage layer are in editing mode, but
> simply loaded in one QGIS view.
>
> There is a solution for this behaviour or is this a bug?
>
> Regards,
>
>
> Enrico
> _______________________________________________
> Qgis-user mailing list
> [hidden email]
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20190926/10ada3cf/attachment-0001.html>

------------------------------

This e-mail and any files transmitted with it are intended solely for the use of the individual or entity to whom they are addressed. It may contain unclassified but sensitive or protectively marked material and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All traffic may be subject to recording and/or monitoring in accordance with relevant legislation. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Dorset Council. Dorset Council does not accept service of documents by fax or other electronic means. Virus checking: Whilst all reasonable steps have been taken to ensure that this electronic communication and its attachments whether encoded, encrypted or otherwise supplied are free from computer viruses, Dorset Council accepts no liability in respect of any loss, cost, damage or expense suffered as a result of accessing this message or any of its attachments. For information on how Dorset Council processes your information, please see www.dorsetcouncil.gov.uk/416433
_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks (Andrea Peri)

Paul Wittle-3
In reply to this post by Alessandro Pasotti-2

Hi,

 

I couldn’t see a bug report which I thought matched so I have added one as requests but please accept my apologies if it does turn out to be a duplicate or if my use of the word deadlock is incorrect.

 

The ticket can be found at https://github.com/qgis/QGIS/issues/32034

 

It would be great if others that have experienced the issue could provide any further relevant comments as I’ve not got it as well documented as I’d like. Sorry, time constraints in the office.

 

Many thanks,

Paul

 

 

From: Alessandro Pasotti <[hidden email]>
Sent: 27 September 2019 08:20
To: Paul Wittle <[hidden email]>
Cc: [hidden email]
Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

 

 

On Fri, Sep 27, 2019 at 9:13 AM Paul Wittle <[hidden email]> wrote:

Hi Andrea and Enrico,

Thank you for providing an answer even if it was not what I was hoping to hear; it was the conclusion I'd reached myself.

Has this issue been considered given the official move from shapefile to geopackage as the default format?

My understanding was that shapefiles could be used over a network and whilst multiple people editing was dangerous it did not cause deadlocks.

Combined with the issues relating to MapInfo tab files over a network I've got into some hot water over my attempt to get QGIS rolled out in our organisation now. I'm going to have to think carefully about my next steps as it can be difficult to educate large numbers of staff and the two issues mean that people using QGIS incorrectly can cause pretty big issues.

To summarise,
 - You open a geopackage and save to the network; someone else comes along and opens it in their QGIS and everything looks okay until the PCs deadlock...ICT help calls and data corruption may occur.
 - You open a MapInfo tab file from the network and it looks fine (accept that it my draw in a user projection). MapInfo user receive errors but this is unknown to the QGIS user...ICT help calls result.

Whilst I'm glad the forums have helped to diagnose both behaviours I would personally say that the issues pose a bit of a threat to software adoption by larger companies and institutions that may be using Windows networks and potentially migrating from MapInfo.

In both cases my gut feel is that the best solution might be to look into use detection. If GDAL (I assume) can be improved to detect that either file type is already open then it might be possible to simply ban a second user from opening the file at the same time. This might frustrate some users but most importantly it would make the application safer from an ICT perspective.

These are of course just my personal opinion from my particular use case so please don't be offended by them if you disagree but I'd be really happy to hear how others are approaching the issues and/or opposing views?

Many thanks,
Paul

 

 

Hi Paul,

 

A deadlock is a QGIS bug (and quite a serious one).

 

Can you please file an issue on https://github.com/qgis/QGIS/issues ?

Please check if it's not there, and if it is, you can add your comments to the existing issue.

 

Thanks

 

--

Alessandro Pasotti
w3:   www.itopen.it

This e-mail and any files transmitted with it are intended solely for the use of the individual or entity to whom they are addressed. It may contain unclassified but sensitive or protectively marked material and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All traffic may be subject to recording and/or monitoring in accordance with relevant legislation. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Dorset Council. Dorset Council does not accept service of documents by fax or other electronic means. Virus checking: Whilst all reasonable steps have been taken to ensure that this electronic communication and its attachments whether encoded, encrypted or otherwise supplied are free from computer viruses, Dorset Council accepts no liability in respect of any loss, cost, damage or expense suffered as a result of accessing this message or any of its attachments. For information on how Dorset Council processes your information, please see www.dorsetcouncil.gov.uk/416433
_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks (Andrea Peri)

ginetto
without resting importance to the issue, reading the bug report, I can read you moved from oracle spatial to geopackage! why not postgis?

Luigi Pirelli

**************************************************************************************************
* LinkedIn: https://www.linkedin.com/in/luigipirelli
* Stackexchange: http://gis.stackexchange.com/users/19667/luigi-pirelli
* GitHub: https://github.com/luipir
* Book: Mastering QGIS3 - 3rd Edition
* Hire a team: http://www.qcooperative.net
**************************************************************************************************


On Fri, 27 Sep 2019 at 09:39, Paul Wittle <[hidden email]> wrote:

Hi,

 

I couldn’t see a bug report which I thought matched so I have added one as requests but please accept my apologies if it does turn out to be a duplicate or if my use of the word deadlock is incorrect.

 

The ticket can be found at https://github.com/qgis/QGIS/issues/32034

 

It would be great if others that have experienced the issue could provide any further relevant comments as I’ve not got it as well documented as I’d like. Sorry, time constraints in the office.

 

Many thanks,

Paul

 

 

From: Alessandro Pasotti <[hidden email]>
Sent: 27 September 2019 08:20
To: Paul Wittle <[hidden email]>
Cc: [hidden email]
Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

 

 

On Fri, Sep 27, 2019 at 9:13 AM Paul Wittle <[hidden email]> wrote:

Hi Andrea and Enrico,

Thank you for providing an answer even if it was not what I was hoping to hear; it was the conclusion I'd reached myself.

Has this issue been considered given the official move from shapefile to geopackage as the default format?

My understanding was that shapefiles could be used over a network and whilst multiple people editing was dangerous it did not cause deadlocks.

Combined with the issues relating to MapInfo tab files over a network I've got into some hot water over my attempt to get QGIS rolled out in our organisation now. I'm going to have to think carefully about my next steps as it can be difficult to educate large numbers of staff and the two issues mean that people using QGIS incorrectly can cause pretty big issues.

To summarise,
 - You open a geopackage and save to the network; someone else comes along and opens it in their QGIS and everything looks okay until the PCs deadlock...ICT help calls and data corruption may occur.
 - You open a MapInfo tab file from the network and it looks fine (accept that it my draw in a user projection). MapInfo user receive errors but this is unknown to the QGIS user...ICT help calls result.

Whilst I'm glad the forums have helped to diagnose both behaviours I would personally say that the issues pose a bit of a threat to software adoption by larger companies and institutions that may be using Windows networks and potentially migrating from MapInfo.

In both cases my gut feel is that the best solution might be to look into use detection. If GDAL (I assume) can be improved to detect that either file type is already open then it might be possible to simply ban a second user from opening the file at the same time. This might frustrate some users but most importantly it would make the application safer from an ICT perspective.

These are of course just my personal opinion from my particular use case so please don't be offended by them if you disagree but I'd be really happy to hear how others are approaching the issues and/or opposing views?

Many thanks,
Paul

 

 

Hi Paul,

 

A deadlock is a QGIS bug (and quite a serious one).

 

Can you please file an issue on https://github.com/qgis/QGIS/issues ?

Please check if it's not there, and if it is, you can add your comments to the existing issue.

 

Thanks

 

--

Alessandro Pasotti
w3:   www.itopen.it

This e-mail and any files transmitted with it are intended solely for the use of the individual or entity to whom they are addressed. It may contain unclassified but sensitive or protectively marked material and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All traffic may be subject to recording and/or monitoring in accordance with relevant legislation. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Dorset Council. Dorset Council does not accept service of documents by fax or other electronic means. Virus checking: Whilst all reasonable steps have been taken to ensure that this electronic communication and its attachments whether encoded, encrypted or otherwise supplied are free from computer viruses, Dorset Council accepts no liability in respect of any loss, cost, damage or expense suffered as a result of accessing this message or any of its attachments. For information on how Dorset Council processes your information, please see www.dorsetcouncil.gov.uk/416433
_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks (Andrea Peri)

Paul Wittle-3

Hi,

 

We have a plan to migrate from Oracle to PostGIS but there is a lot of work to move other associated systems.

 

I should point out we did not move from Oracle to GeoPackage; we use Oracle for other systems. We are migrating users from MapInfo to QGIS and so I told the users to keep using the tab files at the start. This resulted in MapInfo errors and got me in hot water so I suggested they use GeoPackage instead as that is the official data type. Unfortunately that then resulted in the file deadlocks so I developed a custom python plugin to go with our install of QGIS and used that to give access to some (but not all) datasets using our existing Oracle spatial database.

 

I hope that provides sufficient background but you can probably see it’s not going well really as I’ve had to change my advice three times already.

Paul

 

From: Luigi Pirelli <[hidden email]>
Sent: 27 September 2019 08:59
To: Paul Wittle <[hidden email]>
Cc: Alessandro Pasotti <[hidden email]>; [hidden email]
Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

 

without resting importance to the issue, reading the bug report, I can read you moved from oracle spatial to geopackage! why not postgis?

 

Luigi Pirelli

**************************************************************************************************
* LinkedIn: https://www.linkedin.com/in/luigipirelli
* Stackexchange: http://gis.stackexchange.com/users/19667/luigi-pirelli
* GitHub: https://github.com/luipir
* Book: Mastering QGIS3 - 3rd Edition

* Hire a team: http://www.qcooperative.net
**************************************************************************************************

 

 

On Fri, 27 Sep 2019 at 09:39, Paul Wittle <[hidden email]> wrote:

Hi,

 

I couldn’t see a bug report which I thought matched so I have added one as requests but please accept my apologies if it does turn out to be a duplicate or if my use of the word deadlock is incorrect.

 

The ticket can be found at https://github.com/qgis/QGIS/issues/32034

 

It would be great if others that have experienced the issue could provide any further relevant comments as I’ve not got it as well documented as I’d like. Sorry, time constraints in the office.

 

Many thanks,

Paul

 

 

From: Alessandro Pasotti <[hidden email]>
Sent: 27 September 2019 08:20
To: Paul Wittle <[hidden email]>
Cc: [hidden email]
Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

 

 

On Fri, Sep 27, 2019 at 9:13 AM Paul Wittle <[hidden email]> wrote:

Hi Andrea and Enrico,

Thank you for providing an answer even if it was not what I was hoping to hear; it was the conclusion I'd reached myself.

Has this issue been considered given the official move from shapefile to geopackage as the default format?

My understanding was that shapefiles could be used over a network and whilst multiple people editing was dangerous it did not cause deadlocks.

Combined with the issues relating to MapInfo tab files over a network I've got into some hot water over my attempt to get QGIS rolled out in our organisation now. I'm going to have to think carefully about my next steps as it can be difficult to educate large numbers of staff and the two issues mean that people using QGIS incorrectly can cause pretty big issues.

To summarise,
 - You open a geopackage and save to the network; someone else comes along and opens it in their QGIS and everything looks okay until the PCs deadlock...ICT help calls and data corruption may occur.
 - You open a MapInfo tab file from the network and it looks fine (accept that it my draw in a user projection). MapInfo user receive errors but this is unknown to the QGIS user...ICT help calls result.

Whilst I'm glad the forums have helped to diagnose both behaviours I would personally say that the issues pose a bit of a threat to software adoption by larger companies and institutions that may be using Windows networks and potentially migrating from MapInfo.

In both cases my gut feel is that the best solution might be to look into use detection. If GDAL (I assume) can be improved to detect that either file type is already open then it might be possible to simply ban a second user from opening the file at the same time. This might frustrate some users but most importantly it would make the application safer from an ICT perspective.

These are of course just my personal opinion from my particular use case so please don't be offended by them if you disagree but I'd be really happy to hear how others are approaching the issues and/or opposing views?

Many thanks,
Paul

 

 

Hi Paul,

 

A deadlock is a QGIS bug (and quite a serious one).

 

Can you please file an issue on https://github.com/qgis/QGIS/issues ?

Please check if it's not there, and if it is, you can add your comments to the existing issue.

 

Thanks

 

--

Alessandro Pasotti
w3:   www.itopen.it

This e-mail and any files transmitted with it are intended solely for the use of the individual or entity to whom they are addressed. It may contain unclassified but sensitive or protectively marked material and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All traffic may be subject to recording and/or monitoring in accordance with relevant legislation. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Dorset Council. Dorset Council does not accept service of documents by fax or other electronic means. Virus checking: Whilst all reasonable steps have been taken to ensure that this electronic communication and its attachments whether encoded, encrypted or otherwise supplied are free from computer viruses, Dorset Council accepts no liability in respect of any loss, cost, damage or expense suffered as a result of accessing this message or any of its attachments. For information on how Dorset Council processes your information, please see www.dorsetcouncil.gov.uk/416433

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

This e-mail and any files transmitted with it are intended solely for the use of the individual or entity to whom they are addressed. It may contain unclassified but sensitive or protectively marked material and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All traffic may be subject to recording and/or monitoring in accordance with relevant legislation. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Dorset Council. Dorset Council does not accept service of documents by fax or other electronic means. Virus checking: Whilst all reasonable steps have been taken to ensure that this electronic communication and its attachments whether encoded, encrypted or otherwise supplied are free from computer viruses, Dorset Council accepts no liability in respect of any loss, cost, damage or expense suffered as a result of accessing this message or any of its attachments. For information on how Dorset Council processes your information, please see www.dorsetcouncil.gov.uk/416433
_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks (Andrea Peri)

Andrea Peri
Have you tried to use spatialite instead of geopackage. ?

Il ven 27 set 2019, 10:06 Paul Wittle <[hidden email]> ha scritto:

Hi,

 

We have a plan to migrate from Oracle to PostGIS but there is a lot of work to move other associated systems.

 

I should point out we did not move from Oracle to GeoPackage; we use Oracle for other systems. We are migrating users from MapInfo to QGIS and so I told the users to keep using the tab files at the start. This resulted in MapInfo errors and got me in hot water so I suggested they use GeoPackage instead as that is the official data type. Unfortunately that then resulted in the file deadlocks so I developed a custom python plugin to go with our install of QGIS and used that to give access to some (but not all) datasets using our existing Oracle spatial database.

 

I hope that provides sufficient background but you can probably see it’s not going well really as I’ve had to change my advice three times already.

Paul

 

From: Luigi Pirelli <[hidden email]>
Sent: 27 September 2019 08:59
To: Paul Wittle <[hidden email]>
Cc: Alessandro Pasotti <[hidden email]>; [hidden email]
Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

 

without resting importance to the issue, reading the bug report, I can read you moved from oracle spatial to geopackage! why not postgis?

 

Luigi Pirelli

**************************************************************************************************
* LinkedIn: https://www.linkedin.com/in/luigipirelli
* Stackexchange: http://gis.stackexchange.com/users/19667/luigi-pirelli
* GitHub: https://github.com/luipir
* Book: Mastering QGIS3 - 3rd Edition

* Hire a team: http://www.qcooperative.net
**************************************************************************************************

 

 

On Fri, 27 Sep 2019 at 09:39, Paul Wittle <[hidden email]> wrote:

Hi,

 

I couldn’t see a bug report which I thought matched so I have added one as requests but please accept my apologies if it does turn out to be a duplicate or if my use of the word deadlock is incorrect.

 

The ticket can be found at https://github.com/qgis/QGIS/issues/32034

 

It would be great if others that have experienced the issue could provide any further relevant comments as I’ve not got it as well documented as I’d like. Sorry, time constraints in the office.

 

Many thanks,

Paul

 

 

From: Alessandro Pasotti <[hidden email]>
Sent: 27 September 2019 08:20
To: Paul Wittle <[hidden email]>
Cc: [hidden email]
Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

 

 

On Fri, Sep 27, 2019 at 9:13 AM Paul Wittle <[hidden email]> wrote:

Hi Andrea and Enrico,

Thank you for providing an answer even if it was not what I was hoping to hear; it was the conclusion I'd reached myself.

Has this issue been considered given the official move from shapefile to geopackage as the default format?

My understanding was that shapefiles could be used over a network and whilst multiple people editing was dangerous it did not cause deadlocks.

Combined with the issues relating to MapInfo tab files over a network I've got into some hot water over my attempt to get QGIS rolled out in our organisation now. I'm going to have to think carefully about my next steps as it can be difficult to educate large numbers of staff and the two issues mean that people using QGIS incorrectly can cause pretty big issues.

To summarise,
 - You open a geopackage and save to the network; someone else comes along and opens it in their QGIS and everything looks okay until the PCs deadlock...ICT help calls and data corruption may occur.
 - You open a MapInfo tab file from the network and it looks fine (accept that it my draw in a user projection). MapInfo user receive errors but this is unknown to the QGIS user...ICT help calls result.

Whilst I'm glad the forums have helped to diagnose both behaviours I would personally say that the issues pose a bit of a threat to software adoption by larger companies and institutions that may be using Windows networks and potentially migrating from MapInfo.

In both cases my gut feel is that the best solution might be to look into use detection. If GDAL (I assume) can be improved to detect that either file type is already open then it might be possible to simply ban a second user from opening the file at the same time. This might frustrate some users but most importantly it would make the application safer from an ICT perspective.

These are of course just my personal opinion from my particular use case so please don't be offended by them if you disagree but I'd be really happy to hear how others are approaching the issues and/or opposing views?

Many thanks,
Paul

 

 

Hi Paul,

 

A deadlock is a QGIS bug (and quite a serious one).

 

Can you please file an issue on https://github.com/qgis/QGIS/issues ?

Please check if it's not there, and if it is, you can add your comments to the existing issue.

 

Thanks

 

--

Alessandro Pasotti
w3:   www.itopen.it

This e-mail and any files transmitted with it are intended solely for the use of the individual or entity to whom they are addressed. It may contain unclassified but sensitive or protectively marked material and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All traffic may be subject to recording and/or monitoring in accordance with relevant legislation. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Dorset Council. Dorset Council does not accept service of documents by fax or other electronic means. Virus checking: Whilst all reasonable steps have been taken to ensure that this electronic communication and its attachments whether encoded, encrypted or otherwise supplied are free from computer viruses, Dorset Council accepts no liability in respect of any loss, cost, damage or expense suffered as a result of accessing this message or any of its attachments. For information on how Dorset Council processes your information, please see www.dorsetcouncil.gov.uk/416433

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

This e-mail and any files transmitted with it are intended solely for the use of the individual or entity to whom they are addressed. It may contain unclassified but sensitive or protectively marked material and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All traffic may be subject to recording and/or monitoring in accordance with relevant legislation. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Dorset Council. Dorset Council does not accept service of documents by fax or other electronic means. Virus checking: Whilst all reasonable steps have been taken to ensure that this electronic communication and its attachments whether encoded, encrypted or otherwise supplied are free from computer viruses, Dorset Council accepts no liability in respect of any loss, cost, damage or expense suffered as a result of accessing this message or any of its attachments. For information on how Dorset Council processes your information, please see www.dorsetcouncil.gov.uk/416433
_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks (Andrea Peri)

Paul Wittle-3

Hi,

 

My understanding is that GeoPackage and Spatialite are  both based on SQLite so I’ve been assuming both may be susceptible to this bug?

 

Paul

 

From: Andrea Peri <[hidden email]>
Sent: 27 September 2019 09:25
To: Paul Wittle <[hidden email]>
Cc: Gino Pirelli <[hidden email]>; qgis-user <[hidden email]>
Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

 

Have you tried to use spatialite instead of geopackage. ?

 

Il ven 27 set 2019, 10:06 Paul Wittle <[hidden email]> ha scritto:

Hi,

 

We have a plan to migrate from Oracle to PostGIS but there is a lot of work to move other associated systems.

 

I should point out we did not move from Oracle to GeoPackage; we use Oracle for other systems. We are migrating users from MapInfo to QGIS and so I told the users to keep using the tab files at the start. This resulted in MapInfo errors and got me in hot water so I suggested they use GeoPackage instead as that is the official data type. Unfortunately that then resulted in the file deadlocks so I developed a custom python plugin to go with our install of QGIS and used that to give access to some (but not all) datasets using our existing Oracle spatial database.

 

I hope that provides sufficient background but you can probably see it’s not going well really as I’ve had to change my advice three times already.

Paul

 

From: Luigi Pirelli <[hidden email]>
Sent: 27 September 2019 08:59
To: Paul Wittle <[hidden email]>
Cc: Alessandro Pasotti <[hidden email]>; [hidden email]
Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

 

without resting importance to the issue, reading the bug report, I can read you moved from oracle spatial to geopackage! why not postgis?

 

Luigi Pirelli

**************************************************************************************************
* LinkedIn: https://www.linkedin.com/in/luigipirelli
* Stackexchange: http://gis.stackexchange.com/users/19667/luigi-pirelli
* GitHub: https://github.com/luipir
* Book: Mastering QGIS3 - 3rd Edition

* Hire a team: http://www.qcooperative.net
**************************************************************************************************

 

 

On Fri, 27 Sep 2019 at 09:39, Paul Wittle <[hidden email]> wrote:

Hi,

 

I couldn’t see a bug report which I thought matched so I have added one as requests but please accept my apologies if it does turn out to be a duplicate or if my use of the word deadlock is incorrect.

 

The ticket can be found at https://github.com/qgis/QGIS/issues/32034

 

It would be great if others that have experienced the issue could provide any further relevant comments as I’ve not got it as well documented as I’d like. Sorry, time constraints in the office.

 

Many thanks,

Paul

 

 

From: Alessandro Pasotti <[hidden email]>
Sent: 27 September 2019 08:20
To: Paul Wittle <[hidden email]>
Cc: [hidden email]
Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

 

 

On Fri, Sep 27, 2019 at 9:13 AM Paul Wittle <[hidden email]> wrote:

Hi Andrea and Enrico,

Thank you for providing an answer even if it was not what I was hoping to hear; it was the conclusion I'd reached myself.

Has this issue been considered given the official move from shapefile to geopackage as the default format?

My understanding was that shapefiles could be used over a network and whilst multiple people editing was dangerous it did not cause deadlocks.

Combined with the issues relating to MapInfo tab files over a network I've got into some hot water over my attempt to get QGIS rolled out in our organisation now. I'm going to have to think carefully about my next steps as it can be difficult to educate large numbers of staff and the two issues mean that people using QGIS incorrectly can cause pretty big issues.

To summarise,
 - You open a geopackage and save to the network; someone else comes along and opens it in their QGIS and everything looks okay until the PCs deadlock...ICT help calls and data corruption may occur.
 - You open a MapInfo tab file from the network and it looks fine (accept that it my draw in a user projection). MapInfo user receive errors but this is unknown to the QGIS user...ICT help calls result.

Whilst I'm glad the forums have helped to diagnose both behaviours I would personally say that the issues pose a bit of a threat to software adoption by larger companies and institutions that may be using Windows networks and potentially migrating from MapInfo.

In both cases my gut feel is that the best solution might be to look into use detection. If GDAL (I assume) can be improved to detect that either file type is already open then it might be possible to simply ban a second user from opening the file at the same time. This might frustrate some users but most importantly it would make the application safer from an ICT perspective.

These are of course just my personal opinion from my particular use case so please don't be offended by them if you disagree but I'd be really happy to hear how others are approaching the issues and/or opposing views?

Many thanks,
Paul

 

 

Hi Paul,

 

A deadlock is a QGIS bug (and quite a serious one).

 

Can you please file an issue on https://github.com/qgis/QGIS/issues ?

Please check if it's not there, and if it is, you can add your comments to the existing issue.

 

Thanks

 

--

Alessandro Pasotti
w3:   www.itopen.it

This e-mail and any files transmitted with it are intended solely for the use of the individual or entity to whom they are addressed. It may contain unclassified but sensitive or protectively marked material and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All traffic may be subject to recording and/or monitoring in accordance with relevant legislation. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Dorset Council. Dorset Council does not accept service of documents by fax or other electronic means. Virus checking: Whilst all reasonable steps have been taken to ensure that this electronic communication and its attachments whether encoded, encrypted or otherwise supplied are free from computer viruses, Dorset Council accepts no liability in respect of any loss, cost, damage or expense suffered as a result of accessing this message or any of its attachments. For information on how Dorset Council processes your information, please see www.dorsetcouncil.gov.uk/416433

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

This e-mail and any files transmitted with it are intended solely for the use of the individual or entity to whom they are addressed. It may contain unclassified but sensitive or protectively marked material and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All traffic may be subject to recording and/or monitoring in accordance with relevant legislation. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Dorset Council. Dorset Council does not accept service of documents by fax or other electronic means. Virus checking: Whilst all reasonable steps have been taken to ensure that this electronic communication and its attachments whether encoded, encrypted or otherwise supplied are free from computer viruses, Dorset Council accepts no liability in respect of any loss, cost, damage or expense suffered as a result of accessing this message or any of its attachments. For information on how Dorset Council processes your information, please see www.dorsetcouncil.gov.uk/416433

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

This e-mail and any files transmitted with it are intended solely for the use of the individual or entity to whom they are addressed. It may contain unclassified but sensitive or protectively marked material and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All traffic may be subject to recording and/or monitoring in accordance with relevant legislation. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Dorset Council. Dorset Council does not accept service of documents by fax or other electronic means. Virus checking: Whilst all reasonable steps have been taken to ensure that this electronic communication and its attachments whether encoded, encrypted or otherwise supplied are free from computer viruses, Dorset Council accepts no liability in respect of any loss, cost, damage or expense suffered as a result of accessing this message or any of its attachments. For information on how Dorset Council processes your information, please see www.dorsetcouncil.gov.uk/416433
_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks (Andrea Peri)

Tobias Wendorff
In reply to this post by Andrea Peri
Am 27.09.2019 um 10:24 schrieb Andrea Peri <[hidden email]>:

Have you tried to use spatialite instead of geopackage. ?

Why not plain SQLite? Nobody needs and uses the spatial functions of Spatialite, they are even not part of bloatware GPKG (sorry, the created db-files are huge without any compression).

The only reason is indexing and this could be forked off GPGK and Spatialite.

To the topic: I think, it‘s always a bad idea to let multiple users work on a single SQLite-based database. It hasn‘t been created for this reason.

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks (Andrea Peri)

ginetto
I suppose the question by Andrea was related if using a different qgis provider continue to generate the deadlock

Luigi Pirelli

**************************************************************************************************
* LinkedIn: https://www.linkedin.com/in/luigipirelli
* Stackexchange: http://gis.stackexchange.com/users/19667/luigi-pirelli
* GitHub: https://github.com/luipir
* Book: Mastering QGIS3 - 3rd Edition
* Hire a team: http://www.qcooperative.net
**************************************************************************************************


On Fri, 27 Sep 2019 at 10:50, Tobias Wendorff <[hidden email]> wrote:
Am 27.09.2019 um 10:24 schrieb Andrea Peri <[hidden email]>:

Have you tried to use spatialite instead of geopackage. ?

Why not plain SQLite? Nobody needs and uses the spatial functions of Spatialite, they are even not part of bloatware GPKG (sorry, the created db-files are huge without any compression).

The only reason is indexing and this could be forked off GPGK and Spatialite.

To the topic: I think, it‘s always a bad idea to let multiple users work on a single SQLite-based database. It hasn‘t been created for this reason.
_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks (Andrea Peri)

Paul Wittle-3

Hi,

 

I think my perspective is more related to whether or not the documentation / software is guiding the users to the right conclusion. It is clear that multiple users should not be editing the same file but my original question had two points really:

  • Does the user/software know someone else is already editing?
  • Should the software permit it at all?

 

My current thought is that QGIS probably doesn’t know how many people are reading or writing to the file and so it is unable to give the user any messages that would drive good practice. I’m unclear myself whether the issue is caused by two editors or just one editor and one or more reader. I’d have to do more testing on that but basically it would be great if it just popped up and said you can’t edit this file as someone else has it open for editing.

 

That hopefully sounds good but the bigger issue appears to be how do you do it?

 

Paul

 

P.S. I have heard about the compression issue but I was hoping that will be resolved in the future given the prominence given to the format but the OGC.

 

 

 

From: Luigi Pirelli <[hidden email]>
Sent: 27 September 2019 09:56
To: Tobias Wendorff <[hidden email]>
Cc: Andrea Peri <[hidden email]>; qgis-user <[hidden email]>; Paul Wittle <[hidden email]>
Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

 

I suppose the question by Andrea was related if using a different qgis provider continue to generate the deadlock

 

Luigi Pirelli

**************************************************************************************************
* LinkedIn: https://www.linkedin.com/in/luigipirelli
* Stackexchange: http://gis.stackexchange.com/users/19667/luigi-pirelli
* GitHub: https://github.com/luipir
* Book: Mastering QGIS3 - 3rd Edition

* Hire a team: http://www.qcooperative.net
**************************************************************************************************

 

 

On Fri, 27 Sep 2019 at 10:50, Tobias Wendorff <[hidden email]> wrote:

Am 27.09.2019 um 10:24 schrieb Andrea Peri <[hidden email]>:

Have you tried to use spatialite instead of geopackage. ?

 

Why not plain SQLite? Nobody needs and uses the spatial functions of Spatialite, they are even not part of bloatware GPKG (sorry, the created db-files are huge without any compression).

 

The only reason is indexing and this could be forked off GPGK and Spatialite.

 

To the topic: I think, it‘s always a bad idea to let multiple users work on a single SQLite-based database. It hasn‘t been created for this reason.

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

This e-mail and any files transmitted with it are intended solely for the use of the individual or entity to whom they are addressed. It may contain unclassified but sensitive or protectively marked material and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All traffic may be subject to recording and/or monitoring in accordance with relevant legislation. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Dorset Council. Dorset Council does not accept service of documents by fax or other electronic means. Virus checking: Whilst all reasonable steps have been taken to ensure that this electronic communication and its attachments whether encoded, encrypted or otherwise supplied are free from computer viruses, Dorset Council accepts no liability in respect of any loss, cost, damage or expense suffered as a result of accessing this message or any of its attachments. For information on how Dorset Council processes your information, please see www.dorsetcouncil.gov.uk/416433
_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks (Andrea Peri)

Jonathan Moules-4
In reply to this post by Tobias Wendorff

(A little late).

TL;DR: at least for QGIS, is - never multi-user edit SQLite/SpatiaLite/GeoPackages on network file systems.

SQLite, (and therefore SpatiaLite and GeoPackage) has quite a few caveats when it comes to multiple users trying to edit it at once.

https://www2.sqlite.org/howtocorrupt.html

(my bold)

"SQLite depends on the underlying filesystem to do locking as the documentation says it will. But some filesystems contain bugs in their locking logic such that the locks do not always behave as advertised. This is especially true of network filesystems and NFS in particular. If SQLite is used on a filesystem where the locking primitives contain bugs, and if two or more threads or processes try to access the same database at the same time, then database corruption might result."

And there's also:

https://www.sqlite.org/whentouse.html

Put simply (Note: I'm not an expert): It's fine to edit SQLite databases if they're not on a network file system with as many users as you want, or if they are on a network and you can guarantee only one process is going to write. However if multiple people/processes want to write to a network file system, you'll need a piece of middleware to manage the process, otherwise there's a good chance of corruption as Paul is seeing.

It may also be that QGIS is doing some of the other things on the "how to corrupt" page too. I imagine it will only get worse if you use multiple different software packages to edit simultaneously.

Cheers,

Jonathan

On 2019-09-27 09:50, Tobias Wendorff wrote:
Am 27.09.2019 um 10:24 schrieb Andrea Peri [hidden email]:

Have you tried to use spatialite instead of geopackage. ?
Why not plain SQLite? Nobody needs and uses the spatial functions of Spatialite, they are even not part of bloatware GPKG (sorry, the created db-files are huge without any compression).

The only reason is indexing and this could be forked off GPGK and Spatialite.

To the topic: I think, it‘s always a bad idea to let multiple users work on a single SQLite-based database. It hasn‘t been created for this reason.

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks (Andrea Peri)

Paul Wittle-3

Hi,

 

I think the issue is not really the downsides of the file format so much as the more recent promotion of GeoPackage as a default format over shapefiles.

 

In my view it is unreasonable to expect the average user to delve into this level of detail and I would personally expect that the software (or documentation) would make clear any serious issues such as data corruption risks. Personally I’d go further and say work to avoid the situation at all.

 

This is why I asked the question really as there seem to me to be two key points:

  1. There is some evidence that read-only access may be editing the file, see https://github.com/qgis/QGIS/issues/23991
  2. Ideally it would be possible for QGIS to prevent multiple edit sessions

 

IMHO the usefulness of the format seems limited if it is not possible for multiple users to view the same data. That said, the bug report I’ve linked to shows that other agree and the issue is being dealt with using the normal methods.

 

The second point is really the focus of why I originally asked the question. It is clear that SQLite itself cannot achieve this as it is unaware of edit sessions however QGIS could simply create a sidecar file as a worst case and that should work. If the sidecar file exists then editing is not possible unless you own the file otherwise the file is available for editing.

 

In simple terms I want QGIS to say no (and explain it is already being edited in a dialog) if a second user tries to put a GeoPackage in edit mode when someone else is already editing it.  

 

I’ve already created a section in my bespoke plugin which corrects the bad projections for MapInfo tab files so I might just write the functionality above myself using Python.

 

Cheers,

Paul

 

From: Jonathan Moules <[hidden email]>
Sent: 07 October 2019 15:17
To: Tobias Wendorff <[hidden email]>; Andrea Peri <[hidden email]>
Cc: qgis-user <[hidden email]>; Paul Wittle <[hidden email]>
Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

 

(A little late).

TL;DR: at least for QGIS, is - never multi-user edit SQLite/SpatiaLite/GeoPackages on network file systems.

SQLite, (and therefore SpatiaLite and GeoPackage) has quite a few caveats when it comes to multiple users trying to edit it at once.

https://www2.sqlite.org/howtocorrupt.html

(my bold)

"SQLite depends on the underlying filesystem to do locking as the documentation says it will. But some filesystems contain bugs in their locking logic such that the locks do not always behave as advertised. This is especially true of network filesystems and NFS in particular. If SQLite is used on a filesystem where the locking primitives contain bugs, and if two or more threads or processes try to access the same database at the same time, then database corruption might result."

And there's also:

https://www.sqlite.org/whentouse.html

Put simply (Note: I'm not an expert): It's fine to edit SQLite databases if they're not on a network file system with as many users as you want, or if they are on a network and you can guarantee only one process is going to write. However if multiple people/processes want to write to a network file system, you'll need a piece of middleware to manage the process, otherwise there's a good chance of corruption as Paul is seeing.

It may also be that QGIS is doing some of the other things on the "how to corrupt" page too. I imagine it will only get worse if you use multiple different software packages to edit simultaneously.

Cheers,

Jonathan

On 2019-09-27 09:50, Tobias Wendorff wrote:

Am 27.09.2019 um 10:24 schrieb Andrea Peri [hidden email]:
 
Have you tried to use spatialite instead of geopackage. ?
 
Why not plain SQLite? Nobody needs and uses the spatial functions of Spatialite, they are even not part of bloatware GPKG (sorry, the created db-files are huge without any compression).
 
The only reason is indexing and this could be forked off GPGK and Spatialite.
 
To the topic: I think, it‘s always a bad idea to let multiple users work on a single SQLite-based database. It hasn‘t been created for this reason.



_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
This e-mail and any files transmitted with it are intended solely for the use of the individual or entity to whom they are addressed. It may contain unclassified but sensitive or protectively marked material and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All traffic may be subject to recording and/or monitoring in accordance with relevant legislation. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Dorset Council. Dorset Council does not accept service of documents by fax or other electronic means. Virus checking: Whilst all reasonable steps have been taken to ensure that this electronic communication and its attachments whether encoded, encrypted or otherwise supplied are free from computer viruses, Dorset Council accepts no liability in respect of any loss, cost, damage or expense suffered as a result of accessing this message or any of its attachments. For information on how Dorset Council processes your information, please see www.dorsetcouncil.gov.uk/416433
_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks (Andrea Peri)

jaroslaw.sadowski
In reply to this post by Jonathan Moules-4

Hi everybody,

 

Very interesting thread…

Does the same apply if we only read data from geopackage (not overwrite/edit it) on NFS? I have a situation with deadlocks when several users are only trying to read data (without competitive writing).

Can anyone confirm this with his case?

 

greeting from Poland

Jarosław Sadowski

 

From: Qgis-user <[hidden email]> On Behalf Of Jonathan Moules
Sent: Monday, October 7, 2019 4:17 PM
To: Tobias Wendorff <[hidden email]>; Andrea Peri <[hidden email]>
Cc: qgis-user <[hidden email]>; Paul Wittle <[hidden email]>
Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

 

(A little late).

TL;DR: at least for QGIS, is - never multi-user edit SQLite/SpatiaLite/GeoPackages on network file systems.

SQLite, (and therefore SpatiaLite and GeoPackage) has quite a few caveats when it comes to multiple users trying to edit it at once.

https://www2.sqlite.org/howtocorrupt.html

(my bold)

"SQLite depends on the underlying filesystem to do locking as the documentation says it will. But some filesystems contain bugs in their locking logic such that the locks do not always behave as advertised. This is especially true of network filesystems and NFS in particular. If SQLite is used on a filesystem where the locking primitives contain bugs, and if two or more threads or processes try to access the same database at the same time, then database corruption might result."

And there's also:

https://www.sqlite.org/whentouse.html

Put simply (Note: I'm not an expert): It's fine to edit SQLite databases if they're not on a network file system with as many users as you want, or if they are on a network and you can guarantee only one process is going to write. However if multiple people/processes want to write to a network file system, you'll need a piece of middleware to manage the process, otherwise there's a good chance of corruption as Paul is seeing.

It may also be that QGIS is doing some of the other things on the "how to corrupt" page too. I imagine it will only get worse if you use multiple different software packages to edit simultaneously.

Cheers,

Jonathan

On 2019-09-27 09:50, Tobias Wendorff wrote:

Am 27.09.2019 um 10:24 schrieb Andrea Peri [hidden email]:
 
Have you tried to use spatialite instead of geopackage. ?
 
Why not plain SQLite? Nobody needs and uses the spatial functions of Spatialite, they are even not part of bloatware GPKG (sorry, the created db-files are huge without any compression).
 
The only reason is indexing and this could be forked off GPGK and Spatialite.
 
To the topic: I think, it‘s always a bad idea to let multiple users work on a single SQLite-based database. It hasn‘t been created for this reason.



_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks (Andrea Peri)

Jonathan Moules-4
In reply to this post by Paul Wittle-3
Hi Paul,

I think we're approaching this from opposing directions. It is *exactly*
the "downsides" of the file format that impose these constraints. The
problem is that QGIS isn't handling them well which is where we get
situations like this.

I 100% agree with you about it being unreasonable for users to know
about this, and QGIS definitely needs to handle it better, especially if
it's the default format. That's why I came in with the docs to explain
where the problem probably lies.


 > 2. Ideally it would be possible for QGIS to prevent multiple edit
sessions

Multiple edit sessions are absolutely fine, on a *local* filesystem at
the SQLite file level. It may *not* be fine at the data level: if you
and I edit the same polygon at the same time and then commit, what
happens? Which does QGIS keep? That's an application-level problem for
QGIS (no idea if it's addressed). For the SQLite file itself, *only*
multiple editors via the network is a problem (well, at the other things
on that link). It's definitely something QGIS needs to be able to handle
better / warn about / lock.

I'd suggest raising this second issue on the QGIS tracker as a bug.

Cheers,

Jonathan


On 2019-10-07 15:37, Paul Wittle wrote:

> Hi,
>
> I think the issue is not really the downsides of the file format so much as the more recent promotion of GeoPackage as a default format over shapefiles.
>
> In my view it is unreasonable to expect the average user to delve into this level of detail and I would personally expect that the software (or documentation) would make clear any serious issues such as data corruption risks. Personally I’d go further and say work to avoid the situation at all.
>
> This is why I asked the question really as there seem to me to be two key points:
>
>    1.  There is some evidence that read-only access may be editing the file, see https://github.com/qgis/QGIS/issues/23991
>    2.  Ideally it would be possible for QGIS to prevent multiple edit sessions
>
> IMHO the usefulness of the format seems limited if it is not possible for multiple users to view the same data. That said, the bug report I’ve linked to shows that other agree and the issue is being dealt with using the normal methods.
>
> The second point is really the focus of why I originally asked the question. It is clear that SQLite itself cannot achieve this as it is unaware of edit sessions however QGIS could simply create a sidecar file as a worst case and that should work. If the sidecar file exists then editing is not possible unless you own the file otherwise the file is available for editing.
>
> In simple terms I want QGIS to say no (and explain it is already being edited in a dialog) if a second user tries to put a GeoPackage in edit mode when someone else is already editing it.
>
> I’ve already created a section in my bespoke plugin which corrects the bad projections for MapInfo tab files so I might just write the functionality above myself using Python.
>
> Cheers,
> Paul
>
> From: Jonathan Moules <[hidden email]>
> Sent: 07 October 2019 15:17
> To: Tobias Wendorff <[hidden email]>; Andrea Peri <[hidden email]>
> Cc: qgis-user <[hidden email]>; Paul Wittle <[hidden email]>
> Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)
>
>
> (A little late).
>
> TL;DR: at least for QGIS, is - never multi-user edit SQLite/SpatiaLite/GeoPackages on network file systems.
>
> SQLite, (and therefore SpatiaLite and GeoPackage) has quite a few caveats when it comes to multiple users trying to edit it at once.
>
> https://www2.sqlite.org/howtocorrupt.html
>
> (my bold)
>
> "SQLite depends on the underlying filesystem to do locking as the documentation says it will. But some filesystems contain bugs in their locking logic such that the locks do not always behave as advertised. This is especially true of network filesystems and NFS in particular. If SQLite is used on a filesystem where the locking primitives contain bugs, and if two or more threads or processes try to access the same database at the same time, then database corruption might result."
>
> And there's also:
>
> https://www.sqlite.org/whentouse.html
>
> Put simply (Note: I'm not an expert): It's fine to edit SQLite databases if they're not on a network file system with as many users as you want, or if they are on a network and you can guarantee only one process is going to write. However if multiple people/processes want to write to a network file system, you'll need a piece of middleware to manage the process, otherwise there's a good chance of corruption as Paul is seeing.
>
> It may also be that QGIS is doing some of the other things on the "how to corrupt" page too. I imagine it will only get worse if you use multiple different software packages to edit simultaneously.
>
> Cheers,
>
> Jonathan
> On 2019-09-27 09:50, Tobias Wendorff wrote:
>
> Am 27.09.2019 um 10:24 schrieb Andrea Peri <[hidden email]><mailto:[hidden email]>:
>
>
>
> Have you tried to use spatialite instead of geopackage. ?
>
>
>
> Why not plain SQLite? Nobody needs and uses the spatial functions of Spatialite, they are even not part of bloatware GPKG (sorry, the created db-files are huge without any compression).
>
>
>
> The only reason is indexing and this could be forked off GPGK and Spatialite.
>
>
>
> To the topic: I think, it‘s always a bad idea to let multiple users work on a single SQLite-based database. It hasn‘t been created for this reason.
>
>
>
> _______________________________________________
>
> Qgis-user mailing list
>
> [hidden email]<mailto:[hidden email]>
>
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
>
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
>
> This e-mail and any files transmitted with it are intended solely for the use of the individual or entity to whom they are addressed. It may contain unclassified but sensitive or protectively marked material and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All traffic may be subject to recording and/or monitoring in accordance with relevant legislation. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Dorset Council. Dorset Council does not accept service of documents by fax or other electronic means. Virus checking: Whilst all reasonable steps have been taken to ensure that this electronic communication and its attachments whether encoded, encrypted or otherwise supplied are free from computer viruses, Dorset Council accepts no liability in respect of any loss, cost, damage or expense suffered as a result of accessing this message or any of its attachments. For information on how Dorset Council processes your information, please see www.dorsetcouncil.gov.uk/416433


_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks (Andrea Peri)

Jonathan Moules-4
In reply to this post by jaroslaw.sadowski

Hi Jarosław,

> Does the same apply if we only read data from geopackage (not overwrite/edit it) on NFS? I have a situation with deadlocks when several users are only trying to read data (without competitive writing).

At the file level for SQLite itself, there are basically no constraints for reading as far as I know. You can have an effectively infinite number of readers (or however many your filesystem allow to access a single file at a time). Per the When to use page (https://www.sqlite.org/whentouse.html) (my bold):

"High Concurrency

 SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution."

I would suggest your situation is a QGIS/GeoPackage-driver bug/limitation.

Cheers,

Jonathan


On 2019-10-07 15:46, [hidden email] wrote:
Hi everybody,

 

Very interesting thread…

Does the same apply if we only read data from geopackage (not overwrite/edit it) on NFS? I have a situation with deadlocks when several users are only trying to read data (without competitive writing).

Can anyone confirm this with his case?

 

greeting from Poland

Jarosław Sadowski

 

From: Qgis-user [hidden email] On Behalf Of Jonathan Moules
Sent: Monday, October 7, 2019 4:17 PM
To: Tobias Wendorff [hidden email]; Andrea Peri [hidden email]
Cc: qgis-user [hidden email]; Paul Wittle [hidden email]
Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)

 

(A little late).

TL;DR: at least for QGIS, is - never multi-user edit SQLite/SpatiaLite/GeoPackages on network file systems.

SQLite, (and therefore SpatiaLite and GeoPackage) has quite a few caveats when it comes to multiple users trying to edit it at once. 

https://www2.sqlite.org/howtocorrupt.html

(my bold)

"SQLite depends on the underlying filesystem to do locking as the documentation says it will. But some filesystems contain bugs in their locking logic such that the locks do not always behave as advertised. This is especially true of network filesystems and NFS in particular. If SQLite is used on a filesystem where the locking primitives contain bugs, and if two or more threads or processes try to access the same database at the same time, then database corruption might result."

And there's also:

https://www.sqlite.org/whentouse.html

Put simply (Note: I'm not an expert): It's fine to edit SQLite databases if they're not on a network file system with as many users as you want, or if they are on a network and you can guarantee only one process is going to write. However if multiple people/processes want to write to a network file system, you'll need a piece of middleware to manage the process, otherwise there's a good chance of corruption as Paul is seeing.

It may also be that QGIS is doing some of the other things on the "how to corrupt" page too. I imagine it will only get worse if you use multiple different software packages to edit simultaneously.

Cheers,

Jonathan

On 2019-09-27 09:50, Tobias Wendorff wrote:

Am 27.09.2019 um 10:24 schrieb Andrea Peri  [hidden email] [hidden email]:
 
Have you tried to use spatialite instead of geopackage. ?

 
Why not plain SQLite? Nobody needs and uses the spatial functions of Spatialite, they are even not part of bloatware GPKG (sorry, the created db-files are huge without any compression).
 
The only reason is indexing and this could be forked off GPGK and Spatialite.
 
To the topic: I think, it‘s always a bad idea to let multiple users work on a single SQLite-based database. It hasn‘t been created for this reason.





_______________________________________________
Qgis-user mailing list
[hidden email] [hidden email] 
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user



_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks (Andrea Peri)

Saber Razmjooei
In reply to this post by Jonathan Moules-4
Hi, 


 > 2. Ideally it would be possible for QGIS to prevent multiple edit
sessions

Multiple edit sessions are absolutely fine, on a *local* filesystem at
the SQLite file level. It may *not* be fine at the data level: if you
and I edit the same polygon at the same time and then commit, what
happens? Which does QGIS keep?

We are working on a library to handle these type of conflict resolution (currently it supports SQLite based databases):

Regards
Saber

 
That's an application-level problem for
QGIS (no idea if it's addressed). For the SQLite file itself, *only*
multiple editors via the network is a problem (well, at the other things
on that link). It's definitely something QGIS needs to be able to handle
better / warn about / lock.

I'd suggest raising this second issue on the QGIS tracker as a bug.

Cheers,

Jonathan


On 2019-10-07 15:37, Paul Wittle wrote:
> Hi,
>
> I think the issue is not really the downsides of the file format so much as the more recent promotion of GeoPackage as a default format over shapefiles.
>
> In my view it is unreasonable to expect the average user to delve into this level of detail and I would personally expect that the software (or documentation) would make clear any serious issues such as data corruption risks. Personally I’d go further and say work to avoid the situation at all.
>
> This is why I asked the question really as there seem to me to be two key points:
>
>    1.  There is some evidence that read-only access may be editing the file, see https://github.com/qgis/QGIS/issues/23991
>    2.  Ideally it would be possible for QGIS to prevent multiple edit sessions
>
> IMHO the usefulness of the format seems limited if it is not possible for multiple users to view the same data. That said, the bug report I’ve linked to shows that other agree and the issue is being dealt with using the normal methods.
>
> The second point is really the focus of why I originally asked the question. It is clear that SQLite itself cannot achieve this as it is unaware of edit sessions however QGIS could simply create a sidecar file as a worst case and that should work. If the sidecar file exists then editing is not possible unless you own the file otherwise the file is available for editing.
>
> In simple terms I want QGIS to say no (and explain it is already being edited in a dialog) if a second user tries to put a GeoPackage in edit mode when someone else is already editing it.
>
> I’ve already created a section in my bespoke plugin which corrects the bad projections for MapInfo tab files so I might just write the functionality above myself using Python.
>
> Cheers,
> Paul
>
> From: Jonathan Moules <[hidden email]>
> Sent: 07 October 2019 15:17
> To: Tobias Wendorff <[hidden email]>; Andrea Peri <[hidden email]>
> Cc: qgis-user <[hidden email]>; Paul Wittle <[hidden email]>
> Subject: Re: [Qgis-user] GeoPackage deadlocks (Andrea Peri)
>
>
> (A little late).
>
> TL;DR: at least for QGIS, is - never multi-user edit SQLite/SpatiaLite/GeoPackages on network file systems.
>
> SQLite, (and therefore SpatiaLite and GeoPackage) has quite a few caveats when it comes to multiple users trying to edit it at once.
>
> https://www2.sqlite.org/howtocorrupt.html
>
> (my bold)
>
> "SQLite depends on the underlying filesystem to do locking as the documentation says it will. But some filesystems contain bugs in their locking logic such that the locks do not always behave as advertised. This is especially true of network filesystems and NFS in particular. If SQLite is used on a filesystem where the locking primitives contain bugs, and if two or more threads or processes try to access the same database at the same time, then database corruption might result."
>
> And there's also:
>
> https://www.sqlite.org/whentouse.html
>
> Put simply (Note: I'm not an expert): It's fine to edit SQLite databases if they're not on a network file system with as many users as you want, or if they are on a network and you can guarantee only one process is going to write. However if multiple people/processes want to write to a network file system, you'll need a piece of middleware to manage the process, otherwise there's a good chance of corruption as Paul is seeing.
>
> It may also be that QGIS is doing some of the other things on the "how to corrupt" page too. I imagine it will only get worse if you use multiple different software packages to edit simultaneously.
>
> Cheers,
>
> Jonathan
> On 2019-09-27 09:50, Tobias Wendorff wrote:
>
> Am 27.09.2019 um 10:24 schrieb Andrea Peri <[hidden email]><mailto:[hidden email]>:
>
>
>
> Have you tried to use spatialite instead of geopackage. ?
>
>
>
> Why not plain SQLite? Nobody needs and uses the spatial functions of Spatialite, they are even not part of bloatware GPKG (sorry, the created db-files are huge without any compression).
>
>
>
> The only reason is indexing and this could be forked off GPGK and Spatialite.
>
>
>
> To the topic: I think, it‘s always a bad idea to let multiple users work on a single SQLite-based database. It hasn‘t been created for this reason.
>
>
>
> _______________________________________________
>
> Qgis-user mailing list
>
> [hidden email]<mailto:[hidden email]>
>
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
>
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
>
> This e-mail and any files transmitted with it are intended solely for the use of the individual or entity to whom they are addressed. It may contain unclassified but sensitive or protectively marked material and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All traffic may be subject to recording and/or monitoring in accordance with relevant legislation. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Dorset Council. Dorset Council does not accept service of documents by fax or other electronic means. Virus checking: Whilst all reasonable steps have been taken to ensure that this electronic communication and its attachments whether encoded, encrypted or otherwise supplied are free from computer viruses, Dorset Council accepts no liability in respect of any loss, cost, damage or expense suffered as a result of accessing this message or any of its attachments. For information on how Dorset Council processes your information, please see www.dorsetcouncil.gov.uk/416433


_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user


--

_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks (Andrea Peri)

Fernando M. Roxo da Motta
In reply to this post by jaroslaw.sadowski
On Mon, 7 Oct 2019 16:46:29 +0200, <[hidden email]> wrote:


> Hi everybody,
>
>  
>
> Very interesting thread…
>
> Does the same apply if we only read data from geopackage (not
> overwrite/edit it) on NFS? I have a situation with deadlocks when
> several users are only trying to read data (without competitive
> writing).
>
> Can anyone confirm this with his case?
>

  Competitive reading over NFS should pose no problem, whatever the
file format.  If you are getting deadlocks in readonly access it seems
like you can have some other problems in the software accessing (like
pretend to open readonly but set some update flag),  or in the
server/client configuration. There are a number of inherent issues in
NFS that are independent of the data type or software access.

  If we include writing there is another whole universe of problems
with NFS, most of them shared by others network distributed
filesystems.  One huge problem is in the file locking over NFS, it is
not guaranteed that it will work in any way.  It can work sometimes and
not work in other similar situation. Another huge problem is the
latency of the protocol.  NFS, and many other distributed filesystems
over network, are not atomic, in the sense that an interaction that
changes the file content (writing, deleting, ...) or state (locking) is
not sent immediately to the server, besides the characteristic network
latency.   This can lead to many conflicts and race conditions.

  I would suggest that if you have a base of data, whatever the size,
that must be shared and accessed for edition over network for a number
of workers, independent of the number of competitive access, the best
approach is to store those data in a database with a dedicated server
as PostgreSQL or even MySQL/MariaDB).   Edition of data file content
over network don't look like a smart move to me.

  HTH


>  
>
> greeting from Poland
>
> Jarosław Sadowski
>
>  
>
> From: Qgis-user <[hidden email]> On Behalf Of
> Jonathan Moules Sent: Monday, October 7, 2019 4:17 PM
> To: Tobias Wendorff <[hidden email]>; Andrea Peri
> <[hidden email]> Cc: qgis-user <[hidden email]>; Paul
> Wittle <[hidden email]> Subject: Re: [Qgis-user]
> GeoPackage deadlocks (Andrea Peri)
>
>  
>
> (A little late).
>
> TL;DR: at least for QGIS, is - never multi-user edit
> SQLite/SpatiaLite/GeoPackages on network file systems.
>
> SQLite, (and therefore SpatiaLite and GeoPackage) has quite a few
> caveats when it comes to multiple users trying to edit it at once.
>
> https://www2.sqlite.org/howtocorrupt.html
>
> (my bold)
>
> "SQLite depends on the underlying filesystem to do locking as the
> documentation says it will. But some filesystems contain bugs in
> their locking logic such that the locks do not always behave as
> advertised. This is especially true of network filesystems and NFS in
> particular. If SQLite is used on a filesystem where the locking
> primitives contain bugs, and if two or more threads or processes try
> to access the same database at the same time, then database
> corruption might result."
>
> And there's also:
>
> https://www.sqlite.org/whentouse.html
>
> Put simply (Note: I'm not an expert): It's fine to edit SQLite
> databases if they're not on a network file system with as many users
> as you want, or if they are on a network and you can guarantee only
> one process is going to write. However if multiple people/processes
> want to write to a network file system, you'll need a piece of
> middleware to manage the process, otherwise there's a good chance of
> corruption as Paul is seeing.
>
> It may also be that QGIS is doing some of the other things on the
> "how to corrupt" page too. I imagine it will only get worse if you
> use multiple different software packages to edit simultaneously.
>
> Cheers,
>
> Jonathan
>
> On 2019-09-27 09:50, Tobias Wendorff wrote:
>
> Am 27.09.2019 um 10:24 schrieb Andrea Peri
> <mailto:[hidden email]> <[hidden email]>:
> Have you tried to use spatialite instead of geopackage. ?
>
>  
> Why not plain SQLite? Nobody needs and uses the spatial functions of
> Spatialite, they are even not part of bloatware GPKG (sorry, the
> created db-files are huge without any compression). The only reason
> is indexing and this could be forked off GPGK and Spatialite.
> To the topic: I think, it‘s always a bad idea to let multiple users
> work on a single SQLite-based database. It hasn‘t been created for
> this reason.
>
>
>
>
>
> _______________________________________________
> Qgis-user mailing list
> [hidden email] <mailto:[hidden email]>
> List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
>








  Roxo

--
---------------- Non luctari, ludare -------------------+ WYSIWYG
Fernando M. Roxo da Motta <[hidden email]>              | Editor?
Except where explicitly stated I speak on my own behalf.|  VI !!
                PU5RXO                                  | I see text,
------------ Quis custodiet ipsos custodes?-------------+ I get text!
 
_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks (Andrea Peri)

Patrick Dunford-2
In reply to this post by Paul Wittle-3
It's not a network issue and neither is it a Windows issue. Two
processes accessing geopackage on a local computer can run into the same
issue. SQlite is designed for a single user. It is very reliable when
used as designed.

On 27/09/19 7:13 PM, Paul Wittle wrote:

> Hi Andrea and Enrico,
>
> Thank you for providing an answer even if it was not what I was hoping to hear; it was the conclusion I'd reached myself.
>
> Has this issue been considered given the official move from shapefile to geopackage as the default format?
>
> My understanding was that shapefiles could be used over a network and whilst multiple people editing was dangerous it did not cause deadlocks.
>
> Combined with the issues relating to MapInfo tab files over a network I've got into some hot water over my attempt to get QGIS rolled out in our organisation now. I'm going to have to think carefully about my next steps as it can be difficult to educate large numbers of staff and the two issues mean that people using QGIS incorrectly can cause pretty big issues.
>
> To summarise,
>   - You open a geopackage and save to the network; someone else comes along and opens it in their QGIS and everything looks okay until the PCs deadlock...ICT help calls and data corruption may occur.
>   - You open a MapInfo tab file from the network and it looks fine (accept that it my draw in a user projection). MapInfo user receive errors but this is unknown to the QGIS user...ICT help calls result.
>
> Whilst I'm glad the forums have helped to diagnose both behaviours I would personally say that the issues pose a bit of a threat to software adoption by larger companies and institutions that may be using Windows networks and potentially migrating from MapInfo.
>
> In both cases my gut feel is that the best solution might be to look into use detection. If GDAL (I assume) can be improved to detect that either file type is already open then it might be possible to simply ban a second user from opening the file at the same time. This might frustrate some users but most importantly it would make the application safer from an ICT perspective.
>
> These are of course just my personal opinion from my particular use case so please don't be offended by them if you disagree but I'd be really happy to hear how others are approaching the issues and/or opposing views?
>
> Many thanks,
> Paul
>
_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Reply | Threaded
Open this post in threaded view
|

Re: GeoPackage deadlocks (Andrea Peri)

Francesco Pelullo


Il gio 17 ott 2019, 12:54 Patrick Dunford <[hidden email]> ha scritto:
It's not a network issue and neither is it a Windows issue. Two
processes accessing geopackage on a local computer can run into the same
issue. SQlite is designed for a single user. It is very reliable when
used as designed.

Maybe in next releases, QGIS could add a new function that create a temporary .gpkg.lock file in same directory of .gpkg file and thus allows other QGIS instances to open same .gpkg in read only mode.

Ciao
--FP


_______________________________________________
Qgis-user mailing list
[hidden email]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
12