https://issues.qgis.org/https://issues.qgis.org/favicon.ico2018-10-11T09:37:56ZQGIS Issue TrackingQGIS Application - Bug report #20077: geopackage - table operations via network shares take a very long timehttps://issues.qgis.org/issues/20077?journal_id=941922018-10-11T09:37:56ZJürgen Fischerjef@norbit.de
<ul><li><strong>Related to</strong> <i><a class="issue tracker-1 status-5 priority-4 priority- closed" href="/issues/20068">Bug report #20068</a>: Geopackage: problems with renaming or drop tables</i> added</li></ul> QGIS Application - Bug report #20077: geopackage - table operations via network shares take a very long timehttps://issues.qgis.org/issues/20077?journal_id=941992018-10-11T10:44:49ZEven Rouaulteven.rouault@spatialys.com
<ul></ul><p>Can you retry your tests by defining the following environment variables (may require restarting QGIS each time you change) ?</p>
<ul>
<li>OGR_SQLITE_JOURNAL=DELETE</li>
<li>SQLITE_USE_OGR_VFS=YES</li>
<li>and possibly the combinations of both</li>
</ul>
<p>Note that SQLite is notoriously known not to be appropriate for databases stored on network shares...</p> QGIS Application - Bug report #20077: geopackage - table operations via network shares take a very long timehttps://issues.qgis.org/issues/20077?journal_id=942042018-10-11T14:41:06ZBurghardt Scholleburghardt.scholle@stadt.wolfsburg.de
<ul></ul>Okay, it's been a long day but ...<br />wow, the environment variable "SQLITE_USE_OGR_VFS=YES" has done it - under Windows and Xubuntu! How did I go about it? <br />I installed QGIS Nightly (from <a class="external" href="https://qgis.org/ubuntu-nightly">https://qgis.org/ubuntu-nightly</a>) in a fresh Xubuntu installation. This version only comes with GDAL/OGR 2.2.3, but no matter. For testing purposes I have executed an "export SQLITE_USE_OGR_VFS=YES" from the command line and then started QGIS from there as well. Loading the QGIS project with my "network"-geopackage and testing the table creation was sucessful and very fast! Awesome. <br />After this success I set a global variable by creating a file "qgis-gpkg.sh" in the directory "/etc/profile.d" with the following content: <br />----
<ol>
<li>QGIS environment variable for geopackage and shared network folder<br />export SQLITE_USE_OGR_VFS=YES<br />----<br />The computer must then be restarted. Afterwards, the table operations via network shares worked perfectly and with the expected performance.<br />Under Windows I added "export SQLITE_USE_OGR_VFS=YES" in my system environment variables. Afterwards I had to log off and on again so that the change would have an effect. Thank you very much for pointing that out!</li>
</ol>
<p>In view of the fact that geopackage will replace shape as the default format in the future, maybe we should set this system variables during installation in future QGIS versions. I believe that in practice many users use network drives (because of a central data backup, data sharing, etc.).</p> QGIS Application - Bug report #20077: geopackage - table operations via network shares take a very long timehttps://issues.qgis.org/issues/20077?journal_id=942102018-10-11T19:00:07ZEven Rouaulteven.rouault@spatialys.com
<ul></ul><p>The use of SQLITE_USE_OGR_VFS=YES has the side effect of not implementing SQLite locking, which explains the speed increase. But this may lead to database corruption in case of concurrent edits. So this is not the perfect solution. But that migt be something to enable when the database is detected to be on a network share</p> QGIS Application - Bug report #20077: geopackage - table operations via network shares take a very long timehttps://issues.qgis.org/issues/20077?journal_id=942282018-10-12T05:16:06ZBurghardt Scholleburghardt.scholle@stadt.wolfsburg.de
<ul></ul><p>Okay, that's not nice, of course. The concurrent use is one of the main advantage when storing data on a shared network folder. What surprises me is that the problems only occur when one assign a table name that already existed in the geopackage. If one create a new table with an unused name, these problems do not occur. With QGIS master, a table with a previously used name is created correctly - it only takes forever. I don't understand why there is such a huge difference between a geopackage stored locally or in the network.</p>