Hi, I have been using MySQL for various embedded situation over the years. I am currently working on a new version of an existing project which I'm also migrating to MariaDB (I think I prefer Monty's style!).
OK. Some tables in this database get update from an external source in a complex manner, so I am using InnoDB on these table with a transaction to ensure the operation is either completed or completely abandoned.
This is an embedded app, and it can be interupted abruptly, leaving the InnoDB tables blocked.
The procedure in this case is to set InnoDB recover to 4, dump the database, manually delete the InnoDB files, restart normally, restore, and then force an update from the external source.
I'm hoping I will eventually be able to solve this using Aria tables, but in the meantime is there any way I can automate the process. I don't need to recover the data in the InnoDB tables following a crash, I just need to know that it has happened so I can get a new dataset.
Thanks in advance for any help or suggestions.
Answer Answered by knielsen in this comment.
The correct fix here is to avoid the InnoDB corruption in the first place, not try to work around it.
This kind of thing should not have to happen no matter how systems crash. But you need to configure your setup (virtualisation software + host disk system) so that when InnoDB does fsync(), it really gets to the disk and does not lie to InnoDB about it. If you've done that and still get this, it would indicate a bug in InnoDB I think that should be fixed...