Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Not a Bug
-
Affects Version/s: 10.0.16
-
Fix Version/s: N/A
-
Component/s: Storage Engine - Connect
-
Labels:None
-
Environment:CentOS release 6.5 (X86_64)
Description
I am testing with Easysoft ODBC-Access Drive to create a CONNECT table linked to a .mdb file.
It is working, but I found that MariaDB is limiting the column name to 24 characters when the original .mdb file has a column name that is longer.
Therefore, I am getting an error telling me the column does not exist.
Can this be fixed so I can use column names longer than 24 characters?
Installing Easysoft ODBC-Access Drive
Download from http://www.easysoft.com/products/data_access/odbc-access-driver/index.html#section=tab-1 su root tar xvf odbc-access-1.1.0-linux-x86-64-ul64.tar cd odbc-access-1.1.0-linux-x86-64-ul64 ./install vi /etc/odbc.ini ### [ACCESS_SAMPLE] Description=Easysoft ODBC-Access Driver Driver=Easysoft ODBC-ACCESS mdbfile=/var/lib/mysql/db1.mdb smbpath= lockfile= smblib= smbuser= smbauth= readonly=yes exclusive=no ignore_rel=no ###
checking isql
[root@kc0022 ~]# isql ACCESS_SAMPLE +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> SELECT * FROM t_test_short +-----------+---------------------------------------------------+---------------------------------------------------+----------------------+ | 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 | +-----------+---------------------------------------------------+---------------------------------------------------+----------------------+ SQLRowCount returns -1 3 rows fetched SQL> SELECT * FROM t_test_long +-----------+---------------------------------------------------+---------------------------------------------------+----------------------+ | id | name_of_members_who_have_contacted | 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 | +-----------+---------------------------------------------------+---------------------------------------------------+----------------------+ SQLRowCount returns -1 3 rows fetched SQL> quit;
making a CONNECT table
[root@kc0022 ~]# mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
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_short`
-> engine=CONNECT table_type=ODBC tabname='t_test_short' connection='DSN=ACCESS_SAMPLE';
Query OK, 0 rows affected (0.57 sec)
MariaDB [test]> EXPLAIN `mdb_t_test_short`;
+---------------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+-------------------+-----------------------------+
| id | int(10) | NO | | NULL | |
| name | varchar(50) | YES | | NULL | |
| place | varchar(50) | YES | | NULL | |
| register_date | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+---------------+-------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.07 sec)
MariaDB [test]> SELECT * FROM `mdb_t_test_short`;
+----+------+--------+---------------------+
| 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 |
+----+------+--------+---------------------+
3 rows in set (0.04 sec)
MariaDB [test]> CREATE OR REPLACE TABLE `mdb_t_test_long`
-> engine=CONNECT table_type=ODBC tabname='t_test_long' connection='DSN=ACCESS_SAMPLE';
Query OK, 0 rows affected (0.09 sec)
MariaDB [test]> EXPLAIN `mdb_t_test_long`;
+--------------------------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-------------+------+-----+-------------------+-----------------------------+
| id | int(10) | NO | | NULL | |
| name_of_members_who_have | varchar(50) | YES | | NULL | |
| place | varchar(50) | YES | | NULL | |
| register_date | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------------------+-------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.06 sec)
MariaDB [test]> SELECT * FROM `mdb_t_test_long`;
ERROR 1296 (HY000): Got error 174 '[unixODBC][Easysoft ODBC]General error column 'NAME_OF_MEMBERS_WHO_HAVE' not found in specified tables' from CONNECT
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Can you tell me if it happens only on ODBC tables or with other CONNECT tables as well?
I have tested this both on Windows an Linux.
On windows
CREATE OR REPLACE TABLE db1_exec ( command varchar(150) not null, number int(5) not null flag=1, message varchar(255) flag=2) engine=CONNECT table_type=ODBC connection='DSN=MS Access Db1' OPTION_LIST='Execsrc=1'; select * from db1_exec where command = 'CREATE TABLE t_long (id integer, name_of_members_who_have_contacted varchar(50), place varchar(50), register_date datetime)'; select * from db1_exec where command = "INSERT INTO t_long VALUES(0,'Tom','US','2014-12-01')"; select * from db1_exec where command = "INSERT INTO t_long VALUES(1,'Sara','France','2015-01-02')"; select * from db1_exec where command = "INSERT INTO t_long VALUES(2,'Jef','Poland','2015-01-15')"; CREATE OR REPLACE TABLE t_long engine=CONNECT table_type=ODBC connection='DSN=MS Access Db1'; select * from t_long;
| id | name_of_members_who_have_contacted | 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 |
explain t_long;
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| id | int(10) | YES | <null> | ||
| name_of_members_who_have_contacted | varchar(50) | YES | <null> | ||
| place | varchar(50) | YES | <null> | ||
| register_date | timestamp | NO | <null> | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
This shows that CONNECT does not do any truncation of column names to 24 characters.
On Linux
After installing the easySoft stuff, I did the same test and this time, while it was working all right with isbl, the column name have been truncated to 24 characters when using CONNECT.
After adding some tracing in the code, I finally found the reason. When defining the table without specifying the columns, MariaDB calls CONNECT's connect_assisted_discovery function that calls the ODBCColumns function that get the columns definition via the ODBC SQLColumns function.
When allocating the memory to store the results, it also interrogates the ODBC server to know some of its limitations, for instance:
n = ocp->GetMaxValue(SQL_MAX_COLUMNS_IN_TABLE);
To know the maximum number of columns in a table and:
n = ocp->GetMaxValue(SQL_MAX_COLUMN_NAME_LEN);
to know the size of the buffer to allocate to receive column names.
What happen is that on Windows this last function returns 128 but on Linux it returns 24! This is why the column names are truncated.
This is not a CONNECT problem but a unixODBC/MSSQL problem that should be transferred to them. The possible workaround:
- Don't use on Linux column names longer than 24 characters
- Specify the columns in the CREATE TABLE statement.
Indeed, when column are specified, you can use column names longer that that and they are accepted when querying the table! As a matter of fact, this unixODBC/MSSQL bug seems limited to the return of the SQLGetInfo function (called by GetMaxValue)
Thank you for your work specifying the reason.
It was a problem of the ODBC driver (I contacted easysoft and they fixed it for the new release).
FederatedX doesn't have the problem, so it doesn't seem to be a caused by table discovery as such – probably a Connect problem, then?