Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Not a Bug
-
Affects Version/s: 10.0.15
-
Fix Version/s: N/A
-
Component/s: Storage Engine - Connect
-
Labels:
-
Environment:CentOS 7
Description
I have been attempting to try out using the CONNECT storage engine to connect to MS SQL Server via MariaDB 10.0 on CentOS 7.
I did the following to set up the environment:
#Install EPEL sudo rpm -ivh http://download.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-5.noarch.rpm #Install unixODBC sudo yum install unixODBC unixODBC-devel #Install FreeTDS sudo yum install freetds freetds-devel #Install and load CONNECT sudo yum install MariaDB-connect-engine mysql -u root --execute="INSTALL SONAME 'ha_connect';"
Then I created the two files tds.driver.template and tds.datasource.template.
tds.driver.template
[FreeTDS]
Description = ODBC for TDS protocol
Driver = /usr/lib64/libtdsodbc.so
tds.datasource.template
[connect_test] Driver = FreeTDS Description = MSSQL Server Trace = No Server = 192.168.1.12 Database = connect_test Port = 1433 TDS_Version = 7.1
And executed:
#Install FreeTDS Driver for ODBC sudo odbcinst -i -d -f tds.driver.template #Install data source for ODBC sudo odbcinst -i -s -l -f tds.datasource.template
Everything appears to work via ODBC's isql client:
[gmontee@localhost ~]$ isql connect_test sa 'password' +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> SELECT * FROM dbo.test_table; +------------+---------------------------------------------------+ | a | b | +------------+---------------------------------------------------+ +------------+---------------------------------------------------+ SQLRowCount returns 0 SQL> INSERT INTO dbo.test_table VALUES(1, 'correct'); SQLRowCount returns 1 SQL> INSERT INTO dbo.test_table VALUES(2, 'horse'); SQLRowCount returns 1 SQL> INSERT INTO dbo.test_table VALUES(3, 'battery'); SQLRowCount returns 1 SQL> SELECT * FROM dbo.test_table; +------------+---------------------------------------------------+ | a | b | +------------+---------------------------------------------------+ | 1 | correct | | 2 | horse | | 3 | battery | +------------+---------------------------------------------------+ SQLRowCount returns 3 3 rows fetched
However, connecting to the same data source with CONNECT doesn't work. I've tried the following different table options:
-- DSN
-- fields not specified
-- schema qualified in TABNAME
CREATE TABLE test_table
ENGINE=CONNECT
TABLE_TYPE=ODBC
TABNAME='dbo.test_table'
CONNECTION='DSN=connect_test;UID=sa;PWD=password';
-- DSN
-- fields not specified
-- schema in DBNAME
CREATE TABLE test_table
ENGINE=CONNECT
TABLE_TYPE=ODBC
DBNAME='dbo'
TABNAME='test_table'
CONNECTION='DSN=connect_test;UID=sa;PWD=password';
-- DSN
-- fields not specified
-- use default schema
CREATE TABLE test_table
ENGINE=CONNECT
TABLE_TYPE=ODBC
TABNAME='test_table'
CONNECTION='DSN=connect_test;UID=sa;PWD=password';
-- DSN
-- fields specified
-- schema qualified in TABNAME
CREATE TABLE test_table (
a int,
b varchar(50)
)
ENGINE=CONNECT
TABLE_TYPE=ODBC
TABNAME='dbo.test_table'
CONNECTION='DSN=connect_test;UID=sa;PWD=password';
-- DSN
-- fields specified
-- schema in DBNAME
CREATE TABLE test_table (
a int,
b varchar(50)
)
ENGINE=CONNECT
TABLE_TYPE=ODBC
DBNAME='dbo'
TABNAME='test_table'
CONNECTION='DSN=connect_test;UID=sa;PWD=password';
-- DSN
-- fields specified
-- use default schema
CREATE TABLE test_table (
a int,
b varchar(50)
)
ENGINE=CONNECT
TABLE_TYPE=ODBC
TABNAME='test_table'
CONNECTION='DSN=connect_test;UID=sa;PWD=password';
-- DSN-less
-- fields specified
-- schema qualified in TABNAME
CREATE TABLE test_table (
a int,
b varchar(50)
)
ENGINE=CONNECT
TABLE_TYPE=ODBC
TABNAME='dbo.test_table'
CONNECTION='DRIVER=FreeTDS;Server=192.168.1.12;Port=1433;TDS_Version=7.1;Database=connect_test;UID=sa;PWD=password';
They all return the same vague error when attempting to connect:
ERROR 1296 (HY000): Got error 174 '[unixODBC][FreeTDS][SQL Server]Unable to connect to data source' from CONNECT
I am using MS SQL Server Express 2012 which can be downloaded for free.
Considering how many components are in play here (SQL Server, FreeTDS, unixODBC, CONNECT, MariaDB), there's a good chance I'm missing an option, but I'm not sure what that would be.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
I am using ubuntu 14.04 LTS (32 bits)
By the way, I added to CONNECT the possibility use use SQLConnect or SQLDriverConnect. However, this will be available only in the next release.
I am using ubuntu 14.04 LTS (32 bits)
Great, thanks! I'll look into the differences and see if the FreeTDS developers have any insight into the problem.
By the way, I added to CONNECT the possibility use use SQLConnect or SQLDriverConnect. However, this will be available only in the next release.
Awesome! Thanks for all your work on CONNECT.
It worked on 64-bit Ubuntu 14.04 on my first try.
Set up ODBC stuff:
sudo apt-get install unixodbc
sudo apt-get install tdsodbc
cat > tds.driver.template <<EOM
[FreeTDS]
Description = ODBC for TDS protocol
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
EOM
cat > tds.datasource.template <<EOM
[connect_test]
Driver = FreeTDS
Description = MSSQL Server
Trace = No
Server = iwo9jlpffq.database.windows.net
Database = connect_test
Port = 1433
TDS_Version = 7.1
EOM
sudo odbcinst -i -d -f tds.driver.template
sudo odbcinst -i -s -l -f tds.datasource.template
Add MariaDB repo:
sudo apt-get install software-properties-common sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db sudo add-apt-repository 'deb http://ftp.utexas.edu/mariadb/repo/10.0/ubuntu trusty main' sudo apt-get update
Install MariaDB and connect:
sudo apt-get update sudo apt-get install mariadb-server-10.0 mariadb-client-10.0 sudo apt-get install mariadb-connect-engine-10.0 mysql -u root --execute="INSTALL SONAME 'ha_connect';" mysql -u root --execute="CREATE DATABASE tmp;"
Now test it out:
gmontee@gmontee-VirtualBox:~$ mysql -u root tmp
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 44
Server version: 10.0.16-MariaDB-1~trusty mariadb.org binary distribution
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 [tmp]> CREATE TABLE test_table
-> ENGINE=CONNECT
-> TABLE_TYPE=ODBC
-> TABNAME='dbo.test_table'
-> CONNECTION='DSN=connect_test;UID=connect_test;PWD=Password1';
Query OK, 0 rows affected (2.24 sec)
MariaDB [tmp]> SELECT * FROM test_table;
+---+---------+
| a | b |
+---+---------+
| 1 | correct |
| 2 | horse |
| 3 | battery |
+---+---------+
3 rows in set (0.54 sec)
The issues I have been having seem specific to the packages included in CentOS 7.
I talked to the FreeTDS developer here, but FreeTDS and unixODBC turned out to be irrelevant to this problem.
It turns out that SELinux in CentOS 7 doesn't allow /usr/sbin/mysqld to make outgoing TCP connections. I set SELinux to permissive mode, and now everything works. /var/log/messages shows:
Feb 4 18:56:17 localhost setroubleshoot: SELinux is preventing /usr/sbin/mysqld from name_connect access on the tcp_socket . For complete SELinux messages. run sealert -l d7f77d24-7340-40f3-ae75-a41cc1c4f454 Feb 4 18:56:17 localhost python: SELinux is preventing /usr/sbin/mysqld from name_connect access on the tcp_socket . ***** Plugin catchall_boolean (89.3 confidence) suggests ****************** If you want to allow mysql to connect any Then you must tell SELinux about this by enabling the 'mysql_connect_any' boolean. You can read 'None' man page for more details. Do setsebool -P mysql_connect_any 1 ***** Plugin catchall (11.6 confidence) suggests ************************** If you believe that mysqld should be allowed name_connect access on the tcp_socket by default. Then you should report this as a bug. You can generate a local policy module to allow this access. Do allow this access for now by executing: # grep mysqld /var/log/audit/audit.log | audit2allow -M mypol # semodule -i mypol.pp
/var/log/audit/audit.log shows:
type=AVC msg=audit(1423094175.109:433): avc: denied { name_connect } for pid=3193 comm="mysqld" dest=1433 scontext=system_u:system_r:mysqld_t:s0 tcontext=system_u:object_r:mssql_port_t:s0 tclass=tcp_socket
type=SYSCALL msg=audit(1423094175.109:433): arch=c000003e syscall=42 success=no exit=-115 a0=3a a1=7f9de7233400 a2=10 a3=7f9de72331a0 items=0 ppid=1716 pid=3193 auid=4294967295 uid=991 gid=989 euid=991 suid=991 fsuid=991 egid=989 sgid=989 fsgid=989 tty=(none) ses=4294967295 comm="mysqld" exe="/usr/sbin/mysqld" subj=system_u:system_r:mysqld_t:s0 key=(null)
We may want to document this in CONNECT's ODBC documentation.
Indeed, this must be documented, along some with other connection issues.
I got ODBC logging to work. isql and CONNECT appear to be using different mechanisms to connect to the data source.
isql's trace log (connecting via SQLConnect.c):
[ODBC][5693][1422469905.334248][__handles.c][460] Exit:[SQL_SUCCESS] Environment = 0x1e39750 [ODBC][5693][1422469905.334334][SQLAllocHandle.c][375] Entry: Handle Type = 2 Input Handle = 0x1e39750 [ODBC][5693][1422469905.334373][SQLAllocHandle.c][493] Exit:[SQL_SUCCESS] Output Handle = 0x1e3a050 [ODBC][5693][1422469905.334414][SQLConnect.c][3700] Entry: Connection = 0x1e3a050 Server Name = [connect_test][length = 12 (SQL_NTS)] User Name = [sa][length = 2 (SQL_NTS)] Authentication = [********][length = 8 (SQL_NTS)] UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE' [ODBC][5693][1422469905.366705][SQLConnect.c][4273] Exit:[SQL_SUCCESS]And CONNECT's trace log (connecting via SQLDriverConnect.c):
[ODBC][2474][1422469178.558581][__handles.c][460] Exit:[SQL_SUCCESS] Environment = 0x7ffdad374c00 [ODBC][2474][1422469178.558675][SQLAllocHandle.c][375] Entry: Handle Type = 2 Input Handle = 0x7ffdad374c00 [ODBC][2474][1422469178.558714][SQLAllocHandle.c][493] Exit:[SQL_SUCCESS] Output Handle = 0x7ffda6b74000 [ODBC][2474][1422469178.558755][SQLSetConnectOption.c][345] Entry: Connection = 0x7ffda6b74000 Option = SQL_ATTR_LOGIN_TIMEOUT Value = 15 [ODBC][2474][1422469178.558791][SQLSetConnectOption.c][508] Exit:[SQL_SUCCESS] [ODBC][2474][1422469178.558828][SQLSetConnectOption.c][345] Entry: Connection = 0x7ffda6b74000 Option = SQL_ATTR_ACCESS_MODE Value = 1 [ODBC][2474][1422469178.558859][SQLSetConnectOption.c][508] Exit:[SQL_SUCCESS] [ODBC][2474][1422469178.558900][SQLDriverConnect.c][726] Entry: Connection = 0x7ffda6b74000 Window Hdl = 0x1 Str In = [DSN=connect_test;UID=sa;PWD=********][length = 36 (SQL_NTS)] Str Out = 0x7ffd92800090 Str Out Max = 512 Str Out Ptr = 0x7ffdc285ea50 Completion = 0 UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE' DIAG [08S01] [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist DIAG [S1000] [FreeTDS][SQL Server]Unable to connect to data source [ODBC][2474][1422469178.585108][SQLDriverConnect.c][1353] Exit:[SQL_ERROR]