1. cat /etc/issue
   CentOS release 6.4 (Final)
   Kernel \r on an \m

Hi,

It's supposed to be fixed in the current 10.0 tree, if you can build from source, please try it out.
I think it was fixed in this revision: http://bazaar.launchpad.net/~maria-captains/maria/10.0/revision/3747

You will also need to install unixODBC-dev before compiling.

Hi Elena,

Yep, that revision has the fix. Thanks!

MariaDB [test]> SELECT VERSION();
----------------

----------------

----------------
1 row in set (0.00 sec)

MariaDB [test]> create table subscrib engine=CONNECT table_type=ODBC tabname='crm.subscrib' connection='DSN=crm_test';
ERROR 1105 (HY000): [unixODBC][Driver Manager]Data source name not found, and no default driver specified
MariaDB [test]>

Could you provide working ha_connect.so? Or I need replace whole server?

And question could this plugin also work with MariaDB 5.5.30?

Which OS do you need ha_connect.so for?

No, this plugin does not work with 5.5.30.
It heavily uses the new assisted discovery feature, introduced in 10.0.x

Hi Mikhail,

To get ha_connect.so, I used the MariaDB-connect-engine package in the RHEL repo. To get the CREATE TABLE to work, I downloaded the source tarball Elena linked to, compiled the new binary, and replaced the packaged mysqld with this new one. Worked like a charm!

There is no a need to update the server itself. mysqld from 10.0.2 should work.
The problem was in ha_connect..so, which should be upgraded.

> Which OS do you need ha_connect.so for?
CentOS 6.4

Oops. Sorry. I have CentOS 6.3 only.

To clarify one detail:
There are two ways to compile Connect:
a. As a dynamic module ha_connect.so
This is the default.

b. As a part of mysqld binary.
Run "cmake -DWITH_CONNECT_STORAGE_ENGINE=1" for this.

MariaDB official binaries compile it as ha_connect.so, to avoid
mysqld dependency on libxml2 and unixODBC.

So, what to upgrade actually depends on what are you using.
In case of a) you need to upgrade ha_connect.so.
In case of b) you need to upgrade mysqld.

Alexander,
I think no differences between CentOS 6.3 and CentOS 6.4 in our case. Please upload your library.

To make sure: you need ha_connect.so to use with the MariaDB official 10.0.2 packages, for CentOS-6.4 (64bit?). Is everything correct?

yes

I sent ha_connect.so (compiled with libxml2 and unixodbc) to your mailbox.

I replaced library, but something still wrong

Query: create table subscrib engine=CONNECT table_type=ODBC tabname='crm.subscrib' connection='DSN=crm_test'

Error Code: 2013
Lost connection to MySQL server during query

Alas, it did not work. Then I can suggest to install everything from the latest sources,
or wait for the next release.

After update to 10.0.3 I have another error:

1. rpm -qa | grep MariaDB | sort
   MariaDB-client-10.0.3-1.x86_64
   MariaDB-common-10.0.3-1.x86_64
   MariaDB-compat-10.0.3-1.x86_64
   MariaDB-connect-engine-10.0.3-1.x86_64
   MariaDB-server-10.0.3-1.x86_64

Query: create table subscrib engine=CONNECT table_type=ODBC tabname='crm.subscrib' connection='DSN=crm_test'

Error Code: 1105
[unixODBC][Driver Manager]Can't open lib '/usr/cachesys/bin/libcacheodbc.so' : file not found

1. ldd /usr/cachesys/bin/libcacheodbc.so
   linux-vdso.so.1 => (0x00007fff21599000)
   libstdc+.so.6 => /usr/lib64/libstdc+.so.6 (0x00007f42aca7c000)
   libm.so.6 => /lib64/libm.so.6 (0x00007f42ac7f8000)
   libc.so.6 => /lib64/libc.so.6 (0x00007f42ac464000)
   libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f42ac24e000)
   libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f42ac031000)
   /lib64/ld-linux-x86-64.so.2 (0x00007f42acfe4000)

what is wrong now??

I try use cache ODBC driver from this location: ftp://ftp.intersystems.com/pub/cache/odbc/2013/cache.2013.1.0.ODBC-lnxsusex64.tar.gz

Also trying: ftp://ftp.intersystems.com/pub/cache/odbc/2013/cache.2013.1.0.ODBC-lnxrhx64.tar.gz and have same result.

my odbc configs

Can you please run this command:

isql -vvv crm_test

Does it connect? What is its output?

Thanks.

After some experimenting, I think that the problem is in the file permissions.

Note, mysqld is run under the user "mysql" by default.
So make sure that the file /usr/cachesys/bin/libcacheodbc.so is accessible by the user "mysql".

These commands should fix the problem:

chmod a+rx /usr/cachesys
chmod a+rx /usr/cachesys/bin
chmod a+r /usr/cachesys/bin/libcacheodbc.so

Helped on my box.

Please give feedback.

1. isql -vvv crm_test mobill mobill
   ---------------------------------------

   Connected!

   sql-statement

   help [tablename]

   quit

   ---------------------------------------
   SQL> select top 2 ID from crm.subscrib
   ------------

   ID

   ------------

   9

   11

   ------------
   SQLRowCount returns 2
   2 rows fetched
   SQL>

But after all chmod operation I have same error message

<e>Query: create table subscrib engine=CONNECT table_type=ODBC tabname='crm.subscrib' connection='DSN=crm_test;UID=mobill;PWD=mobill'

Error Code: 1105
[unixODBC][Driver Manager]Can't open lib '/usr/cachesys/bin/libcacheodbc.so' : file not found

[root@crm-dev etc]# su mysql
bash-4.1$ ldd /usr/cachesys/bin/libcacheodbc.so
linux-vdso.so.1 => (0x00007fff7cedd000)
libstdc+.so.6 => /usr/lib64/libstdc+.so.6 (0x00007fafd1709000)
libm.so.6 => /lib64/libm.so.6 (0x00007fafd1485000)
libc.so.6 => /lib64/libc.so.6 (0x00007fafd10f1000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fafd0edb000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fafd0cbe000)
/lib64/ld-linux-x86-64.so.2 (0x00007fafd1c72000)

Can you please try to restart mysqld?

Just tried to switch ODBC trace on. But it did not help much.
The log prints exactly the same error:

[ODBC][930][1372327342.367908][SQLConnect.c][1111]Can't open lib '/home/bar/tmp/
bin/libcacheodbc.so' : file not found

No additional information

Any updates?

Sorry, no ideas. The error happens on the ODBC side.
You may try to talk to the libcacheodbc maintainers.

I think they says what problem in MariaDB because isql is worked.

It's very hard to guess why this happens.
The ODBC error message is obviously wrong, as the file does exist.

I have no ideas other than tracing mysqld in gdb through the ODBC
sources and see why it returns this error.

I am having the same issue with 10.0.4 x64 on CentOS 6.2 2.6.32-220.
I am using FreeTDS to connect to Sybase.
This has worked before.

ODBC Info

[mysql@dev rbensley 10:02:16] $ odbcinst -j

unixODBC 2.2.14

DRIVERS............: /etc/odbcinst.ini

SYSTEM DATA SOURCES: /etc/odbc.ini

FILE DATA SOURCES..: /etc/ODBCDataSources

USER DATA SOURCES..: /home/mysql/.odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

[mysql@dev rbensley 10:03:32] $ odbcinst -q -d

[PostgreSQL]

[MySQL]

[ODBC Drivers]

[freetds]

[mysql@dev rbensley 10:03:41] $ odbcinst -q -s

[tkdevsy]

I get the following output, but no information in error log or ODBC trace file:

mysql 10:38:46 [NY DEV 001] [pmc] [3108]> create table TestTable engine=CONNECT table_type=ODBC connection='tkdevsy';

ERROR 1105 (HY000): Unsupported table type ODBC

Error (Code 1105): Unsupported table type ODBC

Error (Code 1030): Got error 122 "Internal (unspecified) error in handler" from storage engine CONNECT

I had this issue in 10.0.3, but restarting the server fixed it.

What does "ldd basedir/lib/plugin/ha_connect.so return for you?

(where basedir is MariaDB install directory).

Which binaries are you using? Did you download them from the MariaDB site,
or build from sources?

If you use MariaDB compiled from source, ODBC and MYSQL table types require the following precompiler definitions:
ODBC_SUPPORT
MYSQL_SUPPORT
Otherwise the corresponding code is not compiled and you get the message:
"Unsupported table type %s" where %s is replaced by ODBC or MYSQL.

Olivier, I am using the package I downloaded from mariadb.org: mariadb-10.0.4-linux-x86_64.tar.gz

Alexandar, the ldd output:

[mysql@dev rbensley 05:07:04] $ ldd /data/versions/mysql/mariadb-10.0.4-linux-x86_64/lib/plugin/ha_connect.so

ldd: warning: you do not have execution permission for `/data/versions/mysql/mariadb-10.0.4-linux-x86_64/lib/plugin/ha_connect.so'

        linux-vdso.so.1 =>  (0x00007fffc1daa000)

        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f481a907000)

        libxml2.so.2 => /usr/lib64/libxml2.so.2 (0x00007f481a5b5000)

        libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007f481a2af000)

        libm.so.6 => /lib64/libm.so.6 (0x00007f481a02b000)

        libc.so.6 => /lib64/libc.so.6 (0x00007f4819c9a000)

        /lib64/ld-linux-x86-64.so.2 (0x0000003c67800000)

        libdl.so.2 => /lib64/libdl.so.2 (0x00007f4819a96000)

        libz.so.1 => /lib64/libz.so.1 (0x00007f4819880000)

        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f4819669000)

I tried adding execution permission to ha_connect.so, but that didn't do anything.

Now going to re-test in 10.0.3.

Richard,
unfortunately this tar.gz was built without ODBC support.

Please download an RPM package from

http://mirror.timeweb.ru/mariadb/mariadb-10.0.4/yum/centos5-amd64/rpms/MariaDB-10.0.4-centos5-x86_64-connect-engine.rpm

extract ha_connect.so from it and put it instead of ha_connect.so coming from tar.gz.

Btw, why do you prefer binary tar.gz instead of RPM? (just curious)

Thanks Alexander, I assume this was just a mistake?

That worked perfectly.

We have used the tar's for deployment for around 5 years now. We run multiple instances per host, globally. Physical split between databases. This same mechanism works on Solaris and CentOS.

This also allows us to run multiple versions of MySQL/MariaDB. RPM's via puppet are being considered in the future. But we are waiting for 10.x to go GA, so we can run a single verison to handle all our needs (GTID, Multi-source replication, CONNECT, handlersocket etc).

Is just a support option when building the binaries.

Richard, the problem is that this binary distribution is built on a very old
Ubuntu machine with an old unixODBC version which is not supported by
ConnectSE. We're discussing what can be done.

Stop what about me. I am not using Ubuntu.

It can be a problem related to the way the connection string is written. Each data source has its own syntax.
This would have to be checked with the data source (Intersystems?) documentation.

In addition, there can be several ways to specify the connection. Sometimes you can specify the driver to use
(via DRIVER=) and add additional information such as the database specification.
This one can be just a name or the name of the file containing the database (as for MS access or SQLite3).
In that case, using a console client it is sometimes possible to give a relative path name, but most ODBC drivers
require a FULL path name.

Alternatively, you can specify a pre-defined data source (via DSN=) However, some drivers accept some of the pre-defined
parameters to be changed by specifying them in the connection string, but some others, such as the MySQL connector,
use the pre-defined parameters and ignore those added to the connection string.

All his makes specifying the connection string a non trivial task.

But isql is works!!!

Query: create table subscrib engine=CONNECT table_type=ODBC tabname='crm.subscrib' connection='DSN=crm_test'

Error Code: 1105
[unixODBC][Driver Manager]Can't open lib '/usr/cachesys/bin/libcacheodbc.so' : file not found

[root@crm-dev ~]# ldd /usr/cachesys/bin/libcacheodbc.so
linux-vdso.so.1 => (0x00007ffffc5d9000)
libstdc+.so.6 => /usr/lib64/libstdc+.so.6 (0x00007f464359e000)
libm.so.6 => /lib64/libm.so.6 (0x00007f4643319000)
libc.so.6 => /lib64/libc.so.6 (0x00007f4642f86000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f4642d70000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f4642b52000)
/lib64/ld-linux-x86-64.so.2 (0x00007f4643b0e000)

Mikhail,

I just installed Cache and checked it with the Connect engine
with MariaDB-10.0.5. It worked fine.

This what I exactly did:

• Installed Cache server from cache-server-2013.1.2.601.0su-1.rh.x86_64.rpm
• Installed Cache ODBC driver from cache.2013.1.0.ODBC-lnxsusex64.tar.gz.
• Added these lines into /etc/odbc.ini:
  [Samplesunixodbc]
  Driver = /usr/cachesys/bin/libcacheodbc.so
  Description = Cache ODBC driver
  Host = localhost
  Namespace = SAMPLES
  #UID = _SYSTEM
  #Password = SYS
  Port = 1972
  Protocol = TCP
  Query Timeout = 1
  Static Cursors = 0
  Trace = off
  TraceFile = iodbctrace.log
  Authentication Method = 0
  Security Level = 2
  Service Principal Name = cache/localhost.domain.com

• Made sure it works in isql under user "mysql" with a demo table
  sample.person, which was installed together with Cache server.
  Note, It's important to test the same user that runs MariaDB here!

$ sudo -u mysql isql Samplesunixodbc
---------------------------------------

---------------------------------------
SQL> SELECT TOP 3 id,name FROM sample.person
-------------------------------------------------------------+

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

-------------------------------------------------------------+
SQLRowCount returns 3

• Made a corresponding table in MariaDB

mysql> drop table if exists t1; create table t1 (ID INT, Name VARCHAR(50)) engine=CONNECT table_type=ODBC tabname='sample.person' connection='DSN=Samplesunixodbc'; select * from t1 limit 3;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.05 sec)

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

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

--------------------+
3 rows in set (0.02 sec)

Everything worked fine. I still think that your problem
happens because the user "mysql" does not have enough
permissions to load the ODBC driver library.

Alexander, can you try experiment
1) without table definition.
use query: create table t1 engine=CONNECT table_type=ODBC tabname='sample.person' connection='DSN=Samplesunixodbc';
2) using another user, for example:
UID = crm
Password = crm

1) It does not work without a table definition. I found a bug, fixing it now.
But it works fine with a table definition.
Does it now work for you WITH the table definition? What was the problem?

2) I don't know how to create a new cache user with the given UID and Password.
Currently I'm getting this:

mysql> drop table if exists t1; create table t1 engine=CONNECT table_type=ODBC connection='DSN=Samplesunixodbc;UID=crm;Password=crm';
Query OK, 0 rows affected (0.00 sec)

ERROR 1105 (HY000): [unixODBC][Cache ODBC][State : S1000][Native Code 417]
[libcacheodbc.so]
Access Denied

which looks like a correct answer.

2) I've found how to create a new user. It seems to work fine.

Note, the correct connection parameter for password is "PWD=crm",
it's not "Password=crd":

mysql> drop table if exists t1; create table t1 (ID INT, Name VARCHAR(50)) tabname='sample.person' engine=CONNECT table_type=ODBC connection='DSN=Samplesunixodbc2;UID=crm;PWD=crm'; select * from t1 limit 1;
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.06 sec)
-------------------+

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

-------------------+
1 row in set (0.01 sec)

Whitch OS?

1. cat /etc/issue
   CentOS release 6.4 (Final)
   Kernel \r on an \m

I am download ODBC from here ftp://ftp.intersystems.com/pub/cache/odbc/2013/cache.2013.1.0.ODBC-lnxsusex64.tar.gz

$ cat /etc/fedora-release
Fedora release 18 (Spherical Cow)

> Note, the correct connection parameter for password is "PWD=crm",
it's not "Password=crd":

Very strange because isql not work if I use PWD param in odbc.ini

My odbc.ini config:

[Samplesunixodbc]
Driver = /usr/cachesys/bin/libcacheodbc.so
Description = Cache ODBC driver
Host = 10.10.4.18
Namespace = CRM
UID = mobill
;PWD = mobill
Password = mobill
Port = 1972
Protocol = TCP
Query Timeout = 1
Static Cursors = 0
Trace = off
TraceFile = iodbctrace.log
Authentication Method = 0
Security Level = 2
Service Principal Name = cache/localhost.domain.com

[root@crm-dev etc]# isql -vvv Samplesunixodbc
---------------------------------------

---------------------------------------

For some reasons Cache implements them differently:

• it must be "Password=" in /etc/odbc.ini
• but it must be "PWD=" in the connection string.
  You can ask on the Cache mailing lists why this inconsistency.

According to this page it must be "PWD=" on the connection string:
http://msdn.microsoft.com/en-us/library/ms715433%28v=vs.85%29.aspx
So it seems to work correctly.

are you use SELinux? Try switch in Enforcing mode

I am swith my SELinux to Permissive mode and CONNECT engine begins work.

but still not work this construction:
create table subscrib engine=CONNECT table_type=ODBC tabname='crm.subscrib' connection='DSN=crm_test'

Error Code: 1105
No data found for table crm.subscrib

type=AVC msg=audit(1384890085.406:76): avc: denied

for pid=1433 comm="mysqld" path="/usr/cachesys/bin/libcacheodbc.so" dev=dm-0 ino=3279212 scontext=unconfined_u:system_r:mysqld_t:s0 tcontext=unconfined_u:object_r:usr_t:s0 tclass=file

We'll fix this problem soon:

create table subscrib engine=CONNECT table_type=ODBC tabname='crm.subscrib' connection='DSN=crm_test'

In the meanwhile please use explicit table structure definition.

Thanks!

What about SELinux? I think process mysqld must be labeled as isql for works with ODBC drivers.

Yes, you should configure your SELinux to allow the user "mysql" and the process "mysqld"
to open /usr/cachesys/bin/libcacheodbc.so.

By the way, does this work on your box:

sudo -u mysql isql Samplesunixodbc

?

I.e. I'm curious what SELinux actually disallows:

• the user "mysql"
• or the process "mysqld"
• or both.

1. isql -vvv crm_test
   ---------------------------------------

   Connected!

   sql-statement

   help [tablename]

   quit

   ---------------------------------------
   SQL> quit
   [root@crm-dev ~]# sudo -u mysql isql crm_test
   ---------------------------------------

   Connected!

   sql-statement

   help [tablename]

   quit

   ---------------------------------------
   SQL>

SELinux not block process by user, SELinux uses file system labeling for block.
https://access.redhat.com/site/documentation/en-US/Red_Hat_Enterprise_Linux/4/html/SELinux_Guide/rhlcommon-section-0068.html
I think some labels or roles must be added for mysqld for correct work MariaDB and CONNECT engine under RHEL6.
If you not have this problem under Fedora 18 with enabled SELinux, seems it already fixed bug of SELinux policy and must be reported to Red Hat for backporting to RHEL6.

Binary tar does not include ODBC extensions for CONNECT:

rbensley@lndwc-27-236:Downloads$ ldd mariadb-10.0.6-linux-x86_64/bin/mysqld
linux-vdso.so.1 => (0x00007fffe31fe000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00000033c3400000)
libaio.so.1 => /lib64/libaio.so.1 (0x0000003ee1400000)
librt.so.1 => /lib64/librt.so.1 (0x00000033c4400000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00000033d8400000)
libdl.so.2 => /lib64/libdl.so.2 (0x00000033c3800000)
libstdc+.so.6 => /lib64/libstdc+.so.6 (0x00000033d0000000)
libm.so.6 => /lib64/libm.so.6 (0x00000033c4000000)
libc.so.6 => /lib64/libc.so.6 (0x00000033c3000000)
/lib64/ld-linux-x86-64.so.2 (0x00000033c2c00000)
libfreebl3.so => /lib64/libfreebl3.so (0x00007f64c1b86000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00000033c5000000)

This is the same for the glibc 2.14+ package.

Richard,

The binary for glibc-2.14+ does include ODBC support.
You need to run "ldd ha_connect.so" instead of "ldd mysqld" to check this.