[MDEV-7503] CONNECT table for mdb files Created: 2015-01-25  Updated: 2022-11-20  Resolved: 2022-11-20

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.0.15
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Takuya Aoki (Inactive) Assignee: Olivier Bertrand
Resolution: Cannot Reproduce Votes: 1
Labels: None
Environment:

CentOS release 6.5 (X86_64)

Attachments: File db1.mdb    

 Description   

Like in the below manual,
I want to make a CONNECT table which can read .mdb files.
I am using mdbtools which is the only free software I know to make a ODBC connection to .mdb files.
The CREATE TABLE command is going in an error at the moment.

https://mariadb.com/kb/en/mariadb/connect-table-types-odbc-table-type-accessing-tables-from-other-dbms/

Installing mdbtools

Download from
 
http://fossies.org/linux/privat/mdbtools-0.7.1.tar.gz/
 
 
 
su root
 
yum install gcc autoconf libtool automake flex bison glib2-devel txt2man
 
tar zxf mdbtools-0.7.1.tar.gz
 
cd mdbtools-0.7.1
 
./configure --with-unixodbc=/usr
 
make
 
make install
 
vi /etc/odbc.ini
 
###
 
[testmdb3]
 
Description = db1
 
Driver = /usr/local/lib/libmdbodbc.so
 
Database = /opt/dmi/dmscore/rawdata/db1.mdb
 
###
 
 

checking isql

[root@kc1060 mysql]# isql testmdb3
 
+---------------------------------------+
 
| Connected!                            |
 
|                                       |
 
| sql-statement                         |
 
| help [tablename]                      |
 
| quit                                  |
 
|                                       |
 
+---------------------------------------+
 
SQL> SELECT * FROM t_test
 
+------------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------+---------------------+
 
| id         | name                                                                                                | place                                                                                               | register_date       |
 
+------------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------+---------------------+
 
| 0          | Tom                                                                                                 | US                                                                                                  | 12/01/2014 12:00:00 |
 
| 1          | Sara                                                                                                | France                                                                                              | 01/02/2015 12:00:00 |
 
| 2          | Jef                                                                                                 | Poland                                                                                              | 01/15/2015 12:00:00 |
 
+------------+-----------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------+---------------------+
 
SQLRowCount returns 3
 
3 rows fetched

making a CONNECT table

[root@kc1060 mysql]# mysql -u root
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 2
 
Server version: 10.0.15-MariaDB MariaDB Server
 
 
 
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [(none)]> use test;
 
Reading table information for completion of table and column names
 
You can turn off this feature to get a quicker startup with -A
 
 
 
Database changed
 
MariaDB [test]> CREATE OR REPLACE TABLE `mdb_t_test_2` engine=CONNECT table_type=ODBC tabname='t_test' connection='DSN=testmdb3';
 
ERROR 2013 (HY000): Lost connection to MySQL server during query

record in log file(/var/lib/mysql/servername.err)

File not found
 
Unable to locate database /opt/dmi/dmscore/rawdata/db1.mdb
 
08003: [unixODBC][Driver Manager]Connnection does not exist, Native=0
 
terminate called after throwing an instance of 'DBX*'
 
CntCheckDB: dbuserp=0x7f8e864aa0c0
 
cat=0x7f8e864102c0 oldhandler=(nil) newhandler=0x7f8e8646c620
 
CntCheckDB: dbuserp=0x7f8e864aa0c0
 
cat=0x7f8e864102c0 oldhandler=(nil) newhandler=0x7f8e8646e420
 
CntCheckDB: dbuserp=0x7f8e864aa0c0
 
cat=0x7f8e864102c0 oldhandler=(nil) newhandler=0x7f8e86470220
 
CntCheckDB: dbuserp=0x7f8e864aa0c0
 
cat=0x7f8e864102c0 oldhandler=(nil) newhandler=0x7f8e86472020
 
CntCheckDB: dbuserp=0x7f8e864aa0c0
 
cat=0x7f8e864102c0 oldhandler=(nil) newhandler=0x7f8e86473e20
 
CntCheckDB: dbuserp=0x7f8e864aa0c0
 
cat=0x7f8e864102c0 oldhandler=(nil) newhandler=0x7f8e86475c20
 
CntCheckDB: dbuserp=0x7f8e864aa0c0
 
cat=0x7f8e864102c0 oldhandler=(nil) newhandler=0x7f8e86477a20
 
CntCheckDB: dbuserp=0x7f8e864aa0c0
 
cat=0x7f8e864102c0 oldhandler=(nil) newhandler=0x7f8e86479820
 
CntCheckDB: dbuserp=0x7f8e864aa0c0
 
cat=0x7f8e864102c0 oldhandler=(nil) newhandler=0x7f8e8647b620
 
CntCheckDB: dbuserp=0x7f8e864aa0c0
 
cat=0x7f8e864102c0 oldhandler=(nil) newhandler=0x7f8e8647fc20
 
150126 20:53:08 [ERROR] mysqld got signal 6 ;
 
This could be because you hit a bug. It is also possible that this binary
 
or one of the libraries it was linked against is corrupt, improperly built,
 
or misconfigured. This error can also be caused by malfunctioning hardware.
 
 
 
To report this bug, see http://kb.askmonty.org/en/reporting-bugs
 
 
 
We will try our best to scrape up some info that will hopefully help
 
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.
 
 
 
Server version: 10.0.15-MariaDB
 
key_buffer_size=4294967296
 
read_buffer_size=1073741824
 
max_used_connections=1
 
max_threads=153
 
thread_count=1
 
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 165882953 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x0x7f8e97eb2008
 
Attempting backtrace. You can use the following information to find out
 
where mysqld died. If you see no messages after this, something went
 
terribly wrong...
 
stack_bottom = 0x7f90a953ed30 thread_stack 0x48000
 
(my_addr_resolve failure: fork)
 
/usr/sbin/mysqld(my_print_stacktrace+0x2b) [0xb70d4b]
 
/usr/sbin/mysqld(handle_fatal_signal+0x398) [0x7257b8]
 
/lib64/libpthread.so.0() [0x352620f710]
 
/lib64/libc.so.6(gsignal+0x35) [0x3525e32625]
 
/lib64/libc.so.6(abort+0x175) [0x3525e33e05]
 
/usr/lib64/libstdc++.so.6(__gnu_cxx::__verbose_terminate_handler()+0x12d) [0x3530ebea7d]
 
/usr/lib64/libstdc++.so.6() [0x3530ebcbd6]
 
/usr/lib64/libstdc++.so.6() [0x3530ebcc03]
 
/usr/lib64/libstdc++.so.6() [0x3530ebcd22]
 
/usr/lib64/mysql/plugin/ha_connect.so(ODBConn::ThrowDBX(char*)+0) [0x7f8e981a9db0]
 
/usr/lib64/mysql/plugin/ha_connect.so(ODBConn::VerifyConnect()+0x9c) [0x7f8e981aa18c]
 
/usr/lib64/mysql/plugin/ha_connect.so(ODBConn::Open(char*, unsigned int)+0x3c) [0x7f8e981acfec]
 
/usr/lib64/mysql/plugin/ha_connect.so(ODBCColumns(_global*, char*, char*, char*, char*, int, bool)+0x210) [0x7f8e981adac0]
 
/usr/lib64/mysql/plugin/ha_connect.so(+0x767fb) [0x7f8e9813c7fb]
 
/usr/sbin/mysqld() [0x6511e9]
 
/usr/sbin/mysqld(mysql_create_table_no_lock(THD*, char const*, char const*, HA_CREATE_INFO*, Alter_info*, bool*, int)+0xef) [0x651a5f]
 
/usr/sbin/mysqld(mysql_create_table(THD*, TABLE_LIST*, HA_CREATE_INFO*, Alter_info*)+0x11d) [0x651c9d]
 
/usr/sbin/mysqld(mysql_execute_command(THD*)+0x5f5a) [0x5d711a]
 
/usr/sbin/mysqld() [0x5d79d2]
 
/usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x1b20) [0x5d9b90]
 
/usr/sbin/mysqld(do_handle_one_connection(THD*)+0x453) [0x6956a3]
 
/usr/sbin/mysqld(handle_one_connection+0x42) [0x695772]
 
/lib64/libpthread.so.0() [0x35262079d1]
 
/lib64/libc.so.6(clone+0x6d) [0x3525ee88fd]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7f8e86491020): is an invalid pointer
 
Connection ID (thread ID): 3
 
Status: NOT_KILLED
 
 
 
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on
 
 
 
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
 
information that should help you find out what is causing the crash.



 Comments   
Comment by Takuya Aoki (Inactive) [ 2015-01-26 ]

I also tested on 10.0.16 build-7290.
Other environments are the same.

[root@kc1060 mysql]# mysql -u root
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 3
 
Server version: 10.0.16-MariaDB MariaDB Server
 
 
 
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [(none)]> use test;
 
Reading table information for completion of table and column names
 
You can turn off this feature to get a quicker startup with -A
 
 
 
Database changed
 
MariaDB [test]> CREATE OR REPLACE TABLE `mdb_t_test_2` engine=CONNECT table_type=ODBC tabname='t_test' connection='DSN=testmdb3';
 
ERROR 2006 (HY000): MySQL server has gone away
 
No connection. Trying to reconnect...
 
Connection id:    4
 
Current database: test
 
 
 
ERROR 1105 (HY000): [unixODBC][Driver Manager]Connnection does not exist
 
MariaDB [test]> exit;

record in log file(/var/lib/mysql/servername.err)

File not found
 
Unable to locate database /opt/dmi/dmscore/rawdata/db1.mdb
 
08003: [unixODBC][Driver Manager]Connnection does not exist, Native=0
 
Error: SQLDisconnect rc=-1

Comment by Olivier Bertrand [ 2015-01-27 ]

Apparently there are two issues in what you are reporting:

(1) ERROR 1105 (HY000): [unixODBC][Driver Manager]Connnection does not exist

This can be caused by something wrong in the unixodbc data source description. The above message is a driver message and is not issued by CONNECT. It tells that given the connection 'DSN=testmdb3' it is unable to contact the data source. Before I try to reproduce this, be sure to check all is right in the data source definition.

(2) Crash of theMariaDB server when trying to create the CONNECT table. Looking into the log, it seems to happen in the function VerifyConnect. I remember having already experienced crashes in this function in the past and now it is no more called. Are you using an old CONNECT version? I don't remember when this was fixed but I am sure that version 10.0.15 does not call it anymore.

Comment by Takuya Aoki (Inactive) [ 2015-01-27 ]

I tested in 2 versions.

10.0.16 build-7290 (1) ERROR 1105 (HY000): [unixODBC][Driver Manager]Connnection does not exist
10.0.15 (2) Crash of the MariaDB server

As you say, the crash problem seems to be fixed already.
Concerning ERROR 1105 (HY000) in 10.0.16 build-7290, I found it was because MariaDB did not have permission to read the file.
I fixed the problem (changed the file location to /var/lib/mysql and changed owner to mysql:mysql, permission to 777).
I am getting a different error which is,
ERROR 1105 (HY000): Unsupported SQL type 52

/etc/odbc.ini

[testmdb3]
 
Description = db1
 
Driver = /usr/local/lib/libmdbodbc.so
 
Database = /var/lib/mysql/db1.mdb

making a CONNECT table

[root@kc1060 mysql]# mysql -u root
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 3
 
Server version: 10.0.16-MariaDB MariaDB Server
 
 
 
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [(none)]> use test2;
 
Database changed
 
MariaDB [test2]> CREATE TABLE `mdb_t_test_2` engine=CONNECT table_type=ODBC tabname='t_test' connection='DSN=testmdb3';
 
ERROR 2006 (HY000): MySQL server has gone away
 
No connection. Trying to reconnect...
 
Connection id:    4
 
Current database: test2
 
 
 
ERROR 1105 (HY000): Unsupported SQL type 52

record in log file(/var/lib/mysql/servername.err)

DBMS: MDBTOOLS, Version: 0.7.1, rc=0
 
ODBCColumns: max=4096 len=0,0,0
 
AVB: mp=(nil) type=1 nval=4096 len=0 check=1 blank=0
 
AVB: mp=(nil) type=1 nval=4096 len=0 check=1 blank=0
 
AVB: mp=(nil) type=1 nval=4096 len=0 check=1 blank=0
 
AVB: mp=(nil) type=1 nval=4096 len=128 check=1 blank=0
 
AVB: mp=(nil) type=3 nval=4096 len=6 check=1 blank=0
 
AVB: mp=(nil) type=1 nval=4096 len=0 check=1 blank=0
 
AVB: mp=(nil) type=7 nval=4096 len=10 check=1 blank=0
 
AVB: mp=(nil) type=7 nval=4096 len=10 check=1 blank=0
 
AVB: mp=(nil) type=3 nval=4096 len=6 check=1 blank=0
 
AVB: mp=(nil) type=3 nval=4096 len=6 check=1 blank=0
 
AVB: mp=(nil) type=3 nval=4096 len=6 check=1 blank=0
 
AVB: mp=(nil) type=1 nval=4096 len=0 check=1 blank=0
 
Getting col results ncol=12
 
Columns: NBCOL=12 NBLIN=4

Comment by Olivier Bertrand [ 2015-01-27 ]

From your description I see two problems:

(1) ERROR 1105 (HY000): Unsupported SQL type 52. Looking into sql.h and sqlext.h I see not ODBC data type equal to 52. Do you know what data types you are using in your table?

(2) After printing the above messge, the connect_assisted_discovery function returns HA_ERR_INTERNAL_ERROR to MariaDB. The crash happens then but not in CONNECT.

Comment by Takuya Aoki (Inactive) [ 2015-01-28 ]

About (1)
I get below for integer types
ERROR 1105 (HY000): Unsupported SQL type 52

I get below for text types
ERROR 1105 (HY000): Unsupported SQL type 12849

About(2)
I don't know what connect_assisted_discovery function is.
Do you mean the crash in version 10.0.15 is happening because of reasons outside MariaDB?

Comment by Olivier Bertrand [ 2015-01-28 ]

All these SQL types number do not exist. I don't know where they can come from. There is something strange in your data source.

connect_assisted_discovery is the function called when a table is created with no column definition. It retrieves the column definition from the data source and builds the complete create table statement that you can see using the SHOW CREATE TABLE command. It is a CONNECT function but, after it returns, no more CONNECT code is executed.

The reason for the crash is indeed in MariaDB, but may be outside of CONNECT.

Comment by Takuya Aoki (Inactive) [ 2015-02-02 ]

I added the data file so you can have a look.
It is a normal .mdb file made in Access 2000 so maybe mdbtools is doing something that MariaDB doesn't accept.

Comment by Olivier Bertrand [ 2015-02-03 ]

Using your data file, I was able to read it as well on Windows and Linux (ubuntu)

On Windows

CREATE OR REPLACE TABLE t_test engine=CONNECT table_type=ODBC connection='DSN=MS Access Db1';
 
select * from t_test;

id name place register_date
0 Tom US 2014-12-01 00:00:00
1 Sara France 2015-01-02 00:00:00
2 Jef Poland 2015-01-15 00:00:00

On Linux (ubuntu)

I downloaded and install MDBTools. After that I was able to connect to the mdb file and read the t_test table using the same create table than on Windows.

There seem to be nothing special with CONNECT. However, this and some other related issues such as MDEV-7508 and MDEV-7522 demonstrate that there is still many problems with unixODBC and the way CONNECT uses it that are platform dependant. Presently, I don't see what I can do more, being unable to reproduc the errors on any of my machines.

Generated at Thu Feb 08 07:20:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.