WinGRASS 6.4.1: SQLite driver errors: `Unable to open database'

[rvanderweide]

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>

[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.

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"

Markus M

[neteler]

rvanderweide, did the suggestion work for you?

[hamish]

Replying to 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.

can anyone reproduce this?

?, Hamish

[glynn]

Replying to hamish:

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.

[neteler]

Replying to 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

[mmetz]

Replying to neteler:

Replying to 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.

Markus M

[hamish]

unless someone has a bright idea for a minimally invasive fix, bumping it down the road a little ...

[mmetz]

Replying to 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.

[mlennert]

Replying to mmetz:

Replying to 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>

Is this related ?

[mmetz]

Replying to mlennert:

Replying to mmetz:

Replying to 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.

[mlennert]

Replying to mmetz:

Replying to mlennert:

Replying to mmetz:

Replying to 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.

Apparently it is not...

[mlennert]

Sorry, I had read your remark to quickly:

Replying to 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.

[neteler]

Replying to mmetz:

Replying to mlennert:

Replying to mmetz:

...

With the latest wingrass7 (July 18)

Careful! this is a GRASS 6 ticket:

...

The sqlite mapset has the default settings

DB_DRIVER: sqlite
DB_DATABASE: $GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db

Yes, that's the GRASS 6 directory structure.

And this:

You must have changed DB_DATABASE to

DB_DATABASE: $GISDBASE/$LOCATION_NAME/$MAPSET/sqlite/sqlite.db

... would be the GRASS 7 structure which is unrelated to this ticket.

[mlennert]

Replying to neteler:

Replying to mmetz:

Replying to mlennert:

Replying to 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.

[mlennert]

Replying to mlennert:

Sorry, I had read your remark to quickly:

Replying to 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.

[mmetz]

Replying to 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.

[neteler]

Replying to mmetz:

Replying to 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.

Please someone test this on Windows.

[hellik]

Replying to mmetz:

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.

After intensive testing this with an actual winGRASS6.4.4svn closing the ticket as invalid as suggested.

[hamish]

The trouble of sqlite blocking the $MAPSET dir on Windows remains AFAICT, since the pragma journal mode noted in comment:6 (or another fix) was never implemented.

I don't see where it was said that the ticket is invalid, for GRASS 6 the default location of sqlite.db remains $MAPSET. I think mmetz was talking about the (new) default dir in GRASS 7. Suggest to reopen.

Hamish