#1358: WinGRASS 6.4.1: SQLite driver errors: `Unable to open database'
---------------------------+------------------------------------------------
Reporter: rvanderweide | Owner: grass-dev@…
Type: defect | Status: new
Priority: normal | Milestone:
Component: Database | Version: 6.4.1 RCs
Keywords: SQLite driver | Platform: MSWindows 7
Cpu: x86-64 |
---------------------------+------------------------------------------------
I keep on getting SQLite driver errors when trying to use a newly created
SQLite database ('unable to open database'). By default, GRASS starts up
connected to DBF. Connecting to an SQLite database works fine -- when the
database does not yet exist, it seems to create an empty database, but i
can't seem to access it. For example, errors emerge when i wish to import
a shapefile, OR copy an attribute table from a shapefile that was
previously imported using the DBF driver. I recall that an earlier version
of GRASS 6.4 used SQLite as the default driver. With this version, all
worked well, i.e. i did NOT have the problems described below.
Let me attach the command lines for both examples:
(1) Problem importing shapefile while connected to a SQLite database:
GRASS 6.4> db.connect driver=sqlite database="c:\Test\sqlite.db"
GRASS 6.4> v.in.ogr dsn=C:\Test\pse.shp layer=pse output=pse
Datum <Palestine_1923> not recognised by GRASS and no parameters found
Projection of input dataset and current location appear to match
Layer: pse
DBMI-SQLite driver error:
Unable to open database: unable to open database file
Unable to open database <c:\test\sqlite.db> by driver <sqlite>
ERROR: Unable open database <c:\test\sqlite.db> by driver <sqlite>
(Fri May 06 13:33:01 2011) Command finished (0 sec)
(2) Problem copying attribute table (imported in DBF) to SQLite database):
GRASS 6.4> db.connect driver=sqlite database="c:\Test\sqlite.db"
GRASS 6.4> g.copy vect=pse@pov4,pse_sqlite
Copy vector <pse@pov4> to current mapset as <pse_sqlite>
DBMI-SQLite driver error:
Unable to open database: unable to open database file
WARNING: Unable to open database <sqlite> by driver <c:\Test\sqlite.db>
no database is open
WARNING: Unable to copy table <pse_sqlite>
WARNING: Cannot copy <pse@pov4> to current mapset as <pse_sqlite>
This is most probably a SQLite problem because the database is in the same
folder like the shapefile to be imported. Files in this folder are opened
by OGR, after that sqlite tries to create a new database in that folder
but does not have exclusive rights on that folder because other files in
that folder are opened.
Related to that, the folder where the sqlite database is supposed to be
stored must exist, the database file itself not necessarily, otherwise
sqlite complains again.
Try creating a new folder with nothing in it and then run
{{{
db.connect driver=sqlite database="c:\<name of new empty
folder>\sqlite.db"
}}}
Replying to [comment:3 mmetz]:
> This is most probably a SQLite problem because the database is
> in the same folder like the shapefile to be imported. Files in
> this folder are opened by OGR, after that sqlite tries to create
> a new database in that folder but does not have exclusive rights
> on that folder because other files in that folder are opened.
so does OGR hold open ''all'' files in the dir, even unrelated filenames
to the shapefile layer? how about same layer name but .sqlite or .db
extension? (ie unknown to the shapefile format)
I would not think that you could fopen() a dir in Windows, holding the
rest of the dir hostage.
> I would not think that you could fopen() a dir in Windows, holding the
rest of the dir hostage.
The message "unable to open database file" corresponds to SQLITE_CANTOPEN,
which occurs if SQLite can't open the database for non-straightfoward
reasons (there are separate codes for the more common cases, e.g. where
the database doesn't exist, the database is locked, insufficient
permissions, etc).
Apparently, this can happen on Windows due to other programs scanning
files in the background, which prevents them from being replaced.
TortoiseSVN and Windows Search are common culprits. Use of "PRAGMA
journal_mode ..." has been suggested as a workaround.
Replying to [comment:6 glynn]:
> Apparently, this can happen on Windows due to other programs scanning
files in the background, which prevents them from being replaced.
TortoiseSVN and Windows Search are common culprits. Use of "PRAGMA
journal_mode ..." has been suggested as a workaround.
Replying to [comment:7 neteler]:
> Replying to [comment:6 glynn]:
> > Apparently, this can happen on Windows due to other programs scanning
files in the background, which prevents them from being replaced.
TortoiseSVN and Windows Search are common culprits. Use of "PRAGMA
journal_mode ..." has been suggested as a workaround.
>
> Any ideas how to implement this?
>
> http://www.sqlite.org/pragma.html#pragma_journal_mode
In this particular example, the import failed because of this 'feature' of
recent sqlite versions: http://www.sqlite.org/lockingv3.html
"When SQLite creates a journal file on Unix, it opens the
directory that contains that file and calls fsync() on the
directory, in an effort to push the directory information to disk."
Here, both the shapefile to be imported and the sqlite database were in
the same folder. The OGR dsn/layer is opened first, then the sqlite db.
This fails because the OGR dsn/layer is still open because GRASS still
needs to read it. But this example is unusual and would not happen in a
standard GRASS mapset.
Anyway, I have changed the default location of the sqlite db in trunk
r52829 from $MAPSET/sqlite.db to $MAPSET/sqlite/sqlite.db, such that
sqlite, just like the dbf driver, gets its own exclusive directory. This
does however not solve the issues mentioned by Glynn but at least some
other issues I found with recent sqlite versions.
Replying to [comment:9 hamish]:
> unless someone has a bright idea for a minimally invasive fix, bumping
it down the road a little ...
With GRASS default settings, i.e. the sqlite database in the default
folder, this does not happen. Changing priority to minor, but the ticket
could also be closed as invalid. Essentially, this is a sqlite bug with
sqlite calling fsync() on the directory of the sqlite database even if the
directory contains files not related to the sqlite database.
Replying to [comment:10 mmetz]:
> Replying to [comment:9 hamish]:
> > unless someone has a bright idea for a minimally invasive fix, bumping
it down the road a little ...
>
> With GRASS default settings, i.e. the sqlite database in the default
folder, this does not happen. Changing priority to minor, but the ticket
could also be closed as invalid. Essentially, this is a sqlite bug with
sqlite calling fsync() on the directory of the sqlite database even if the
directory contains files not related to the sqlite database.
With the latest wingrass7 (July 18), after installation and download of
the NC data set, I get the following error:
{{{
g.copy vect=roadsmajor@PERMANENT,roads2
DBMI-SQLite driver error:
Unable to open database:
C:\grassdata\North_Carolina\user1\sqlite\sqlite.db
unable to open database file
no database is open
Copy vector <roadsmajor@PERMANENT> to current mapset as <roads2>
DBMI-SQLite driver error:
Unable to open database:
C:\grassdata\North_Carolina\user1\sqlite\sqlite.db
unable to open database file
WARNING: Impossible d'ouvrir la base de données
<C:\grassdata\North_Carolina\user1\sqlite\sqlite.db> avec le driver
<sqlite>
no database is open
WARNING: Impossible de copier la table <roads2>
WARNING: Unable to copy <roadsmajor@PERMANENT> to current mapset as
<roads2>
}}}
Replying to [comment:11 mlennert]:
> Replying to [comment:10 mmetz]:
> > Replying to [comment:9 hamish]:
> > > unless someone has a bright idea for a minimally invasive fix,
bumping it down the road a little ...
> >
> > With GRASS default settings, i.e. the sqlite database in the default
folder, this does not happen. Changing priority to minor, but the ticket
could also be closed as invalid. Essentially, this is a sqlite bug with
sqlite calling fsync() on the directory of the sqlite database even if the
directory contains files not related to the sqlite database.
>
> With the latest wingrass7 (July 18), after installation and download of
the NC data set, I get the following error:
>
{{{
g.copy vect=roadsmajor@PERMANENT,roads2
DBMI-SQLite driver error:
Unable to open database:
C:\grassdata\North_Carolina\user1\sqlite\sqlite.db
unable to open database file
no database is open
[...]
}}}
The sqlite mapset has the default settings
{{{
DB_DRIVER: sqlite
DB_DATABASE: $GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db
}}}
You must have changed DB_DATABASE to
{{{
DB_DATABASE: $GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db
}}}
but I guess that path does not exist.
The sqlite driver should be intelligent enough to create a new database
including the path to the new database if need be.
Replying to [comment:12 mmetz]:
> Replying to [comment:11 mlennert]:
> > Replying to [comment:10 mmetz]:
> > > Replying to [comment:9 hamish]:
> > > > unless someone has a bright idea for a minimally invasive fix,
bumping it down the road a little ...
> > >
> > > With GRASS default settings, i.e. the sqlite database in the default
folder, this does not happen. Changing priority to minor, but the ticket
could also be closed as invalid. Essentially, this is a sqlite bug with
sqlite calling fsync() on the directory of the sqlite database even if the
directory contains files not related to the sqlite database.
> >
> > With the latest wingrass7 (July 18), after installation and download
of the NC data set, I get the following error:
> >
> {{{
> g.copy vect=roadsmajor@PERMANENT,roads2
> DBMI-SQLite driver error:
> Unable to open database:
> C:\grassdata\North_Carolina\user1\sqlite\sqlite.db
> unable to open database file
> no database is open
> [...]
> }}}
>
> The sqlite mapset has the default settings
> {{{
> DB_DRIVER: sqlite
> DB_DATABASE: $GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db
> }}}
>
> You must have changed DB_DATABASE to
> {{{
> DB_DATABASE: $GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db
> }}}
I didn't touch any config settings. Just installed using the native GRASS
installer, downloaded the NC dataset, launched GRASS and tried a g.copy.
From your comment:8 above, I thought that now the second form is the
normal form (i.e. sqlite/sqlite.db).
>
> but I guess that path does not exist.
Yes, it does not exist.
>
> The sqlite driver should be intelligent enough to create a new database
including the path to the new database if need be.
Replying to [comment:12 mmetz]:
> The sqlite mapset has the default settings
{{{
DB_DRIVER: sqlite
DB_DATABASE: $GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db
}}}
In the NC dataset downloaded by the wingrass7 installer, there is no
sqlite mapset. I tried the user1 mapset which does not have the sqlite/
directory, not an sqlite.db file. I created a sqlite/ directory (but not
the sqlite.db file) to test whether the sqlite driver might not be able to
create the directory, but that didn't help.
Replying to [comment:15 neteler]:
> Replying to [comment:12 mmetz]:
> > Replying to [comment:11 mlennert]:
> > > Replying to [comment:10 mmetz]:
> ...
> > > With the latest wingrass7 (July 18)
>
> Careful! this is a GRASS 6 ticket:
Yes, I know, but since in comment:8 in reaction to this ticket a
modification was made in trunk, I thought that my issue was related, but
if you want, I can open a separate ticket.
Replying to [comment:14 mlennert]:
> Sorry, I had read your remark to quickly:
>
> Replying to [comment:12 mmetz]:
> > The sqlite mapset has the default settings
> {{{
> DB_DRIVER: sqlite
> DB_DATABASE: $GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db
> }}}
>
> In the NC dataset downloaded by the wingrass7 installer, there is no
sqlite mapset. I tried the user1 mapset which does not have the sqlite/
directory, not an sqlite.db file. I created a sqlite/ directory (but not
the sqlite.db file) to test whether the sqlite driver might not be able to
create the directory, but that didn't help.
Strike that. I got confused between two different GISDBASEs while trying
to rule out space in directory names issues. Actually once the sqlite
directory is created, g.copy works as expected, and so the sqlite driver
seems to correctly create the sqlite.db file if it is absent, but it does
not create the directory.
Replying to [comment:12 mmetz]:
>
> The sqlite driver should be intelligent enough to create a new database
including the path to the new database if need be.
Done in trunk r57276, but only tested on GNU/Linux.
Replying to [comment:18 mmetz]:
> Replying to [comment:12 mmetz]:
> >
> > The sqlite driver should be intelligent enough to create a new
database including the path to the new database if need be.
>
> Done in trunk r57276, but only tested on GNU/Linux.