A field note on migrating the platform's storage layer from MySQL to KingbaseES: compatibility mode, sequences, case and collation, plan differences, performance re-testing, and a rollback plan — with a checklist.
In domestic IT replacement projects, the database is one of the hardest components to swap. Before an observability platform can even be delivered, it has to clear the storage hurdle: can the metadata, configuration, and alert-history stores run on KingbaseES? This article records how we moved the Jujing OBSERVE storage layer from MySQL to KingbaseES, and the traps we hit along the way.
KingbaseES ships a MySQL compatibility mode. Initialize with --case-insensitive and the MySQL-compatible parameters, and most INSERT, SELECT, and JOIN scenarios work unchanged. The first step of a migration is not rewriting SQL — it is getting the driver connected with the right compatibility mode.
On the driver side, KingbaseES provides an official JDBC driver, and you can also connect over the PostgreSQL protocol with a standard driver. In our Go services we use pgx directly and point search_path at the business schema to avoid a pile of prefixes.
We migrated structure first, then data. The official migration tool exports the MySQL schema, which we convert into KingbaseES DDL and execute; then we load data in bulk — a few thousand rows at a time — because row-by-row inserts on a large table can run all day. MySQL stays online during the move, and both sides write concurrently for a while so we can compare results; we only cut over traffic once they match.
The first is auto-increment primary keys. KingbaseES simulates auto-increment with sequences, so after migrating MySQL's AUTO_INCREMENT you must create sequences manually and bind defaults — otherwise inserts fail with "column cannot be null." We scripted every table's primary key into serial or an explicit sequence, and named sequences with a table_column_seq convention to keep maintenance simple.
The second is case. Even with compatibility mode on, unquoted identifiers still fold to lowercase in KingbaseES, while MySQL preserves them as-is. Before migrating, normalize all table and column names to lowercase with underscores and drop any camelCase. Then double-check your ORM field mappings — you can end up with "the table exists but the column won't read."
The third is charset and collation. KingbaseES's default collation differs from MySQL's utf8mb4_general_ci, so fuzzy queries like LIKE '%中文%' can return different results. Specify --encoding=UTF8 and pick the right collation at init, then re-run the same query set to compare before going live.
KingbaseES plans queries differently from MySQL, and the same complex query can choose a different index. After migration we ran EXPLAIN on every slow query and added composite indexes to a few hot tables; query latency dropped from three times the original to roughly even. Do not assume your MySQL-era index design still applies — re-checking the execution plan is more valuable than anything else.
Watch the connection count too. KingbaseES ships a low max_connections by default, while the platform's collectors, alert engine, and API all open connection pools and can hit the limit. Raise max_connections at deploy time and tighten each pool's max_idle to match, otherwise peak hours turn into a queue of "waiting for a connection."
The MySQL compatibility mode covers most cases, but not all. The edges we hit included some stored-procedure syntax, behavioral differences in JSON functions, and the relaxed GROUP BY behavior (ONLY_FULL_GROUP_BY). Before migrating, run a real SQL inventory and go through every function, stored procedure, and trigger item by item — don't wait until go-live to hit one. Whatever genuinely does not translate, rewrite it in native KingbaseES style; it ends up more stable anyway.
A migration must keep an exit path. We kept MySQL as the rollback point until the dual-run period ended and a week passed without anomalies, then took it offline. On the KingbaseES side we run a logical backup daily so we can restore to a point in time if something goes wrong.
The checklist:
Once KingbaseES runs cleanly, one hard item drops off the platform's domestic-component list. Database adaptation has no shortcuts, but the traps are fixed — hit them once and you can write the SOP.