These days I tried to configure ODBC to access MySQL from Oracle 10g, and I believed all of my configurations were correct, while when I run a select statement from Oracle database, I always got below error:
SQL> select * from "testa"@mysql;
select * from "testa"@mysql
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC]DRV_InitTdp: errors.h (2112): ; [MySQL][ODBC
5.2(a) Driver]Access denied for user 'root'@'localhost' (using password: YES)
(SQL State: S1000; SQL Code: 1045)
ORA-02063: preceding 2 lines from MYSQLIt seemed maybe the password was not correct, but I already confirmed more than two times and even copied the password.
Below were some key files for the ODBC configuration:
ora10g@olinux511[/oracle/product/ora10g]$ cat /usr/local/etc/odbcinst.ini [MySQL ODBC 5.2] Driver=/usr/local/lib/libmyodbc5a.so UsageCount=1 ora10g@olinux511[/oracle/product/ora10g]$ cat ~/.odbc.ini [mysql] driver=MySQL ODBC 5.2 Database=test server=127.0.0.1 port=3306 option=3 User=root Password=My#DB ora10g@olinux511[/oracle/product/ora10g]$ isql -v mysql +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select * from testa; +-----------+ | id | +-----------+ | 1 | +-----------+ SQLRowCount returns 1 1 rows fetched SQL> quit
Using the isql command, I confirmed the ODBC configuration files were correct.
And about the Oracle ODBC configuration files:
ora10g@olinux511[/oracle/product/ora10g]$ cat /oracle/product/ora10g/network/admin/listener.ora
# listener.ora Network Configuration File: /oracle/product/ora10g/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/product/ora10g)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = mysql)
(ORACLE_HOME = /oracle/product/ora10g)
(PROGRAM = /oracle/product/ora10g/bin/hsodbc)
(ENV="LD_LIBRARY_PATH=/usr/lib:/oracle/product/ora10g/lib")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = olinux511.dbcloudsvc.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
ora10g@olinux511[/oracle/product/ora10g]$ cat /oracle/product/ora10g/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/product/ora10g/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
MYSQL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SID = mysql)
)
(HS = OK)
)
ora10g@olinux511[/oracle/product/ora10g]$ tnsping mysql
TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 20-JUN-2018 13:20:28
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SID = mysql)) (HS = OK))
OK (0 msec)Then I created a database link and did a test:
SQL> create database link mysql connect to "root" identified by "My#DB" using 'mysql';
Database link created.
SQL> select * from "testa"@mysql;
select * from "testa"@mysql
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC]DRV_InitTdp: errors.h (2112): ; [MySQL][ODBC
5.2(a) Driver]Access denied for user 'root'@'localhost' (using password: YES)
(SQL State: S1000; SQL Code: 1045)
ORA-02063: preceding 2 lines from MYSQLOn the same server, I had Oracle 11g installed so I did the same test on it.
The only difference was about the Listener configuration file:
ora11g@olinux511[/oracle/product/ora10g]$ cat /oracle/product/ora11g/network/admin/listener.ora
# listener.ora Network Configuration File: /oracle/product/ora10g/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/product/ora11g)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = mysql)
(ORACLE_HOME = /oracle/product/ora11g)
(PROGRAM = /oracle/product/ora11g/bin/dg4odbc)
(ENV="LD_LIBRARY_PATH=/usr/lib:/oracle/product/ora11g/lib")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = olinux511.dbcloudsvc.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)And I found it worked well on Oracle 11g:
SQL> create database link mysql connect to "root" identified by "My#DB" using 'mysql';
Database link created.
SQL> select * from "testa"@mysql;
id
----------
1
1 row selected.Very weird, right? It should be a bug in the Oracle 10g hsodbc program and it wasted me two nights to check the configurations.
Below is a workaround for me: change the password of the MySQL user and make sure not special characters included.
ora10g@olinux511[/oracle/product/ora10g]$ mysqladmin -u root password "QWE123asd" -p
Enter password:
ora10g@olinux511[/oracle/product/ora10g]$ vi ~/.odbc.ini
ora10g@olinux511[/oracle/product/ora10g]$ cat ~/.odbc.ini
[mysql]
driver=MySQL ODBC 5.2
Database=test
server=127.0.0.1
port=3306
option=3
User=root
Password=QWE123asd
ora10g@olinux511[/oracle/product/ora10g]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Jun 20 13:35:03 2018
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop database link mysql;
Database link dropped.
SQL> create database link mysql connect to "root" identified by "QWE123asd" using 'mysql';
Database link created.
SQL> select * from "testa"@mysql;
id
----------
1
SQL>So I changed the password from "My#DB" to "QWE123asd" and this issue was fixed! 
