# Cassandra to MySQL Proxy

A Go-based proxy that translates the Cassandra Native Protocol (CQL) to the MySQL protocol, enabling ScyllaDB/Cassandra workloads to run on MySQL with minimal application changes.

## Project Overview

This proxy lets ScyllaDB and Apache Cassandra applications connect to MySQL databases by implementing the Cassandra Native Protocol (versions 3, 4, and 5) on the frontend and translating queries to MySQL on the backend.

Why this proxy: move CQL workloads to MySQL without retooling drivers or rewiring applications, keeping familiar workflows while the proxy handles protocol and storage translation.

## Key Features

- **Protocol Bridge**: Cassandra Native Protocol v3/v4/v5 plus LZ4/Snappy compression
- **CQL to MySQL Translation**: DDL/DML conversion with automatic type and schema mapping
- **Extended SQL Mode**: Optional non-Cassandra-compatible features under `executor.protocol_mode=extended_sql`
- **Prepared Statements**: CQL prepared statements with optional MySQL prepared caching
- **Tenant Isolation**: Per-user MySQL pools with REPEATABLE READ isolation
- **Operational Visibility**: Admin metrics, structured logging, and TLS support

## Key Feature Details

### Protocol and Client Compatibility

Native Protocol v3/v4/v5 on the frontend (segment framing for v5) with LZ4 (v3-v5) and Snappy (v3-v4) compression, so existing CQL clients connect without special adapters.

### Translation and Schema Mapping

Translates CQL DDL and DML into MySQL equivalents (keyspace to database, table/index mapping) with automatic CQL type mapping; see the type mapping table below. CQL collections are stored as JSON in MySQL and returned as JSON strings.

### Prepared Statements and Execution

CQL prepared statements are cached and translated to SQL, with optional MySQL prepared statement caching under `mysql.prepared`. Positional parameters are supported; named parameters are rejected.

### Tenant Isolation and Pooling

Each authenticated user receives an isolated MySQL connection pool, and REPEATABLE READ isolation prevents cross-user resource starvation.

### Operations and Observability

The admin HTTP server exposes Prometheus metrics and optional pprof endpoints, structured logging is configurable, and TLS is supported for client listeners and MySQL connections.

## Architecture

```
┌─────────────────┐     Cassandra      ┌─────────────────┐     MySQL      ┌──────────────┐
│   Cassandra     │     Native         │    Cassandra    │     Native     │              │
│   Application   ├────► Protocol      │     Proxy       ├───► Protocol   │   MySQL      │
│  (CQL Client)   │    (Frontend)      │   (CQL→SQL)     │   (Backend)    │   Server     │
└─────────────────┘                    └─────────────────┘                └──────────────┘
```

## Installation

### Prerequisites

- Go 1.23 or later
- MySQL 5.7/8.0 server
- ScyllaDB or Cassandra client application

### Building from Source

```bash
git clone <repository-url>
cd cassandra-on-mysql
go build -o bin/proxy ./cmd/proxy/...
```

## Quick Start

### Starting the Proxy

```bash
./bin/proxy -config config.yaml
```

By default, the proxy listens on port 9042 (Cassandra default port).

### Configuration

Create a `config.yaml` file:

```yaml
# MySQL backend configuration
mysql:
  host: "localhost:3306"
  max_open_conns: 100
  max_idle_conns: 10
  connect_timeout: 30s
  read_timeout: 30s
  write_timeout: 30s
  conn_max_lifetime: 1h
  conn_max_idle_time: 10m
  tls:
    mode: "true" # true, skip-verify, preferred, or a custom config name
    # ca_file: "/path/to/ca.pem"
    # cert_file: "/path/to/client-cert.pem"
    # key_file: "/path/to/client-key.pem"
    # server_name: "mysql.example.com"
    # insecure_skip_verify: false
  dsn_params:
    # interpolateParams: "true"
  prepared:
    enabled: false
    max_statements: 500
    idle_ttl: 10m
    cleanup_interval: 1m
    prewarm_on_prepare: false

# CQL server configuration
server:
  max_connections: 10000
  # admin:
  #   address: "127.0.0.1:6060"
  #   pprof:
  #     enabled: true
  #     path: "/debug/pprof"
  listeners:
    - name: "plain"
      address: "0.0.0.0:9042"
      tls:
        mode: "disabled"
    # - name: "tls"
    #   address: "0.0.0.0:9142"
    #   tls:
    #     mode: "auto" # auto generates a self-signed cert for the host name
    #     # cert_file: "/path/to/server-cert.pem"
    #     # key_file: "/path/to/server-key.pem"
    #     # client_ca_file: "/path/to/client-ca.pem"
    #     # client_auth: "require_and_verify"
    #     # min_version: "1.2"
    #     # max_version: "1.3"
```

## Configuration Details

### User-Specific Connection Pools

Each authenticated user gets their own MySQL connection pool using the server's default isolation level. This provides:

- **Resource Isolation**: Users cannot starve each other's connections
- **Security**: Credentials and permissions are isolated

### MySQL DSN Parameters

Use `mysql.dsn_params` to pass custom query parameters to the Go MySQL driver. These values are merged with the proxy defaults (parseTime/loc/charset/collation/timeouts), and custom entries override the defaults when keys overlap.

### MySQL Prepared Statement Cache

Prepared-statement cache settings live under `mysql.prepared` and control whether MySQL-level prepared statements are cached, how many are retained, and eviction timing.

### Executor Protocol Mode

`executor.protocol_mode` controls whether executor behavior is strict Cassandra-compatible or allows SQL-oriented extensions:

- `strict_cassandra` (default): keep compatibility constraints.
- `extended_sql`: enable non-compatible extensions below.

`extended_tidb` is still accepted as a legacy alias and normalized to `extended_sql`.

When `executor.protocol_mode=extended_sql`, the proxy enables:

- Column schema defaults and update expressions on `CREATE TABLE`, including `DEFAULT current_timestamp` / `ON UPDATE current_timestamp`.
- Configurable additive duplicate-key insert handling via `executor.insert_duplicate_increment_columns`:
  `col = col + VALUES(col)`.
- Full-primary-key equality arithmetic `UPDATE` upsert for `col = col +/- <term>` with null-safe accumulation:
  `COALESCE(col, 0) + VALUES(col)`.
- Safe `BATCH` update merge optimization (same table, same assignment columns, full PK equality).

### UNLOGGED BATCH Parallel Mode

`executor.unlogged_batch_parallel` enables best-effort parallel execution for `UNLOGGED BATCH`.
`executor.unlogged_merge_before_parallel` controls whether `UNLOGGED` best-effort parallel mode coalesces compatible INSERTs first (default `true`, with split fallback on merged failure).
Use `executor.unlogged_batch_parallelism` to cap worker goroutines (`<=0` defaults to `CPU cores * 4`).
Use `executor.unlogged_batch_max_mysql_connections` to cap MySQL connections consumed by `UNLOGGED BATCH` workers, preserving headroom for `SELECT` and other non-batch requests (`<=0` defaults to 75% of the effective MySQL connection budget).
`executor.batch_insert_merge_max_rows` and `executor.batch_insert_merge_max_bytes` control INSERT merge limits in batch optimization (`<=0` defaults to `256` rows and `256KB`).

### Advertised Address Override

`system_tables.advertised_address` can be overridden at startup using `-advertised-address` or the `SYSTEM_TABLES_ADVERTISED_ADDRESS` environment variable (flag takes precedence).
- **Performance**: Connection pools are sized per user

Isolation is enforced per session using the MySQL `transaction_isolation` setting.

### Consistency Levels

All Cassandra consistency levels are mapped to MySQL REPEATABLE READ isolation:

- `ONE`, `TWO`, `THREE` → REPEATABLE READ
- `QUORUM`, `ALL` → REPEATABLE READ
- `LOCAL_QUORUM`, `EACH_QUORUM` → REPEATABLE READ
- Any consistency level specified in queries is acknowledged but mapped to REPEATABLE READ

## CQL to MySQL Type Mapping

| CQL Type | MySQL Type | Notes |
|----------|------------|-------|
| `ascii` | `VARCHAR` | ASCII only |
| `bigint` | `BIGINT` | 64-bit integer |
| `blob` | `BLOB` | Binary data |
| `boolean` | `BOOLEAN` | True/False |
| `counter` | `BIGINT` | With auto-increment |
| `decimal` | `DECIMAL` | Fixed-precision decimal |
| `double` | `DOUBLE` | 64-bit float |
| `float` | `FLOAT` | 32-bit float |
| `int` | `INT` | 32-bit integer |
| `text` | `TEXT` | UTF-8 text |
| `timestamp` | `DATETIME` | With timezone |
| `timeuuid` | `VARCHAR(36)` | UUID v1 |
| `uuid` | `VARCHAR(36)` | UUID |
| `varchar` | `VARCHAR` | Variable length text |
| `varint` | `BIGINT` | Arbitrary precision |
| `date` | `DATE` | Date only |
| `time` | `TIME` | Time only |
| `list<type>` | `TEXT` | Stored as JSON array |
| `set<type>` | `TEXT` | Stored as JSON array (unique) |
| `map<k,v>` | `TEXT` | Stored as JSON object |
| `tuple<...>` | `TEXT` | Stored as JSON array |
| `frozen<...>` | `TEXT` | Nested collections as JSON |

## Supported CQL Features

### DDL (Data Definition Language)

- `CREATE KEYSPACE` → Maps to `CREATE DATABASE`
- `CREATE TABLE` → Creates MySQL table with appropriate types
- `ALTER TABLE` → Alters MySQL table
- `DROP TABLE` → Drops MySQL table
- `DROP KEYSPACE` → Drops MySQL database
- `CREATE INDEX` → Creates MySQL index

### DML (Data Manipulation Language)

- `SELECT` with WHERE, ORDER BY, LIMIT
- `INSERT` with collections
- `UPDATE` with collections and conditions
- `DELETE` with WHERE clauses
- `BATCH` statements

Notes:
- Raw TTL clauses are supported with direct TiDB syntax in `CREATE/ALTER TABLE` when `executor.protocol_mode=extended_sql`.
  Examples:
  - `CREATE TABLE ... TTL = created_at + INTERVAL 30 DAY TTL_ENABLE = 'ON' TTL_JOB_INTERVAL = '24h'`
  - `ALTER TABLE ... TTL_ENABLE = 'OFF'`
  - `ALTER TABLE ... TTL_JOB_INTERVAL = '12h'`
  - `ALTER TABLE ... REMOVE TTL`
- `USING TTL`/`USING TIMESTAMP` and `IF` conditions are currently rejected.
- Prepared statements support positional parameters only (named values are rejected).
- `UNLOGGED BATCH` can run in best-effort parallel mode with `executor.unlogged_batch_parallel=true` (non-atomic by design).
- `extended_sql` mode adds non-Cassandra-compatible extensions for defaults/on-update, additive insert conflict updates, and full-PK arithmetic upsert.

### Collections

Collections are stored as JSON in MySQL JSON columns and returned to clients as JSON strings (not native CQL collection encodings):

```sql
-- CQL
CREATE TABLE users (
    id UUID PRIMARY KEY,
    tags SET<TEXT>,
    scores LIST<INT>,
    metadata MAP<TEXT, TEXT>
);

-- Inserts as JSON
INSERT INTO users (id, tags, scores, metadata)
VALUES (
    uuid(),
    '["tech","database"]',
    '[95,87,92]',
    '{"role":"admin","team":"backend"}'
);
```

### Prepared Statements

CQL prepared statements are cached and translated to MySQL prepared statements.

### Lightweight Transactions

`IF EXISTS` and `IF NOT EXISTS` are translated to MySQL transactions with appropriate checks.

## Testing

### Running the Proxy

```bash
# In terminal 1: Start the proxy
./bin/proxy

# In terminal 2: Connect with cqlsh (Cassandra shell)
cqlsh localhost 9042
```

### Example Queries

```cql
-- Create keyspace
CREATE KEYSPACE mykeyspace WITH replication = {
    'class': 'SimpleStrategy',
    'replication_factor': 1
};

USE mykeyspace;

-- Create table
CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    username TEXT,
    email TEXT,
    created_at TIMESTAMP,
    tags SET<TEXT>,
    metadata MAP<TEXT, TEXT>
);

-- Insert data
INSERT INTO users (user_id, username, email, created_at, tags, metadata)
VALUES (
    uuid(),
    'john_doe',
    'john@example.com',
    toTimestamp(now()),
    {'active', 'verified'},
    {'department': 'engineering', 'role': 'senior'}
);

-- Query data
SELECT * FROM users WHERE username = 'john_doe';

-- Update with collections
UPDATE users SET tags = tags + {'premium'} WHERE user_id = ?;
```

## Monitoring

### Prometheus Metrics

Prometheus metrics are served by the admin HTTP server. Enable the admin listener and metrics (pprof is enabled by default at `/debug/pprof`):

```yaml
server:
  admin:
    address: "0.0.0.0:6060"
    pprof:
      enabled: true
      path: "/debug/pprof"
    metrics:
      enabled: true
      path: "/metrics"               # default
      slow_request_threshold: 250ms  # optional: logs slow requests with stage timings
```

With the config above, metrics are available at `http://127.0.0.1:6060/metrics` (replace the host/port with your admin address). Set `server.admin.pprof.enabled: false` to disable pprof or change `server.admin.pprof.path` to move it.

The admin server also exposes lifecycle endpoints:

- `GET /ready` - readiness probe (`200` when serving, `503` while draining)
- `GET /healthz` - liveness probe (`200` when process is alive)
- `GET|POST /drain` - enter drain mode

### Graceful drain contract

On `SIGTERM` (or `POST /drain`) the proxy:

1. Flips `/ready` to `503` so the load balancer / kube-proxy stops sending **new**
   connections to this pod.
2. Waits `server.drain.readiness_delay`, then closes listeners.
3. For each existing connection: stops reading new requests, lets in-flight
   requests finish (their responses are flushed), then closes the socket cleanly
   so drivers reconnect without erroring. Closes are staggered to avoid a
   synchronized reconnect storm.
4. After `server.drain.wait_timeout`, force-closes any connection still busy.

**Operator requirements (especially behind an L4 NLB):**

- An L4 NLB pins existing TCP flows to this pod; readiness only affects new
  connections. Traffic moves off this pod only when drain breaks the connection
  and the driver reconnects through the NLB. Set `readiness_delay` >= the NLB
  target deregistration / health-check propagation time (typically 20-40s).
- `system_tables.advertised_address` must be the **NLB VIP**, not the pod IP, or
  reconnects come straight back to the terminating pod.
- Ensure `terminationGracePeriodSeconds` > `readiness_delay + wait_timeout`, or
  SIGKILL truncates the drain.

Exported metrics include:

- Request-level metrics
- `proxy_request_total{opcode="..."}` - Total request count by textual CQL opcode
- `proxy_request_in_flight` - Current number of in-flight CQL requests
- `proxy_request_completed_total{opcode="...",op_type="...",status="ok|error"}` - Completed requests by result
- `proxy_request_duration_seconds{opcode="...",op_type="...",status="ok|error"}` - End-to-end request latency histogram
- Current `opcode` labels use protocol names such as `QUERY`, `PREPARE`, `EXECUTE`, `BATCH`, `AUTH_RESPONSE`; unknown opcodes are omitted from request metrics
- Current `op_type` labels use string operation names. They default to the protocol opcode name and become more specific for CQL work when known, for example `select`, `insert`, `update`, `delete`, `batch`, or `use`
- `proxy_request_stage_seconds{stage="..."}` - Per-stage latency histogram
- Current `stage` labels: `frame_read`, `queue_wait`, `handler`, `batch_parse`, `batch_decode`, `batch_merge_plan`, `batch_execute_wait`, `schema_sync`, `schema_cache_refresh`, `cql_meta_cache_refresh`, `mysql_pool_wait`, `mysql_query`, `result_encode`, `write_lock_wait`, `write_flush`, `total`

- CQL statement metrics
- `proxy_cql_query_total{query_type="...",status="ok|error"}` - CQL query throughput by statement type
- `proxy_cql_query_duration_seconds{query_type="...",status="ok|error"}` - End-to-end CQL query latency by statement type
- Current `query_type` labels: `select`, `insert`, `update`, `delete`, `batch`, `use`, `describe`, `ddl_keyspace`, `ddl_table`, `ddl_type`, `ddl_index`, `ddl_view`, `ddl_trigger`, `ddl_function`, `ddl_aggregate`, `truncate`, `other`, `unknown`

- MySQL backend metrics
- `proxy_mysql_operation_total{operation="...",status="ok|error"}` - MySQL operation throughput
- `proxy_mysql_operation_seconds{operation="...",status="ok|error"}` - MySQL operation latency histogram
- `proxy_mysql_error_total{operation="...",error_type="..."}` - MySQL error counters by type
- Current `operation` labels: `pool_wait`, `prepare`, `begin_tx`, `query_read`, `query_write`, `exec_write`
- Current `error_type` labels: `timeout`, `connection`, `driver`, `other`

- MySQL pool metrics
- `proxy_mysql_pool_open{tenant="...",user="..."}` - Open MySQL connections
- `proxy_mysql_pool_in_use{tenant="...",user="..."}` - In-use MySQL connections
- `proxy_mysql_pool_wait_total{tenant="...",user="..."}` - Cumulative MySQL pool wait count snapshot
- `proxy_mysql_pool_wait_seconds_total{tenant="...",user="..."}` - Cumulative time spent waiting for pool connections

- Index selection metrics
- `proxy_index_selection_total{outcome="...",rule="...",index_type="...",covering="true|false",order_by="true|false",back_table="true|false"}` - Sampled index selection outcomes for `SELECT` planning
- This metric is currently sampled at 1/100 calls to keep cardinality and write volume low
- Current `index_type` labels: `primary`, `unique`, `secondary`
- Current `outcome` labels come from the planner and include values such as `selected`, `selected_or`, `allow_filtering`, `no_index`, `no_match`, `or_disallowed`, `unsupported_where`, and `error`
- Current `rule` labels are planner-rule identifiers such as `none`, `no_schema_manager`, `index_lookup`, `condition_extract`, and `or_expand`

### Logging

Structured logging with configurable levels:

```bash
# Set log level
LOG_LEVEL=debug ./bin/proxy
```

## Development

### Project Structure

```
.
├── cmd/proxy/           # Main proxy server
├── pkg/
│   ├── cql/            # CQL protocol implementation
│   ├── mysqlbackend/   # MySQL connection pooling
│   ├── translator/     # CQL to SQL translation
│   ├── executor/       # Query execution
│   ├── parser/         # CQL parser (WIP)
│   ├── schema/         # Schema management
│   ├── auth/           # Authentication
│   ├── session/        # Session management
│   ├── logger/         # Logging utilities
│   └── metrics/        # Metrics collection
├── internal/config/    # Configuration
└── test/integration/   # Integration tests
```

### Building

```bash
# Build all packages
go build ./...

# Run tests
go test ./...

# Run integration tests (requires MySQL env vars)
MYSQL_HOST=127.0.0.1 MYSQL_PORT=3306 MYSQL_USER=root MYSQL_PASSWORD=pass \
  go test -tags=integration ./test/integration/...

# Run integration tests with race detection
MYSQL_HOST=127.0.0.1 MYSQL_PORT=3306 MYSQL_USER=root MYSQL_PASSWORD=pass \
  go test -race -tags=integration ./test/integration/...

# Build with race detection
go build -race ./cmd/proxy/...
```

## Limitations & Known Issues

1. **Query Translation**: Complex CQL queries with custom functions may not fully translate
2. **Performance**: Translation overhead may impact performance for simple queries
3. **Paging**: Token-based pagination not yet fully implemented
4. **Materialized Views**: Not yet supported
5. **Secondary Indexes**: Basic support, advanced features pending

## Roadmap

- [ ] Complete CQL parser with full grammar support
- [ ] Query optimizer for better SQL generation
- [ ] Connection pooling optimizations
- [ ] Advanced metrics and tracing
- [ ] Load balancing across MySQL replicas
- [ ] Automatic DDL synchronization
- [ ] Performance benchmarks and tuning

## Contributing

Contributions are welcome! Please:

1. Fork the repository
2. Create a feature branch
3. Add tests for new functionality
4. Ensure all tests pass
5. Submit a pull request

## License

[License information here]

## Support

For issues and questions:
- Open an issue on GitHub
- Check the troubleshooting guide
- Review the CQL to SQL translation documentation