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

NeilZhang
NeilZhang
管理员
140
文章
106.8千
浏览
Oracle MySQL评论1,314字数 874阅读2分54秒阅读模式

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:

  1. SQL> select * from "testa"@mysql;
  2. select * from "testa"@mysql
  3.                       *
  4. ERROR at line 1:
  5. ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
  6. [Generic Connectivity Using ODBC]DRV_InitTdp: errors.h (2112): ; [MySQL][ODBC
  7. 5.2(a) Driver]Access denied for user 'root'@'localhost' (using password: YES)
  8. (SQL State: S1000; SQL Code: 1045)
  9. 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:

  1. ora10g@olinux511[/oracle/product/ora10g]$ cat /usr/local/etc/odbcinst.ini
  2. [MySQL ODBC 5.2]
  3. Driver=/usr/local/lib/libmyodbc5a.so
  4. UsageCount=1
  5.  
  6. ora10g@olinux511[/oracle/product/ora10g]$ cat ~/.odbc.ini
  7. [mysql]
  8. driver=MySQL ODBC 5.2
  9. Database=test
  10. server=127.0.0.1
  11. port=3306
  12. option=3
  13. User=root
  14. Password=My#DB
  15.  
  16. ora10g@olinux511[/oracle/product/ora10g]$ isql -v mysql
  17. +---------------------------------------+
  18. | Connected!                            |
  19. |                                       |
  20. | sql-statement                         |
  21. | help [tablename]                      |
  22. | quit                                  |
  23. |                                       |
  24. +---------------------------------------+
  25. SQL> select * from testa;
  26. +-----------+
  27. | id        |
  28. +-----------+
  29. | 1         |
  30. +-----------+
  31. SQLRowCount returns 1
  32. 1 rows fetched
  33. SQL> quit

Using the isql command, I confirmed the ODBC configuration files were correct.

And about the Oracle ODBC configuration files:

  1. ora10g@olinux511[/oracle/product/ora10g]$ cat /oracle/product/ora10g/network/admin/listener.ora
  2. # listener.ora Network Configuration File: /oracle/product/ora10g/network/admin/listener.ora
  3. # Generated by Oracle configuration tools.
  4.  
  5. SID_LIST_LISTENER =
  6.   (SID_LIST =
  7.     (SID_DESC =
  8.       (SID_NAME = PLSExtProc)
  9.       (ORACLE_HOME = /oracle/product/ora10g)
  10.       (PROGRAM = extproc)
  11.     )
  12.     (SID_DESC =
  13.       (SID_NAME = mysql)
  14.       (ORACLE_HOME = /oracle/product/ora10g)
  15.       (PROGRAM = /oracle/product/ora10g/bin/hsodbc)
  16.       (ENV="LD_LIBRARY_PATH=/usr/lib:/oracle/product/ora10g/lib")
  17.     )
  18.   )
  19.  
  20. LISTENER =
  21.   (DESCRIPTION_LIST =
  22.     (DESCRIPTION =
  23.       (ADDRESS = (PROTOCOL = TCP)(HOST = olinux511.dbcloudsvc.com)(PORT = 1521))
  24.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
  25.     )
  26.   )
  27.  
  28. ora10g@olinux511[/oracle/product/ora10g]$ cat /oracle/product/ora10g/network/admin/tnsnames.ora
  29. # tnsnames.ora Network Configuration File: /oracle/product/ora10g/network/admin/tnsnames.ora
  30. # Generated by Oracle configuration tools.
  31.  
  32. EXTPROC_CONNECTION_DATA =
  33.   (DESCRIPTION =
  34.     (ADDRESS_LIST =
  35.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
  36.     )
  37.     (CONNECT_DATA =
  38.       (SID = PLSExtProc)
  39.       (PRESENTATION = RO)
  40.     )
  41.   )
  42.  
  43. MYSQL =
  44.   (DESCRIPTION =
  45.     (ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1521))
  46.     (CONNECT_DATA =
  47.       (SID = mysql)
  48.     )
  49.     (HS = OK)
  50.   )
  51.  
  52. ora10g@olinux511[/oracle/product/ora10g]$ tnsping mysql
  53.  
  54. TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 20-JUN-2018 13:20:28
  55.  
  56. Copyright (c) 1997,  2010, Oracle.  All rights reserved.
  57.  
  58. Used parameter files:
  59.  
  60.  
  61. Used TNSNAMES adapter to resolve the alias
  62. Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SID = mysql)) (HS = OK))
  63. OK (0 msec)

Then I created a database link and did a test:

  1. SQL> create database link mysql connect to "root" identified by "My#DB" using 'mysql';
  2.  
  3. Database link created.
  4.  
  5. SQL> select * from "testa"@mysql;
  6. select * from "testa"@mysql
  7.                       *
  8. ERROR at line 1:
  9. ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
  10. [Generic Connectivity Using ODBC]DRV_InitTdp: errors.h (2112): ; [MySQL][ODBC
  11. 5.2(a) Driver]Access denied for user 'root'@'localhost' (using password: YES)
  12. (SQL State: S1000; SQL Code: 1045)
  13. 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:

  1. ora11g@olinux511[/oracle/product/ora10g]$ cat /oracle/product/ora11g/network/admin/listener.ora
  2. # listener.ora Network Configuration File: /oracle/product/ora10g/network/admin/listener.ora
  3. # Generated by Oracle configuration tools.
  4.  
  5. SID_LIST_LISTENER =
  6.   (SID_LIST =
  7.     (SID_DESC =
  8.       (SID_NAME = PLSExtProc)
  9.       (ORACLE_HOME = /oracle/product/ora11g)
  10.       (PROGRAM = extproc)
  11.     )
  12.     (SID_DESC =
  13.       (SID_NAME = mysql)
  14.       (ORACLE_HOME = /oracle/product/ora11g)
  15.       (PROGRAM = /oracle/product/ora11g/bin/dg4odbc)
  16.       (ENV="LD_LIBRARY_PATH=/usr/lib:/oracle/product/ora11g/lib")
  17.     )
  18.   )
  19.  
  20. LISTENER =
  21.   (DESCRIPTION_LIST =
  22.     (DESCRIPTION =
  23.       (ADDRESS = (PROTOCOL = TCP)(HOST = olinux511.dbcloudsvc.com)(PORT = 1521))
  24.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
  25.     )
  26.   )

And I found it worked well on Oracle 11g:

  1. SQL> create database link mysql connect to "root" identified by "My#DB" using 'mysql';
  2.  
  3. Database link created.
  4.  
  5. SQL> select * from "testa"@mysql;
  6.  
  7.         id
  8. ----------
  9.          1
  10.  
  11. 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.

  1. ora10g@olinux511[/oracle/product/ora10g]$ mysqladmin -u root password "QWE123asd" -p
  2. Enter password:
  3. ora10g@olinux511[/oracle/product/ora10g]$ vi ~/.odbc.ini
  4. ora10g@olinux511[/oracle/product/ora10g]$ cat ~/.odbc.ini
  5. [mysql]
  6. driver=MySQL ODBC 5.2
  7. Database=test
  8. server=127.0.0.1
  9. port=3306
  10. option=3
  11. User=root
  12. Password=QWE123asd
  13. ora10g@olinux511[/oracle/product/ora10g]$ sqlplus "/as sysdba"
  14.  
  15. SQL*Plus: Release 10.2.0.5.0 - Production on Wed Jun 20 13:35:03 2018
  16.  
  17. Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
  18.  
  19.  
  20. Connected to:
  21. Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
  22. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  23.  
  24. SQL> drop database link mysql;
  25.  
  26. Database link dropped.
  27.  
  28. SQL> create database link mysql connect to "root" identified by "QWE123asd" using 'mysql';
  29.  
  30. Database link created.
  31.  
  32. SQL> select * from "testa"@mysql;
  33.  
  34.         id
  35. ----------
  36.          1
  37.  
  38. SQL>

So I changed the password from "My#DB" to "QWE123asd" and this issue was fixed! Got ORA-28500 during ODBC configuration from Oracle 10g to MySQL

 
  • 本文由 NeilZhang 发表于21/06/2018 01:38:23
  • Repost please keep this link: https://www.dbcloudsvc.com/blogs/oracle/got-ora-28500-during-odbc-configuration-from-oracle-10g-to-mysql/
mysql.user table is damaged. Please run mysql_upgrade

加载中...

匿名

发表评论

匿名网友
:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:
确定