{"record":{"id":"40ad34de14b71dfa","repo":"OtterMind/Chat2DB","slug":"existing-routine-definition-is-empty","errorCode":null,"errorMessage":"Existing routine definition is empty","messagePattern":"Existing routine definition is empty","errorType":"exception","errorClass":"IllegalStateException","httpStatus":null,"severity":"error","filePath":"chat2db-community-server/chat2db-community-plugins/chat2db-community-mysql/src/main/java/ai/chat2db/plugin/mysql/MysqlRoutineManager.java","lineNumber":156,"sourceCode":"        String qualifiedName = mysqlQualifiedName(databaseName, routineName);\n        String dropSql = SQL_DROP + routineType + \" IF EXISTS \" + qualifiedName;\n        return new RoutineMigrationPlan(\n                routineType,\n                databaseName,\n                routineName,\n                qualifiedName,\n                dropSql,\n                ddl);\n    }\n\n    private PreviousRoutineDefinition capturePreviousRoutine(Connection connection, RoutineMigrationPlan migrationPlan) {\n        if (!routineExists(connection, migrationPlan)) {\n            return PreviousRoutineDefinition.missing();\n        }\n\n        String ddl = showCreateRoutine(connection, migrationPlan);\n        if (StringUtils.isBlank(ddl)) {\n            throw new IllegalStateException(\"Existing routine definition is empty\");\n        }\n        return PreviousRoutineDefinition.existing(\n                ensureSqlEndsWithSemicolon(qualifyCreateRoutineDdl(ddl, migrationPlan)));\n    }\n\n    private ExecuteResponse handleCreateFailure(Connection connection, RoutineMigrationPlan migrationPlan,\n            PreviousRoutineDefinition previousRoutine,\n            Exception createException) {\n        if (!previousRoutine.exists()) {\n            return migrationFailure(\n                    migrationPlan,\n                    \"Routine migration failed. No previous routine definition existed. Original error: \"\n                            + rootMessage(createException),\n                    FAILURE_STAGE_APPLY,\n                    false,\n                    false);\n        }\n","sourceCodeStart":138,"sourceCodeEnd":174,"githubUrl":"https://github.com/OtterMind/Chat2DB/blob/5ee1e990e73fbcae1969dc554be254fedb3ab888/chat2db-community-server/chat2db-community-plugins/chat2db-community-mysql/src/main/java/ai/chat2db/plugin/mysql/MysqlRoutineManager.java#L138-L174","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","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."],"exampleFix":"// before\nroutineManager.executeMigration(connection, operation);\n\n// after\ntry {\n    routineManager.executeMigration(connection, operation);\n} catch (IllegalStateException e) {\n    if (e.getMessage().contains(\"Existing routine definition is empty\")) {\n        throw new BusinessException(\"routine.migration.privilegeOrConcurrentDrop\",\n            new Object[]{e.getMessage()}, e);\n    }\n    throw e;\n}","handlingStrategy":"try-catch","validationCode":"// Verify SHOW_ROUTINE / SELECT on mysql.proc is granted before migrating.\n// Cannot be fully prevented in code; ensure privileges beforehand.","typeGuard":null,"tryCatchPattern":"try {\n    routineManager.executeMigration(connection, operation);\n} catch (IllegalStateException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"Existing routine definition is empty\")) {\n        throw new BusinessException(\"routine.migration.privilegeOrConcurrentDrop\",\n            new Object[]{e.getMessage()}, e);\n    }\n    throw e;\n}","preventionTips":["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."],"tags":["mysql","routine","migration","privilege"],"backgroundTag":null,"analyzedSha":"5ee1e990e73fbcae1969dc554be254fedb3ab888","analyzedAt":"2026-08-14T07:05:03.077Z","schemaVersion":2},"datasetVersion":"2026-08-14T10:17:34.591Z"}