[Bug] db.tables module doesn't see vector map table if table was created with ODBC DB connection

[tmszi]

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:

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

2. Extract archive with cd /tmp/shp && unzip hybas_lake_eu_lev01-12_v1c.zip
3. Create new location (project) according shapefile grass -c /tmp/shp/hybas_lake_eu_lev01_v1c.shp hybas
4. 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

6. 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       = 

7. Create DB backend new DB with test name and new user with test name
8. 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            =

9. Check the ODBC DB connection

$ isql -w test_mysql test
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| echo [string]                         |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> 

10. Set GRASS GIS DB ODBC connection

db.connect driver=odbc database=test_mysql

11. Set GRASS GIS DB ODBC connection login

db.login driver=odbc database=test_mysql user=test password=test

12. Try import some shapefile with

 v.in.ogr input=/tmp/shp/hybas_lake_eu_lev01_v1c.shp output=hybas_lake_eu_lev01_v1

13. 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:                                                                 |
 |                                                                            |
 +----------------------------------------------------------------------------+

13. Check the imported vector map table with db.tables -p module (table is not founded)

GRASS hybas/PERMANENT:~ > db.tables -p
No tables found

15. Check the imported vector map table with MySQL DB mysql cli 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)

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