Initialization Guide

The directories contain initialization scripts for MySQL, OceanBase in MySQL mode, Oracle, PostgreSQL, and openGauss.

To Shenyu Users

  • mysql/schema.sql

    The canonical initialization script for both MySQL and OceanBase in MySQL mode.

    For OceanBase, execute this script against a MySQL-mode tenant with an account allowed to create the shenyu database. For example, using obclient:

    obclient -h <host> -P <port> -u '<user>@<tenant>' -p < db/init/mysql/schema.sql

    Start ShenYu Admin with the ob Spring profile (--spring.profiles.active=ob) and configure application-ob.yml for the tenant. Keep the oceanbase database dialect, jdbc:oceanbase:// URL and com.oceanbase.jdbc.Driver. This script is for fresh installations and recreates tables. Existing OceanBase installations must use their version-specific *-ob.sql scripts in db/upgrade. OceanBase Oracle mode is outside the scope of this shared MySQL initialization.

  • oracle/schema.sql

    The file is the Oracle initialization script.

  • pg/create-database.sql, pg/create-table.sql

    The canonical initialization scripts for PostgreSQL and openGauss.

    Create the shenyu database using pg/create-database.sql if it does not already exist, then execute pg/create-table.sql against that database. For example, for openGauss:

    gsql -h <host> -p <port> -U <user> -d postgres -v ON_ERROR_STOP=1 -f db/init/pg/create-database.sql
gsql -h <host> -p <port> -U <user> -d shenyu -v ON_ERROR_STOP=1 -f db/init/pg/create-table.sql

    PostgreSQL users can run the same files with psql instead of gsql. Enter the database password when prompted; see the gsql command reference. Start ShenYu Admin with the og Spring profile (--spring.profiles.active=og) for openGauss and configure application-og.yml. Keep the opengauss database dialect, jdbc:opengauss:// URL, org.opengauss.Driver, and openGauss MyBatis type handlers. PostgreSQL continues to use its pg profile and driver.

    The table script recreates tables and is only for fresh installations. Existing openGauss installations must use their version-specific *-og.sql scripts in db/upgrade; PostgreSQL installations use *-pg.sql.

Fresh-install scope

MySQL and OceanBase

This consolidation changes fresh MySQL installations as well as OceanBase, not only the OceanBase script path. In the canonical mysql/schema.sql, orphan permission seeds are removed, loggingKafka uses bootstrapServer instead of namesrvAddr, and its default-namespace configuration is aligned with the plugin configuration. Existing resource grants are retained. The upstream removal of SOFA/TARS resources and their associated seeds is preserved.

These are initialization defaults, not an upgrade migration. Do not re-run the initialization script against an existing database; continue to use the separate version-specific MySQL or OceanBase scripts in db/upgrade.

PostgreSQL and openGauss

This consolidation changes fresh PostgreSQL installations as well as openGauss, not only the openGauss script path. The canonical pg/create-table.sql removes orphan permissions, gives paramMapping and modifyResponse their custom rule-page defaults, adds the request rule-page handle, and puts Kafka security handles at selector scope. It also consolidates Dubbo‘s load-balancing handle, fixes loggingKafka’s bootstrapServer configuration, and aligns plugin/default-namespace configs, including aiProxy fallback defaults without the obsolete prompt field.

PostgreSQL's table definitions and constraints are retained. Existing resource grants remain available, and the upstream removal of SOFA/TARS resources and their associated seeds is preserved. These are fresh-install defaults, not changes to existing databases; historical PostgreSQL and openGauss upgrade scripts are unchanged.

Maintaining the shared MySQL/OceanBase schema

Update mysql/schema.sql for both engines. The native SQL matrix validates MySQL 8.0 and OceanBase 4.3.5 LTS; it does not establish compatibility with every version.

The shared source retains the MySQL scaling tables and permissions, MCP selector, namespace/lookup secondary indexes, and all common unique constraints. The old OceanBase permission/object, resource/parent and user-role/user indexes are covered by the shared indexes with the same leading columns. API-key permission IDs differ between the old copies, but their role/resource grants are identical.

Both engines now use the current aiProxy fallback fields and aiPrompt handles; the obsolete aiProxy prompt field is absent from AiProxyHandle. Token limiter dictionaries use the supported contextPath resolver instead of the old unsupported default entry. The loggingKafka plugin and default namespace use the same topic, sampling/body-size/compression defaults and the bootstrapServer field consumed by the Kafka plugin. Permissions pointing to absent resources have been removed from fresh seeds; every existing resource retains its admin grant.

Known cross-dialect difference: the H2 seed in shenyu-admin/src/main/resources/sql-script/h2/schema.sql still uses DEFAULT_KEY_RESOLVER (default) rather than CONTEXT_PATH_KEY_RESOLVER (contextPath). AiTokenLimiterEnum.getByName falls back to CONTEXT_PATH for the unsupported value. H2 dictionary alignment remains a separate follow-up; this consolidation does not change the H2 schema.

Historical upgrade scripts remain separate and unchanged. The Admin distribution packages the whole db directory, including this guide and the shared script.

Maintaining the shared PostgreSQL/openGauss schema

Update pg/create-table.sql for both engines. The native SQL matrix verifies PostgreSQL 15 and openGauss lite 5.0.1 using this complete shared source, without a compatibility overlay. Storage Compose and Kubernetes E2E setup copy the same file. The Admin distribution packages the whole db directory, so the shared script and this guide remain available in release packages.

The former copies differed in the following ways:

  • The discovery-upstream index now remains unique on (discovery_handler_id, upstream_url) for both fresh installations. The old openGauss index was non-unique despite its name. The proxy API-key mapping unique index, primary keys and lookup indexes are also retained.
  • PostgreSQL‘s tag.ext non-null constraint is retained; Admin always builds extension JSON when creating a tag. Discovery columns use PostgreSQL’s order; runtime mappers name their columns explicitly.
  • The keyAuth, tcp, basicAuth and mock resource menus and admin grants missing from the old openGauss copy are retained. API-key permission IDs use the PostgreSQL values; their role/resource grants were identical. Permissions targeting absent resources are removed. The aiRequestTransformer edit permission retains the correct spelling.
  • The openGauss custom rule-page defaults for paramMapping and modifyResponse, the request rule-page handle and Kafka selector security handles are retained. Dubbo uses one loadBalance rule handle with the supported spelling and random default. Cache retains cacheType and a single timeoutSeconds handle; Elasticsearch retains the required index-name field. Dubbo retry/ timeout handle IDs, aiProxy fallback ordering and timestamp precision use PostgreSQL's values.
  • basicAuth keeps valid escaped JSON. loggingKafka uses bootstrapServer, and aiProxy retains its fallback defaults without the obsolete prompt field. Their default-namespace configs match the plugin defaults, as do the custom rule-page defaults. All seeded plugins retain their default-namespace relation.

Native fresh-install checks inspect columns/nullability, primary keys, indexes and uniqueness; reject orphan relations, duplicate natural keys and invalid JSON; and require admin grants and default-namespace relations. They also attempt a duplicate discovery upstream and require the intended unique index to reject it. Historical upgrade scripts remain separate and unchanged, and upgrade validation still loads the original openGauss schema from the immutable released baseline.