Oceanbase的变量参数ob_query_timeout不适用于obdumper？

by

ob_query_timeout 是 OceanBase 数据库服务器的一个系统变量（System Variable）。它设置在数据库服务端，用于控制在该数据库连接上执行的所有 SQL 查询的最大执行时间。obdumper 是一个独立的客户端工具，它通过 OBDC 或 Java 驱动与数据库建立连接。理论来说也是一个客户端，应该是继承DB的变量设置，但是近期一客户obdumper一提示query timeou超时，且值并非DB级的参数，这是怎么回事？

obdumper导出报错如下：

$ tail data_par/logs/ob-loader-dumper.error
2025-10-24 05:02:26 [ERROR] Error: SQLTransientConnectionException: Timeout, query has reached the maximum query timeout: 300000000(us), maybe you can adjust the session variable ob_query_timeout or query_timeout hint, and try again.

数据库的变量参数查询

obclient(root@sys)[oceanbase]> show variables like 'ob_%_timeout%';
+---------------------+------------------+
| Variable_name       | Value            |
+---------------------+------------------+
| ob_pl_block_timeout | 3216672000000000 |
| ob_query_timeout    | 10000000         |
| ob_trx_idle_timeout | 86400000000      |
| ob_trx_lock_timeout | -1               |
| ob_trx_timeout      | 86400000000      |
+---------------------+------------------+
5 rows in set (0.007 sec)

调整DB的变量参数，发现并没有在obdumper中生效

obclient配置
set global ob_query_timeout=36000000000;

obdumper 在设计上是一个自包含的JAVA应用程序，它拥有自己的一套参数体系来应对各种运维场景，特别是处理可能耗时很长的大数据量导出任务。直接依赖数据库端的 ob_query_timeout 是不可靠且不灵活的，如果因为数据库负载高导致某个复杂查询稍慢，就被服务端的 ob_query_timeout 断掉，会导致整个导出任务失败，这不符合导出工具的职责。obdumper 有自己专门的参数来控制超时行为，在{obdumper}/conf下的配置文件中。

查看obdumper的命令行参数

[admin@observer1 bin]$ ./obdumper -h
Neither the JAVA_HOME nor the JRE_HOME environment variable is defined (Oracle JDK 1.8.0_3xx+)
At least one of these environment variable is needed to run this program

[admin@observer1 ~]$ which java
/bin/java
[admin@observer1 ~]$ ls -l /bin/java
lrwxrwxrwx 1 root root 22 Oct 20 10:47 /bin/java -> /etc/alternatives/java

[admin@observer1 ~]$ cd /etc/alternatives
[admin@observer1 alternatives]$ ls
alt-java       jjs.1.gz           jre_1.8.0_openjdk_exports  keytool.1.gz          mta-mailq          mta-rmail        orbd          policytool.1.gz   servertool       unpack200.1.gz
alt-java.1.gz  jre                jre_exports                ld                    mta-mailqman       mta-sendmail     orbd.1.gz     rmid              servertool.1.gz
java           jre_1.8.0          jre_openjdk                libnssckbi.so.x86_64  mta-newaliases     mta-sendmailman  pack200       rmid.1.gz         tnameserv
java.1.gz      jre_1.8.0_exports  jre_openjdk_exports        mta                   mta-newaliasesman  ncman            pack200.1.gz  rmiregistry       tnameserv.1.gz
jjs            jre_1.8.0_openjdk  keytool                    mta-aliasesman        mta-pam            nmap             policytool    rmiregistry.1.gz  unpack200

[admin@observer1 alternatives]$ pwd
/etc/alternatives

[admin@observer1 alternatives]$ export JRE_HOME=/etc/alternatives

[admin@observer1 alternatives]$ cd /home/admin/oceanbase/ob-loader-dumper-4.2.8.2-RELEASE
[admin@observer1 ob-loader-dumper-4.2.8.2-RELEASE]$ cd bin


[admin@observer1 bin]$ ./obdumper -h
The JRE_HOME environment variable is NOT defined correctly as the "/etc/alternatives/bin/java" is not found

[admin@observer1 bin]$ ./obdumper -h
Neither the JAVA_HOME nor the JRE_HOME environment variable is defined (Oracle JDK 1.8.0_3xx+)
At least one of these environment variable is needed to run this program

[admin@observer1 ~]$ which java
/bin/java
[admin@observer1 ~]$ ls -l /bin/java
lrwxrwxrwx 1 root root 22 Oct 20 10:47 /bin/java -> /etc/alternatives/java

[admin@observer1 ~]$ cd /etc/alternatives
[admin@observer1 alternatives]$ ls
alt-java       jjs.1.gz           jre_1.8.0_openjdk_exports  keytool.1.gz          mta-mailq          mta-rmail        orbd          policytool.1.gz   servertool       unpack200.1.gz
alt-java.1.gz  jre                jre_exports                ld                    mta-mailqman       mta-sendmail     orbd.1.gz     rmid              servertool.1.gz
java           jre_1.8.0          jre_openjdk                libnssckbi.so.x86_64  mta-newaliases     mta-sendmailman  pack200       rmid.1.gz         tnameserv
java.1.gz      jre_1.8.0_exports  jre_openjdk_exports        mta                   mta-newaliasesman  ncman            pack200.1.gz  rmiregistry       tnameserv.1.gz
jjs            jre_1.8.0_openjdk  keytool                    mta-aliasesman        mta-pam            nmap             policytool    rmiregistry.1.gz  unpack200

[admin@observer1 alternatives]$ pwd
/etc/alternatives

[admin@observer1 alternatives]$ export JRE_HOME=/etc/alternatives

[admin@observer1 alternatives]$ cd /home/admin/oceanbase/ob-loader-dumper-4.2.8.2-RELEASE
[admin@observer1 ob-loader-dumper-4.2.8.2-RELEASE]$ cd bin


[admin@observer1 bin]$ ./obdumper -h
The JRE_HOME environment variable is NOT defined correctly as the "/etc/alternatives/bin/java" is not found

[admin@observer1 bin]$ java -version
openjdk version "1.8.0_412"
OpenJDK Runtime Environment (build 1.8.0_412-b08)
OpenJDK 64-Bit Server VM (build 25.412-b08, mixed mode)

没有找到oracle java的安装，目标结构不同，本机有套openjdk ，临时投机取巧一下。

[root@observer1 ~]# cd /etc/alternatives
[root@observer1 alternatives]# mkdir bin
[root@observer1 alternatives]# cd bin
[root@observer1 bin]# pwd
/etc/alternatives/bin
[root@observer1 bin]# ln -s ../java ./java
[root@observer1 bin]# ll
total 0
lrwxrwxrwx 1 root root 7 Oct 24 11:34 java -> ../java


[admin@observer1 bin]$ export JRE_HOME=/etc/alternatives

[admin@observer1 bin]$ ./obdumper --help
   ____                                  ____
  / __ \  _____  ___   ____ _   ____    / __ )  ____ _   _____  ___
 / / / / / ___/ / _ \ / __ `/  / __ \  / __  | / __ `/  / ___/ / _ \
/ /_/ / / /__  /  __// /_/ /  / / / / / /_/ / / /_/ /  (__  ) /  __/
\____/  \___/  \___/ \__,_/  /_/ /_/ /_____/  \__,_/  /____/  \___/

Note: 篇幅原因不再展示，obdumper是独立的应用，可以单独下载，建议使用更新的版本， 里面会有一些优化参数，如在4.3.2中有–mem 4g 选项可以指定内存，防止JVM内存不足，可以-Denable.parallel.write=true 开启并行写功能，导出后会将文件合并成单文件， 同时新版本还有可能带来读取文件性能优化的提升。

obdump的配置文件

[admin@observer1 ob-loader-dumper-4.2.8.2-RELEASE]$ cd conf
[admin@observer1 conf]$ ls
cli.properties  decrypt.properties  log4j2.xml  log4j.properties  session.config.json
[admin@observer1 conf]$ pwd
/home/admin/oceanbase/ob-loader-dumper-4.2.8.2-RELEASE/conf
[admin@observer1 conf]$ cat session.config.json
{
  "init_sql": {
    "oracle": [
      "set names utf8",
      "set autocommit=1",
      "set session ob_query_timeout=300000000",
      "set session ob_trx_timeout=180000000",
      "set session net_read_timeout=86400",
      "set session net_write_timeout=86400",
      "set session sql_select_limit=9223372036854775807",
      "alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'",
      "alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS:FF9'",
      "alter session set nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS.FF TZR TZD'"
    ],
    "mysql": [
      "set names utf8",
      "set autocommit=1",
      "set session ob_query_timeout=300000000",
      "set session ob_trx_timeout=180000000",
      "set session net_read_timeout=86400",
      "set session net_write_timeout=86400",
      "set session sql_select_limit=9223372036854775807"
    ]
  },
  "jdbc_url_options": {
    "characterEncoding": "utf8",
    "socketTimeout": 1800000,
    "connectTimeout": 1800000,
    "zeroDateTimeBehavior": "convertToNull",
    "useServerPrepStmts": true,
    "noDatetimeStringSync": true,
    "useCompression": true,
    "log": false,
    "allowMultiQueries": true,
    "useLocalSessionState": true,
    "cachePrepStmts": true,
    "useSqlStringCache": true,
    "extendOracleResultSetClass": true,
    "useLobLocatorV2": false
  },
  "jdbc_hamode": null,
  "direct_path_load": {
    "rpc_connect_timeout": "15000",
    "rpc_execute_timeout": "20000",
    "runtime_retry_times": "5",
    "runtime_retry_intervals": "50",
    "task_timeout": "2592000000000",
    "heartbeat_timeout": "60000000"
  }
}

Note： 这里就有我们obdumper默认带的参数，里面有session级变量的修改ob_query_timeout。

本案例增加该参数即可。

— over —

Leave a Comment