grass7 - v.reclass with sqlite driver: "Cannot step: SQL logic error or missing database"

[mlennert]

With today's devel7 trunk, v.reclass segfaults when trying to use a string column as reclass criterium. Using nc_spm_06 demo data:

v.reclass in=boundary_county out=test_recl column=DOT_GROUP_

segfaults, whereas

v.reclass in=boundary_county out=test_recl2 column=DOT_COUNTY

works as expected (DOT_COUNTY is integer).

In grass65svn, both commands work as expected.

This also obviously makes v.dissolve fail on such columns.

Backtrace attached.

Moritz

[mlennert]

gdb backtrace

[neteler]

For me both commands work (I did: svn up ; make distclean).

Markus

[mlennert]

Replying to neteler:

For me both commands work

Using grass7 and the above example from the nc_spm_06 data set ?

(I did: svn up ; make distclean).

Same here...

I'll have to see what's going on locally, then.

Moritz

[martinl]

Replying to mlennert:

With today's devel7 trunk, v.reclass segfaults when trying to use a string column as reclass criterium. Using nc_spm_06 demo data:

v.reclass in=boundary_county out=test_recl column=DOT_GROUP_

segfaults, whereas

v.reclass in=boundary_county out=test_recl2 column=DOT_COUNTY

works as expected (DOT_COUNTY is integer).

In grass65svn, both commands work as expected.

This also obviously makes v.dissolve fail on such columns.

Works fine with DBF driver. When reclassing data using string column you get with SQLite driver know error.

DBMI-SQLite driver error:
Cannot step:
SQL logic error or missing database

No segfault here.

[neteler]

For me even that works:

GRASS 7.0.svn (nc_spm_08):~/grassdata/nc_spm_08/neteler > v.reclass in=boundary_county out=test_recl column=DOT_GROUP_
 100%
Building topology for vector map <test_recl>...
Registering primitives...
2836 primitives registered
268782 vertices registered
Building areas...
 100%
926 areas built
130 isles built
Attaching islands...
 100%
Attaching centroids...
 100%
Number of nodes: 2040
Number of primitives: 2836
Number of points: 0
Number of lines: 0
Number of boundaries: 1910
Number of centroids: 926
Number of areas: 926
Number of isles: 130
v.reclass complete. 926 features reclassed.

GRASS 7.0.svn (nc_spm_08):~/grassdata/nc_spm_08/neteler > db.connect -p
driver:sqlite
database:$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db
schema:
group:

[martinl]

Replying to neteler:

For me even that works:

GRASS 7.0.svn (nc_spm_08):~/grassdata/nc_spm_08/neteler > v.reclass in=boundary_county out=test_recl column=DOT_GROUP_
GRASS 7.0.svn (nc_spm_08):~/grassdata/nc_spm_08/neteler > db.connect -p
driver:sqlite
database:$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db
schema:
group:

v.db.connect boundary_county -g

?

I suppose DBF driver. Then it works... The key is the driver used for input vector map.

Martin

[neteler]

After the fix in the spatial index I can reproduce the problem with SQLite:

g.gisenv set=DEBUG=3
v.reclass in=boundary_county out=test_recl column=DOT_GROUP_ --o
...
db_driver_open_database()
D3/3: name = '/home/neteler/grassdata/nc_spm_08/sqlite/sqlite.db'
D2/3: name2 = '/home/neteler/grassdata/nc_spm_08/sqlite/sqlite.db'
D3/3:   SQL: SELECT cat, DOT_GROUP_ FROM boundary_county ORDER BY DOT_GROUP_
D3/3: Escaped SQL: SELECT cat, DOT_GROUP_ FROM boundary_county ORDER BY DOT_GROUP_
D3/3: describe_table()
D3/3: ncols = 2
D3/3: sqltype = 3
D3/3: litetype = 1
D3/3: sqltype = 1
D3/3: litetype = 3
D3/3: nkcols = 2
D3/3: sqltype = 3
D3/3: litetype = 1
D2/3: col: cat, nkcols 0, litetype : 1, sqltype 3
D3/3: sqltype = 1
D3/3: litetype = 3
D2/3: col: DOT_GROUP_, nkcols 1, litetype : 3, sqltype 1
D3/3:   926 rows selected
D3/3:   key type = 15
D3/3: execute: create table test_recl (cat integer, DOT_GROUP_ varchar(20))
D3/3: db__create_index()
D3/3:  SQL: create unique index test_recl_cat on test_recl ( cat )
D3/3: fetch row = -1
DBMI-SQLite driver error:
Cannot step:
SQL logic error or missing database

ERROR: Unable to fetch data from table <boundary_county>

No mention on ​http://www.sqlite.org/omitted.html, so I don't know why this statement is rejected.

Markus

[mlennert]

Replying to neteler:

After the fix in the spatial index I can reproduce the problem with SQLite:

I confirm: no more segfault, don't where that came from, but same error as Markus and Martin. Changing title of bug

[wilsonadam]

I can confirm this in GRASS 6.4.0RC5 (2009) with a string field (using my data, but it does the same thing).

v.reclass in=pgrid out=pgrid2 type=centroid column=gridID

DBMI-SQLite driver error:
Cannot step:
SQL logic error or missing database

ERROR: Unable to fetch data from table <pgrid>

[neteler]

Still failing in current GRASS 7 SVN:

GRASS 7.0.svn (nc_spm_08@grass70):~ > v.reclass in=myboundary_county out=test_recl column=DOT_GROUP_
DBMI-SQLite driver error:
Cannot step:
SQL logic error or missing database

ERROR: Unable to fetch data from table <myboundary_county>
GRASS 7.0.svn (nc_spm_08@grass70):~ > v.db.connect -p myboundary_county
Vector map <myboundary_county> is connected by:
layer <1/boundary_county> table <myboundary_county> in database </home/neteler/grassdata/nc_spm_08/grass70/sqlite.db> through driver <sqlite> with key <cat>

It likely affects #1418 (v.dissolve failure) which suppresses error messages from v.reclass.

[mmetz]

Replying to neteler:

Still failing in current GRASS 7 SVN:

and the two other branches.

It likely affects #1418 (v.dissolve failure) which suppresses error messages from v.reclass.

Debugging a bit, the problem of v.reclass with the sqlite driver seems to be that the same database is opened twice (once by each driver instance), which causes database locking, therefore it is not possible to use one driver instance to fetch records from one table and use another driver instance to insert records into another table, all in the same sqlite database.

BTW, there is a test in lib/db if a database is already opened when opening a database, this test is called quite often but it never complains.

Markus M

[mmetz]

Replying to mmetz:

Debugging a bit, the problem of v.reclass with the sqlite driver seems to be that the same database is opened twice (once by each driver instance), which causes database locking, therefore it is not possible to use one driver instance to fetch records from one table and use another driver instance to insert records into another table, all in the same sqlite database.

Now it's possible, the other driver just has to wait a little bit until the first driver is finished.

Fixed in r45156, r48157, r48158 (7.0, 6.5, 6.4.2, respectively).

Markus M

[mmetz]

Replying to mmetz:

Replying to mmetz:

Debugging a bit, the problem of v.reclass with the sqlite driver seems to be that the same database is opened twice (once by each driver instance), which causes database locking, therefore it is not possible to use one driver instance to fetch records from one table and use another driver instance to insert records into another table, all in the same sqlite database.

Now it's possible, the other driver just has to wait a little bit until the first driver is finished.

Correction, only now (trunk r48333) it's possible. There were more bugs, both in v.reclass and in the sqlite driver.

A test, within the sqlite mapset of nc_spm_08:

g.copy vect=zipcodes_wake,my_zipcodes_wake
v.dissolve input=my_zipcodes_wake@sqlite layer=1 column=ZIPNAME output=zip_names

Markus M

[neteler]

Backport of trunk r48333 to GRASS 6.5 in r51261. Can I backport to 6.4 as well? The zipcodes_wake example in comment 11 succeeds.

[neteler]

Backported to 6.4 in r52912 by mmetz. Closing.

[mmetz]

Replying to neteler:

Backported to 6.4 in r52912 by mmetz. Closing.

That was only the sqlite driver. v.reclass itself is now fixed in r52955.