SunKing's Blog

留言簿(42)

Java链

友链

阅读排行榜

评论排行榜

Oracle通过DbLink远程访问MySQL数据库BLOB字段的问题调查

昨天帮XW调查了一个Oracle通过DbLink远程访问MySQL数据库的问题,记录一笔
问题:
     Oracle通过DbLink远程访问MySQL数据库时,查询SELECT一张带BLOB字段表时,
     该BLOB字段值为NULL时正常,非NULL时报ORA-28500错误。

调查过程:
数据库环境
     本机ORACLE10.2.0,SID为ORCL
     远程MySQL5.0,DB为test
创建DbLink
1.下载并安装 mysql-connector-odbc-5.1.5-win32.msi
2.MySQL开启远程访问权限
     GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'rootpwd' WITH GRANT OPTION;
3. ODBC中系统DSN中创建驱动MySQL ODBC 5.1 Driver的配置:
         DataSource:test
         Server:192.168.1.9
         Port:3306
         User:root
         Password:rootpwd
         Database:test
    并测试成功
4.停止ORACLE的服务,并做如下配置
D:\oracle\product\10.2.0\db_1\hs\admin目录下添加文件initmd35.ora,内容如下:
         HS_FDS_CONNECT_INFO = test
         HS_FDS_TRACE_LEVEL = ON
修改D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora,修改后内容如下:
         SID_LIST_LISTENER =
           (SID_LIST =
             (SID_DESC =
               (GLOBAL_DBNAME = ORCL)
               (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
               (SID_NAME = ORCL)
             )
             (SID_DESC =
               (SID_NAME = test)
               (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
               (PROGRAM = hsodbc)
             )
           )

         LISTENER =
           (DESCRIPTION =
             (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
           )
修改D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora,修改后内容如下:
         ORCL =
           (DESCRIPTION =
             (ADDRESS_LIST =
               (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
             )
             (CONNECT_DATA =
               (SERVER = DEDICATED)
               (SERVICE_NAME = orcl)
             )
           )
         test =
             (DESCRIPTION =
                 (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) 
                     (CONNECT_DATA = (SID = test) )
                    (HS = OK)
             )
5.重启ORACLE的服务
6.创建DbLink
         create public database link test
             connect to "root" identified by "rootpwd"
             using '(DESCRIPTION =
             (ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT =1521) )
             (CONNECT_DATA = (SID = test) )
             (HS=OK)
             )';
tnsping test测试通过,我用网上很多人说的
         create public database link test
             connect to "root"
             identified by "rootpwd"
             using 'test';
没能成功,也没去追究为什么。报的错是:
         第 1 行出现错误:
         ORA-28545: 连接代理时 Net8 诊断到错误
         Unable to retrieve text of NETWORK/NCR message 65535
         ORA-02063: 紧接着 2 lines (起自 MD35)

调查过程:
在MySQL创建了一张带BLOB字段的表
         CREATE TABLE `TEST_BLOB` (
                 `AA` VARCHAR( 10 ) DEFAULT '1' NOT NULL ,
                 `BB` BLOB
         );
   安装mysql-gui-tools-5.0-r17-win32.msi并用MySQL Query Browser插入了一条BB字段为空的数据
   执行select "BB" from "TEST_BLOB"@test; 报错,说是有BLOB字段必须要有一个唯一属性字段,
   把AA字段删除,并添加一个属性INT(11)的id字段,并设成主键, 查询成功。
   上传了一个文件到MySQL中TEST_BLOB表的BB字段中,
   再执行select "BB" from "TEST_BLOB"@test;再现了错误ORA-28500
                 ORA-28500: 连接 ORACLE 到非 Oracle 系统时返回此信息:
                  [Generic Connectivity Using ODBC]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"TEST_BLOB" WHERE "id"=1' at line 1 (SQL State: 37000; SQL Code: 1064) 
                 ORA-02063: 紧接着2 lines(源于MYSQL)
    OK,接下来开始调查原因:
    开始一直以为ORACLE就根本不支持远程传送BLOB字段,因为网上很多人都说解决不了这个问题,
    但有一篇关于ORACLE通过DbLink调用SQLServer的BLOG字段的贴子,居然可以成功执行
             insert into foo select blobcolumn from remoteTable@dl_remote where rownum = 1;
     而类似的insert into...select和create table...as select的语句,我执行都报失败,
     调查过程中多次报诸如  ORA-00997: 非法使用 LONG 数据类型  等错误;
     很奇怪为什么SQLServer行而MySQL却不行,
     再仔细看ORA-28500错误消息里有一句[You have an error in your SQL syntax],明显是语句错误,
     并且错误发生在 '"TEST_BLOB" WHERE "id"=1 ,这个WHERE "id" = 1我从来就没有写过,怪事,
     难道是MySQL中执行的SQL文是已经修改过的SQL,有了这个想法以后,一切都好办了,
     打开MySQL的LOG功能以后,再执行select "BB" from "TEST_BLOB"@test; 发现MySQL的LOG中执行的SQL语句是
         091029 17:59:35       5 Query       SELECT `A1`.`id` AS c001 FROM  `TEST_BLOB` `A1`
                                           5 Query       SELECT `A1`.`id` AS c001 FROM  `TEST_BLOB` `A1`
                                           5 Query       SELECT "BB" FROM "TEST_BLOB" WHERE "id"=1
     想想应该还有一些语句的,不然怎么会查出有id字段,只不过是LOG中没显示出来罢了,不管
     出错的是最后一句,原因是表名由双引号括起来在MySQL中执行不正确,前面两句是单引号括起来没问题
     而SQLSERVER中双引号把表名括起来是可以正常执行的,这应该就是DbLink到SQLSERVER正常而MySQL不正常的原因吧
     再深究这条带双引号的SQL文是由ORACLE发出的,还是ODBC发出的,
     通过使用自己做的TcpMonitor小工具,发现是由ORACLE发出的,
     没辙了,ORACLE的BUG,到止为止。

posted on 2009-10-30 12:00 SunKing's Blog 阅读(5711) 评论(0)  编辑  收藏


只有注册用户登录后才能发表评论。


网站导航: