writing gpkg and sqlite on samba shares fails #628
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. |
|
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
changed the title
|
I think there is little chance that we can solve this within sf; it uses GDAL to read & write gpkg. |
|
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. |
|
Checked writing a GPKG file to the remote samba share in the shell with It failed and left a 0 Byte 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 |
|
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. |
|
@rnuske please do read the driver documentation:
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. |
|
And the |
|
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. |
|
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. |
|
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. |
|
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. |
|
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). |
|
@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? |
|
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. |
|
Could somebody try writing to a local temp-file and using |
|
|
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... |
|
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 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? |
|
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. |
|
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? |
|
If you need a testing connection to a Samba Server I could set something up temporarily. Sent with GitHawk |
|
Maybe this is a good heuristic:
|
|
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: #628 (comment) It works for me, but I do not know if it may have any side effects. |
|
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 |
|
As from my side: complete lack of ability to test. |
|
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? |
|
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 Anyhow, would a temporary workaround within Currently I do |
|
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. |
|
Relevant: How to make SQLite run safely on CIFS mounted file system? (Hint: You don't, you can't.) We could also try Very similar question on SO: https://stackoverflow.com/q/2740806/946850. Someone had success mounting with @rnuske @pat-s: Could you please try OGR_SQLITE_PRAGMA=locking_mode=EXCLUSIVE,journal_mode=MEMORY ogr2ogr -f GPKG dat1.gpkg dat1.shpor perhaps other combinations? |
|
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. |
|
yeah Can I somehow help with other tests? session_info() |
|
What environment variables did you set? |
|
And just to be sure: the test was again unsuccessful? |
|
edited the comment. writing GPKG was SUCCESSFUL! |
|
I'm wondering if setting the environment variable |
|
No success on my side. 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 |
|
Doesn't help, tried that already. Same error message. The same error message also appears when I write into a clean directory. @rnuske Did you set any env variable or did you just use the most recent Github version of |
|
I didn't set any environment variables. |
|
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 ( 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 |
|
Did one of you try with setting the environment variables @krlmlr suggested? @pat-s : it looks like in your setup the |
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! |
|
You can do this by starting R from the bash prompt as $ VARIABLE=VALUE Rcheck in R: > Sys.getenv("VARIABLE")
[1] "VALUE"Use quotes around |
|
Guess I am doing it wrong? Referring to #628 (comment) 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: |
|
Or, in R (but perhaps before loading any packages): Sys.setenv("OGR_SQLITE_PRAGMA" = "locking_mode=EXCLUSIVE,journal_mode=MEMORY") |
|
Ah, yes, of course! |
* should remove warnings if StartTransaction fails * might work if StartTransaction succeeds, but writing features fails
|
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. identical error messages with environment variables session_info() |
|
Writing now also works for me! 🎉
Warning messages still appear, no matter if the suggested env variable is set or not. Here is the output of a call including 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
>
|
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.
In a new R Session with the above data set
Versions:
geo-packages are from ubuntugis-unstable
The text was updated successfully, but these errors were encountered: