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

> 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.

Source: https://schemity.com/blog/mysql-round-trip-invoice-ninja-sylius/

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](https://schemity.com/blog/postgres-round-trip-mastodon-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.

| | Invoice Ninja v5.13.47 | Sylius v2.3.0 |
| --- | --- | --- |
| What it is | Invoicing and billing | E-commerce platform |
| Created by | Laravel, `php artisan migrate` | Symfony with Doctrine, `doctrine:migrations:migrate` |
| Tables | 80 | 115 |
| Columns | 1,727 | 738 |
| Foreign keys | 138: 127 `ON DELETE CASCADE`, 114 `ON UPDATE CASCADE` | 141: 71 `ON DELETE CASCADE`, 10 `ON DELETE SET NULL` |
| Unique keys | 34 | 78 |
| `UNSIGNED` columns | 437 | 0 |
| `timestamp(6)` columns | 141 | 0 |
| `tinyint(1)` flags | 224 | 40 |
| Indexes on a column prefix | 3 | 0 |
| `ENUM` / `JSON` columns | 13 / 2 | 0 / 15 |
| Collation | `utf8mb4_unicode_ci` throughout | `utf8mb4_unicode_ci`, one table `utf8mb4_0900_ai_ci` |
| Column comments | 0 | 16 |
| Views, triggers, routines, `CHECK` | none | none |

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.

![Schemity's Import SQL summary for Invoice Ninja's mysqldump on an empty diagram: the last rows of the table list, from subscriptions to webhooks, each marked New with its column count, and the button Import (80 new, 0 updated)](https://schemity.com/images/blog/mysql-round-trip-invoice-ninja-sylius/invoiceninja-import-summary.webp)

![Part of the Invoice Ninja diagram read from the live database at full size, with the Activity, Documents & Tags legend in view, the Accounts & Users legend beside it and the minimap in the bottom right corner; the status bar reads Entities: 80, Relations: 138 (+132 virtual hidden), MySQL: 8.4.6](https://schemity.com/images/blog/mysql-round-trip-invoice-ninja-sylius/invoiceninja-minimap.webp)

## 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.

| Check | What it compares | What it catches |
| --- | --- | --- |
| Catalog diff | Tables, columns, indexes and foreign keys, as `information_schema` reports them | Anything missing, added or changed in the schema |
| Probes, accept or refuse | Which rows each database accepts | A unique key or foreign key that behaves differently |
| Probes, read back | What each database stored | A value rounded or defaulted differently |

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:

```sql
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.

| | Invoice Ninja | Sylius |
| --- | --- | --- |
| Script ran | no, error 1170, ran once patched | yes |
| Columns that lost their collation | 593 | 258 |
| Tables that lost their collation | all 80 | 114 of 115 |
| `timestamp(6)` written as `timestamp` | 141 | 0 |
| `double(8,2)` written as `double` | 2 | 0 |
| Column comments lost | 0 | 16 |
| Probes that match the original | 8 of 11 | 7 of 8 |

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

| Probe | Original | Before the fixes | After |
| --- | --- | --- | --- |
| Invoice Ninja: a timestamp set to `10:00:00.123456` | stores `.123456` | stores `10:00:00` | stores `.123456` |
| Invoice Ninja: a discount of `1.239` in a `double(8,2)` | stores `1.24` | stores `1.239` | stores `1.24` |
| Invoice Ninja: two company keys differing only by a trailing space | refused, duplicate | accepted | refused |
| Sylius: two customer emails differing only by a trailing space | refused, duplicate | accepted | refused |

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](https://schemity.com/doc/export-sql-ddl/) 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.

![Schemity's index dialog for clients_client_hash(20)_index on Invoice Ninja's clients table, with Keys set to client_hash(20) and Type BTREE, over the table's index list, where the same index shows the key client_hash(20)](https://schemity.com/images/blog/mysql-round-trip-invoice-ninja-sylius/index-prefix.webp)

### 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](https://dev.mysql.com/doc/refman/8.4/en/charset-binary-collations.html) 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](https://schemity.com/doc/reverse-engineer-database/) 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](https://schemity.com/blog/mysql-unsigned-vs-signed-primary-key/).

![Schemity's field dialog for companies.company_key in the Invoice Ninja diagram imported from its dump: a VARCHAR of length 100, Unique, with Charset utf8mb4 and Collation utf8mb4_unicode_ci](https://schemity.com/images/blog/mysql-round-trip-invoice-ninja-sylius/field-collation.webp)

### 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](https://dev.mysql.com/doc/refman/8.4/en/fractional-seconds.html). 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](https://schemity.com/doc/tables-and-fields/) 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.

![Schemity's field dialog for updated_at on a three-table test diagram: TIMESTAMP with Precision 6, Default CURRENT_TIMESTAMP(6), and On update, set to the current time ticked, with the note that MySQL sets the column whenever the row's values change and a link to the MySQL documentation](https://schemity.com/images/blog/mysql-round-trip-invoice-ninja-sylius/field-timestamp-on-update.webp)

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](https://dev.mysql.com/doc/refman/8.4/en/floating-point-types.html), 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.

![Schemity's field dialog for recurring_quotes.discount in the Invoice Ninja diagram imported from its dump: a DOUBLE with Precision 8 and Scale 2 and a custom default of 0.00](https://schemity.com/images/blog/mysql-round-trip-invoice-ninja-sylius/field-double-precision.webp)

### 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](https://schemity.com/doc/resync-database/) 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.

| | Import SQL | Live connection |
| --- | --- | --- |
| Invoice Ninja: catalog differences | none | none |
| Invoice Ninja: probes that match the original | 11 of 11 | 11 of 11 |
| Sylius: catalog differences | 16 column comments | 16 column comments |
| Sylius: probes that match the original | 8 of 8 | 8 of 8 |

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.

![Schemity's Import SQL summary for Invoice Ninja's mysqldump on the diagram read from the live database: the last rows of the table list, from subscriptions to webhooks, each marked Unchanged, and the button Import (0 new, 0 updated) disabled; the status bar reads Entities: 80, Relations: 138 (+132 virtual hidden)](https://schemity.com/images/blog/mysql-round-trip-invoice-ninja-sylius/import-sql-unchanged.webp)

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.

![Schemity's Entity SQL dialog for Invoice Ninja's clients table, in the diagram imported from its dump, scrolled to the end: text columns with CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, created_at, updated_at and deleted_at as TIMESTAMP(6), the table's ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, and CREATE INDEX clients_client_hash(20)_index ON clients (client_hash(20))](https://schemity.com/images/blog/mysql-round-trip-invoice-ninja-sylius/view-sql-clients.webp)

## 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](https://github.com/doctrine/dbal/blob/4.0.0/UPGRADE.md); 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](https://schemity.com/blog/sqlserver-round-trip-bitwarden-smartstore/) and [Campfire and Jellyfin](https://schemity.com/blog/sqlite-round-trip-campfire-jellyfin/), and Snowflake [with TPC-DS](https://schemity.com/blog/snowflake-round-trip-tpcds/).
- **Schemas this size are not a stress test.** 80 and 115 tables are typical; a thousand-table schema is covered in [a separate post](https://schemity.com/blog/database-diagram-1000-tables-memory-cpu/).

## 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:

```sql
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.

## Frequently asked questions

### How do you import mysqldump into a database diagram without losing collations?

The diagram has to keep each table's and each column's character set and collation in every form mysqldump writes them: `DEFAULT CHARSET` and `COLLATE` on the table, a bare `COLLATE` on a column that takes its table's, `CHARACTER SET` and `COLLATE` on a column set on its own, and nothing on a column whose collation is its character set's default. Schemity's Import SQL now reads all four, as the live database reports the same columns. Without them a rebuilt database falls back to the server default, which on MySQL 8 is `utf8mb4_0900_ai_ci` and compares trailing spaces differently from `utf8mb4_unicode_ci`.

### Why does MySQL refuse an index on a TEXT column?

A `TEXT` or `BLOB` column can only be indexed by a prefix of its first N characters, so `CREATE INDEX ... (client_hash)` fails with error 1170, `BLOB/TEXT column used in key specification without a key length`. The index has to be written as `(client_hash(20))`. Invoice Ninja has three such indexes, and a diagram that keeps only the column name writes a script MySQL refuses.

### Does timestamp(6) matter on MySQL?

Yes. A MySQL `TIMESTAMP`, `DATETIME` or `TIME` with no precision keeps whole seconds, and MySQL rounds a fraction away without a warning. `timestamp(6)` keeps microseconds, so writing it back as `timestamp` changes every stored value. On PostgreSQL the same two spellings are one type, because PostgreSQL's default precision is already 6.