SuperAPI AI Directory

obproxy-routing-troubleshooting

General & other

View repository

Updated 10-16 02:35

About this skill

OBProxy Routing and Connection Configuration Troubleshooting

Triggers and Boundaries

  • This SOP: OBProxy/ODP routing policies, weak read, read-only replicas, read/write splitting, tenant/cluster name resolution,proxyro、rs_list, OB 2.0 protocol, proxy-side memory and internal metadata query timeouts.
  • Does not replace: OBProxy log collection/in-package interpretation → obproxy-log-analysis; single SQL execution plan/locks → observer-sql-analysis。

Prerequisites

  • Be as specific as possible: OBProxy/ODP version, OceanBase version, connection string, whether multiple ODP/VIP, whether through 2883, whether within a transaction.
  • When configuration is needed, log in to root@proxysys, use SHOW proxyconfig / ALTER proxyconfig。
  • For multiple clusters pass cluster_config_path; when Observer-side facts are involved use db_query to query sys/tenant views.

Branches: Select the main path

Symptom Main path Source
decode varchar param value failed, many remote transactions Stage A: Partition key within the first 4KB of the SQL sop_001
Weak read hint still goes to leader, read/write splitting not effective Stage B: Weak read and proxy_route_policy sop_133, sop_213, sop_251, sop_252
cluster not exist、proxyro authentication failure Stage C: Tenant/cluster resolution and proxyro sop_041, sop_075, sop_095, sop_141
Proxy cannot connect after OBD deployment Stage D:rs_list sop_124
Single ODP ERROR 2013 Stage E: ODP memory and connection freeze sop_147
MySQL migration wants to connect using only username Stage F:enable_full_username sop_152
GET_LOCK / RELEASE_LOCK reports 1235 Stage G: OB 2.0 protocol sop_187
SHOW CREATE TABLE missing global index or local/global Stage H: DDL compatibility mode sop_203, sop_222
New table creation slow through proxy, fast when directly connected, many tablets Stage I: Proxy internal metadata query timeout sop_253
KILL reports Unknown thread id Stage J: Executing SHOW/KILL on the same ODP sop_139

Stage A: Partition Key and 4KB Parsing

OBProxy only parses routing information within the first 4KB of the SQL.INSERT When large fields such as longtext in decode varchar param value failedare placed before the partition key,

Action: Place partition key columns as early as possible in the column order, avoiding large fields before the partition key.

Stage B: Weak Read and Read/Write Splitting

The weak read hint only declares consistency; whether followers are prioritized also depends on the proxy routing policy.

-- 优先使用会话级或 SQL hint,避免先改全局
SET SESSION ob_read_consistency = 'weak';
SELECT /*+READ_CONSISTENCY(WEAK)*/ * FROM t1;

-- 代理侧优先 follower:会影响该 ODP 后面的客户端,变更前确认范围和回滚值
ALTER proxyconfig SET proxy_route_policy = 'follower_first';
  • proxy_route_policy When the default is an empty string, weak read may still go to the leader.
  • SET GLOBAL ob_read_consistency and ALTER proxyconfig both have global/ODP-level impact; prioritize reproducing with session/hint, and when changes are truly needed, record the old value, change scope, and rollback SQL.
  • In OceanBase 4.x, ob_proxy_readonly_transaction_routing_policy is deprecated.
  • A SELECT within a standard transaction cannot be routed to a follower separately; it is recommended to autocommit=1 perform a separate weak read under follower_first。
  • A explain route without WHERE goes to __all_dummy, showing a follower is designed behavior, do not misjudge.
  • You can use gv$sql_audit to see the actual svr_ip、plan_type, and check whether the partition key is missing.

Phase C: Cluster/Tenant Resolution and proxyro

Symptom Diagnosis and Action
ERROR 4669: cluster not exist, root can connect but business users cannot Shorten the naming when the full username/tenant/cluster string is too long.
cluster not exist and uses https config server Older OBProxy versions have incomplete support for obproxy_config_server_url=https ; switch to rslist/http or upgrade.
Modify proxyro then reading authorization packet In root@proxysys check the password entry for proxy_config , keep it consistent with the Observer sys tenant proxyro password; in OCP scenarios, disassociate, change the password, and re-associate.
proxyro@sys 1045 By default, proxyro login is prohibited; when needed ALTER PROXYCONFIG SET skip_proxyro_check='true';

Phase D:rs_list

When the Proxy deployed by OBD cannot connect but direct connection to Observer works, verify whether rs_list in the deployment configuration equals the actual OB node address; if incorrect, fix it and redeploy.

Phase E: Single ODP 2013

ERROR 2013 ... reading authorization packet occurs on only one ODP, check obproxy_error.log for 2013 and trace, then go to obproxy.log to check connection freeze / insufficient memory.

ALTER proxyconfig SET proxy_mem_limited = '4G';

Adjust according to actual memory; do not mechanically apply 4G.

Phase F: Connect to a fixed tenant with username only

Applicable to rslist mode, single-tenant proxy.

ALTER proxyconfig SET enable_full_username = 'false';
ALTER proxyconfig SET proxy_tenant_name = '<tenant_name>';

This ODP essentially connects only to the tenant specified by proxy_tenant_name ; multiple tenants require multiple ODPs.

Phase G:GET_LOCK and OB 2.0 protocol

When executing GET_LOCK / RELEASE_LOCK via 2883 reports ERROR 1235 , check whether the old ODP still enables single-leader routing and uses the MySQL protocol.

ALTER proxyconfig SET enable_ob_protocol_v2 = true;
ALTER proxyconfig SET enable_single_leader_node_routing = false;
ALTER proxyconfig SET client_session_id_version = 2;

ODP 4.3.1.4+ defaults are more reasonable; old values may still be retained after a hot upgrade.

Phase H:SHOW CREATE TABLE compatibility mode

When global indexes or local/global are not displayed via proxy SHOW CREATE TABLE , check _show_ddl_in_compat_mode and proxy init_sql。

SHOW proxyconfig LIKE 'init_sql';
ALTER proxyconfig SET init_sql='set _show_ddl_in_compat_mode = 0;';

Multiple ODP nodes need to be set separately. You can also retry after SET _show_ddl_in_compat_mode = 0; in the session.

Phase I: Creating tables via proxy is slow

With an excessive number of tablets, internal OBProxy queries to virtual tables such as __all_virtual_proxy_sub_partition may exceed short_async_task_timeout。

ALTER proxyconfig SET short_async_task_timeout = '20s';
CALL DBMS_STATS.GATHER_TABLE_STATS('oceanbase', '__all_virtual_tablet_to_ls', method_opt=>'for all columns size 1');
CALL DBMS_STATS.GATHER_TABLE_STATS('oceanbase', '__all_virtual_proxy_sub_partition', method_opt=>'for all columns size 1');

You can also upgrade OBProxy.

Phase J: KILL under multiple ODPs

When OBProxy < 4.3.0 and multiple ODPs + VIP,SHOW PROCESSLIST and KILL must land on the same ODP. First confirm the ODP where the session is located, then execute via the same ODP KILL。

Output format

  1. Conclusion table: symptom, matched branch, key parameters/SQL, source SOP.
  2. Executable actions: list the ALTER proxyconfig, session variables, SQL rewrites, or deployment fixes that need to be executed.
  3. Boundary notes: note the transaction context, version differences, and whether multiple ODP nodes need to be handled one by one.

Self-check

  • Does the weak-read issue distinguish READ_CONSISTENCY(WEAK) and proxy_route_policy='follower_first'?
  • cluster not exist Are overly long naming, https config server, rslist, and fixed tenant investigated separately?
  • SHOW CREATE TABLE Does the issue avoid being misjudged as index loss?
  • When only OBProxy logs need to be viewed, switch to obproxy-log-analysis?