Hi,
Some additional data points:
The customer noticed that the tables were re-created in a logical order,
i.e. the same way a fresh migration would. Which reeks of application
logic, but because the customer is absolutely sure that no deployment or
migation was done, it makes me suspect that something MariaDB-internal
re-created tables in the order they were originally created. Which
MariaDB is aware of, because `create_time` is stored in
`information_schema.tables`:
```
MariaDB [app_db]> select table_name, create_time from
information_schema.tables where table_schema = 'app_db' order by
create_time asc;
+-----------------+---------------------+
| table_name | create_time |
+-----------------+---------------------+
| migrations | 2026-04-02 14:11:27 |
| password_resets | 2026-04-02 14:11:28 |
| clients | 2026-04-02 14:11:28 |
| client_website | 2026-04-02 14:11:29 |
| servers | 2026-04-02 14:11:29 |
| contacts | 2026-04-02 14:11:29 |
| client_contact | 2026-04-02 14:11:29 |
| domains | 2026-04-02 14:11:30 |
| users | 2026-04-02 14:11:30 |
| databases | 2026-04-02 14:11:30 |
| hosting | 2026-04-02 14:11:30 |
| websites | 2026-04-02 14:11:30 |
+-----------------+---------------------+
12 rows in set (0.001 sec)
```
... and `table_id` is monotonic.
Another interesting observation: the `migrations` table has two cols,
`migration` and `batch`. The latter is written by the application to log
in which 'batch' a specific migration was run. If the app somehow
migrated the database fresh, the only distinct value would be '1'. But
'batch' is monotonic as well, so not all migrations were run in a single
batch - which rules out that a fresh database migration was done in the
app, and confirms there must have been some kind of restoration (aside
from the fact that there is no data in any migration, and the data is
back, which already more or less confirmed this).
I also considered that some kind of InnoDB crash recovery might have
gone on, but as far as I can see, the server was not under memory
pressure and MariaDB did not OOM or anything similar. Also, no other
databases on the same server were unreachable during the relevant
period. Could anything trigger recovery on a single database with these
symptoms?
During my search, I stumbled upon MDEV-8845:
A table can disappear when running ALTER queries on it.
... which is from a whopping 10 years ago, but it does make me wonder:
are any such bugs in the same realm known and relevant? The server is
running 11.8.8 on Debian (from the MariaDB repo, not Debian stable).
William David Edwards schreef op 2026-07-06 22:55:
Hi,
I'm currently debugging a case where I've ruled out everything I can
think of, and I'm hoping someone here has seen this pattern before or
has creative ideas.
Some facts & figures:
- Database: ~9.3 MiB
- 12 tables
- .frm + .ibd files
- Standalone instance; no replica, no Galera, no clustering
- innodb_file_per_table=ON
What happened on 2026-04-02:
- 14:05 CET: application gets `SQLSTATE[42S02] ... Table 'app_db.users'
doesn't exist` (error 1146). User confirms via phpMyAdmin that the
database `app_db` still exists but has no tables.
- ~14:11 CET: everything works again. Data is intact (!)
On disk:
- All 12 .frm and all 12 .ibd files have Birth timestamps clustered in
a window of a few seconds, at `2026-04-02 14:11:30.xxx`.
- Order per table is .frm first, .ibd a few ms later. Example for one
table:
- foo.frm Birth `14:11:30.152`
- foo.ibd Birth `14:11:30.184`
- No #sql-* orphan files anywhere in the datadir.
You would think every table in this database was dropped, and then
freshly created at 14:11:30.
But: the data was back. So either:
- The data was actually gone, but has been automatically restored. But
we have no such process or code, and MariaDB doesn't either AFAIK (?)
- The tables were temporarily invisible. But why and how? Neither
`ALTER TABLE` nor `OPTIMIZE TABLE` nor `ALTER TABLE FORCE` boast that
behaviour (never seen it at least). Also, none of those DDLs were run
by us or the customer.
What I've ruled out:
- Dump import - not run by us nor the customer
- MariaDB upgrade
- OPTIMIZE TABLE - not run by us nor the customer
- ALTER TABLE ... FORCE / ENGINE=InnoDB / schema changes — not run by
us nor the customer *and* table wouldn't be invisible
- Missing/dropped grants — the error would be 1044/1142, not 1146
- Replication / Galera — N/A because standalone
- Application deploy/migration — according to the customer, the site
using this database wasn't deployed on that date
Anything you've seen that could explain this? Edge cases in InnoDB
recovery, weird interactions between FLUSH TABLES and file operations,
tooling that does silent per-table rebuilds, etc. — I'll take anything
creative.
With kind regards,
William David Edwards
Met vriendelijke groeten,
William David Edwards
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
