What causes "Existing routine definition is empty" in OtterMind/Chat2DB?

Grant the migrating user the SHOW_ROUTINE privilege (MySQL 8.0+) or SELECT on mysql.proc (older versions).

OtterMind/Chat2DB · error · IllegalStateException

Existing routine definition is empty

Error message

Existing routine definition is empty

What it means

Thrown during routine migration (capturePreviousRoutine) when information_schema confirms the routine exists but the subsequent SHOW CREATE FUNCTION/PROCEDURE returned a blank or null DDL string. This is an IllegalStateException (unexpected server state) rather than a user-input error: the catalog row and the SHOW CREATE result disagree.

Source

Thrown at chat2db-community-server/chat2db-community-plugins/chat2db-community-mysql/src/main/java/ai/chat2db/plugin/mysql/MysqlRoutineManager.java:156

        String qualifiedName = mysqlQualifiedName(databaseName, routineName);
        String dropSql = SQL_DROP + routineType + " IF EXISTS " + qualifiedName;
        return new RoutineMigrationPlan(
                routineType,
                databaseName,
                routineName,
                qualifiedName,
                dropSql,
                ddl);
    }

    private PreviousRoutineDefinition capturePreviousRoutine(Connection connection, RoutineMigrationPlan migrationPlan) {
        if (!routineExists(connection, migrationPlan)) {
            return PreviousRoutineDefinition.missing();
        }

        String ddl = showCreateRoutine(connection, migrationPlan);
        if (StringUtils.isBlank(ddl)) {
            throw new IllegalStateException("Existing routine definition is empty");
        }
        return PreviousRoutineDefinition.existing(
                ensureSqlEndsWithSemicolon(qualifyCreateRoutineDdl(ddl, migrationPlan)));
    }

    private ExecuteResponse handleCreateFailure(Connection connection, RoutineMigrationPlan migrationPlan,
            PreviousRoutineDefinition previousRoutine,
            Exception createException) {
        if (!previousRoutine.exists()) {
            return migrationFailure(
                    migrationPlan,
                    "Routine migration failed. No previous routine definition existed. Original error: "
                            + rootMessage(createException),
                    FAILURE_STAGE_APPLY,
                    false,
                    false);
        }

View on GitHub (pinned to 5ee1e990e7)

Solutions

Grant the migrating user the SHOW_ROUTINE privilege (MySQL 8.0+) or SELECT on mysql.proc (older versions).
Retry the migration after confirming the routine still exists; if it was concurrently dropped, the migration will proceed as a fresh create.
Check the connection's effective privileges via SHOW GRANTS before attempting migration.
If the routine is genuinely corrupted, drop and recreate it manually, then retry the migration.

Example fix

// before
routineManager.executeMigration(connection, operation);

// after
try {
    routineManager.executeMigration(connection, operation);
} catch (IllegalStateException e) {
    if (e.getMessage().contains("Existing routine definition is empty")) {
        throw new BusinessException("routine.migration.privilegeOrConcurrentDrop",
            new Object[]{e.getMessage()}, e);
    }
    throw e;
}

Defensive patterns

Strategy: try-catch

Validate before calling

// Verify SHOW_ROUTINE / SELECT on mysql.proc is granted before migrating.
// Cannot be fully prevented in code; ensure privileges beforehand.

Try / catch

try {
    routineManager.executeMigration(connection, operation);
} catch (IllegalStateException e) {
    if (e.getMessage() != null && e.getMessage().contains("Existing routine definition is empty")) {
        throw new BusinessException("routine.migration.privilegeOrConcurrentDrop",
            new Object[]{e.getMessage()}, e);
    }
    throw e;
}

Prevention

Run routine migrations with an account holding SHOW_ROUTINE (or SELECT on mysql.proc).
Avoid migrating routines that may be concurrently dropped.
Check SHOW GRANTS for the connection before migration.
Treat empty-capture as a privilege/concurrency problem, not a code bug.

When it happens

Trigger: executeMigration/previewMigration → capturePreviousRoutine finds routineExists()==true, then showCreateRoutine() returns an empty Create-column value. Occurs when the connected user lacks SHOW_ROUTINE privilege (or SELECT on mysql.proc on older MySQL), when the routine was dropped between the two queries, or when the server returns a NULL DDL for a corrupted routine.

Common situations: Connecting with a low-privilege account that can read information_schema.routines but cannot execute SHOW CREATE; migrating immediately after another session dropped the routine; MySQL 8.0 with partial privileges; a replication lag scenario where the routine exists on a read replica catalog but SHOW CREATE fails.

Related errors

AI-assisted analysis of OtterMind/Chat2DB@5ee1e990e7 (2026-08-14). Data as JSON: /api/errors/40ad34de14b71dfa. Report an issue: GitHub.