Migrating from MyCat to DBLE: Parameter Settings, Sharding Functions, and SQL Compatibility

Background – The client environment experienced several issues and decided to migrate from MyCat to DBLE with minimal changes.

Problem List – The migration required attention to parameter settings, sharding functions, data node data, and business SQL compatibility.

1. Parameter Settings

MyCat side parameters:

<system>
<property name="defaultSqlParser">druidparser</property>
<property name="processors">4</property>
<property name="processorBufferPool">20480000</property>
<property name="processorBufferLocalPercent">100</property>
<property name="frontSocketSoRcvbuf">10485760</property>
<property name="frontSocketSoSndbuf">41943040</property>
<property name="frontSocketNoDelay">1</property>
<property name="backSocketSoRcvbuf">41943040</property>
<property name="backSocketSoSndbuf">10485760</property>
<property name="backSocketNoDelay">1</property>
<property name="maxPacketSize">2048576000</property>
<property name="memoryPageSize">100m</property>
</system>

DBLE recommendations: the properties defaultSqlParser , memoryPageSize , and processorBufferLocalPercent are deprecated and can be omitted; processorBufferPool is renamed to bufferPoolPageSize ; other parameters can remain unchanged in server.xml (see DBLE docs).

Note – MyCat and DBLE manage memory differently; DBLE lacks a thread‑local concept, so consult the DBLE memory manager documentation.

2. Sharding Function

MyCat sharding rule example:

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">8</property>
</function>

Testing showed that DBLE’s default hash algorithm fails for values exceeding the LONG range (e.g., 20210810143211157000000000036 ). The recommendation is to replace the algorithm with HashString or use a custom DBLE algorithm that mirrors MyCat’s PartitionByMod implementation.

Custom function example:

<function name="test_func" class="com.actiontech.dble.custom.sharding.algorithm.PartitionByMod">
    <property name="count">8</property>
</function>

3. Data Node Data – By using DBLE’s custom sharding function, the original MyCat data node configuration can remain unchanged, greatly reducing migration effort.

4. Business SQL

MyCat often pushes SQL directly to backend nodes, causing compatibility problems; DBLE disables many of these SQL patterns for correctness but re‑opens some with careful control. Specific supported/unsupported cases include:

Support for INSERT INTO … SELECT … on vertical tables.

Row‑number passthrough for designated tables.

UPDATE with sub‑queries is not supported due to distributed transaction and broadcast execution issues.

Global‑table + sharding‑table SQL handling has been optimized in DBLE 2.20.04.x.

For more enhancements made by DBLE to MyCat, see the linked documentation.