Revisión MySQL — PlataformaIA SaaS B2B

🔐

Seguridad — Issues Críticos

Crítico

1

Usuario de app con ALL PRIVILEGES ON *.*

Blast radius máximo · Cualquier SQLi compromete toda la instancia

El usuario app tiene acceso total a todas las bases de datos y puede hacer DROP, CREATE USER, y acceder a mysql.user. Un error de inyección SQL en cualquier endpoint compromete toda la instancia.

security/users.sql

GRANT ALL PRIVILEGES ON *.* TO 'app'@'%';
CREATE USER 'plataformaia_app'@'%' IDENTIFIED BY '<secretmanager>';
GRANT SELECT, INSERT, UPDATE, DELETE ON plataformaia.* TO 'plataformaia_app'@'%';
ALTER USER 'plataformaia_app'@'%' REQUIRE SSL;
 
-- Usuario separado solo para migraciones (CI/CD)
CREATE USER 'plataformaia_migrate'@'localhost' IDENTIFIED BY '<secretmanager>';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX ON plataformaia.* TO 'plataformaia_migrate'@'localhost';
 
-- Eliminar usuarios anónimos
DROP USER IF EXISTS ''@'localhost';
DROP USER IF EXISTS ''@'%';
              

⚠️ Credenciales deben ir en el secret manager de plataforma (AWS Secrets Manager, Vault, etc.), nunca en variables de entorno hardcodeadas ni en repositorios.

🏗️

Esquema — Tipos de Datos y Charset

Crítico + Alto

2

FLOAT para cantidades económicas y métricas exactas

Errores de redondeo en totales financieros · Problemas en reportes de BI

total_sent FLOAT en campaigns y duration FLOAT en events acumulan error de punto flotante. Para cantidades que se suman o comparan, usar DECIMAL o enteros.

schema/campaigns.sql

CREATE TABLE campaigns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    total_sent FLOAT,
    total_sent BIGINT UNSIGNED NOT NULL DEFAULT 0,  -- conteo exacto de envíos
    ...
              

schema/events.sql

CREATE TABLE events (
    duration FLOAT,              -- segundos con decimales flotante
    duration_ms BIGINT UNSIGNED NOT NULL DEFAULT 0,  -- duración en ms (entero exacto)
    ...
              

3

VARCHAR(36) como PK en tablas calientes (UUID string)

Alta presión en buffer pool · B-tree fragmentado · Joins más lentos

La tabla users usa VARCHAR(36) como PK. En MySQL/InnoDB la PK es el índice clustered — guardar UUIDs en texto ocupa 5-6x más espacio y degrada la localidad de caché. Dos opciones:

schema/users.sql — Opción A: BINARY(16) con helpers

CREATE TABLE users (
    id VARCHAR(36) PRIMARY KEY,
    id BINARY(16) NOT NULL,
    uuid VARCHAR(36) GENERATED ALWAYS AS (INSERT(INSERT(INSERT(INSERT(
        HEX(id),9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-')) VIRTUAL,
    PRIMARY KEY (id),
    ...
              

schema/users.sql — Opción B (más sencilla): BIGINT UNSIGNED con UUID_TO_BIN

-- Si aún en diseño, cambiar a BIGINT UNSIGNED AUTO_INCREMENT + UUID externo
CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    external_uuid BINARY(16) NOT NULL UNIQUE DEFAULT (UUID_TO_BIN(UUID())),
    PRIMARY KEY (id)
);
              

⚠️ Con 500K rows esto es migrable sin downtime via pt-online-schema-change o gh-ost. A 50M+ rows el costo es mayor — migrar cuanto antes.

4

charset utf8 en lugar de utf8mb4

Emojis y caracteres suplementarios se truncan silenciosamente

La tabla users usa CHARSET=utf8 (alias de utf8mb3 en MySQL 8), que solo soporta hasta U+FFFF. Cualquier emoji en nombres o emails causará error o truncado silencioso.

schema/users.sql

) ENGINE=InnoDB CHARSET=utf8; ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

5

ENUM para campo plan — fragilidad ante cambios

Bajo · Cada cambio de valor requiere ALTER TABLE (lock en versiones <8.0 instant)

schema/users.sql

    plan ENUM('free','pro','enterprise'),
    plan VARCHAR(32) NOT NULL DEFAULT 'free',
    -- Con lookup table si se necesitan metadatos de plan
    CONSTRAINT chk_users_plan CHECK (plan IN ('free','pro','enterprise','custom'))
              

⚡

Queries — Paginación y Deadlocks

Alto

6

OFFSET 500 en tabla de 4M filas — escaneo lineal

Query >2s en producción · Empeora con el crecimiento

MySQL con OFFSET 500 descarta las primeras 500 filas después de leerlas. En una tabla de 4M filas con paginación profunda esto degrada a O(n).

EXPLAIN — Antes (OFFSET)

type:ref

key:user_id FK (sin índice)

rows:~180,000

Extra:Using filesort

tiempo:2.4s

EXPLAIN — Después (keyset)

type:range

key:idx_campaigns_user_status_created

rows:~20

Extra:Using index condition

tiempo:4ms

queries/campaigns-list.sql — Keyset pagination

-- Antes: OFFSET (lento en páginas profundas)
SELECT * FROM campaigns
WHERE user_id = ?
ORDER BY created_at DESC LIMIT 20 OFFSET 500;
 
-- Después: keyset con cursor (created_at, id)
SELECT id, name, status, total_sent, created_at
FROM campaigns
WHERE user_id = ?
  AND (created_at, id) < (?, ?)   -- cursor de la última fila de la página anterior
ORDER BY created_at DESC, id DESC
LIMIT 20;
 
-- Índice compuesto necesario:
CREATE INDEX idx_campaigns_user_created_id
    ON campaigns (user_id, created_at, id);
              

7

Worker de jobs con deadlocks — UPDATE sin SKIP LOCKED

Workers concurrentes compiten por la misma fila · Reintentos en cascada

El UPDATE ... WHERE status='pending' ORDER BY created_at LIMIT 1 adquiere locks en todas las filas que coinciden antes de limitarlas — múltiples workers colisionan. La solución es SELECT ... FOR UPDATE SKIP LOCKED en transacción.

queries/worker-claim.sql

-- Antes: lock-contention garantizada entre workers
UPDATE jobs SET status = 'processing', started_at = NOW()
WHERE status = 'pending' ORDER BY created_at LIMIT 1;
 
-- Después: claim atómico sin deadlock
START TRANSACTION;
 
SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;  -- salta filas ya lockeadas por otros workers
 
UPDATE jobs
SET status = 'processing', started_at = CURRENT_TIMESTAMP
WHERE id = ?;  -- id obtenido del SELECT anterior
 
COMMIT;
 
-- Índice necesario para el WHERE status:
CREATE INDEX idx_jobs_status_created ON jobs (status, created_at);
              

ℹ️ Nota MySQL 8.0: SKIP LOCKED está soportado desde 8.0. Usar solo para workloads tipo cola — no para lecturas donde la consistencia es crítica (contabilidad, comprobaciones de permisos).

🔌

Connection Pool — pool_recycle > wait_timeout

Crítico

🚨

Conexiones estables rotas silenciosamente: pool_recycle=600s pero MySQL cierra conexiones inactivas a los wait_timeout=300s. La app obtiene conexiones del pool que el servidor ya cerró → Lost connection to MySQL server en producción.

db/engine.py — SQLAlchemy (Python/FastAPI)

from sqlalchemy import create_engine
 
engine = create_engine(
    "mysql+mysqlconnector://plataformaia_app:secret@db.internal/plataformaia",
    pool_size=10,
    max_overflow=5,
    pool_timeout=30,
    pool_recycle=600,   # mayor que wait_timeout=300 → conexiones rotas
    pool_recycle=240,   # menor que wait_timeout=300, con margen de seguridad
    # pool_pre_ping faltaba
    pool_pre_ping=True, # recupera tras failover o reconexión de red
    connect_args={"connect_timeout": 5},
)
          

db/pool.js — Node.js (mysql2)

import mysql from 'mysql2/promise';
 
const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 30000,
});
          

🗓️

Plan de Migración Seguro

5 fases

1

Inmediato (sin migración) — Seguridad y pool

Revocar ALL PRIVILEGES del usuario app, crear usuarios con least-privilege
Cambiar pool_recycle=240 + activar pool_pre_ping=True
Activar slow query log: SET GLOBAL slow_query_log='ON'; SET GLOBAL long_query_time=1;
Habilitar REQUIRE SSL para usuarios app

2

Sprint 1 — Índices (ALGORITHM=INPLACE, sin lock)

Crear idx_campaigns_user_created_id (user_id, created_at, id)
Crear idx_jobs_status_created (status, created_at)
Índice FK en events.campaign_id
Validar con EXPLAIN antes y después

3

Sprint 2 — Tipos de datos (con pt-osc / gh-ost)

Cambiar total_sent FLOAT → BIGINT UNSIGNED
Cambiar duration FLOAT → duration_ms BIGINT UNSIGNED
Cambiar CHARSET=utf8 → utf8mb4 en tabla users
Dry-run con pt-online-schema-change --dry-run

4

Sprint 3 — PK de users (alto impacto, ventana de mantenimiento)

Migrar id VARCHAR(36) → BIGINT UNSIGNED con gh-ost (shadow table)
Añadir columna external_uuid BINARY(16) para compatibilidad de API
Doble escritura durante rollout, validar conteo antes de flip
Rollback: la shadow table antigua permanece 24h

5

Sprint 4 — Worker + paginación (cambio de código)

Desplegar keyset pagination en API de campañas
Refactorizar worker a SELECT FOR UPDATE SKIP LOCKED
Feature flag para rollout gradual y comparación de latencia

📋

Resumen de Anti-Patterns Detectados

Anti-Pattern	Riesgo	Tabla Afectada	Patrón Correcto	Sprint
ALL PRIVILEGES en runtime	Crítico	app user	Least-privilege por DB	Inmediato
FLOAT para métricas	Crítico	campaigns, events	BIGINT o DECIMAL	S2
pool_recycle > wait_timeout	Crítico	Pool config	Recycle < wait_timeout + pre_ping	Inmediato
VARCHAR(36) como PK hot	Alto	users	BIGINT UNSIGNED + uuid externo	S3
Deep OFFSET pagination	Alto	campaigns	Keyset (created_at, id)	S4
Worker UPDATE sin SKIP LOCKED	Medio	jobs	SELECT FOR UPDATE SKIP LOCKED	S4
charset utf8 (utf8mb3)	Medio	users	utf8mb4_unicode_ci	S2
ENUM para campo volátil	Bajo	users	VARCHAR + CHECK constraint	S2

✅ Versión MySQL 8.0.36 soporta todos los patrones recomendados: SKIP LOCKED, row aliases en ON DUPLICATE KEY UPDATE, BINARY(16) con UUID_TO_BIN(), CHECK constraints, y ALGORITHM=INPLACE para la mayoría de los ALTER TABLE.

PlataformaIA — Revisión MySQL 8.0

Inmediato (sin migración) — Seguridad y pool

Sprint 1 — Índices (ALGORITHM=INPLACE, sin lock)

Sprint 2 — Tipos de datos (con pt-osc / gh-ost)

Sprint 3 — PK de users (alto impacto, ventana de mantenimiento)

Sprint 4 — Worker + paginación (cambio de código)