Context Navigation

← Previous Ticket
Next Ticket →

#3919 new defect

db.dropcolumn and v.db.dropcolumn do not drop columns

Reported by:	veroandreo	Owned by:	grass-dev@…
Priority:	normal	Milestone:	7.8.3
Component:	Database	Version:	git-releasebranch78
Keywords:	db.dropcolumn, v.db.dropcolumn	Cc:
CPU:	x86-64	Platform:	Linux

Description

When trying to drop one (or many) columns from the attribute table of a vector map using G7:v.db.dropcolumn and G7:db.dropcolumn, I get the following error and the column(s) are not removed:

v.db.dropcolumn map=segs_stats_map column=aspect_5m_cba_min
DBMI-SQLite driver error:
Error in sqlite3_prepare():
incomplete input

DBMI-SQLite driver error:
Error in sqlite3_prepare():
incomplete input

ERROR: Error while executing: 'CREATE TEMPORARY TABLE segs_stats_map_backup
       (cat INTEGER, IGN_Cba_02m_blue_min DOUBLE PRECISION,
       IGN_Cba_02m_blue_max DOUBLE PRECISION, IGN_Cba_02m_blue_range DOUBLE
       PRECISION, IGN_Cba_02m_blue_mean DOUBLE PRECISION,
       IGN_Cba_02m_blue_stddev DOUBLE PRECISION, IGN_Cba_02m_blue_median
       DOUBLE PRECISION, IGN_Cba_02m_blue_first_quart DOUBLE PRECISION,
       IGN_Cba_02m_blue_third_quart DOUBLE PRECISION,
       IGN_Cba_02m_blue_perc_90
       ...
       IGN_Cba_02m_ndvi_median_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_median_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_first_quart_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_first_quart_nbrstddev DOUBLE PRE'
ERROR: Deleting column failed


db.dropcolumn -f table=segs_stats_map column=aspect_5m_cba_min
DB settings already defined, nothing to do
Forcing ...
DBMI-SQLite driver error:
Error in sqlite3_prepare():
incomplete input

DBMI-SQLite driver error:
Error in sqlite3_prepare():
incomplete input

ERROR: Error while executing: 'CREATE TEMPORARY TABLE
       segs_stats_map_backup(cat INTEGER, IGN_Cba_02m_blue_min DOUBLE
       PRECISION, IGN_Cba_02m_blue_max DOUBLE PRECISION,
       IGN_Cba_02m_blue_range DOUBLE PRECISION,
       ...
       IGN_Cba_02m_ndvi_first_quart_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_first_quart_nbrstddev DOUBLE PREC'
ERROR: Cannot continue (problem deleting column)

According to the manual, one only needs to pass the table or vector name and the column name(s). What else can be missing?

I checked with G7:v.connect and the table is connected to the vector. The vector is the output of i.segment.stats.

I tried both in 78 branch and dev, and I get the same error.

Change History (9)

comment:1 by veroandreo, 5 years ago

Update: I just tried with a smaller vector in NC location and it works as expected... The vector I was working with before is 407 columns by ~470000 rows... can that be the reason?

comment:2 by mlennert, 5 years ago

From the error message, it looks like an issue with the length of the SQL statement creating the temporary table (necessary since SQLite does not implement ALTER TABLE DROP COLUMN). But AFAICT, SQLite's query size limit is pretty big (1 000 000 bytes by default).

Could you try by altering your local copy to print the column definition:

coltypes = ", ".join(coltypes)
+print(coltypes)

That would allow to run an SQL statement directly in SQLite to test whether this works.

If this is the issue, we could test for the length of the column definition and cut it into several parts if necessary using ALTER TABLE ADD COLUMN with parts 2+.

comment:3 by veroandreo, 5 years ago

So, I added the print line locally... It seems that the SQL statement is too long indeed. Here is the output:

v.db.dropcolumn map=segs_stats_map column=dem_5m_cba_min_nbrmean
DBMI-SQLite driver error:
Error in sqlite3_prepare():
incomplete input

DBMI-SQLite driver error:
Error in sqlite3_prepare():
incomplete input

ERROR: Error while executing: 'CREATE TEMPORARY TABLE segs_stats_map_backup
       (cat INTEGER, IGN_Cba_02m_blue_min DOUBLE PRECISION,
       IGN_Cba_02m_blue_max DOUBLE PRECISION, IGN_Cba_02m_blue_range DOUBLE
       PRECISION, IGN_Cba_02m_blue_mean DOUBLE PRECISION,
       IGN_Cba_02m_blue_stddev DOUBLE PRECISION, IGN_Cba_02m_blue_median
       DOUBLE PRECISION, IGN_Cba_02m_blue_first_quart DOUBLE PRECISION,
       IGN_Cba_02m_blue_third_quart DOUBLE PRECISION,
       IGN_Cba_02m_blue_perc_90 DOUBLE PRECISION, IGN_Cba_02m_green_min
       DOUBLE PRECISION, IGN_Cba_02m_green_max DOUBLE PRECISION,
       IGN_Cba_02m_green_range DOUBLE PRECISION, IGN_Cba_02m_green_mean
       DOUBLE PRECISION, IGN_Cba_02m_green_stddev DOUBLE PRECISION,
       IGN_Cba_02m_green_median DOUBLE PRECISION,
       IGN_Cba_02m_green_first_quart DOUBLE PRECISION,
       IGN_Cba_02m_green_third_quart DOUBLE PRECISION,
       IGN_Cba_02m_green_perc_90 DOUBLE PRECISION, IGN_Cba_02m_ndvi_min
       DOUBLE PRECISION, IGN_Cba_02m_ndvi_max DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_range DOUBLE PRECISION, IGN_Cba_02m_ndvi_mean
       DOUBLE PRECISION, IGN_Cba_02m_ndvi_stddev DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_median DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_first_quart DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_third_quart DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_perc_90 DOUBLE PRECISION, IGN_Cba_02m_ndwi_min
       DOUBLE PRECISION, IGN_Cba_02m_ndwi_max DOUBLE PRECISION,
       IGN_Cba_02m_ndwi_range DOUBLE PRECISION, IGN_Cba_02m_ndwi_mean
       DOUBLE PRECISION, IGN_Cba_02m_ndwi_stddev DOUBLE PRECISION,
       IGN_Cba_02m_ndwi_median DOUBLE PRECISION,
       IGN_Cba_02m_ndwi_first_quart DOUBLE PRECISION,
       IGN_Cba_02m_ndwi_third_quart DOUBLE PRECISION,
       IGN_Cba_02m_ndwi_perc_90 DOUBLE PRECISION, IGN_Cba_02m_nir_min
       DOUBLE PRECISION, IGN_Cba_02m_nir_max DOUBLE PRECISION,
       IGN_Cba_02m_nir_range DOUBLE PRECISION, IGN_Cba_02m_nir_mean DOUBLE
       PRECISION, IGN_Cba_02m_nir_stddev DOUBLE PRECISION,
       IGN_Cba_02m_nir_median DOUBLE PRECISION, IGN_Cba_02m_nir_first_quart
       DOUBLE PRECISION, IGN_Cba_02m_nir_third_quart DOUBLE PRECISION,
       IGN_Cba_02m_nir_perc_90 DOUBLE PRECISION, IGN_Cba_02m_pan_min DOUBLE
       PRECISION, IGN_Cba_02m_pan_max DOUBLE PRECISION,
       IGN_Cba_02m_pan_range DOUBLE PRECISION, IGN_Cba_02m_pan_mean DOUBLE
       PRECISION, IGN_Cba_02m_pan_stddev DOUBLE PRECISION,
       IGN_Cba_02m_pan_median DOUBLE PRECISION, IGN_Cba_02m_pan_first_quart
       DOUBLE PRECISION, IGN_Cba_02m_pan_third_quart DOUBLE PRECISION,
       IGN_Cba_02m_pan_perc_90 DOUBLE PRECISION, IGN_Cba_02m_pan_11_ASM_min
       DOUBLE PRECISION, IGN_Cba_02m_pan_11_ASM_max DOUBLE PRECISION,
       IGN_Cba_02m_pan_11_ASM_range DOUBLE PRECISION,
       IGN_Cba_02m_pan_11_ASM_mean DOUBLE PRECISION,
       IGN_Cba_02m_pan_11_ASM_stddev DOUBLE PRECISION,
       IGN_Cba_02m_pan_11_ASM_median DOUBLE PRECISION,
       IGN_Cba_02m_pan_11_ASM_first_quart DOUBLE PRECISION,
       IGN_Cba_02m_pan_11_ASM_third_quart DOUBLE PRECISION,
       IGN_Cba_02m_pan_11_ASM_perc_90 DOUBLE PRECISION,
       IGN_Cba_02m_pan_11_IDM_min DOUBLE PRECISION,
       IGN_Cba_02m_pan_11_IDM_max DOUBLE PRECISION,
       IGN_Cba_02m_pan_11_IDM_range DOUBLE PRECISION,
       IGN_Cba_02m_pan_11_IDM_mean DOUBLE PRECISION,
       IGN_Cba_02m_pan_11_IDM_stddev DOUBLE PRECISION,
       IGN_Cba_02m_pan_11_IDM_median DOUBLE PRECISION,
       IGN_Cba_02m_pan_11_IDM_first_quart DOUBLE PRECISION,
       IGN_Cba_02m_pan_11_IDM_third_quart DOUBLE PRECISION,
       IGN_Cba_02m_pan_11_IDM_perc_90 DOUBLE PRECISION, IGN_Cba_02m_red_min
       DOUBLE PRECISION, IGN_Cba_02m_red_max DOUBLE PRECISION,
       IGN_Cba_02m_red_range DOUBLE PRECISION, IGN_Cba_02m_red_mean DOUBLE
       PRECISION, IGN_Cba_02m_red_stddev DOUBLE PRECISION,
       IGN_Cba_02m_red_median DOUBLE PRECISION, IGN_Cba_02m_red_first_quart
       DOUBLE PRECISION, IGN_Cba_02m_red_third_quart DOUBLE PRECISION,
       IGN_Cba_02m_red_perc_90 DOUBLE PRECISION, IGN_Cba_02m_ssi_min DOUBLE
       PRECISION, IGN_Cba_02m_ssi_max DOUBLE PRECISION,
       IGN_Cba_02m_ssi_range DOUBLE PRECISION, IGN_Cba_02m_ssi_mean DOUBLE
       PRECISION, IGN_Cba_02m_ssi_stddev DOUBLE PRECISION,
       IGN_Cba_02m_ssi_median DOUBLE PRECISION, IGN_Cba_02m_ssi_first_quart
       DOUBLE PRECISION, IGN_Cba_02m_ssi_third_quart DOUBLE PRECISION,
       IGN_Cba_02m_ssi_perc_90 DOUBLE PRECISION, dem_5m_cba_min DOUBLE
       PRECISION, dem_5m_cba_max DOUBLE PRECISION, dem_5m_cba_range DOUBLE
       PRECISION, dem_5m_cba_mean DOUBLE PRECISION, dem_5m_cba_stddev
       DOUBLE PRECISION, dem_5m_cba_median DOUBLE PRECISION,
       dem_5m_cba_first_quart DOUBLE PRECISION, dem_5m_cba_third_quart
       DOUBLE PRECISION, dem_5m_cba_perc_90 DOUBLE PRECISION,
       twi_5m_cba_min DOUBLE PRECISION, twi_5m_cba_max DOUBLE PRECISION,
       twi_5m_cba_range DOUBLE PRECISION, twi_5m_cba_mean DOUBLE PRECISION,
       twi_5m_cba_stddev DOUBLE PRECISION, twi_5m_cba_median DOUBLE
       PRECISION, twi_5m_cba_first_quart DOUBLE PRECISION,
       twi_5m_cba_third_quart DOUBLE PRECISION, twi_5m_cba_perc_90 DOUBLE
       PRECISION, neighbors_count DOUBLE PRECISION,
       IGN_Cba_02m_blue_min_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_blue_min_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_blue_max_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_blue_max_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_blue_range_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_blue_range_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_blue_mean_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_blue_mean_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_blue_stddev_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_blue_stddev_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_blue_median_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_blue_median_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_blue_first_quart_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_blue_first_quart_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_blue_third_quart_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_blue_third_quart_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_blue_perc_90_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_blue_perc_90_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_green_min_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_green_min_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_green_max_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_green_max_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_green_range_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_green_range_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_green_mean_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_green_mean_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_green_stddev_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_green_stddev_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_green_median_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_green_median_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_green_first_quart_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_green_first_quart_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_green_third_quart_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_green_third_quart_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_green_perc_90_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_green_perc_90_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_min_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_min_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_max_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_max_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_range_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_range_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_mean_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_mean_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_stddev_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_stddev_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_median_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_median_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_first_quart_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_first_quart_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_third_quart_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_third_quart_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_perc_90_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_ndvi_perc_90_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_ndwi_min_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_ndwi_min_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_ndwi_max_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_ndwi_max_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_ndwi_range_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_ndwi_range_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_ndwi_mean_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_ndwi_mean_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_ndwi_stddev_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_ndwi_stddev_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_ndwi_median_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_ndwi_median_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_ndwi_first_quart_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_ndwi_first_quart_nbrstddev DOUBLE PRECISION,
       IGN_Cba_02m_ndwi_third_quart_nbrmean DOUBLE PRECISION,
       IGN_Cba_02m_ndwi_thi'
ERROR: Deleting column failed

comment:4 by neteler, 5 years ago

Your statement longer than 8192 chars but

include/dbmi.h
142:#define DB_SQL_MAX      8192

So we need to increase this definition to address the problem with tables having a big number of columns with long names.

Can we just define more chars or are there any sideeffects?

The last increase was just done some months ago:

https://github.com/OSGeo/grass/commit/34fbd93db5f1e0e3cc78fa20eb04bc8c6434a9de

Last edited 5 years ago by neteler (previous) (diff)

comment:5 by veroandreo, 5 years ago

I tried changing both to 16384 and 32768, and compiling again, but still the same problem. It reaches the same place as above in the table. So, it seems the change does not have any effect. Any other place I should change?

comment:6 by neteler, 5 years ago

Milestone:	7.8.1 → 7.8.2

Ticket retargeted after milestone closed

comment:7 by neteler, 5 years ago

Milestone:	7.8.2

Ticket retargeted after milestone closed

comment:8 by neteler, 5 years ago

Milestone:	→ 7.8.3

comment:9 by wenzeslaus, 4 years ago

Not really migrated, but mostly continued in:

PR:1124 lib: Increase max size of SQL statements
https://github.com/OSGeo/grass/issues/1126 [Feat] db.execute: Detect that SQL statement is longer than DB_SQL_MAX

Note: See TracTickets for help on using tickets.

Download in other formats: