Sf: writing gpkg and sqlite on samba shares fails
I'm lost. I can't figure how to write GPKG or sqlite files with st_write(). Examples from #470 fail as well (see below).
Files are written but are empty. st_write() does not tell that it failed to write GPKG. GPKG takes way longer to crash than sqlite.
library(sf)
n = 1000
d = data.frame(a = 1:n, X = rnorm(n,1,1), Y = rnorm(n,1,1))
mp1 = st_as_sf(d, coords = c("X","Y"))
st_write(mp1, dsn="dat1.gpkg")
Writing layer `dat1' to data source `dat1.gpkg' using driver `GPKG'
features: 1000
fields: 1
geometry type: Point
Warning messages:
1: In CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), :
GDAL Error 1: sqlite3_exec(COMMIT) failed: database is locked
2: In CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), :
GDAL Error 1: sqlite3_exec(PRAGMA synchronous = OFF) failed: Safety level may not be changed inside a transaction
3: In CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), :
GDAL Error 1: sqlite3_exec(BEGIN) failed: cannot start a transaction within a transaction
In a new R Session with the above data set
st_write(mp1, dsn="dat2.sqlite")
Creating dataset dat2.sqlite failed.
Error in CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), :
Creation failed.
In addition: Warning message:
In CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), :
GDAL Error 1: sqlite3_exec(CREATE TABLE geometry_columns ( f_table_name VARCHAR, f_geometry_column VARCHAR, geometry_type INTEGER, coord_dimension INTEGER, srid INTEGER, geometry_format VARCHAR );CREATE TABLE spatial_ref_sys ( srid INTEGER UNIQUE, auth_name TEXT, auth_srid TEXT, srtext TEXT)) failed: database is locked
Versions:
geo-packages are from ubuntugis-unstable
> sf_extSoftVersion()
GEOS GDAL proj.4 lwgeom GDAL_with_GEOS
"3.5.1" "2.2.2" "4.9.2" NA "true"
>
> sessionInfo()
R version 3.4.3 (2017-11-30)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Ubuntu 16.04.3 LTS
Matrix products: default
BLAS: /usr/lib/openblas-base/libblas.so.3
LAPACK: /usr/lib/libopenblasp-r0.2.18.so
locale:
[1] LC_CTYPE=de_DE.UTF-8 LC_NUMERIC=C
[3] LC_TIME=de_DE.UTF-8 LC_COLLATE=de_DE.UTF-8
[5] LC_MONETARY=de_DE.UTF-8 LC_MESSAGES=en_GB.UTF-8
[7] LC_PAPER=de_DE.UTF-8 LC_NAME=C
[9] LC_ADDRESS=C LC_TELEPHONE=C
[11] LC_MEASUREMENT=de_DE.UTF-8 LC_IDENTIFICATION=C
attached base packages:
[1] stats graphics grDevices utils datasets methods base
other attached packages:
[1] sf_0.6-0
loaded via a namespace (and not attached):
[1] compiler_3.4.3 magrittr_1.5 class_7.3-14 tools_3.4.3
[5] DBI_0.7 pillar_1.1.0 units_0.5-1 Rcpp_0.12.15
[9] udunits2_0.13 grid_3.4.3 e1071_1.6-8 classInt_0.1-24
[13] rlang_0.1.6
rnuske
All 58 comments
Hi. Was also going to report a similar problem, but then I saw this. I have the same problem on gpkg, but strangely it only happens when trying to write to a remote share. Writing on my (Linux) desktop works.
lbusett
on 26 Jan 2018
oh no, @lbusett you are right! My test data were all written to a folder on a samba share.
I tried it again on the local hard drive (different computer but same software versions) and it works perfectly!
Will change the title of the issue.
rnuske
on 26 Jan 2018
I think there is little chance that we can solve this within sf; it uses GDAL to read & write gpkg.
edzer
on 26 Jan 2018
After a bit of research, my bet is on permissions/SELinux (whatever it is) problems for sqlite. This may shed some light:
https://stackoverflow.com/a/3330616/6871135
Can not test up to next week, though.
lbusett
on 26 Jan 2018
Checked writing a GPKG file to the remote samba share in the shell with ogr2ogr :
rnuske@PC:~/remote/H/test_gpkg$ ogr2ogr -f GPKG dat1.gpkg dat1.shp
ERROR 1: sqlite3_exec(COMMIT) failed: database is locked
ERROR 1: sqlite3_exec(PRAGMA synchronous = OFF) failed: Safety level may not be changed inside a transaction
ERROR 1: sqlite3_exec(BEGIN) failed: cannot start a transaction within a transaction
ERROR 1: Transaction not established
It failed and left a 0 Byte dat1.gpkg behind.
The same command on my local hard drive works flawlessly.
If its a GDAL/OGR problem (as it seems to be), sf probably only can ensure to properly report the error and maybe give a hint on st_write's help page.
rnuske
on 29 Jan 2018
I have no clue where in the sf sources this particular case could be detected; at the GDAL API level it tries to catch all errors it can as far as I can tell; of course I may have overlooked something.
edzer
on 29 Jan 2018
@rnuske please do read the driver documentation:
SQLite databases often due not work well over NFS, or some other networked file system protocols due to the poor support for locking. It is safest to operate only on SQLite files on a physical disk of the local system.
is very clear and explains why. Both sf read/write and rgdal read/write build on GDAL, so that documentation is always the best source of information. Remember to check which GDAL version you are running too, as the documentation of drivers also tracks changes by version.
rsbivand
on 29 Jan 2018
And the st_write documentation points to the GDAL drivers page...
edzer
on 29 Jan 2018
I probably was overwhelmed by the current run to make GPKG the new Shapefile and expected everything to work everywhere just perfectly. It might be too early for that and my attempt too careless. I was just confused to see a warning In the R session after a failed write.
Following @rsbivand @edzer advice, I tried to get the necessary clues from the GPKG documentation. Which I find hard. There is nothing in this regard under Limitations. Amd the second paragraph talking about SQLite only mentions read/write access. But this is out of the scope of this project. And you are absolutely right that I could have found it on the SQLite documentation.
I apologize if my remark was too harsh. My English became very rusty due to not using it regurlarly.
rnuske
on 29 Jan 2018
Please ask GDAL to add the SQLite note about only writing to local physical drives to the GPKG page. I agree that this is not obvious, but unless the GDAL driver reports transaction failure, we can't check correct completion in a straightforward way.
rsbivand
on 29 Jan 2018
Ouch... this really makes it difficult to make the transition to gpkg...
A workaround appears to be to mount the network share using the "norbl" flag
(See https://stackoverflow.com/a/1409536/6871135 )
I just tried that on my machine and it seems to work. I however do not know if that change could have other (undesirable) consequences.
lbusett
on 29 Jan 2018
done, I asked for an enhancement in the GDAL issue tracker.
The missing transaction failure seems to be is a different issue, which I do not feel competent to report at GDAL.
rnuske
on 29 Jan 2018
I confirm the issue. Also for me, when writing to a local directory everything seems to work in the first place but the written file has no features (probably a different issue). .shp works fine.
pat-s
on 5 Feb 2018
@krlmlr you mentioned you had an idea that this could be related with the type of locking applied - I forgot the details, could you pls briefly repeat here?
Would it be worth an experiment to switch off doing the write in a single transaction? This would slow down writing, but better slow than impossible?
edzer
on 5 Feb 2018
I'm not sure how to do this via GDAL, I'm not even sure SQLite itself is capable of opening a database in exclusive mode. (I expected this, but can't find this option in the documentation.)
It may be worthwhile investigating the GDAL code to see what happens under the hood. If you're creating a file from scratch, it might be safest to create it in a temporary location and then copy.
krlmlr
on 6 Feb 2018
Could somebody try writing to a local temp-file and using file.copy()? Can the status of a file system as networked be determined within R?
rsbivand
on 6 Feb 2018
Could somebody try writing to a local temp-file and using file.copy()?
file.copy() works to transfer the file to the samba share. Reading afterwards is fine.
pat-s
on 6 Feb 2018
The OP got three GDAL error 1's; this emits a warning, but that can easily be changed (if useful).
GDAL says it's' an application-defined error; we could deal with this seperately when driver is GPKG (or sqlite). Just need to find out where this is triggered...
edzer
on 6 Feb 2018
I was wondering why the check for successful transaction did not fire. It might be caused by GDAL/OGR crashing earlier and that check can not be reached or the transaction was not started properly.
To check for the second case I printed the return value of poDS->StartTransaction() to the R console. It is 0 => OGRERR_NONE when writing to local hard drive and 6 => OGRERR_FAILURE when writing to a samba share.
Would it be possible to catch that error or shall the transaction start fail without consequences for drivers which do not need or support transactions?
rnuske
on 7 Feb 2018
But that is not an error - it's simply an indication that transaction won't work here and shouldn't be used. You can read in #470 that initially we wrote every GPKG without transactions, which gave it a large speed disadvantage compared to shapefiles.
edzer
on 7 Feb 2018
Is there a way of imitating a networked drive which would let us replicate this - say a network drive in a container setting or similar? Will a network drive on the same machine fail in the same way?
rsbivand
on 7 Feb 2018
If you need a testing connection to a Samba Server I could set something up temporarily.
Sent with GitHawk
pat-s
on 7 Feb 2018
Maybe this is a good heuristic:
- if driver is GPKG and transaction fails, stop trying
- write instead to temporary file (assuming that doens't fail!)
- when finished copy that file to destination
edzer
on 8 Feb 2018
Sounds reasonable to me.
Since also the failure takes a long time, would it be reasonable to allow the user to make the "tempfile-->copy" the default by setting an option in .Rprofile?
By the way: does anyone has any insights about the workaround I proposed here: https://github.com/r-spatial/sf/issues/628#issuecomment-361252797
It works for me, but I do not know if it may have any side effects.
lbusett
on 8 Feb 2018
Ran into this issue again today. "write - copy - delete" is quite annoying in the long run. (Unfortunately, "write - move" does not work across file-systems when using fs).
Any proceedings within the last 2 months?
And any plans when this issue will be tackled again?
pat-s
on 16 Apr 2018
As from my side: complete lack of ability to test.
edzer
on 16 Apr 2018
Note that @rnuske did add documentation on the OGR side in the Wiki. There will not be an rgdal/sf solution to this - the only solution is to work around the way that SQLite interacts with network locking, and from the SQLite documentation, this isn't a priority because they don't control the underlying OS. So write/update locally and copy to network on completion. Do transactional RDB handle this internally, protecting the user from the OS?
rsbivand
on 16 Apr 2018
I see. This is really a bummer in my opinion for the long-term success of the Geopackage file format. There should be at least a proper error message. But yeah, this is all unrelated to sf.
Anyhow, would a temporary workaround within write_sf() be accepted that writes to a temporary directory when that error is faced and then copies the file to the desired location? @edzer
Currently I do
write_sf()
fs::file_copy()
fs::file_delete()
Well, it's a work-around for a specific category of users of a specific driver. It will create overhead for other users of that driver, call for ad hoc code, and may cause frustration e.g. when writing a massive file in the case that /tmp/ (or wherever tmp files are written) does not have the required capacity. Can't you write a wrapper around write_sf and publish that in a gist? We could point to that from the user docs.
edzer
on 17 Apr 2018
The earlier ogr2ogr test in https://github.com/r-spatial/sf/issues/628#issuecomment-361178229 suggests that a transaction failure should be taken seriously in the following part: https://github.com/r-spatial/sf/blob/1df5b521194a03613bca3a575cb65fd8e0a32800/src/gdal_write.cpp#L226
Unless/until the GDAL driver is fixed, it would be kind if sf offered a workaround to write to a temporary file and then copy. Maybe with an optional argument use_temp_file = NA, where NA means "use /tmp as necessary and talk about it"?
krlmlr
on 18 Apr 2018
Well spotted, Kirill! We indeed do not distinguish a failed transaction (OGRERR_FAILURE) from the case where drivers don't support transactions (OGRERR_UNSUPPORTED_OPERATION), which in this case might make sense. Do you think that starting the transaction triggers the file lock & breaks on network drives?
edzer
on 18 Apr 2018
Looking at the ogr2ogr output, a commit operation fails, which triggers further errors downstream.
I have briefly looked at the GDAL sources found at https://github.com/OSGeo/gdal/. Transaction-related commands are sent as regular SQL queries: https://github.com/OSGeo/gdal/blob/5e2d6cb208082d88745ee25e49c7e02063239c33/gdal/ogr/ogrsf_frmts/sqlite/ogrsqlitedatasource.cpp#L2962-L2965
Databases are opened with the SQLITE_OPEN_NOMUTEX flag, but this seems to be relevant only to how multiple threads are synchronized:
https://github.com/OSGeo/gdal/blob/master/gdal/ogr/ogrsf_frmts/sqlite/ogrsqlitedatasource.cpp#L744
I'm seeing that user-defined (?) pragmas are executed when connecting, maybe these don't play well on Samba? https://github.com/OSGeo/gdal/blob/2a269ed2b136bd254b213f39846587c1eb237662/gdal/ogr/ogrsf_frmts/sqlite/ogrsqlitedatasource.cpp#L876
(The code links refer to OGRSQLiteBaseDataSource which is a base class to GDALGeoPackageDataset, the relevant driver:)
Long story short, the code looks good. It might be possible to create/open a database in a similar fashion (with the same pragmas) using the RSQLite package and hit the same error.
@rouault: Why would ogr2ogr fail consistently with the gpkg driver if writing to a Samba share? Maybe it would help to set PRAGMA locking_mode = EXCLUSIVE or use BEGIN_EXCLUSIVE for newly created databases?
krlmlr
on 19 Apr 2018
Does PRAGMA locking_mode = EXCLUSIVE really help for the Samba share case ? Did you try it by setting the OGR_SQLITE_PRAGMA environmenet variable to "locking_mode=EXCLUSVE" ?
rouault
on 19 Apr 2018
Thanks for the pointer. I don't know, it's worth a try. I don't have access to a Samba share, though.
Are there any pragmas executed by default, other than cache_size and synchronous?
krlmlr
on 19 Apr 2018
Are there any pragmas executed by default, other than cache_size and synchronous?
yes page_size=4096 as well
rouault
on 19 Apr 2018
Relevant: How to make SQLite run safely on CIFS mounted file system? (Hint: You don't, you can't.)
We could also try journal_mode=MEMORY, with currently released drivers it most likely has to come last in the OGR_SQLITE_PRAGMA env var. According to https://www.sqlite.org/wal.html#advantages, WAL doesn't work on network drives.
Very similar question on SO: https://stackoverflow.com/q/2740806/946850. Someone had success mounting with nobrl on Linux.
@rnuske @pat-s: Could you please try
OGR_SQLITE_PRAGMA=locking_mode=EXCLUSIVE,journal_mode=MEMORY ogr2ogr -f GPKG dat1.gpkg dat1.shp
or perhaps other combinations?
krlmlr
on 19 Apr 2018
This commit tries to catch the error when starting a transaction, and in case of error tries writing to a tmp file (which should not be on a networked drive), and copy that afterwards. As mentioned, I can't test this myself.
edzer
on 19 Apr 2018
yeah
test case from first posting writing to the same samba share now writes successfull (GPKG can be opened by QGIS and shows 1000 points) :-D
but displays a different set of warnings :-(
> st_write(mp1, dsn="dat1.gpkg")
Writing layer `dat1' to data source `dat1.gpkg' using driver `GPKG'
Writing layer `dat1' to data source `/tmp/Rtmpsbe3mQ/file1f7a44c7da99' using driver `GPKG'
features: 1000
fields: 1
geometry type: Point
Warning messages:
1: In CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), :
GDAL Error 1: sqlite3_exec(COMMIT) failed: database is locked
2: In CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), :
GDAL Error 1: sqlite3_exec(PRAGMA synchronous = OFF) failed: Safety level may not be changed inside a transaction
3: In CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), :
GDAL Error 1: sqlite3_exec(BEGIN) failed: cannot start a transaction within a transaction
4: In CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), :
GDAL Error 1: Transaction not established
5: In CPL_write_ogr(obj, tmp, layer, driver, as.character(dataset_options), :
GDAL Message 1: The '' extension is not allowed by the GPKG specification, which may cause compatibility problems
Can I somehow help with other tests?
session_info()
> devtools::session_info()
Session info ------------------------------------------------------------------
setting value
version R version 3.4.4 (2018-03-15)
system x86_64, linux-gnu
ui X11
language
collate de_DE.UTF-8
tz Europe/Berlin
date 2018-04-19
Packages ----------------------------------------------------------------------
package * version date source
base * 3.4.4 2018-03-16 local
class 7.3-14 2015-08-30 CRAN (R 3.4.0)
classInt 0.2-3 2018-04-16 CRAN (R 3.4.4)
compiler 3.4.4 2018-03-16 local
datasets * 3.4.4 2018-03-16 local
DBI 0.8 2018-03-02 CRAN (R 3.4.3)
devtools 1.13.5 2018-02-18 CRAN (R 3.4.3)
digest 0.6.15 2018-01-28 CRAN (R 3.4.3)
e1071 1.6-8 2017-02-02 CRAN (R 3.4.2)
graphics * 3.4.4 2018-03-16 local
grDevices * 3.4.4 2018-03-16 local
grid 3.4.4 2018-03-16 local
magrittr 1.5 2014-11-22 CRAN (R 3.4.0)
memoise 1.1.0 2017-04-21 CRAN (R 3.4.0)
methods * 3.4.4 2018-03-16 local
Rcpp 0.12.16 2018-03-13 CRAN (R 3.4.3)
RPostgreSQL 0.6-2 2017-06-24 CRAN (R 3.4.1)
sf * 0.6-2 2018-04-19 Github (r-spatial/sf@22235c9)
spData 0.2.8.3 2018-03-25 CRAN (R 3.4.4)
stats * 3.4.4 2018-03-16 local
tools 3.4.4 2018-03-16 local
udunits2 0.13 2016-11-17 CRAN (R 3.4.0)
units 0.5-1 2018-01-08 CRAN (R 3.4.3)
utils * 3.4.4 2018-03-16 local
withr 2.1.2 2018-03-15 CRAN (R 3.4.3)
What environment variables did you set?
krlmlr
on 19 Apr 2018
And just to be sure: the test was again unsuccessful?
edzer
on 19 Apr 2018
edited the comment. writing GPKG was SUCCESSFUL!
rnuske
on 19 Apr 2018
I'm wondering if setting the environment variable OGR_SQLITE_PRAGMA to "locking_mode=EXCLUSIVE,journal_mode=MEMORY" helps writing directly to the Samba share (and hence get rid of the warnings).
krlmlr
on 19 Apr 2018
No success on my side.
st_write(data_sf[[1]], "test.gpkg")
GDAL Error 1: COMMIT transaction failed: database is lockedWriting layer `laukiz1' to data source `/data/patrick/mod/tree-per-tree/2016/test/laukiz1.gpkg' using driver `GPKG'
GDAL Error 1: sqlite3_prepare_v2(SELECT COUNT(*) FROM sqlite_master WHERE name IN ('gpkg_metadata', 'gpkg_metadata_reference') AND type IN ('table', 'view')) failed: file is encrypted or is not a databasefeatures: 559
fields: 1
geometry type: Point
GDAL Error 1: sqlite3_exec(CREATE TABLE 'laukiz1' ( "fid" INTEGER PRIMARY KEY AUTOINCREMENT, "geom" POINT, "deftot" MEDIUMINT)) failed: file is encrypted or is not a databaseFailed to create feature 0 in laukiz1
GDAL Error 1: sqlite3_get_table(SELECT * FROM sqlite_master WHERE name = 'gpkg_extensions' AND type IN ('table', 'view')) failed: file is encrypted or is not a databaseGDAL Error 1: sqlite3_get_table(SELECT * FROM sqlite_master WHERE name = 'gpkg_extensions' AND type IN ('table', 'view')) failed: file is encrypted or is not a databaseGDAL Error 1: sqlite3_exec(CREATE TABLE gpkg_extensions (table_name TEXT,column_name TEXT,extension_name TEXT NOT NULL,definition TEXT NOT NULL,scope TEXT NOT NULL,CONSTRAINT ge_tce UNIQUE (table_name, column_name, extension_name))) failed: file is encrypted or is not a databaseGDAL Error 1: sqlite3_exec(DELETE FROM gpkg_geometry_columns WHERE table_name = 'laukiz1') failed: file is encrypted or is not a databaseFailed to create feature 0 in laukiz1
Deleting layer `laukiz1' failed
Fehler in CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), :
Feature creation failed.
devtools::session_info("sf")
Session info -----------------------------------------------------------------------------------------------------------------
setting value
version R version 3.4.4 (2018-03-15)
system x86_64, linux-gnu
ui RStudio (1.2.462.1)
language (EN)
collate de_DE.UTF-8
tz Europe/Berlin
date 2018-04-19
Packages ---------------------------------------------------------------------------------------------------------------------
package * version date source
class 7.3-14 2015-08-30 CRAN (R 3.4.4)
classInt 0.2-3 2018-04-16 cran (@0.2-3)
DBI 0.8 2018-03-02 cran (@0.8)
e1071 1.6-8 2017-02-02 CRAN (R 3.4.4)
graphics * 3.4.4 2018-03-15 local
grDevices * 3.4.4 2018-03-15 local
grid 3.4.4 2018-03-15 local
magrittr * 1.5 2014-11-22 CRAN (R 3.4.4)
MASS 7.3-49 2018-02-23 CRAN (R 3.4.4)
methods * 3.4.4 2018-03-15 local
Rcpp 0.12.16 2018-03-13 cran (@0.12.16)
RPostgreSQL 0.6-2 2017-06-24 cran (@0.6-2)
sf * 0.6-2 2018-04-19 Github (r-spatial/sf@be38147)
spData 0.2.8.3 2018-03-25 cran (@0.2.8.3)
stats * 3.4.4 2018-03-15 local
tools 3.4.4 2018-03-15 local
udunits2 0.13 2016-11-17 CRAN (R 3.4.4)
units 0.5-1 2018-01-08 CRAN (R 3.4.4)
utils * 3.4.4 2018-03-15 local
maybe add delete_layer=TRUE?
edzer
on 19 Apr 2018
Doesn't help, tried that already. Same error message.
The same error message also appears when I write into a clean directory.
Everythings fine with the object as writing to a local dir works.
@rnuske Did you set any env variable or did you just use the most recent Github version of sf?
pat-s
on 19 Apr 2018
I didn't set any environment variables.
just devtools::install_github and the first code block at the top of this page
rnuske
on 19 Apr 2018
The example sadly does not work for me. How is your system setup in detail?
For me, R runs on a Debian 9 and I am trying to write to a Windows server mounted via samba (cifs).
Writing locally to the Linux drive works.
sf_extSoftVersion()
GEOS GDAL proj.4 GDAL_with_GEOS
"3.5.1" "2.1.2" "4.9.3" "true"
my local computer (where the R session runs) ubuntu 16.04.4 and the remote one is ubuntu 16.04.2 with samba 4.7. The samba share is mounted at start up using cifs via an fstab entry
rnuske
on 19 Apr 2018
Did one of you try with setting the environment variables @krlmlr suggested?
@pat-s : it looks like in your setup the poDS->StartTransaction() does not return an error, but committing the transaction, where things break before sf can emit an error message. I'll see if I can catch that as well, and retry on /tmp from that point on.
edzer
on 19 Apr 2018
Did one of you try with setting the environment variables @krlmlr suggested?
No because I did not know where they should be set. In R or at the OS level?
Thanks for your effort in this Edzer!
pat-s
on 19 Apr 2018
You can do this by starting R from the bash prompt as
$ VARIABLE=VALUE R
check in R:
> Sys.getenv("VARIABLE")
[1] "VALUE"
Use quotes around VALUE if it contains spaces or other funny symbols.
edzer
on 19 Apr 2018
Guess I am doing it wrong? Referring to https://github.com/r-spatial/sf/issues/628#issuecomment-382674350
Sys.setenv("OGR_SQLITE_PRAGMA") = "locking_mode=EXCLUSIVE,journal_mode=MEMORY"
Fehler in Sys.setenv("OGR_SQLITE_PRAGMA") = "locking_mode=EXCLUSIVE,journal_mode=MEMORY" :
Ziel der Zuweisung expandiert zu keinem Sprachobjekt
Use my pattern:
$ OGR_SQLITE_PRAGMA="locking_mode=EXCLUSIVE,journal_mode=MEMORY" R
...
> Sys.getenv("OGR_SQLITE_PRAGMA")
[1] "locking_mode=EXCLUSIVE,journal_mode=MEMORY"
Or, in R (but perhaps before loading any packages):
Sys.setenv("OGR_SQLITE_PRAGMA" = "locking_mode=EXCLUSIVE,journal_mode=MEMORY")
Ah, yes, of course!
edzer
on 19 Apr 2018
This commit might remove the warnings that @rnuske sees, and might succeed for @pat-s (still with warnings). Feedback appreciated.
edzer
on 20 Apr 2018
Writing GPKG with and without environment variables is successful and leads to identical error messages.
without environmental variables. One error message less compared to yesterday.
> st_write(mp1, dsn="dat1.gpkg")
Writing layer `dat1' to data source `remote/H/dat1.gpkg' using driver `GPKG'
writing first to temporary file /tmp/RtmpWtOqpq/file31e4539ca991
Writing layer `dat1' to data source `/tmp/RtmpWtOqpq/file31e4539ca991' using driver `GPKG'
features: 1000
fields: 1
geometry type: Point
Warning messages:
1: In CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), :
GDAL Error 1: sqlite3_exec(COMMIT) failed: database is locked
2: In CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), :
GDAL Error 1: sqlite3_exec(PRAGMA synchronous = OFF) failed: Safety level may not be changed inside a transaction
3: In CPL_write_ogr(obj, tmp, layer, driver, as.character(dataset_options), :
GDAL Message 1: The '' extension is not allowed by the GPKG specification, which may cause compatibility problems
identical error messages with environment variables
> Sys.setenv("OGR_SQLITE_PRAGMA" = "locking_mode=EXCLUSIVE,journal_mode=MEMORY")
> Sys.getenv("OGR_SQLITE_PRAGMA")
[1] "locking_mode=EXCLUSIVE,journal_mode=MEMORY"
>
> st_write(mp1, dsn="remote/H/dat2.gpkg")
Writing layer `dat2' to data source `remote/H/dat2.gpkg' using driver `GPKG'
writing first to temporary file /tmp/RtmpWtOqpq/file31e41485f573
Writing layer `dat2' to data source `/tmp/RtmpWtOqpq/file31e41485f573' using driver `GPKG'
features: 1000
fields: 1
geometry type: Point
Warning messages:
1: In CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), :
GDAL Error 1: sqlite3_exec(COMMIT) failed: database is locked
2: In CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), :
GDAL Error 1: sqlite3_exec(PRAGMA synchronous = OFF) failed: Safety level may not be changed inside a transaction
3: In CPL_write_ogr(obj, tmp, layer, driver, as.character(dataset_options), :
GDAL Message 1: The '' extension is not allowed by the GPKG specification, which may cause compatibility problems
session_info()
> devtools::session_info('sf')
Session info ------------------------------------------------------------------
setting value
version R version 3.4.4 (2018-03-15)
system x86_64, linux-gnu
ui X11
language
collate de_DE.UTF-8
tz Europe/Berlin
date 2018-04-20
Packages ----------------------------------------------------------------------
package * version date source
class 7.3-14 2015-08-30 CRAN (R 3.4.0)
classInt 0.2-3 2018-04-16 CRAN (R 3.4.4)
DBI 0.8 2018-03-02 CRAN (R 3.4.3)
e1071 1.6-8 2017-02-02 CRAN (R 3.4.2)
graphics * 3.4.4 2018-03-16 local
grDevices * 3.4.4 2018-03-16 local
grid 3.4.4 2018-03-16 local
magrittr 1.5 2014-11-22 CRAN (R 3.4.0)
MASS 7.3-49 2018-02-23 CRAN (R 3.4.3)
methods * 3.4.4 2018-03-16 local
Rcpp 0.12.16 2018-03-13 CRAN (R 3.4.3)
RPostgreSQL 0.6-2 2017-06-24 CRAN (R 3.4.1)
sf * 0.6-2 2018-04-20 Github (r-spatial/sf@7ac7377)
spData 0.2.8.3 2018-03-25 CRAN (R 3.4.4)
stats * 3.4.4 2018-03-16 local
tools 3.4.4 2018-03-16 local
udunits2 0.13 2016-11-17 CRAN (R 3.4.0)
units 0.5-1 2018-01-08 CRAN (R 3.4.3)
utils * 3.4.4 2018-03-16 local
Writing now also works for me! :tada:
delete_layer has no effect but delete_dsn works.
Warning messages still appear, no matter if the suggested env variable is set or not.
Here is the output of a call including delete_dsn = TRUE:
st_write(mp1, dsn="/data/patrick/mod/tree-per-tree/2016/test/dat1.gpkg", delete_dsn=T)
Deleting source `/data/patrick/mod/tree-per-tree/2016/test/dat1.gpkg' using driver `GPKG'
Writing layer `dat1' to data source `/data/patrick/mod/tree-per-tree/2016/test/dat1.gpkg' using driver `GPKG'
features: 1000
fields: 1
geometry type: Point
Failed to create feature 0 in dat1
writing first to temporary file /tmp/RtmpUAJHR6/file8545256f37a
Deleting source `/tmp/RtmpUAJHR6/file8545256f37a' failed
Writing layer `dat1' to data source `/tmp/RtmpUAJHR6/file8545256f37a' using driver `GPKG'
features: 1000
fields: 1
geometry type: Point
Warnmeldungen:
1: In CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), :
GDAL Error 1: COMMIT transaction failed: database is locked
2: In CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), :
GDAL Error 1: sqlite3_prepare_v2(SELECT COUNT(*) FROM sqlite_master WHERE name IN ('gpkg_metadata', 'gpkg_metadata_reference') AND type IN ('table', 'view')) failed: file is encrypted or is not a database
3: In CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), :
GDAL Error 1: sqlite3_exec(CREATE TABLE 'dat1' ( "fid" INTEGER PRIMARY KEY AUTOINCREMENT, "geom" POINT, "a" MEDIUMINT)) failed: file is encrypted or is not a database
4: In CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), :
GDAL Error 1: sqlite3_get_table(SELECT * FROM sqlite_master WHERE name = 'gpkg_extensions' AND type IN ('table', 'view')) failed: file is encrypted or is not a database
5: In CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), :
GDAL Error 1: sqlite3_get_table(SELECT * FROM sqlite_master WHERE name = 'gpkg_extensions' AND type IN ('table', 'view')) failed: file is encrypted or is not a database
6: In CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), :
GDAL Error 1: sqlite3_exec(CREATE TABLE gpkg_extensions (table_name TEXT,column_name TEXT,extension_name TEXT NOT NULL,definition TEXT NOT NULL,scope TEXT NOT NULL,CONSTRAINT ge_tce UNIQUE (table_name, column_name, extension_name))) failed: file is encrypted or is not a database
7: In CPL_write_ogr(obj, dsn, layer, driver, as.character(dataset_options), :
GDAL Error 1: sqlite3_exec(DELETE FROM gpkg_geometry_columns WHERE table_name = 'dat1') failed: file is encrypted or is not a database
8: In CPL_write_ogr(obj, tmp, layer, driver, as.character(dataset_options), :
GDAL Message 1: The '' extension is not allowed by the GPKG specification, which may cause compatibility problems
>
Related issues
tiernanmartin
路
3Comments
Nowosad
路
3Comments
Nosferican
路
3Comments
angela-li
路
4Comments
jsta
路
4Comments