DBLE Cache Mechanism: Primary‑Key Routing Issue, Diagnosis and Resolution

The article introduces DBLE, a widely used open‑source distributed database middleware, and focuses on its cache mechanism, especially the primary‑key routing cache that can cause query result anomalies after a primary‑key update.

Environment and version DBLE version: 2.19.11.99 MySQL version: 5.7.28 Sharded table: test (primary key mobile_no , sharding field user_code , hash sharding, 2 nodes).

Table definition create table test( mobile_no varchar(32) not null, user_code varchar(32) not null, PRIMARY KEY (mobile_no), INDEX idx_usercode(user_code) ) ENGINE=InnoDB CHARACTER SET=utf8mb4;

Reproducing the anomaly

1) Insert two rows so they reside on different nodes:

mysql> insert into test values(13600000001,0);

mysql> insert into test values(13800000002,1);

2) Query each primary key – both rows are returned correctly.

mysql> select * from test where mobile_no=13600000001;

+-------------+-----------+

| mobile_no   | user_code |

+-------------+-----------+

| 13600000001 | 0         |

+-------------+-----------+

mysql> select * from test where mobile_no=13800000002;

+-------------+-----------+

| mobile_no   | user_code |

+-------------+-----------+

| 13800000002 | 1         |

+-------------+-----------+

3) Update the primary key of the first row to the value of the second row (no conflict because they are on different shards) and query the set of both keys:

mysql> update test set mobile_no=13800000002 where user_code=0;

Query OK, 1 row affected

mysql> explain select * from test where mobile_no in (13600000001,13800000002);

| DATA_NODE | TYPE     | SQL/REF                                 |

| dn1       | BASE SQL | select * from test where mobile_no in (13600000001,13800000002) |

| dn2       | BASE SQL | select * from test where mobile_no in (13600000001,13800000002) |

mysql> select * from test where mobile_no in (13600000001,13800000002);

+-------------+-----------+

| 13800000002 | 0         |

| 13800000002 | 1         |

+-------------+-----------+

The result misses the row with mobile_no=13600000001 , indicating that the routing cache still points to the old node.

Root‑cause investigation

Using the DBLE management console (port 9066) to run show @@cache; reveals the TableID2DataNodeCache entry for the test table, showing that the primary‑key‑to‑node mapping has been cached (CUR=2, PUT=5). After the update, the cache is not refreshed, so subsequent queries are routed only to the node that still holds the cached mapping.

Disabling the primary‑key cache

Comment out the line #layedpool.TableID2DataNodeCache =encache,10000,18000 in cacheservice.properties or remove the cacheKey="mobile_no" attribute from schema.xml . After reloading the configuration, the same query routes to both nodes and returns the correct two rows.

DBLE cache types and limits

DBLE provides three cache categories:

SQLRouteCache : caches routing results of SQL statements.

ER_SQL2PARENTID : caches parent‑table shard information for foreign‑key inserts.

TableID2DataNodeCache : caches primary‑key‑to‑node mapping.

Cache configuration syntax is pool. =type,max_size,expire_seconds , where type can be ehcache , mapdb , leveldb , or rocksdb .

Conclusion

Proper use of DBLE’s caching can greatly improve performance, but developers must understand its caching triggers and limitations; otherwise stale cache entries may lead to incorrect query results, as demonstrated by the primary‑key routing issue.