A client implementation on ERPNext is on MariaDB and InnoDB engine. I get a message many times a day that one of the tables has crashed. I think I know how to recover, but is there any advantages to changing to MyISAM as it supports repairing tables, I think.
Does Frappe/ERPNext support MyISAM? Does it require a lot of reconfiguration to move from InnoDB to MyISAM, assuming there are any advantages, of course!
I ran mysqlcheck -o, but despite that I’m getting the error that mysqld: Table ‘__global_search’ is marked as crashed and should be repaired.
Another client instance also has the same issue and in addition has this additional issue: mysqld: Table ‘tabError Log’ is marked as crashed and should be repaired.
I guess you have pretty high number of writes to the those tables.
Both tabError Log & __global_search are already MyISAM tables, and MyISAM tables crash very often if something is writing on them and mariadb crash restarts.
However, they also have quick repair mechanism. Can you try REPAIR TABLE <tablename> command on both tables and observe if they are fixed quickly or not?
Also, when do get that message? On restart or in middle of transactions? Can you also run a sudo journalctl -xe command to print crash logs of mariadb whenever you get that message?
Edit: Also checkout this awesome answer. They used a startup sql script to auto fix any crashed MyISAM tables.
The other thing I see is that the MySQL backup has tripled in size in the last 2 weeks, and I don’t know if it is coincidental, since the time I ran the mysqlcheck -o command. Is there anyway I can purge unwanted tables data?