Skip to content
dbprint

MySQL

Terminal window
$ pip install 'dbprint[mysql]'

The extra carries the MySQL Connector/Python driver. Nothing else is needed — DDL comes from SHOW CREATE TABLE rather than an external binary.

Fully-qualified names are database.table.

Privileges

CREATE USER 'dbprint_ro'@'%' IDENTIFIED BY '...';


GRANT SELECT ON my_db.* TO 'dbprint_ro'@'%';


-- Only where views are profiled.
GRANT SHOW VIEW ON my_db.* TO 'dbprint_ro'@'%';


-- Only when a table is narrowed to a fraction; see "When dbprint writes".
GRANT CREATE TEMPORARY TABLES ON my_db.* TO 'dbprint_ro'@'%';
PrivilegeOnNeeded for
SELECTthe databaseconnecting, enumerating, DDL and statistics — a plain view takes it for DDL only under the default read.views.mode: catalog, which issues no statement against one; a view read in full or filter mode is queried like a table
SHOW VIEWthe databasea view's DDL only
CREATE TEMPORARY TABLESthe databasethe sampled-table copy — without it, a sample-scoped table is refused rather than degraded

What an under-privileged account does

MySQL refuses the connection outright rather than degrading. An account with no grant on the database fails at the first step:

primary: could not connect to MySQL at 127.0.0.1:3306/my_db as 'dbprint_ro':
1044 (42000): Access denied for user 'dbprint_ro'@'%' to database 'my_db'

and the run exits 4 having profiled nothing. There is no partial print to inspect, which makes a missing grant here easier to diagnose than on most other engines.

SHOW VIEW is the one grant that is easy to miss, because it is needed for exactly one thing. A view's DDL comes from SHOW CREATE TABLE, which MySQL gates behind SHOW VIEW for views specifically. Without it the tables profile normally and every view fails:

1 table failed: ProgrammingError: 1142 (42000): SHOW VIEW command denied to user
'dbprint_ro'@'localhost' for table `my_db`.`taxon_names`
  operation: extract_ddl
  first: my_db.taxon_names

A project whose selectors exclude views does not need the grant at all.

Sampling

Constructa RAND(seed) predicate in a wrapping subquery
Seededyes, from the table's own name
looks_like sub-drawORDER BY RAND() LIMIT, not seeded

MySQL has no TABLESAMPLE, so a fraction is expressed as a predicate over a seeded RAND(). The seed makes one statement reproducible; what makes a whole profile coherent is the materialized copy below, since MySQL does not document what multiple references to one seeded RAND() return. Across runs the draw is stable only while scan order is, so treat run-to-run agreement on a sampled MySQL table as likely rather than guaranteed.

The extra distinct-value draw that inferred.looks_like takes on top of the sampled rows takes no seed, because MySQL's behaviour for multiple seeded RAND() references in one statement is undocumented. So on MySQL a shape claim agrees with the rest of a sampled profile at the population level rather than row for row. In practice that means sampled and matched on a column's inferred block describe a draw the other statistics did not see.

When dbprint writes

Only one thing makes dbprint write, and only under one condition: profile.read.tables.materialize is on by default, and it fires only for a table read in sample mode — by a fraction, or by a max_rows ceiling that resolves below the table's size, at whatever level the ceiling is set. A project that narrows nothing, which is what dbprint init scaffolds, never writes. A filter is a predicate rather than a fraction and never materializes.

Where it does fire, the drawn rows are copied once into a session-lifetime TEMPORARY table, and every statistics statement for that table reads the copy. That is what makes the numbers in one file describe one set of rows.

Where CREATE TEMPORARY TABLES is absent the run does not fail the whole connection, but it does refuse that table. RAND(seed)'s behaviour across multiple references in one statement is undocumented, so MySQL cannot fall back to re-evaluating the predicate per statement the way PostgreSQL does — it raises instead:

table 'my_db.germination_trial': connection 'primary' (mysql) could not materialize its
sample of 0.25 (ProgrammingError: 1044 (42000): Access denied for user 'dbprint_ro'@'%' to
database 'my_db'), and this adapter's per-statement fallback cannot be seeded into agreement
across statements. Narrow with mode: filter instead of a sample.

MySQL reports the underlying cause as a plain access-denied error rather than one naming temporary tables specifically, so the parenthesized cause is worth reading in full before concluding the account lost its SELECT rather than CREATE TEMPORARY TABLES.

There is no fallback path to choose here: setting read.tables.materialize: false on a sample-mode table refuses it before any statement runs, for the same reason — MySQL's undocumented RAND(seed) guarantee means an unmaterialized draw is never trusted.

Listed values

A SET column lists its values as MySQL's own text, members in declared order (red,blue); a BIT column lists the integer a query compares with (b'101' is 5).

Column types

TypeProfiled asWhat to know
JSONjsontypes keys are what JSON_TYPE returns
geometry, point, linestring, polygon, their multi forms, geometrycollectionspatialOnly an empty collection counts as empty, since MySQL has no POINT EMPTY. A column holding a geographic SRID reports extent unmeasured. On MariaDB, invalid_count is absent
VECTOR(N)vectornorm and zero_count are always reported unmeasured: no MySQL function outside HeatWave takes a vector's norm

Table kinds

A MariaDB system-versioned table is profiled over its current rows; history rows are never read, and its implicit row_start/row_end columns are not published. Where your session sets system_versioning_asof, the print describes the table as of that time. A SEQUENCE is not listed.

A MariaDB view never carries depends_on: MariaDB's catalog holds only the view's definition text.

A FEDERATED, CONNECT or SPIDER table is listed with external: true (SPEC 2.2.20) and described from the catalog alone. A rule writing any other read.tables.mode for it is refused before any table is read, with exit 1, because the read would reach another live database. MERGE, CSV, ARCHIVE, MEMORY, BLACKHOLE and NDB are ordinary tables.

Credentials in a remote table's DDL

A FEDERATED, CONNECT or SPIDER table keeps its remote login in its own options, and SHOW CREATE TABLE returns it verbatim. ddl.sql replaces the password with [HIDDEN] wherever the options carry one — a URL's user:password@, a PWD= or Password= value, a Spider password, REMOTE_PASSWORD — and keeps the rest of where the rows live. These are the credential masks every adapter's DDL passes through (SPEC 2.1.3), so a table on any engine, a view and a comment are masked the same way. A CONNECTION naming a CREATE SERVER object carries no password; dbprint never reads mysql.servers.

Namespaces

database is optional. Omitted, the session has no default database and the connection reads every database information_schema shows the account, less information_schema, mysql, performance_schema and sys; a database the account cannot read is simply absent. A sampled table's copy lands in the table's own database, so CREATE TEMPORARY TABLES is needed on each database holding a sampled table. With no database, an unqualified name in dbprint check --online SQL assertions has nothing to resolve against.

Time zone

dbprint sets the session time_zone to +00:00 on every connection, so a TIMESTAMP is read as its UTC instant on every path — a view, a filter, a sample — including the hour a daylight-saving zone repeats, which no conversion back from local time can tell apart. DATETIME has no zone and reads unchanged. A read.*.filter predicate and dbprint check --online SQL run in that session too: NOW(), CURDATE(), CURRENT_TIMESTAMP and a string literal compared with a TIMESTAMP column are UTC. Write a filter's boundaries in UTC.

The row-count estimate

A table's size, and a sampled table's row_count, come from information_schema.tables.table_rows. InnoDB's figure is approximate by design — MySQL documents that it may differ from the true count by as much as 40 to 50 percent — and follows deletes when InnoDB next refreshes its own statistics, so a sampled table publishes row_count_method: approximate.

Statement timeout

run.statement_timeout is applied once per session. On Oracle MySQL it is max_execution_time, which bounds read-only SELECT statements only: SHOW CREATE TABLE, the sample copy (CREATE TEMPORARY TABLE ... SELECT), DROP and statements inside stored programs run unbounded there. On MariaDB it is max_statement_time, which covers every statement but is checked at intervals, so a statement can overrun it briefly. A statement that exceeds the limit is cancelled by the server.

Every session also removes NO_BACKSLASH_ESCAPES from its sql_mode, keeping every other mode, and a session that cannot fails the connection naming sql_mode. A JSON document key read back from the data is spelled into SQL as a string literal with backslash escapes, which that mode would turn into the literal's end.

Parallelism

run.parallelism: N opens N connections and profiles up to N tables at once. Each counts against the server's max_connections and the account's MAX_USER_CONNECTIONS; a connection the server refuses leaves the run on the rest, with a warning.

Reference