Improving MyCat Pitfalls with the Distributed Middleware DBLE: Technical Review and Comparison

The article begins with a background on the rapid growth of data‑intensive applications such as mobile internet, IoT, and AI, which have driven the need for sharding and distributed database architectures. It explains why middleware is essential to hide sharding complexity from application developers.

Four ideal characteristics of a middleware are described: transparency (hiding partition details), compatibility (behaving like native MySQL), performance (acceptable latency and higher throughput), security (preserving password policies), and operability (providing management tools).

DBLE, a new distributed middleware built on lessons from MyCat, is introduced. Its architecture consists of protocol, parsing, routing, and execution modules. The article then compares DBLE and MyCat from DBA and developer perspectives.

SQL correctness examples show that MyCat loses rows in range queries due to incorrect routing, mishandles aggregate functions, and fails on UNION/UNION ALL and sub‑queries, while DBLE returns correct results. Example queries are wrapped in select * from employee where id between 511 and 1791 order by id and select id from hotnews union select id from travelrecord .

Join handling is examined: MyCat offers three work‑arounds—global tables, ER tables, and hints—but each has limitations such as inability to handle multi‑table joins, complex join conditions, or high intrusiveness. DBLE’s approach avoids these pitfalls.

Data‑manipulation tests reveal that MyCat requires explicit column lists for INSERT and enforces a custom nextvalue for ... syntax, whereas DBLE accepts standard MySQL INSERT syntax and generates auto‑increment values automatically.

Session variable handling is contrasted: MyCat silently accepts arbitrary SET statements (e.g., SET you=me ) and does not enforce read‑only mode, while DBLE correctly validates and rejects unsupported variables.

From an operations standpoint, MyCat allows any database user to access the management console, posing security risks. DBLE introduces role‑based access control, preventing ordinary users from performing dangerous admin actions.

The article also critiques MyCat’s code quality, highlighting poor bug‑fix implementations, leftover unfinished code paths, copied‑and‑pasted modules, and misleading success responses. Specific examples include a buggy fix for issue #1194 and a switch‑case that omits buffer‑pool initialization, leading to startup failures.

Finally, DBLE’s development process is described: automated static analysis, code‑style checks, continuous integration with Travis CI, behavior‑driven tests using Behave, and coverage tools ensure higher reliability and maintainability.

The piece concludes by inviting readers to follow the DBLE open‑source series for deeper dives into correctness, security, stability, operability, and code‑quality evaluation of open‑source middleware.