MySQL Exec

Instructions

Implementation Reference

• src/tools/mysql-exec.ts:58-86 (handler)

  The main handler function for mysql_exec tool. It resolves the execution context using policy, executes the SQL via the pool registry, and builds the success/error result with affected rows, insert ID, warnings, and elapsed time.

  async (args) => {
    try {
      const context = resolveExecContext(registry, args);
      const startedAt = Date.now();
      const result = await pools.exec(
        context.env,
        context.normalizedSql,
        context.database,
        context.timeoutMs,
      );
      const structuredContent: ExecStructuredContent = {
        ok: true,
        env: context.env,
        database: context.database,
        statementType: context.statementType,
        affectedRows: result.affectedRows,
        insertId: result.insertId,
        warnings: result.warnings,
        elapsedMs: Date.now() - startedAt,
      };
  
      return buildSuccessResult(
        `Affected ${structuredContent.affectedRows} row(s) in ${context.env}${context.database ? `.${context.database}` : ""} in ${structuredContent.elapsedMs}ms.`,
        structuredContent,
      );
    } catch (error) {
      return buildErrorResult(error, args.env);
    }
  },

• src/tools/mysql-exec.ts:22-36 (schema)

  Input schema for mysql_exec tool using Zod. Defines validation for env (environment alias), database (optional override), sql (the statement to execute), and timeoutMs (optional execution timeout).

  export const mysqlExecInputSchema = z.object({
    env: z.string().min(1).describe("Environment alias such as dev or stg."),
    database: z
      .string()
      .min(1)
      .optional()
      .describe("Optional database override allowed by server policy."),
    sql: z.string().min(1).describe("One SQL statement allowed by server policy."),
    timeoutMs: z
      .number()
      .int()
      .min(100)
      .optional()
      .describe("Requested execution timeout in milliseconds."),
  });

• src/tools/mysql-exec.ts:38-88 (registration)

  Registration function for mysql_exec tool. Registers the tool with MCP server including title, description, annotations (destructiveHint: true, readOnlyHint: false), input/output schemas, and the handler function.

  export const registerMysqlExecTool = (
    server: McpServer,
    registry: EnvironmentRegistry,
    pools: MysqlPoolRegistry,
  ) => {
    server.registerTool(
      "mysql_exec",
      {
        title: "MySQL Exec",
        description: "Run one write-capable SQL statement on a configured MySQL environment if policy allows.",
        annotations: {
          title: "MySQL Exec",
          readOnlyHint: false,
          destructiveHint: true,
          idempotentHint: false,
          openWorldHint: false,
        },
        inputSchema: mysqlExecInputSchema,
        outputSchema: buildMysqlExecOutputSchema(),
      },
      async (args) => {
        try {
          const context = resolveExecContext(registry, args);
          const startedAt = Date.now();
          const result = await pools.exec(
            context.env,
            context.normalizedSql,
            context.database,
            context.timeoutMs,
          );
          const structuredContent: ExecStructuredContent = {
            ok: true,
            env: context.env,
            database: context.database,
            statementType: context.statementType,
            affectedRows: result.affectedRows,
            insertId: result.insertId,
            warnings: result.warnings,
            elapsedMs: Date.now() - startedAt,
          };
  
          return buildSuccessResult(
            `Affected ${structuredContent.affectedRows} row(s) in ${context.env}${context.database ? `.${context.database}` : ""} in ${structuredContent.elapsedMs}ms.`,
            structuredContent,
          );
        } catch (error) {
          return buildErrorResult(error, args.env);
        }
      },
    );
  };

• src/core/policy.ts:130-201 (helper)

  resolveExecContext helper function that validates SQL statements for mysql_exec. Checks for single statement, disallows WITH clauses, verifies write permissions, validates statement types (insert/update/delete/replace/ddl), checks DDL permissions, and resolves timeout settings.

  export const resolveExecContext = (
    registry: EnvironmentRegistry,
    args: ExecArgs,
  ): ResolvedExecutionContext & {
    normalizedSql: string;
    statementType: "insert" | "update" | "delete" | "replace" | "ddl";
  } => {
    const environment = registry.getEnvironment(args.env);
    const inspection = inspectSql(args.sql);
  
    if (!inspection.normalizedSql) {
      throw createError("SQL_EMPTY", "SQL cannot be empty.", false);
    }
    if (inspection.hasMultipleStatements) {
      throw createError(
        "SQL_MULTI_STATEMENT",
        "Only one SQL statement is allowed per call.",
        false,
      );
    }
  
    if (inspection.hasLeadingWithClause) {
      throw createError(
        "SQL_NOT_ALLOWED",
        "Top-level WITH statements are not allowed in mysql_exec.",
        false,
      );
    }
  
    if (environment.readOnly || !environment.allowWrite) {
      throw createError(
        "WRITE_DISABLED",
        `Write statements are not allowed for environment '${args.env}'.`,
        false,
      );
    }
  
    if (!isWriteType(inspection.statementType) && inspection.statementType !== "ddl") {
      throw createError(
        "SQL_NOT_ALLOWED",
        `Statement type '${inspection.mainKeyword || inspection.firstKeyword || "unknown"}' is not allowed in mysql_exec.`,
        false,
      );
    }
  
    if (inspection.statementType === "ddl" && !environment.allowDDL) {
      throw createError(
        "DDL_DISABLED",
        `DDL statements are not allowed for environment '${args.env}'.`,
        false,
      );
    }
  
    const database = args.database ?? environment.defaultDatabase;
    assertAllowedDatabase(environment, database);
    assertReferencedDatabasesAllowed(environment, inspection.normalizedSql);
  
    const serverConfig = registry.getServerConfig();
    return {
      env: args.env,
      database,
      environment,
      timeoutMs: resolveTimeout(
        args.timeoutMs,
        environment.defaultTimeoutMs,
        serverConfig.hardTimeoutMs,
        serverConfig.defaultTimeoutMs,
      ),
      normalizedSql: inspection.normalizedSql,
      statementType: inspection.statementType,
    };
  };

• src/core/mysql-pool.ts:54-80 (helper)

  The exec method in MysqlPoolRegistry that performs the actual MySQL execution. Gets a connection from the pool, optionally changes to the specified database, executes the SQL with timeout, and returns affectedRows, insertId, and warnings.

  public async exec(
    env: string,
    sql: string,
    database: string | undefined,
    timeoutMs: number,
  ): Promise<ExecExecutionResult> {
    const pool = this.getPool(env);
    const connection = await pool.getConnection();
    try {
      if (database) {
        await connection.changeUser({ database });
      }
      const [result] = await connection.query<ResultSetHeader>({
        sql,
        timeout: timeoutMs,
      });
      return {
        affectedRows: result.affectedRows,
        insertId: result.insertId === 0 ? undefined : result.insertId,
        warnings: result.warningStatus,
      };
    } catch (error) {
      throw this.mapMysqlError(error);
    } finally {
      connection.release();
    }
  }