Got ORA-28500 during ODBC configuration from Oracle 10g to MySQL

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 MYSQL

It 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 MYSQL

On 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!