MariaDB corrupt. Can I just blow it away and start new? How?

Installation
1

Ok, been noticing that my Database has been growing rapidly over the past 2 months and just now decided to look at the logs (face slap) :

2025-05-08  4:12:09 91 [ERROR] mysqld: Index for table 'states' is corrupt; try to repair it
2025-05-08  4:12:09 91 [ERROR] mysqld: Index for table 'states' is corrupt; try to repair it
2025-05-09  4:12:08 91 [ERROR] InnoDB: We detected index corruption in an InnoDB type table. You have to dump + drop + reimport the table or, in a case of widespread corruption, dump all InnoDB tables and recreate the whole tablespace. If the mariadbd server crashes after the startup or when you dump the tables. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.

Do I really care about history? I don’t think so. Am I wrong?
Can I just blow it up and start new?
Does the db contain more than just history? (like config settings, automations, integrations, devices, etc?

I found instructions on how to fix the database, but I don’t even know how to get into a ‘SQL Prompt’ to do any of that:

Or even where to set these ‘Recovery Modes’:

So, yeah, I’m thinking just blow up the database and start fresh unless that’s a really bad idea. Otherwise how would I best do that?

Thanks!

2

There are tools available for maridb also:

1 Like
3

The database does not hold config info. You can safely recreate the database (delete and let the system recreate it) if you do not care about historical data.

1 Like
4

Ok. I did ‘Analyze [table]’ which returned ‘OK’ (not buying that)
So did an Optimize [table], which took a while. Then Check [table] which returned ‘OK’ again.
I’ll see what happens next daily purge (which is when it throws that error).
If it’s still borked, I’ll just delete and let HA build a new one.
Thanks lordwizzard and os.habitats.tech !!

5

Ok, after doing the ‘Analyze states’ and ‘Optimize states’ commands, I’m no longer getting the index corruption messages. Oddly, while it initially reduced the database by about 10%, it gained all that back in the past 48 house.

So I noticed that the auto_repack only happens Every-Other Sunday, so I wrote a script to Purge and Repack manually. And that cut the database size by over 70%! Success!!!

Thanks Everyone!!

6

Great to here that. DB management has to happen so systems built on small cost effective designs don’t crash from db running out of space and resources. :heart_eyes: