Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Not a Bug
-
Affects Version/s: 10.0.4
-
Fix Version/s: None
-
Component/s: None
-
Labels:
-
Environment:Debian 7.1 and CentOS 6.4
Description
I am trying to connect to SQL Server 2008 R2 using the connect engine and running mysql from linux
I have tried on two different platforms to make this work
Debian 7.1 64-bit and CentOS 6.4 64 bit.
############
Debian 7.1 details
Linux db149 3.2.0-4-amd64 #1 SMP Debian 3.2.46-1+deb7u1 x86_64 GNU/Linux
relevant packages installed:
ii libmariadbclient18 10.0.4+maria-1~wheezy amd64 MariaDB database client library ii libmysqlclient18 10.0.4+maria-1~wheezy amd64 Virtual package to satisfy external depends ii mariadb-client-10.0 10.0.4+maria-1~wheezy amd64 MariaDB database client binaries ii mariadb-client-core-10.0 10.0.4+maria-1~wheezy amd64 MariaDB database core client binaries ii mariadb-common 10.0.4+maria-1~wheezy all MariaDB database common files (e.g. /etc/mysql/conf.d/mariadb.cnf) iU mariadb-connect-engine-10.0 10.0.4+maria-1~wheezy all Connect storage engine for MariaDB iF mariadb-server-10.0 10.0.4+maria-1~wheezy amd64 MariaDB database server binaries ii mariadb-server-core-10.0 10.0.4+maria-1~wheezy amd64 MariaDB database core server files ii libodbc1:amd64 2.2.14p2-5 amd64 ODBC library for Unix ii odbcinst 2.2.14p2-5 amd64 Helper program for accessing odbc ini files ii odbcinst1debian2:amd64 2.2.14p2-5 amd64 Support library for accessing odbc ini files ii tdsodbc:amd64 0.91-2 amd64 ODBC driver for connecting to MS SQL and Sybase SQL servers ii unixodbc 2.2.14p2-5 amd64 Basic ODBC tools ii unixodbc-dev 2.2.14p2-5 amd64 ODBC libraries for UNIX (development files) ii freetds-bin 0.91-2+deb7u1 amd64 FreeTDS command-line utilities ii freetds-common 0.91-2 all configuration files for FreeTDS SQL client libraries
cat /etc/odbc.ini [MSSQLTestServer] Driver = FreeTDS Server = 192.168.1.20 Port = 1433
cat /etc/odbcinst.ini [FreeTDS] Description = TDS driver (Sybase/MS SQL) Driver = libtdsodbc.so Setup = libtdsS.so CPTimeout = CPReuse = Trace = yes TraceFile = /tmp/sql.log ForceTrace = Yes Database = master
Proof that odbc is working from linux to Sql server via the command line. Note that credentials have to be supplied. I have sanitized the username and password.
isql -v MSSQLTestServer user password +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select * from connect_table; +------------+ | id | +------------+ | 1001 | | 1002 | +------------+ SQLRowCount returns 2 2 rows fetched
Attempt to create connect table from within maria
(192.168.1.20:test:5)$ CREATE TABLE connect_table (
-> id int(10) NOT NULL
-> ) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=MSSQLTestServer' table_type=odbc block_size=10 tabname='connect_table';
Query OK, 0 rows affected (0.00 sec)
(192.168.1.20:test:6)$ select * from connect_table;
ERROR 1296 (HY000): Got error 174 '[unixODBC][FreeTDS][SQL Server]Unable to connect to data source' from CONNECT
(192.168.1.20:test:7)$
##################################
CentOs 6.4 details
CentOS release 6.4 (Final)
Linux maria1 2.6.32-358.el6.x86_64 #1 SMP Fri Feb 22 00:31:26 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
MariaDB-common-10.0.4-1.x86_64 MariaDB-connect-engine-10.0.4-1.x86_64 MariaDB-compat-10.0.4-1.x86_64 MariaDB-server-10.0.4-1.x86_64 MariaDB-client-10.0.4-1.x86_64 freetds-0.64-1.el6.rf.x86_64 unixODBC-2.2.14-12.el6_3.x86_64
cat /etc/odbc.ini [MSSQLTestServer] Driver = ODBC Driver 11 for SQL Server Server = 192.168.1.20 Port = 1433
cat /etc/odbcinst.ini [ODBC Driver 11 for SQL Server] Description=Microsoft ODBC Driver 11 for SQL Server Driver=/opt/lib/libmsodbcsql-11.0.so.2270.0 Threading=1 UsageCount=1
Ok on CentOS I first tried to setup Integrated Authentication from Linux to Microsoft SQL Server and I was successful.
I followed this instruction http://msdn.microsoft.com/en-us/library/hh568451.aspx
Here is the proof that it works.
./sqlcmd -E -S SVDWDBCLN1.sellingsource.local -d master
1> select * from connect_table;
2> go
id
-----------
1001
1002
(2 rows affected)
Now keep in mind that in prior test on debian i used freetds as my SQL Server driver.
Here is the result from MySQL Client
MariaDB [test]> CREATE TABLE `connect_table` (
-> `id` int(10) NOT NULL
-> ) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=MSSQLTestServer' `table_type`=odbc `block_size`=10 `tabname`='connect_table';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| connect_table |
+----------------+
1 row in set (0.00 sec)
MariaDB [test]> select * from connect_table;
ERROR 1296 (HY000): Got error 174 '[unixODBC][Driver Manager]Can't open lib '/opt/lib/libmsodbcsql-11.0.so.2270.0' : file not found' from CONNECT
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Olivier,
according to the FreeTDS documentation:
http://freetds.schemamania.org/userguide/odbcconnattr.htm
UID and PWD can only be on the connection string,
and cannot be used in odbc.ini in the DSN configuration.
It would be nice to have a new manual section with tips how to use various ODBC drivers.
Eric,
Have you solved all the problems you had? Can we close the issue?
Thanks for fast feedback!
Yes this problem is solved now. Thank you for the help.
However I am probably going to open up some other tickets because I am
having other issues. Like my where clause not being passed to SQL Server or
every time i do a query to SQL Server the first thing it does is a select
count
which takes a long time on the sql server side and I am getting
some random crashes when i do a select * from table ..
On Wed, Oct 23, 2013 at 11:31 PM, Alexander Barkov (JIRA) <
–
Eric H.
eric.hardway@gmail.com
To access a table via ODBC, CONNECT rephrases the original query and send it to the
data source. The issue is that the entered query if firstly parsed by MariaDB and
therefore must obey the MySQL syntax, while the rephrased query must obey the data
source syntax.
Because both are using SQL, this is generally not a problem. However, because some
details of the syntax can differ, for instance the name of scalar functions, the
rephrased query just ask the used columns values. For instance:
select upper(`first name`), `Salary` * 0.95 from `Employee`;
could be rephrased as:
SELECT "first name", "Salary" FROM "Employee"
The scalar function and expression will be locally calculated by MariaDB. See below
for similar restrictions regarding the WHERE clause.
Where clause not being passed to the data source:
-------------------------------------------------
There can be two causes:
1 - To be able to retrieve the where clause, the storage engine function cond_push
must be called by MariaDB. Since MariaDB 5.5 the engine condition pushdown is OFF by default.
It is therefore necessary to set it ON, for instance by:
set optimizer_switch='engine_condition_pushdown=on';
Or starting mysqld with this parameter set to ON, for instance:
mysqld --console --engine_condition_pushdown=on
Note 1: specifying -console is important to have some error messages from CONNECT printed
because MariaDB does not always retrieve them.
Note 2: since MariaDB 10.0.4, the CONDITION_PUSHDOWN argument is no more accepted.
However, it is no more needed because CONNECT uses condition pushdown unconditionally.
2 - The technique used above (getting only column values) obviously cannot be used here because
the where clause must be executed by the data source. This is why CONNECT extracts only the
"compatible" part of query WHERE clauses and add it to the ODBC query. Currently, clauses
containing scalar functions, expressions or subselect are not passed to the data source.
select Count from table
--------------------------
This was done to answer the info internal engine function. However, this info is not really
important and returning a fixed guess is enough. Therefore, this will be suppressed in the
next version of CONNECT.
Random crashes
--------------
This must be treated by bug reports giving all details allowing to trace the cause of them.
New version
-----------
The new version of CONNECT (in MariaDB 10.0.5) will have many new features addressing in
particular most of the issues listed above.
Eric, I'm closing this ticket.
Please file separate tickets for the remaining problems.
Thanks.
Can you please specify UID and PWD in the connection string on Debian, like this:
CREATE TABLE connect_table (
id int(10) NOT NULL
) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=MSSQLTestServer;UID=sa;PWD=sapwd' table_type=odbc block_size=10 tabname='connect_table';
Note, ConnectSE supports so called discovery, so in many cases it's ok not to specify table structure,
it should be detected automatically. Also, if the local and the remote table names are the same,
then no needs to specify the tabname option. This is the minimum possible create statetent:
CREATE TABLE connect_table ENGINE=CONNECT CONNECTION='DSN=MSSQLTestServer;UID=sa;PWD=passwd' table_type=odbc;