MYSQL 5.7

PROFILES.YML — A COMPLETE WORKING EXAMPLE

TCP with username and password, in ~/.dbt/profiles.yml — DVT reads the same profiles file dbt does:

my_project:
  target: legacy_dev
  outputs:
    legacy_dev:
      type: mysql5
      host: legacy-db.internal.example.com
      port: 3306                # optional — 3306 is the driver default
      user: analytics
      password: "{{ env_var('MYSQL_PASSWORD') }}"
      schema: analytics_db      # on MySQL, the schema IS the database
      threads: 4

No database: key — on the MySQL family, schema: alone names the database you build in (the rule is spelled out below). Then:

$ dvt sync     # installs the mysql5 adapter + driver from the profile
$ dvt debug    # verifies the connection — and names the 5.7 cliff up front

dvt sync sees type: mysql5 and installs the adapter and its driver (mysql-connector-python) into the one DVT environment. The adapter runs there like every other engine — no separate runtime, no side installs.

OTHER WAYS TO CONNECT

Unix socket — for a server on the same machine, point at the socket file instead of a host. If a host is present it wins and the socket is ignored:

    legacy_local:
      type: mysql5
      unix_socket: /var/run/mysqld/mysqld.sock
      user: analytics
      password: "{{ env_var('MYSQL_PASSWORD') }}"
      schema: analytics_db

Plain-TCP servers without TLS — many 5.x boxes never had certificates. If the handshake trips over a half-configured TLS setup, ssl_disabled turns encryption off for this connection explicitly:

    legacy_dev:
      type: mysql5
      host: legacy-db.internal.example.com
      user: analytics
      password: "{{ env_var('MYSQL_PASSWORD') }}"
      schema: analytics_db
      ssl_disabled: true        # explicit: no TLS on this connection

THE 5.7 SURFACE: NO CTES, NO WINDOW FUNCTIONS

CTEs (WITH) and window functions arrived in MySQL 8.0 — a 5.7 server simply does not have them. DVT doesn't soften that: it names it before you hit it. dvt debug prints an engine-preflight row for every mysql5 profile:

Engine preflight:
  mysql5: legacy MySQL 5.x — no CTEs or window functions server-side
  (MySQL 8.0 features); its adapter runs from the one dvt environment
  like every other engine; sources declaring `database:` fail parse

The practical consequences, in order of how often they bite:

Ephemeral models can't run here. dbt inlines an ephemeral model into its consumers as a CTE — and 5.7 has no WITH. Materialize those models as view instead. The classic portable pattern keeps one project runnable on both eras:

{{ config(
    materialized=('view' if target.type == 'mysql5' else 'ephemeral')
) }}

Window functions need a rewrite, not a workaround flag. row_number() over (...)and friends have no 5.7 equivalent; models that need them belong on a newer engine — or run federated, where DVT's own compute does the windowing and only the finished rows land in MySQL 5.7.

DVT's own tooling stays inside the 5.7 dialect. Profiling, metadata reads and generated SQL avoid CTEs on this engine by construction — the cliff is yours to mind only in the SQL you write.

ONE NAMESPACE: SCHEMA IS THE DATABASE

As on all MySQL-family engines, schema: names the database DVT builds in. database: may be omitted (recommended) or set to exactly the same value — anything else is refused before a query runs:

    schema: analytics_db
    database: other_db
On MySQL, database must be omitted or have the same value as schema.

Sources follow the same law: declare schema: only — a source carrying a different database: is a hard parse error on this family. Cross-database reads on one server need nothing special: other_db.some_table is ordinary MySQL SQL.

PURE-PYTHON DRIVER BY DEFAULT (USE_PURE)

The mysql5adapter connects with the driver's pure-Python implementation — use_pure: trueis the default. The reason is blunt: the driver's C extension has a crash class where a failed connect segfaults, and a segfault kills the interpreter outright — no traceback, and no retry can ever fire because no Python exception is ever raised. The pure implementation turns driver failures back into ordinary, catchable errors. This lane carries only DDL, catalog reads and small statements — bulk rows ride DVT's data-movement layer — so the C extension's speed buys nothing here. use_pure: false opts back in if you must.

INCREMENTAL MODELS — NO MERGE, AND THAT'S FINE

MySQL has never had a MERGE statement — 5.7 is no exception. An incremental model with a unique_key runs as delete+insert— matching rows deleted, new rows inserted; without one it's a plain append. There's no incremental_strategy to pick: the key decides. Nothing polices the config either — the materialization never reads incremental_strategy, so a strategy written on a 5.7 model is silently ignored rather than refused, and there is no error text for a wrong one. Note the incremental predicate below is 5.7-safe on purpose — a subquery, not a CTE:

{{ config(
    materialized='incremental',
    unique_key='invoice_id'    -- present: delete+insert; absent: append
) }}

select invoice_id, amount, updated_at
from {{ source('legacy_app', 'invoices') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

The federated lane is the one place the strategy matters. When 5.7 is the target of an f_incremental model, DVT computes the model and lands the rows through its data-movement layer, so the strategy picks a load mode rather than compiling into MySQL SQL. Three names are understood there — append, merge and delete+insert — and the last two resolve to the same key-based upsert on unique_key. insert_overwrite and microbatch are refused by name with DVT025: "has no Sling equivalent — f_incremental supports append, merge and delete+insert". Either upsert without a unique_key warns and falls back to a full refresh rather than failing, as does append without a watermark_column.

The keyless-watermark shape — an f_incremental carrying a watermark_column and no unique_key — is admitted only on the engines DVT has measured end to end: postgres, redshift, mysql, duckdb, sqlite, sqlserver, oracle, clickhouse and trino. MySQL 5.7 is on that list: the connection mapper resolves a mysql5 profile through the MySQL mapper, so the engine key DVT matches against is mysql. A keyless f_incrementallanding here takes the watermark lane, with the watermark column as Sling's update key.

USING IT LIVE: 5.7 AS A FEDERATION SOURCE

Federation is the kindest thing you can do to a legacy server: DVT extracts the rows and computes elsewhere, so the 5.7 box never runs the modern SQL. Declare the connection on a source and join it with anything:

# models/sources.yml
sources:
  - name: legacy_app
    meta:
      connection: legacy_dev    # the profiles.yml output above
    schema: analytics_db
    tables:
      - name: invoices

-- models/marts/invoice_ranks.sql
-- window function over 5.7 data: DVT's engine does the windowing,
-- the 5.7 server only ever hands over rows
{{ config(materialized='f_table') }}

select customer_id, invoice_id,
       row_number() over (partition by customer_id order by amount desc) as rnk
from {{ source('legacy_app', 'invoices') }}

Loads back into 5.7 stage through a temp table — no external staging area, no extra privileges beyond ordinary read/write.

QUOTING, CASE, AND OTHER ENGINE MANNERS

Backticks quote identifiers; "double quotes"are string literals by default, not identifiers. Keep names lower-case and unquoted and the dialect never surprises you — table-name case follows the server's filesystem via lower_case_table_names. Catalog support is full through information_schema (written CTE-free for this engine), so dvt docs generate, dvt metadata, profiling and dvt generate-sources all work.

LOCAL DEV IN ONE LINE

A scratch 5.7 to test against (amd64 image; add the platform flag on Apple Silicon):

$ docker run -d --name dvt-mysql5 --platform linux/amd64 \
    -e MYSQL_ROOT_PASSWORD=devpass -e MYSQL_DATABASE=analytics_db \
    -p 3307:3306 mysql:5.7

Then host: 127.0.0.1, port: 3307, user: root, schema: analytics_db — and dvt debug will greet you with the preflight row above.

REFERENCE — EVERY PROFILE FIELD

Everything the type: mysql5 profile entry reads. Aliases land on the same field — write either spelling, not both.