Round-Tripping Invoice Ninja and Sylius Through a Database Diagram: What MySQL Lost and What We Fixed

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: We put two real MySQL schemas, Invoice Ninja (Laravel) and Sylius (Symfony with Doctrine), through Schemity and rebuilt a database from the SQL it wrote. Before this round of fixes MySQL refused Invoice Ninja's script, and once patched the copies built from the dumps dropped microseconds, stopped rounding a double(8,2) and accepted duplicate keys the originals refuse. After the fixes Invoice Ninja comes back with no difference at all, Sylius differs only in 16 column comments Doctrine no longer uses, and both behave the same in 19 probes.

A database diagram that reads a MySQL schema and writes it back should give you the same database, and before this round of fixes Schemity did not. We took two real open-source schemas, Invoice Ninja v5.13.47 (Laravel, 80 tables) and Sylius v2.3.0 (Symfony with Doctrine, 115 tables), put each through Schemity from its mysqldump and from the live database, and rebuilt a database from the SQL Schemity wrote. MySQL refused Invoice Ninja's script outright, and once it was patched to run, the copies built from both dumps stored different values and accepted rows the originals refuse. After the fixes, Invoice Ninja comes back with no catalog difference at all on either path, Sylius differs only in 16 column comments, and both behave exactly like their originals in 19 behaviour probes.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. This is the second post in a series that tests the round trip on each database it supports; the first, round-tripping Mastodon and Saleor, did the same for PostgreSQL. Reading a schema you did not write is how you understand your schema before changing it, and MySQL turned out to lose different things from PostgreSQL: not keys and indexes, but the column details that decide what a value is.

Which two schemas, and which two paths?

Two schemas created by their own frameworks, chosen because both declare real foreign keys. Not every MySQL application does: WordPress's own CREATE TABLE statements have none, and a round trip of a schema without them would prove little.

Each schema was created by its framework in an empty MySQL 8.4.11 database, Invoice Ninja inside the official invoiceninja/invoiceninja-debian:5.13.47 image and Sylius from sylius/sylius-standard v2.3.0, and then went through Schemity on two paths:

• Import SQL: the mysqldump --no-data file read by Schemity's SQL parser, with no connection.
• Live connection: Schemity's MySQL reader connected to the original database and read information_schema.

Both paths end the same way: the diagram's SQL is applied to an empty database on the same server. The runs used Schemity's own code from a development build, called from scripts rather than through the app's window, and the live path always read the original database, never a copy Schemity had built. The fixes described below are in that build and ship in the next release; until then, the released app behaves like the "before" column.

How do you prove a rebuilt MySQL schema is the same?

Two checks, as in the PostgreSQL post, with one addition MySQL needs.

The catalog diff. A query over information_schema lists one line per fact for the original and for the copy: every table with its engine and collation, every column with its full column_type, nullability, default, extra (auto_increment, on update), collation and comment, every index with its prefix lengths, and every foreign key with its actions. The two lists are diffed line by line.

The behaviour probes. A probe is a few statements in one transaction that is rolled back: insert a row with a microsecond timestamp, insert two company keys that differ only by a trailing space, delete an account and count its companies. Its outcome is "accepted" or "refused at step N" with MySQL's error code, and a copy passes when its outcome is the original's.

The addition is reading back. On MySQL a column that lost a detail usually does not refuse anything: it accepts the row and stores something else. So a probe can end with a SELECT, and the value it reads is part of the outcome.

The 19 probes are 11 for Invoice Ninja and 8 for Sylius, run under MySQL 8.4's default strict sql_mode, which is what makes an out-of-range or too-long value an error rather than a warning. Invoice Ninja's migrations seed a few reference tables, 21 currencies among them, which the copies do not have; every probe uses rows and ids of its own, so no seed row decides an outcome.

What failed before the fixes?

Everything below is from Schemity as of 15 October 2026, through Import SQL, the same scope as the PostgreSQL post's "before" column. The live path was already better: Schemity's MySQL reader kept every collation, and Sylius came back from it differing only in its comments. It refused Invoice Ninja's script at the same index.

MySQL refused Invoice Ninja's script. It stopped at line 492 with:

ERROR 1170 (42000): BLOB/TEXT column 'client_hash' used in key specification without a key length

With the three prefix indexes put back by hand, the script ran, and both copies could be compared with their originals.

The four probes that failed are the four that matter to the data:

Every other probe passed even before the fixes: cascades on delete and update, SET NULL, foreign keys, UNSIGNED, ENUM, JSON validity and column lengths all survived. Each failure had its own cause.

Why did MySQL refuse an index on a TEXT column?

Because MySQL can only index a TEXT column by a prefix of it. Invoice Ninja declares KEY (client_hash(20)) on a TEXT column, and two more on contact_key. The diagram held an index as a list of column names, so the 20 was dropped on both paths and the script said CREATE INDEX ... (client_hash), which MySQL refuses. The diagram now keeps a column prefix as the index's key list, `client_hash`(20), written back as it was, and the table's index list shows it. Converting such a diagram to another database writes a non-unique prefix index as a plain index on the whole column, and leaves a unique one out, named in the script's notes, because uniqueness on the first 20 characters is a different rule from uniqueness on all of them.

Why did keys that differ by a trailing space stop being duplicates?

Because the copies lost their collation, and MySQL 8's default compares trailing spaces differently. Every text column in Invoice Ninja, and all but one in Sylius, is utf8mb4_unicode_ci, which has the PAD SPACE attribute: trailing spaces are ignored, so 'probe-key' and 'probe-key ' are the same value and a unique key refuses the second. Import SQL read no character set or collation from a dump, so the copies took the server default, utf8mb4_0900_ai_ci, which is NO PAD: the two strings differ, and the duplicate goes in. MySQL's documentation on the pad attribute has the rule.

mysqldump makes this easy to miss, because it spells a collation four ways. A table's is DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci. A column that takes its table's gets a bare COLLATE utf8mb4_unicode_ci, with no character set, which is how all 593 of Invoice Ninja's text columns appear. A column set on its own gets CHARACTER SET utf8mb4 COLLATE .... And a column whose collation is its character set's default, as on Sylius's one utf8mb4_0900_ai_ci table, gets nothing. Import SQL now reads all four, takes the character set from the collation's name, and gives a column with no clause its table's, which is how the live reader reports the same columns. The same mismatch is why a foreign key between two CHAR(36) columns can fail with error 3780, covered in signed vs unsigned MySQL keys.

Why did timestamp(6) lose its microseconds?

Because a diagram field had no place for fractional seconds. A MySQL TIMESTAMP, DATETIME or TIME with no precision keeps whole seconds, and MySQL rounds a fraction away without a warning. Invoice Ninja's 141 timestamp(6) columns came back as timestamp, so every created_at and updated_at (51 of each) and deleted_at (39) was rounded to the second: two rows written in the same second sort as equal, and a value compared with the application's own microsecond copy no longer matches.

The live reader knew the 6 but used it only when it restated an existing column, never in a CREATE TABLE. A time column's precision now lives in the field's precision, the slot DECIMAL already used, on every database that has one: MySQL's TIMESTAMP(6), SQL Server's DATETIME2(3), Snowflake's TIMESTAMP_NTZ(9). The field editor offers it up to each database's maximum. Writing TIMESTAMP(6) also needs the clock to match: MySQL refuses DATETIME(6) DEFAULT CURRENT_TIMESTAMP with error 1067, so the default and ON UPDATE are written as CURRENT_TIMESTAMP(6).

Checking this post turned up one more loss in the same columns, in a shape neither schema has: ON UPDATE CURRENT_TIMESTAMP(6), which sets updated_at to the current time whenever a row's values change, even when the UPDATE does not set it. Both paths read it, but the diagram had no place for it, so a table written from the diagram lost it and the copy's updated_at stopped moving. The diagram now keeps it on both paths, the field editor sets it on a TIMESTAMP or DATETIME column, and a rebuilt copy updates the column as the original does.

The fix changed the PostgreSQL post too. Mastodon's 92 timestamp(6) columns had been written back as timestamp, which PostgreSQL treats as the same type, since its default is 6; they now come back as declared, and that post was updated. Converting a diagram between databases had the opposite gap: a PostgreSQL timestamp keeps 6 digits but became MySQL's bare DATETIME, which keeps none. It now becomes DATETIME(6).

Why did double(8,2) stop rounding?

Because the precision and scale of a DOUBLE were never written. DOUBLE(8,2) rounds every value to two decimals as it is stored, so 1.239 becomes 1.24; a plain DOUBLE keeps 1.239. MySQL marks FLOAT(M,D) and DOUBLE(M,D) as deprecated, but a schema that has them still rounds. The field dialog already let you enter a precision and scale for DOUBLE, and Import SQL dropped them while the MySQL export wrote them only for DECIMAL. Both are fixed.

Which bugs did only the live path and a refresh find?

Four, none of which a catalog diff of the import path could show:

• An empty-string default. Invoice Ninja's countries.decimal_separator is DEFAULT ''. MySQL reports that as an empty column_default, and the live reader read it as no default, so a copy built from the live path stored NULL where the original stores an empty string. It now reads ''.
• A refresh straight after the import. Import a dump, connect to the database it came from, and Refresh & merge should find nothing to change. Measured through the app's own merge code, called from a script, it planned 1,210 statements for Invoice Ninja and 537 for Sylius, because the two paths spelled the same schema differently: a tinyint(1) default as '0' against FALSE, a column's character set as empty against utf8mb4, an index type as blank against BTREE, and CHAR against CHARACTER. Each is now read the same way on both paths, and both refreshes list nothing. Mastodon and Saleor, run through the same check, list nothing too.
• Import SQL over the live diagram. Taking these screenshots, importing Invoice Ninja's dump onto the diagram read from its live database marked all 80 tables Updated, although a migration between the two is zero statements. The preview compared the two tables field by field, so the same spelling differences counted as changes. It now asks the planner Refresh & merge uses which tables a migration would touch, and marks the other tables Unchanged.
• Column order, through the app. Found later, while taking the SQLite post's screenshots, and invisible to the scripts here because they skip a step the app takes when it reads a table: the app lists a table's fields key first, foreign keys next and timestamps last, and wrote CREATE TABLE in that order, so a copy built from the diagram met a SELECT * or an INSERT without a column list with its columns in another order. The diagram now remembers each column's position as the table declares it and writes that order, while the canvas keeps its own; a column added in the diagram goes last, where ADD COLUMN puts it. Read and written back the way the app does it, 74 of Invoice Ninja's 80 tables and 81 of Sylius's 115 came back in a different column order before this fix.

What does the round trip give you now?

The same database, on both paths, for both schemas.

A refresh from the original database straight after importing its dump lists no changes for either schema.

The other direction holds in the app's window: Import SQL of the dump onto the diagram read from the live database finds nothing to change.

All 437 UNSIGNED columns, 141 timestamp(6) columns, 3 prefix indexes, 13 ENUM value lists, 279 foreign keys with their actions and every table's and column's collation come back as they were, including the one Sylius table on utf8mb4_0900_ai_ci, whose NO PAD probe accepts the trailing-space pair in the original and in both copies.

What still differs, and why?

Only Sylius's column comments, and they are left out on purpose. Schemity treats a field's description as the diagram's own notes and does not write it to the database: on MySQL a comment can only be changed by restating the whole column with MODIFY COLUMN, and an edit to a note should never be the riskiest statement in a migration. The 16 comments are Doctrine type hints such as (DC2Type:datetime_immutable). Sylius 2.3 runs on Doctrine DBAL 4.5, which no longer writes or reads type comments; its upgrade notes say doctrine/migrations should generate a migration dropping them, so a copy without them is what Doctrine itself would arrive at.

What this benchmark does not show

• The app's window. These runs called Schemity's import, reader, export and comparison code from scripts on a development build. The screenshots are from the app's window on the same development build, and the field dialog for ON UPDATE is on a small test table, since neither schema has that clause.
• The live path before the fixes, in full. The "before" column covers Import SQL. On the live path, Invoice Ninja's script was refused at the same prefix index and Sylius differed only in its comments; Invoice Ninja's live copy was not patched and probed.
• Data. The originals are empty. A schema that matches is the precondition for moving data, not proof that a migration of a full database is safe.
• Views, triggers, routines and CHECK constraints. Neither schema has any, so nothing here tests them on MySQL.
• MariaDB. Everything ran on MySQL 8.4.11; MariaDB's collations and defaults differ.
• Other databases. SQL Server and SQLite have their own posts, with Bitwarden and Smartstore and Campfire and Jellyfin, and Snowflake with TPC-DS.
• Schemas this size are not a stress test. 80 and 115 tables are typical; a thousand-table schema is covered in a separate post.

How to rerun it

Create each schema with its framework against an empty MySQL 8.4 database, take mysqldump --no-data --skip-comments --routines --triggers, put it through Schemity on either path, export the SQL and apply it to an empty database. Then list both catalogs with a query like this one, run with mysql -N -D <database>, and diff the output:

SELECT * FROM (
SELECT CONCAT_WS(' | ', 'table', table_name, engine, table_collation, table_comment)
  FROM information_schema.tables WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE'
UNION ALL
SELECT CONCAT_WS(' | ', 'column', table_name, column_name, column_type,
  IF(is_nullable = 'YES', 'null', 'not null'), IFNULL(CONCAT('default ', column_default), 'no default'),
  extra, IFNULL(collation_name, '-'), column_comment, IFNULL(generation_expression, ''))
  FROM information_schema.columns WHERE table_schema = DATABASE()
UNION ALL
SELECT CONCAT_WS(' | ', 'index', table_name, index_name, IF(non_unique = 0, 'unique', 'non-unique'), index_type,
  GROUP_CONCAT(CONCAT(IFNULL(column_name, expression), IFNULL(CONCAT('(', sub_part, ')'), ''), IF(collation = 'D', ' desc', ''))
    ORDER BY seq_in_index SEPARATOR ', '))
  FROM information_schema.statistics WHERE table_schema = DATABASE()
  GROUP BY table_name, index_name, non_unique, index_type
UNION ALL
SELECT CONCAT_WS(' | ', 'fk', k.table_name, k.constraint_name,
  GROUP_CONCAT(k.column_name ORDER BY k.ordinal_position SEPARATOR ', '),
  k.referenced_table_name, GROUP_CONCAT(k.referenced_column_name ORDER BY k.ordinal_position SEPARATOR ', '),
  CONCAT('on delete ', r.delete_rule), CONCAT('on update ', r.update_rule))
  FROM information_schema.key_column_usage k
  JOIN information_schema.referential_constraints r
    ON r.constraint_schema = k.constraint_schema AND r.constraint_name = k.constraint_name AND r.table_name = k.table_name
  WHERE k.table_schema = DATABASE() AND k.referenced_table_name IS NOT NULL
  GROUP BY k.table_name, k.constraint_name, k.referenced_table_name, r.delete_rule, r.update_rule
UNION ALL
SELECT CONCAT_WS(' | ', 'check', t.table_name, c.constraint_name, c.check_clause)
  FROM information_schema.check_constraints c
  JOIN information_schema.table_constraints t
    ON t.constraint_schema = c.constraint_schema AND t.constraint_name = c.constraint_name AND t.constraint_type = 'CHECK'
  WHERE c.constraint_schema = DATABASE()
) catalog ORDER BY 1;

For behaviour, run a few statements per rule inside START TRANSACTION and ROLLBACK on the original and on the copy, end each with a SELECT of what was stored, and compare both the step MySQL refuses at and the values it reads back. A database that matches on refusals alone can still be rounding every timestamp.