/**
* mysql-mcp - MySQL Adapter
*
* Main MySQL database adapter with connection pooling,
* query execution, and tool registration.
*/
import type { PoolConnection, FieldPacket } from 'mysql2/promise';
import { DatabaseAdapter } from '../DatabaseAdapter.js';
import { ConnectionPool } from '../../pool/ConnectionPool.js';
import type {
DatabaseConfig,
QueryResult,
SchemaInfo,
TableInfo,
IndexInfo,
HealthStatus,
AdapterCapabilities,
ToolDefinition,
ResourceDefinition,
PromptDefinition,
ToolGroup
} from '../../types/index.js';
import { ConnectionError, QueryError, TransactionError } from '../../types/index.js';
import { logger } from '../../utils/logger.js';
// Import tool modules
import { getCoreTools } from './tools/core.js';
import { getTransactionTools } from './tools/transactions.js';
import { getJsonTools, getJsonHelperTools, getJsonEnhancedTools } from './tools/json/index.js';
import { getTextTools, getFulltextTools } from './tools/text/index.js';
import { getPerformanceTools, getOptimizationTools } from './tools/performance/index.js';
import { getAdminTools, getMonitoringTools, getBackupTools } from './tools/admin/index.js';
import { getReplicationTools } from './tools/replication.js';
import { getPartitioningTools } from './tools/partitioning.js';
import { getRouterTools } from './tools/router.js';
import { getProxySQLTools } from './tools/proxysql.js';
import { getShellTools } from './tools/shell/index.js';
// New tool modules (9 new groups)
import { getSchemaTools } from './tools/schema/index.js';
import { getEventTools } from './tools/events.js';
import { getSysSchemaTools } from './tools/sysschema/index.js';
import { getStatsTools } from './tools/stats/index.js';
import { getSpatialTools } from './tools/spatial/index.js';
import { getSecurityTools } from './tools/security/index.js';
import { getClusterTools } from './tools/cluster/index.js';
import { getRoleTools } from './tools/roles.js';
import { getDocStoreTools } from './tools/docstore.js';
import { getMySQLResources } from './resources/index.js';
import { getMySQLPrompts } from './prompts/index.js';
import { SchemaManager } from './SchemaManager.js';
/**
* MySQL Database Adapter
*/
export class MySQLAdapter extends DatabaseAdapter {
readonly type = 'mysql' as const;
readonly name = 'MySQL Adapter';
readonly version = '0.1.0';
private pool: ConnectionPool | null = null;
private activeTransactions = new Map<string, PoolConnection>();
private cachedToolDefinitions: ToolDefinition[] | null = null;
private schemaManager = new SchemaManager(this);
// =========================================================================
// Connection Lifecycle
// =========================================================================
async connect(config: DatabaseConfig): Promise<void> {
if (this.connected) {
logger.warn('Already connected');
return;
}
// Build pool configuration
const poolConfig = {
host: config.host ?? 'localhost',
port: config.port ?? 3306,
user: config.username ?? 'root',
password: config.password ?? '',
database: config.database ?? '',
pool: config.pool,
ssl: config.options?.ssl as boolean | undefined,
charset: config.options?.charset ?? 'utf8mb4',
timezone: config.options?.timezone ?? 'local',
connectTimeout: config.options?.connectTimeout ?? 10000
};
this.pool = new ConnectionPool(poolConfig);
try {
await this.pool.initialize();
this.connected = true;
logger.info('MySQL adapter connected', {
host: poolConfig.host,
port: poolConfig.port,
database: poolConfig.database
});
} catch (error) {
this.pool = null;
throw new ConnectionError(`Failed to connect: ${String(error)}`);
}
}
async disconnect(): Promise<void> {
if (!this.connected || !this.pool) {
return;
}
// Close any active transactions
for (const [id, conn] of this.activeTransactions) {
try {
await conn.rollback();
logger.warn(`Rolled back orphaned transaction: ${id}`);
} catch (error) {
logger.warn(`Failed to rollback orphaned transaction ${id}: ${String(error)}`);
} finally {
try {
conn.release();
} catch {
// Ignore release errors
}
}
}
this.activeTransactions.clear();
await this.pool.shutdown();
this.pool = null;
this.connected = false;
logger.info('MySQL adapter disconnected');
}
async getHealth(): Promise<HealthStatus> {
if (!this.pool) {
return {
connected: false,
error: 'Not connected'
};
}
return this.pool.checkHealth();
}
// =========================================================================
// Query Execution
// =========================================================================
async executeReadQuery(sql: string, params?: unknown[], transactionId?: string): Promise<QueryResult> {
this.validateQuery(sql, true);
return this.executeQuery(sql, params, transactionId);
}
async executeWriteQuery(sql: string, params?: unknown[], transactionId?: string): Promise<QueryResult> {
this.validateQuery(sql, false);
return this.executeQuery(sql, params, transactionId);
}
async executeQuery(sql: string, params?: unknown[], transactionId?: string): Promise<QueryResult> {
if (!this.pool) {
throw new ConnectionError('Not connected to database');
}
if (transactionId) {
const conn = this.getTransactionConnection(transactionId);
if (!conn) {
throw new TransactionError(`Invalid transaction ID: ${transactionId}`);
}
return this.executeOnConnection(conn, sql, params);
}
const startTime = Date.now();
try {
const [results, fields] = await this.pool.execute(sql, params);
return this.processExecutionResult(results, fields, startTime);
} catch (error) {
if (this.isUnsupportedPreparedStatementError(error)) {
// Fallback to text protocol for statements not supported in prepared mode
try {
const [results, fields] = await this.pool.query(sql, params);
return this.processExecutionResult(results, fields, startTime);
} catch (fallbackError) {
const err = fallbackError as Error;
throw new QueryError(`Query fallback failed: ${err.message}`, { sql });
}
}
const err = error as Error;
throw new QueryError(`Query failed: ${err.message}`, { sql });
}
}
/**
* Execute a query on a specific connection (for transactions)
*/
async executeOnConnection(
connection: PoolConnection,
sql: string,
params?: unknown[]
): Promise<QueryResult> {
const startTime = Date.now();
try {
const [results, fields] = await connection.execute(sql, params);
return this.processExecutionResult(results, fields, startTime);
} catch (error) {
if (this.isUnsupportedPreparedStatementError(error)) {
// Fallback to text protocol
try {
const [results, fields] = await connection.query(sql, params);
return this.processExecutionResult(results, fields, startTime);
} catch (fallbackError) {
const err = fallbackError as Error;
throw new QueryError(`Query fallback failed: ${err.message}`, { sql });
}
}
const err = error as Error;
throw new QueryError(`Query failed: ${err.message}`, { sql });
}
}
/**
* Execute raw SQL using query() instead of execute()
* Use this for commands not supported in prepared statement protocol:
* - CHECK TABLE, SAVEPOINT, RELEASE SAVEPOINT, ROLLBACK TO SAVEPOINT
* - SHOW commands with LIKE patterns
*/
async rawQuery(sql: string): Promise<QueryResult> {
if (!this.pool) {
throw new ConnectionError('Not connected');
}
const startTime = Date.now();
try {
// Use query() which doesn't use prepared statements
// Unlike execute(), query() is required for certain MySQL commands
const [results, fields] = await this.pool.query(sql);
return this.processExecutionResult(results, fields, startTime);
} catch (error) {
const err = error as Error;
throw new QueryError(`Raw query failed: ${err.message}`, { sql });
}
}
// =========================================================================
// Transaction Support
// =========================================================================
/**
* Begin a transaction
*/
async beginTransaction(isolationLevel?: string): Promise<string> {
if (!this.pool) {
throw new ConnectionError('Not connected');
}
const connection = await this.pool.getConnection();
const transactionId = crypto.randomUUID();
try {
if (isolationLevel) {
await connection.execute(`SET TRANSACTION ISOLATION LEVEL ${isolationLevel}`);
}
await connection.beginTransaction();
this.activeTransactions.set(transactionId, connection);
return transactionId;
} catch (error) {
connection.release();
throw new TransactionError(`Failed to begin transaction: ${String(error)}`);
}
}
/**
* Commit a transaction
*/
async commitTransaction(transactionId: string): Promise<void> {
const connection = this.activeTransactions.get(transactionId);
if (!connection) {
throw new TransactionError(`Transaction not found: ${transactionId}`);
}
try {
await connection.commit();
} finally {
connection.release();
this.activeTransactions.delete(transactionId);
}
}
/**
* Rollback a transaction
*/
async rollbackTransaction(transactionId: string): Promise<void> {
const connection = this.activeTransactions.get(transactionId);
if (!connection) {
throw new TransactionError(`Transaction not found: ${transactionId}`);
}
try {
await connection.rollback();
} finally {
connection.release();
this.activeTransactions.delete(transactionId);
}
}
/**
* Get connection for a transaction
*/
getTransactionConnection(transactionId: string): PoolConnection | undefined {
return this.activeTransactions.get(transactionId);
}
// =========================================================================
// Schema Operations
// =========================================================================
async getSchema(): Promise<SchemaInfo> {
return this.schemaManager.getSchema();
}
async listTables(databaseName?: string): Promise<TableInfo[]> {
return this.schemaManager.listTables(databaseName);
}
async describeTable(tableName: string): Promise<TableInfo> {
return this.schemaManager.describeTable(tableName);
}
async listSchemas(): Promise<string[]> {
return this.schemaManager.listSchemas();
}
/**
* Get indexes for a table
*/
async getTableIndexes(tableName: string): Promise<IndexInfo[]> {
return this.schemaManager.getTableIndexes(tableName);
}
// =========================================================================
// Capabilities
// =========================================================================
getCapabilities(): AdapterCapabilities {
return {
json: true,
fullTextSearch: true,
vector: false, // MySQL doesn't have native vector support
geospatial: true,
transactions: true,
preparedStatements: true,
connectionPooling: true,
partitioning: true,
replication: true
};
}
getSupportedToolGroups(): ToolGroup[] {
return [
'core',
'json',
'text',
'fulltext',
'performance',
'optimization',
'admin',
'monitoring',
'backup',
'replication',
'partitioning',
'transactions',
'router',
'proxysql',
'shell',
// New groups (9)
'schema',
'events',
'sysschema',
'stats',
'spatial',
'security',
'cluster',
'roles',
'docstore'
];
}
// =========================================================================
// Tool/Resource/Prompt Registration
// =========================================================================
getToolDefinitions(): ToolDefinition[] {
if (this.cachedToolDefinitions) {
return this.cachedToolDefinitions;
}
this.cachedToolDefinitions = [
...getCoreTools(this),
...getTransactionTools(this),
...getJsonTools(this),
...getJsonHelperTools(this),
...getJsonEnhancedTools(this),
...getTextTools(this),
...getFulltextTools(this),
...getPerformanceTools(this),
...getOptimizationTools(this),
...getAdminTools(this),
...getMonitoringTools(this),
...getBackupTools(this),
...getReplicationTools(this),
...getPartitioningTools(this),
...getRouterTools(this),
...getProxySQLTools(this),
...getShellTools(this),
// New tool groups (9 groups, 80 tools)
...getSchemaTools(this),
...getEventTools(this),
...getSysSchemaTools(this),
...getStatsTools(this),
...getSpatialTools(this),
...getSecurityTools(this),
...getClusterTools(this),
...getRoleTools(this),
...getDocStoreTools(this)
];
return this.cachedToolDefinitions;
}
getResourceDefinitions(): ResourceDefinition[] {
return getMySQLResources(this);
}
getPromptDefinitions(): PromptDefinition[] {
return getMySQLPrompts(this);
}
// =========================================================================
// Helpers
// =========================================================================
/**
* Get the connection pool (for monitoring tools)
*/
getPool(): ConnectionPool | null {
return this.pool;
}
/**
* Check if error is due to unsupported prepared statement
*/
private isUnsupportedPreparedStatementError(error: unknown): boolean {
const err = error as { code?: string; message?: string };
const code = err?.code;
const message = typeof err?.message === 'string' ? err.message : '';
// Message is e.g.: "Execute failed: This command is not supported..."
// No debug throw needed now
return (
code === 'ER_UNSUPPORTED_PS' ||
message.toLowerCase().includes('not supported') ||
message.includes('ER_UNSUPPORTED_PS')
);
}
/**
* Process execution results into QueryResult
*/
/**
* Process execution results into QueryResult
*/
private processExecutionResult(
results: unknown,
fields: FieldPacket[] | undefined,
startTime: number
): QueryResult {
const executionTimeMs = Date.now() - startTime;
if (Array.isArray(results)) {
return {
rows: results as Record<string, unknown>[],
executionTimeMs,
columns: Array.isArray(fields) ? fields.map(f => ({
name: f.name,
type: this.getTypeName(f.type ?? 0)
})) : undefined
};
}
const resultInfo = results as {
affectedRows?: number;
insertId?: number | bigint;
warningStatus?: number;
};
return {
rowsAffected: resultInfo.affectedRows,
lastInsertId: resultInfo.insertId,
warningCount: resultInfo.warningStatus,
executionTimeMs
};
}
/**
* Convert MySQL type number to name
*/
private getTypeName(typeNum: number): string {
// MySQL type constants
const types: Record<number, string> = {
0: 'DECIMAL',
1: 'TINYINT',
2: 'SMALLINT',
3: 'INT',
4: 'FLOAT',
5: 'DOUBLE',
6: 'NULL',
7: 'TIMESTAMP',
8: 'BIGINT',
9: 'MEDIUMINT',
10: 'DATE',
11: 'TIME',
12: 'DATETIME',
13: 'YEAR',
14: 'NEWDATE',
15: 'VARCHAR',
16: 'BIT',
245: 'JSON',
246: 'NEWDECIMAL',
247: 'ENUM',
248: 'SET',
249: 'TINYBLOB',
250: 'MEDIUMBLOB',
251: 'LONGBLOB',
252: 'BLOB',
253: 'VARCHAR',
254: 'CHAR',
255: 'GEOMETRY'
};
return types[typeNum] ?? `UNKNOWN(${typeNum})`;
}
}
