MySQL Generated Invisible Primary Keys: GIPK Mode in Production

The worst replication lag I ever untangled was caused by a table with no primary key. An ORM had created a join table with two foreign key columns and nothing else, it grew to about 40 million rows, and every batch of row events the replica tried to apply against it became a full table scan. Sixty thousand row events, and a 40-million-row scan behind each batch of them. Seconds_Behind_Source climbed into five figures while the source sat nearly idle. No error, no warning, just an applier thread doing the slowest possible lookup for every single change because the row events had no key to match on.

MySQL 8.0.30 finally gave this problem a server-side answer: generated invisible primary keys, universally shortened to GIPK. This is what the mode actually does, why PK-less tables hurt in the first place, what an invisible column changes for your applications, how GIPKs behave across replication and dumps, and how to fix the tables you already have.

Why do PK-less InnoDB tables hurt so much?

Two separate penalties, and most people only know about one. The first is internal: InnoDB tables are clustered index organisms, so a table with no primary key and no usable unique key gets a hidden clustered index, GEN_CLUST_INDEX, built on a six-byte row id that InnoDB assigns from a single global counter shared by every PK-less table on the instance. Your rows are physically ordered by an id you cannot see, cannot query, and that means nothing to the table itself, and inserts across all such tables serialize on that shared counter under enough concurrency. The clustered index versus heap note covers why the clustered structure matters so much in the first place.

The second penalty is the one that took down my replica: row-based replication. When a row event arrives, the applier must find the matching row, and its search strategy is controlled by slave_rows_search_algorithms — still the slave_ spelling in 8.0, where it defaults to INDEX_SCAN,HASH_SCAN. With no primary or unique key on the replica's copy of the table, there is no index to search, so the applier falls back to hash scans: one full sweep of the table per batch of row events, hashing rows to match them. That is more merciful than one scan per event, but on a 40-million-row table the mercy is academic. Updates and deletes on a large PK-less table replicate at a pace best measured in geological time. Add the operational insults: pt-online-schema-change and gh-ost both want a primary or unique key to key their work, Group Replication refuses PK-less tables outright, and MySQL 8.0.13 added sql_require_primary_key precisely because operators wanted the server to reject this footgun at DDL time.

What does GIPK mode actually add?

Set sql_generate_invisible_primary_key=ON and every subsequent CREATE TABLE on InnoDB that specifies no primary key silently grows one: a first column named my_row_id, BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, marked INVISIBLE, with PRIMARY KEY (my_row_id) on it. The variable is dynamic and available at both global and session scope, and it defaults to OFF, so nothing changes until you opt in:

SELECT @@sql_generate_invisible_primary_key AS gipk_mode;
SET PERSIST sql_generate_invisible_primary_key = ON;

Three boundaries are worth memorizing. The mode only affects CREATE TABLE; existing tables are never retrofitted, no matter how long the variable stays on. Tables that already declare any primary key are untouched, so enabling it fleet-wide is safe for well-formed schemas. And my_row_id becomes a reserved name in this mode: try to create a PK-less table that already has a column called my_row_id and the CREATE fails with error 4108, failed to generate invisible primary key. In practice that error is how you discover your ORM has opinions you did not know about.

What changes day to day with an invisible column?

Surprisingly little, until the moment it matters. SELECT * and the TABLE statement skip invisible columns, so application reads are unchanged. A positional INSERT with no column list provides values for visible columns only, and the invisible column gets its implicit default, the next auto-increment value, so existing inserts keep working untouched. The classic break is the copy pattern: INSERT INTO t2 SELECT * FROM t1 fails when exactly one of the two tables has a GIPK, because the visible column counts no longer match. The same mismatch bites when you pre-create a table without a GIPK and load a dump whose rows carry my_row_id values positionally.

Visibility of the metadata itself is a separate switch. With show_gipk_in_create_table_and_information_schema at its default ON, the column shows up in SHOW CREATE TABLE, SHOW COLUMNS, SHOW INDEX, and INFORMATION_SCHEMA.COLUMNS and STATISTICS, so it is not actually hidden from you, just from star selects:

SELECT column_name, ordinal_position, data_type, column_key, extra
FROM information_schema.columns
WHERE table_schema = 'app' AND table_name = 'events'
ORDER BY ordinal_position;

My house rule: on tables that humans query interactively, make the key visible with ALTER TABLE events ALTER COLUMN my_row_id SET VISIBLE, which is an instant metadata-only change. Invisibility buys ORM compatibility, not secrecy, and a column people can see in SELECT * is a column people stop being surprised by. Note the guardrails: with GIPKs enabled you cannot drop the generated key if that would leave the table with no primary key at all, and you cannot drop the key while keeping the column.

How do GIPKs behave across replication and dumps?

The setting itself does not replicate: the manual states plainly that sql_generate_invisible_primary_key is ignored by replication applier threads, so a replica does not inherit the mode from the source. What saves the day is that the CREATE TABLE written to the binary log is the rewritten statement, with my_row_id and its primary key spelled out, marked with the versioned comment syntax for the INVISIBLE attribute. Replicas therefore build the same schema regardless of their own GIPK setting, and row events match key to key from the start. From MySQL 8.0.32 there is also a per-channel escape hatch: CHANGE REPLICATION SOURCE TO with REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE makes the replica itself add GIPKs to tables arriving without primary keys on that channel. One sharp edge: row-based binlogging of CREATE TABLE AS SELECT carries the GIPK definition correctly, but statement-based replication of CTAS is explicitly not supported with GIPK mode on.

Dumps got first-class handling in the same release. mysqldump includes GIPK columns and their values by default, which is the round-trip-safe choice, and --skip-generated-invisible-primary-key excludes them when you are loading into something that cannot cope; mysqlpump has the matching option. The versioned INVISIBLE comment means a pre-8.0.23 server restores the column as a plain visible column instead of erroring, which is graceful, though you should know your restore target just silently changed semantics. The dump-and-restore story on large schemas has its own traps, covered in mydumper versus mysqldump.

How do you find and migrate existing PK-less tables?

The audit is one information_schema query, and I run it on every new fleet I inherit:

SELECT t.table_schema, t.table_name, t.table_rows
FROM information_schema.tables AS t
LEFT JOIN information_schema.table_constraints AS tc
  ON tc.table_schema = t.table_schema
 AND tc.table_name = t.table_name
 AND tc.constraint_type = 'PRIMARY KEY'
WHERE t.engine = 'InnoDB'
  AND t.table_schema NOT IN ('mysql', 'sys',
                             'performance_schema', 'information_schema')
  AND tc.constraint_name IS NULL
ORDER BY t.table_rows DESC;

Because GIPK mode never touches existing tables, migration is an explicit ALTER per table:

ALTER TABLE legacy_events
  ADD COLUMN row_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ADD PRIMARY KEY (row_id);

That statement rebuilds the table, so on anything large you want gh-ost or pt-online-schema-change instead, both of which are much happier once they can see the plan above is the goal. Two opinions from scar tissue. First, for existing tables I add a visible, honestly named key rather than replicating the invisible my_row_id style; invisibility exists for schemas you cannot change, and a table you are already altering deserves a key its developers can see and use. Second, if Group Replication or InnoDB Cluster is anywhere in your future, do this audit now, because those layers hard-require primary keys and GIPK is precisely the feature that satisfies their check for new tables while you migrate the old ones. If the replica side of your house is already showing strain, the triage in MySQL replication lag diagnosis pairs well with the fix.

Where MonPG stands on MySQL

I build MonPG, and I will keep this honest: it monitors PostgreSQL today, while MySQL support is in active development and coming soon. The failure mode that opens this note is a monitoring failure as much as a schema failure: a monitor should flag PK-less InnoDB tables before they hit a replica, and graph applier behavior so a table-scan-driven lag spike reads as a schema problem, not a mystery. That is the standard the MySQL work is being built to. The MySQL monitoring (coming soon) page is where it shows up as it lands. Until then, the same evidence-first monitoring already runs on the PostgreSQL side, and the rest of these field notes are on the blog.