Open
Description
Describe the bug
db.tables module doesn't see vector map table if table was created with ODBC DB (tested with MySQL/PostgreSQL DB backend) connection.
To Reproduce
Steps to reproduce the behavior:
- Download shapefiles e.g.
mkdir /tmp/shp && cd /tmp/shp && wget https://data.hydrosheds.org/file/hydrobasins/customized_with_lakes/hybas_lake_eu_lev01-12_v1c.zip
- Extract archive with
cd /tmp/shp && unzip hybas_lake_eu_lev01-12_v1c.zip - Create new location (project) according shapefile
grass -c /tmp/shp/hybas_lake_eu_lev01_v1c.shp hybas - Set ODBC DB connection (MySQL, PostgreSQL DB backend) setting via unixODBC, under Gentoo GNU/Linux distro I have installed:
- dev-db/unixODBC,
- dev-db/postgresql,
- dev-db/psqlodbc,
- dev-db/mysql,
- dev-db/myodbc
- unixODBC DB driver settings
$ cat /etc/unixODBC/odbcinst.ini
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib64/psqlodbcw.so
Setup =
FileUsage =
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib64/myodbc-8.0/libmyodbc8w.so
Setup =
FileUsage =
- Create DB backend new DB with test name and new user with test name
- unixODBC DB connection settings
cat /etc/unixODBC/odbc.ini
[test_pg]
Description = PostgreSQL
Driver = PostgreSQL
Trace = No
TraceFile =
Database = test
Servername = localhost
UserName = test
Password = test
Port = 5432
Protocol =
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =
[test_mysql]
Description = MySQL
Driver = MySQL
Trace = No
TraceFile =
Database = test
Servername = localhost
UserName = test
Password = test
Port = 3306
Protocol =
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =
- Check the ODBC DB connection
$ isql -w test_mysql test
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| echo [string] |
| quit |
| |
+---------------------------------------+
SQL>
- Set GRASS GIS DB ODBC connection
db.connect driver=odbc database=test_mysql
- Set GRASS GIS DB ODBC connection login
db.login driver=odbc database=test_mysql user=test password=test
- Try import some shapefile with
v.in.ogr input=/tmp/shp/hybas_lake_eu_lev01_v1c.shp output=hybas_lake_eu_lev01_v1
- Check the imported vector map info
GRASS hybas/PERMANENT:~ > v.info hybas_lake_eu_lev01_v1
WARNING: Coor file of vector map <hybas_lake_eu_lev01_v1@PERMANENT> is
larger than it should be (18 bytes excess)
Topology not available for vector map <hybas_lake_eu_lev01_v1@PERMANENT>.
Registering primitives...
+----------------------------------------------------------------------------+
| Name: hybas_lake_eu_lev01_v1 |
| Mapset: PERMANENT |
| Project: hybas |
| Database: /home/test |
| Title: |
| Map scale: 1:1 |
| Name of creator: test |
| Organization: |
| Source date: Wed Apr 24 06:46:48 2024 |
| Timestamp (first layer): none |
|----------------------------------------------------------------------------|
| Map format: native |
|----------------------------------------------------------------------------|
| Type of map: vector (level: 1) |
| |
| Number of points: 0 Number of centroids: 0 |
| Number of lines: 0 Number of boundaries: 0 |
| Number of areas: 0 Number of islands: 0 |
| |
| Map is 3D: No |
| Number of dblinks: 1 |
| |
| Projection: Latitude-Longitude |
| |
| N: 0 S: 0 |
| E: 0 W: -2147483648:00:-nanE |
| |
| Digitization threshold: 0 |
| Comment: |
| |
+----------------------------------------------------------------------------+
- Check the imported vector map table with
db.tables -pmodule (table is not founded)
GRASS hybas/PERMANENT:~ > db.tables -p
No tables found
- Check the imported vector map table with MySQL DB
mysqlcli program (table exists)
mysql> show tables;
+------------------------+
| Tables_in_test |
+------------------------+
| hybas_lake_eu_lev01_v1 |
+------------------------+
2 rows in set (0.01 sec)
mysql> describe hybas_lake_eu_lev01_v1;
+-----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| cat | int | YES | | NULL | |
| hybas_id | int | YES | | NULL | |
| next_down | int | YES | | NULL | |
| next_sink | int | YES | | NULL | |
| main_bas | int | YES | | NULL | |
| dist_sink | double | YES | | NULL | |
| dist_main | double | YES | | NULL | |
| sub_area | double | YES | | NULL | |
| up_area | double | YES | | NULL | |
| pfaf_id | int | YES | | NULL | |
| side | varchar(6) | YES | | NULL | |
| lake | int | YES | | NULL | |
| endo | int | YES | | NULL | |
| coast | int | YES | | NULL | |
| order | int | YES | | NULL | |
| sort | int | YES | | NULL | |
+-----------+------------+------+-----+---------+-------+
16 rows in set (0.01 sec)
mysql> SELECT * FROM hybas_lake_eu_lev01_v1;
+------+------------+-----------+------------+------------+-----------+-----------+------------+----------+---------+------+------+------+-------+-------+------+
| cat | hybas_id | next_down | next_sink | main_bas | dist_sink | dist_main | sub_area | up_area | pfaf_id | side | lake | endo | coast | order | sort |
+------+------------+-----------+------------+------------+-----------+-----------+------------+----------+---------+------+------+------+-------+-------+------+
| 1 | 2010000010 | 0 | 2010000010 | 2010000010 | 0 | 0 | 18236071.2 | 18236071 | 2 | M | 0 | 0 | 1 | 0 | 1 |
+------+------------+-----------+------------+------------+-----------+-----------+------------+----------+---------+------+------+------+-------+-------+------+
1 row in set (0.00 sec)
- Try import shapefile vector map again with overwrite flag and see error
GRASS hybas/PERMANENT:~ > v.in.ogr -w input=/tmp/shp/hybas_lake_eu_lev01_v1c.shp output=hybas_lake_eu_lev01_v1 --o
Check if OGR layer <hybas_lake_eu_lev01_v1c> contains polygons...
100%
WARNING: Vector map <hybas_lake_eu_lev01_v1> already exists and will be
overwritten
WARNING: Table <hybas_lake_eu_lev01_v1> linked to vector map
<hybas_lake_eu_lev01_v1> does not exist
Creating attribute table for layer <hybas_lake_eu_lev01_v1c>...
Column name <HYBAS_ID> renamed to <hybas_id>
WARNING: Writing column <hybas_id> with integer 64 as integer 32
Column name <NEXT_DOWN> renamed to <next_down>
WARNING: Writing column <next_down> with integer 64 as integer 32
Column name <NEXT_SINK> renamed to <next_sink>
WARNING: Writing column <next_sink> with integer 64 as integer 32
Column name <MAIN_BAS> renamed to <main_bas>
WARNING: Writing column <main_bas> with integer 64 as integer 32
Column name <DIST_SINK> renamed to <dist_sink>
Column name <DIST_MAIN> renamed to <dist_main>
Column name <SUB_AREA> renamed to <sub_area>
Column name <UP_AREA> renamed to <up_area>
Column name <PFAF_ID> renamed to <pfaf_id>
Column name <SIDE> renamed to <side>
Column name <LAKE> renamed to <lake>
Column name <ENDO> renamed to <endo>
Column name <COAST> renamed to <coast>
Column name <ORDER> renamed to <order>
Column name <SORT> renamed to <sort>
WARNING: Writing column <sort> with integer 64 as integer 32
DBMI-ODBC driver error:
SQLExecDirect():
create table hybas_lake_eu_lev01_v1 ("cat" integer, "hybas_id" integer, "next_down" integer, "next_sink" integer, "main_bas" integer, "dist_sink" double precision, "dist_main" double precision, "sub_area" double precision, "up_area" double precision, "pfaf_id" integer, "side" varchar ( 6 ), "lake" integer, "endo" integer, "coast" integer, "order" integer, "sort" integer)
(1050)
DBMI-ODBC driver error:
SQLExecDirect():
create table hybas_lake_eu_lev01_v1 ("cat" integer, "hybas_id" integer, "next_down" integer, "next_sink" integer, "main_bas" integer, "dist_sink" double precision, "dist_main" double precision, "sub_area" double precision, "up_area" double precision, "pfaf_id" integer, "side" varchar ( 6 ), "lake" integer, "endo" integer, "coast" integer, "order" integer, "sort" integer)
(1050)
^[[?1;2c^[[?1;2cERROR: Unable to create table: 'create table hybas_lake_eu_lev01_v1 ("cat"
integer, "hybas_id" integer, "next_down" integer, "next_sink"
integer, "main_bas" integer, "dist_sink" double precision,
"dist_main" double precision, "sub_area" double precision, "up_area"
double precision, "pfaf_id" integer, "side" varchar ( 6 ), "lake"
integer, "endo" integer, "coast" integer, "order" integer, "sort"
integer)'
Expected behavior
db.tables module should see vector map table if table was created with ODBC DB connection.
System description (please complete the following information):
- Operating System: all
- GRASS GIS version: all