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, useSHOW proxyconfig/ALTER proxyconfig。 - For multiple clusters pass
cluster_config_path; when Observer-side facts are involved usedb_queryto 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_policyWhen the default is an empty string, weak read may still go to the leader.SET GLOBAL ob_read_consistencyandALTER proxyconfigboth 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_policyis deprecated. - A SELECT within a standard transaction cannot be routed to a follower separately; it is recommended to
autocommit=1perform a separate weak read underfollower_first。 - A
explain routewithout WHERE goes to__all_dummy, showing a follower is designed behavior, do not misjudge. - You can use
gv$sql_auditto see the actualsvr_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
- Conclusion table: symptom, matched branch, key parameters/SQL, source SOP.
- Executable actions: list the
ALTER proxyconfig, session variables, SQL rewrites, or deployment fixes that need to be executed. - 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)andproxy_route_policy='follower_first'? cluster not existAre overly long naming, https config server, rslist, and fixed tenant investigated separately?SHOW CREATE TABLEDoes the issue avoid being misjudged as index loss?- When only OBProxy logs need to be viewed, switch to
obproxy-log-analysis?