-1

everyone! I'm trying to avoid breaking of my database in the phpbb3.1 forum. It was crushed twice this month. So I have two questions:
1) Is it safe to convert MyISAM to InnoDB? I mean will extensions work fine? Will forum be workable after updating to next version?
2) In which way I can avoid base corrupting?

p.s. I also posted this question here: https://www.phpbb.com/community/viewtopic.php?f=466&t=2436326

young B
  • 127
  • 1
  • 11

1 Answers1

1

I'll venture a guess. You had a power failure, and when it came back up, MySQL was complaining that some index on some table was corrupted? And that table was MyISAM?

  1. Use myisamchk to repair the tables.
  2. Review the gotchas in http://mysql.rjweb.org/doc.php/myisam2innodb to see if conversion to InnoDB will add new woes. There probably won't be any. A 2-part PRIMARY KEY is about the only thing that is not implemented in InnoDB. Also, if you have too old a version of MySQL, InnoDB may not yet have FULLTEXT indexes (if you need them).
  3. Change my.cnf: key_buffer_size = 20M and innodb_buffer_pool_size equal to about half of available memory.
  4. ALTER TABLE xx ENGINE=InnoDB; for each table xx.

I think (but am not sure) that each update/delete/insert marks the table as possibly corrupt. It writes the changes, but does not clear the mark. When mysqld shuts down cleanly, everything is flushed to disk and these flags are cleared. When mysqld comes back up, it complains about the flags that did not get cleared. So...

Whether or not an index is marked as corrupt depends solely on whether you modified that index and crashed. (Every table has some index, yes?)

Normally, MySQL manages to flush changes to disk before a crash. Only occasionally does the crash happen at a time where the index will really be corrupt. There is a "quick" mode on the repair that simply clears the flag -- you could try that. But if you ever get a mysterious "can't find record" when you know the records exists, you'd better REPAIR it.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • @youngB -- suggest you post my link on the PHPBB forum so they can ponder whether there are potential issues. – Rick James Aug 29 '17 at 17:14
  • Thank for your replay. I have another question to the 3-rd point in this steps. If "key_buffer_size" and "innodb_buffer_pool_size" already have bigger values than you advise, should I make any changes? – young B Aug 31 '17 at 05:59
  • They are the main memory allocation parameters. If they add up to too much, you could be "swapping", which is _terrible_ for performance. Key_buffer is needed for MyISAM; the other is for InnoDB. However, key_buffer should not be zero even when entirely InnoDB. If you have more than 4GB of RAM, 70% of ram is advised for InnoDB's buffer_pool. – Rick James Aug 31 '17 at 14:01
  • @ Rick James, Thanks again. Could you please tell me about the reason of base corrupting if you have any ideas? I noticed session tables has fewer records than other tables, and it corrupts more often than another match bigger tables. That's mean the number of records has not any effect on this problem. – young B Sep 05 '17 at 12:30
  • @youngB - I added a few paragraphs. I suspect it is not table size, but table activity that correlates with 'corruption'. – Rick James Sep 05 '17 at 12:45
  • @ Rick James, Ok. thank you. I think this question could be closed. Unless I will publish more detailed reasons for my problem. – young B Sep 05 '17 at 13:49