[GRASSLIST:9425] DBMI errors with MySQL tables

GRASS GRASS user
,
1

Greetings,

Lately I have been having a lot of DBMI-related errors when working with
GRASS{6,6.1-CVS} vectors that have a MySQL table attached to them.

For example, I have a table that looks like this in MySQL:
gg_bulk_density_points;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| cat | int(11) | YES | | NULL | |
| easting | int(11) | YES | | NULL | |
| northing | int(11) | YES | | NULL | |
| id | varchar(10) | YES | | NULL | |
| pedon_id | varchar(20) | YES | | NULL | |
| the_date | varchar(12) | YES | | NULL | |
| the_time | varchar(6) | YES | | NULL | |
| db_ring_hz1 | float | YES | | NULL | |
| db_ring_hz2 | float | YES | | NULL | |
| db_ring_hz3 | float | YES | | NULL | |
| db_ring_hz4 | float | YES | | NULL | |
| clay_capacity | float | YES | | NULL | |
| dominant_cover | varchar(20) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+

I have connected this table to a vector in GRASS{6,6.1-CVS} like this:
v.db.connect -o map=gg_bulk_density_points driver=mysql
table=gg_bulk_density_points database="host=localhost,dbname=test" key=cat

Running db.columns returns:
db.columns table=gg_bulk_density_points;
cat
easting
northing
id
pedon_id
the_date
the_time
db_ring_hz1
db_ring_hz2
db_ring_hz3
db_ring_hz4
clay_capacity
dominant_cover

Running db.select on this table gives:
echo "select * from gg_bulk_density_points" | db.select
table=gg_bulk_density_points
dbmi: Protocol error

Running db.select on any other vector in this location, with a MySQL table
attached to it works fine...

Also: Trying to add this trouble vector+table in QGIS causes QGIS to die with
"DBMI error"

Any Ideas, as this is really starting to drive me nuts!

Could it possibly be a column type issue?

--
Dylan Beaudette
Soils and Biogeochemistry Graduate Group
University of California at Davis
530.754.7341

2

Ok , it looks like i have narrowed the problem down a bit:

GRASS6.1-CVS (today) + MySQL 4.1.15 server + MySQL 4.1.15 Client includes

If a table in MySQL connected to a GRASS vector has NULL values in ANY of the
columns, GRASS dies with a DBMI error.

I am pretty sure that this used to work. Were there any changes in the MySQL
API from version 4.0 -> 4.1 that may have caused things to break ?

It looks like this behavior was noted in BUG #3337:
http://intevation.de/rt/webrt?serial_num=3337&display=History

It would be a great help for any ideas on where to start hunting for this
bug...

Thanks in advance!

Dylan

On Monday 12 December 2005 12:30 pm, Dylan Beaudette wrote:

Greetings,

Lately I have been having a lot of DBMI-related errors when working with
GRASS{6,6.1-CVS} vectors that have a MySQL table attached to them.

For example, I have a table that looks like this in MySQL:
gg_bulk_density_points;
+----------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------------+-------------+------+-----+---------+-------+

| cat | int(11) | YES | | NULL | |
| easting | int(11) | YES | | NULL | |
| northing | int(11) | YES | | NULL | |
| id | varchar(10) | YES | | NULL | |
| pedon_id | varchar(20) | YES | | NULL | |
| the_date | varchar(12) | YES | | NULL | |
| the_time | varchar(6) | YES | | NULL | |
| db_ring_hz1 | float | YES | | NULL | |
| db_ring_hz2 | float | YES | | NULL | |
| db_ring_hz3 | float | YES | | NULL | |
| db_ring_hz4 | float | YES | | NULL | |
| clay_capacity | float | YES | | NULL | |
| dominant_cover | varchar(20) | YES | | NULL | |

+----------------+-------------+------+-----+---------+-------+

I have connected this table to a vector in GRASS{6,6.1-CVS} like this:
v.db.connect -o map=gg_bulk_density_points driver=mysql
table=gg_bulk_density_points database="host=localhost,dbname=test" key=cat

Running db.columns returns:
db.columns table=gg_bulk_density_points;
cat
easting
northing
id
pedon_id
the_date
the_time
db_ring_hz1
db_ring_hz2
db_ring_hz3
db_ring_hz4
clay_capacity
dominant_cover

Running db.select on this table gives:
echo "select * from gg_bulk_density_points" | db.select
table=gg_bulk_density_points
dbmi: Protocol error

Running db.select on any other vector in this location, with a MySQL table
attached to it works fine...

Also: Trying to add this trouble vector+table in QGIS causes QGIS to die
with "DBMI error"

Any Ideas, as this is really starting to drive me nuts!

Could it possibly be a column type issue?

--
Dylan Beaudette
Soils and Biogeochemistry Graduate Group
University of California at Davis
530.754.7341

3

On Monday 12 December 2005 04:30 pm, Hamish wrote:

On Mon, 12 Dec 2005 15:40:25 -0800

Dylan Beaudette <dylan.beaudette@gmail.com> wrote:
> On Monday 12 December 2005 02:50 pm, Hamish wrote:
> > > | db_ring_hz1 | float | YES | | NULL | |
> > > | db_ring_hz2 | float | YES | | NULL | |
> > > | db_ring_hz3 | float | YES | | NULL | |
> > > | db_ring_hz4 | float | YES | | NULL | |
> >
> > DBF has a 10 char limit to names. Maybe a test for this this is
> > polluting the mysql driver somehow? try making the above four
> > different in the first 10 chars?
>
> Thanks for the quick feedback Hamish. I am not sure that I understand
> what you are suggesting... Do you mean that 10+ character names in
> MySQL are causing the problems? I don't think this is the case, as
> when i replace my NULL values with empty strings or '0' everything
> works fine- even with the long names.
>
> is that what you meant?

Yea, it probably isn't that, but I mentioned it as I noticed in those
four the first 10 chars are not unique. I know that shouldn't affect it,
but you never know...

Probably the NULL handling has to be followed in a debugger to see where
it goes wrong. Have you run GRASS with `g.gisenv set="DEBUG=5"` to
narrow down when the DBMI error happens?

Hamish

Thanks for the tips. Here are the results when I up' the debug level:

g.gisenv set="DEBUG=5"
echo "select *" | db.select table=db_null_test

D2/5: opendir /usr/local/grass-6.1.cvs/driver/db/

D2/5: opendir /usr/local/grass-6.1.cvs/driver/db/

D3/5: db_driver_open_database() driver = mysql database definition =
'host=localhost,dbname=test'
D3/5: parse_conn : host=localhost,dbname=test
D3/5: token 0 : host=localhost
D3/5: token 1 : dbname=test
D3/5: host = localhost, port = 0, socket = (null), dbname = test, user =
(null), password = (null)
D3/5: db_get_login(): mysql host=localhost,dbname=test
D3/5: file = /home/dylan/.grasslogin6
D3/5: ret = 4 : mysql host=localhost,dbname=test xxx xxx
D3/5: ret = 4 : mysql test@localhost xxx xxx
D3/5: load_table()
D3/5: load_table() - not loaded
D3/5: load_table() - number of cols is 1
D3/5: load_table() - number of cols is 2
dbmi: Protocol error

Looks like something is breaking in the load_table() function ?

Hmmmmm...

Poking around in this source code for CVS, the following matches look
interesting:

grep -n -R "load_table()" .

./db/drivers/mysql/table.c:113: G_debug(3, "load_table()");
./db/drivers/mysql/table.c:117: G_debug(3, "load_table() - not loaded");
./db/drivers/mysql/table.c:193: G_debug(3, "load_table() - number of cols is
%d", db.tables[t].ncols);

Not sure how to interpret these debug messages...

Further ideas? maybe we are getting closer...?

--
Dylan Beaudette
Soils and Biogeochemistry Graduate Group
University of California at Davis
530.754.7341

4

On 12/13/05, Dylan Beaudette <dylan.beaudette@gmail.com> wrote:

./db/drivers/mysql/table.c:113: G_debug(3, "load_table()");
./db/drivers/mysql/table.c:117: G_debug(3, "load_table() - not loaded");
./db/drivers/mysql/table.c:193: G_debug(3, "load_table() - number of cols is
%d", db.tables[t].ncols);

Not sure how to interpret these debug messages...

Further ideas? maybe we are getting closer...?

The mysql driver is a quick hack which was written by somebody as
copy-and-paste from dbf which is completely wrong (loading tables
to memery instead of mysql_fetch_field_direct fro each db_fetch etc.)
+ practicaly it is not maintained few years.
It should be completely rewritten.

Radim

5

On Tuesday 13 December 2005 12:15 am, Radim Blazek wrote:

On 12/13/05, Dylan Beaudette <dylan.beaudette@gmail.com> wrote:
> ./db/drivers/mysql/table.c:113: G_debug(3, "load_table()");
> ./db/drivers/mysql/table.c:117: G_debug(3, "load_table() - not
> loaded"); ./db/drivers/mysql/table.c:193: G_debug(3, "load_table() -
> number of cols is %d", db.tables[t].ncols);
>
> Not sure how to interpret these debug messages...
>
> Further ideas? maybe we are getting closer...?

The mysql driver is a quick hack which was written by somebody as
copy-and-paste from dbf which is completely wrong (loading tables
to memery instead of mysql_fetch_field_direct fro each db_fetch etc.)
+ practicaly it is not maintained few years.
It should be completely rewritten.

Radim

Radim,

Thank you for the information on this. Any ideas on what it would take to get
a better functioning MySQL driver? I am not sure that I would have the time
or technical ability to do it myself- but I would be quite willing to help
out on the development of a more robust driver. I use MySQL for all of my
research, and if the interface to GRASS is less than stellar - then I need to
make some choices. I have a rather complete reference for the MySQL 4.1 C API
that might help out.

From my perspective it is quite a daunting task do try and accomplish on my

own.

Anyone else interested in helping?

--
Dylan Beaudette
Soils and Biogeochemistry Graduate Group
University of California at Davis
530.754.7341

6

Radim Blazek wrote:

On 12/13/05, Dylan Beaudette <dylan.beaudette@gmail.com> wrote:
> ./db/drivers/mysql/table.c:113: G_debug(3, "load_table()");
> ./db/drivers/mysql/table.c:117: G_debug(3, "load_table() - not loaded");
> ./db/drivers/mysql/table.c:193: G_debug(3, "load_table() - number of cols is
> %d", db.tables[t].ncols);
>
> Not sure how to interpret these debug messages...
>
> Further ideas? maybe we are getting closer...?

The mysql driver is a quick hack which was written by somebody as
copy-and-paste from dbf which is completely wrong (loading tables
to memery instead of mysql_fetch_field_direct fro each db_fetch etc.)
+ practicaly it is not maintained few years.
It should be completely rewritten.

Presumably you can access MySQL databases using the ODBC driver?

--
Glynn Clements <glynn@gclements.plus.com>

7

On Tuesday 13 December 2005 03:56 pm, Glynn Clements wrote:

Radim Blazek wrote:
> On 12/13/05, Dylan Beaudette <dylan.beaudette@gmail.com> wrote:
> > ./db/drivers/mysql/table.c:113: G_debug(3, "load_table()");
> > ./db/drivers/mysql/table.c:117: G_debug(3, "load_table() - not
> > loaded"); ./db/drivers/mysql/table.c:193: G_debug(3, "load_table() -
> > number of cols is %d", db.tables[t].ncols);
> >
> > Not sure how to interpret these debug messages...
> >
> > Further ideas? maybe we are getting closer...?
>
> The mysql driver is a quick hack which was written by somebody as
> copy-and-paste from dbf which is completely wrong (loading tables
> to memery instead of mysql_fetch_field_direct fro each db_fetch etc.)
> + practicaly it is not maintained few years.
> It should be completely rewritten.

Presumably you can access MySQL databases using the ODBC driver?

Indeed. I hadn't thought about this. Does anyone have an idea as to how robust
the ODBC driver is when connecting to MySQL ?

Thanks!

--
Dylan Beaudette
Soils and Biogeochemistry Graduate Group
University of California at Davis
530.754.7341