Proposal to improve database recovery procedure

[tsuchm]

When updating mariadb binary, fts_* tables are frequently destroyed.

It is often the case that fts_* tables are partially broken. In such case, we can confirm the existence of fts_tags table with following commands:

$ mysql -u root -p
> use redmine;
> show tables;

However, because this table is partially broken, `describe' directive fails.

> describe fts_tags;
ERROR 1016 (HY000): mroonga: failed to open table: <fts_tags>

Thus, the recovery procedure described in https://github.com/clear-code/redmine_full_text_search#how-to-recover-broken-database also fails.

In my experience, explicit destruction of tables is effective to ease this case.

> drop table fts_query_expansions, fts_tag_types, fts_tags, fts_targets, fts_types;

How about adding the above procedure to the recovery procedure document?

[kou]

OK.

Could you open a pull request for this?

BTW, the following SQLs

DROP TABLE IF EXISTS fts_query_expansions;
DROP TABLE IF EXISTS fts_targets;
DROP TABLE IF EXISTS fts_tags;
DROP TABLE IF EXISTS fts_tag_types;
DROP TABLE IF EXISTS fts_types;

are better than drop table fts_query_expansions, fts_tag_types, fts_tags, fts_targets, fts_types; because:

• They doesn't report error if a table already doesn't exit
• They use the same order as RAILS_ENV=production bin/rails redmine:plugins:migrate NAME=full_text_search VERSION=0

[tsuchm]

Thanks a lot! I expect that this improvement will reduce the down time of our next trouble. (I and my colleague took three hours today, and I think an hour was spent to reach this explicit destruction procedure.)

[kou]

I hope that MariaDB upgrade doesn't remove Mroonga by improving Mroonga packages...

It's expected to be happen with Mroonga 11.03 packages because of package name change: https://mroonga.org/en/blog/2021/05/31/mroonga-11.03.html