[I wrote this before I saw Brion's reply...apparently my mail delivery on this list got shut off seemingly without me knowing it...hmmm....
I'll still send the note below for my future documentation purposes...and for those in my group admin-ing our MediaWiki site.]
At 4/28/2005 05:14 PM, Matt England wrote:
I unfortunately was in a position where I ungracefully shutdown my mysql server. After restarting it, I'm finding that MediaWiki reports "Database error: Internal error" after any attempt to save an edited wiki page. It's worth noting that all the changes seem to be accepted, in that if I visit the page the changes I supplied during the edit are displayed.
It appears (initially) that I have at least removed my symptom.
I initially found this while trying to dump my mediawiki database (in an attempt to recreate it somewhere else):
root@biz2tek 5:50pm [/scratch/mediawiki-database-recovery] 93> mysqldump -u root -p mediawiki_1 > mediawiki_1.sql Enter password: mysqldump: Can't get CREATE TABLE for table `searchindex` (Can't open file: 'searchindex.MYI'. (errno: 145)) root@biz2tek 5:50pm [/scratch/mediawiki-database-recovery] 94>
...so then I did some googling on "mysql errno 145" and found this reference:
http://www.karakas-online.de/forum/viewtopic.php?t=332
...and then followed the recommendations there to do the following stuff, which seems to have cleared up my problem. (*whew*)
Is it fair to assume that database-table corruption (particularly the indeces) can be a common issue, particularly during an ungraceful (in my case 'kill -9') database shutdown?
-Matt
root@biz2tek 5:50pm [/scratch/mediawiki-database-recovery] 93> mysqldump -u root -p mediawiki_1 > mediawiki_1.sql Enter password: mysqldump: Can't get CREATE TABLE for table `searchindex` (Can't open file: 'searchindex.MYI'. (errno: 145)) root@biz2tek 5:50pm [/scratch/mediawiki-database-recovery] 94> mysql -u root -p mediawiki_1 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 154 to server version: 4.0.21-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> repair table searchindex; +-------------------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------------------+--------+----------+----------+ | mediawiki_1.searchindex | repair | status | OK | +-------------------------+--------+----------+----------+ 1 row in set (0.30 sec)
mysql> exit Bye root@biz2tek 5:51pm [/scratch/mediawiki-database-recovery] 95> root@biz2tek 5:51pm [/scratch/mediawiki-database-recovery] 95> mysqldump -u root -p mediawiki_1 > mediawiki_1.sql mediawiki_1.sql: File exists. root@biz2tek 5:51pm [/scratch/mediawiki-database-recovery] 96> rm !$ rm mediawiki_1.sql rm: remove regular file `mediawiki_1.sql'? y root@biz2tek 5:51pm [/scratch/mediawiki-database-recovery] 97> mysqldump -u root -p mediawiki_1 > mediawiki_1.sql Enter password: root@biz2tek 5:51pm [/scratch/mediawiki-database-recovery] 98>
At 4/28/2005 05:14 PM, Matt England wrote:
Hello,
I'm in a bit of predicament.
I unfortunately was in a position where I ungracefully shutdown my mysql server. After restarting it, I'm finding that MediaWiki reports "Database error: Internal error" after any attempt to save an edited wiki page. It's worth noting that all the changes seem to be accepted, in that if I visit the page the changes I supplied during the edit are displayed.
I'm trying to debug this problem, for while it seems cosmetic at first, it's certainly disturbing to my users. Further, I want to learn from this and figure out how to recover from this...not to mention figure out where the darn mysql/mediawiki logs are. [[Special:Log]] tells me absolutely nothing. Not one log entry.
I am running mysql 4.0.21, MediaWiki 1.4.0, Apache 2.0.52 (apache and mysql stuff comes from XAMPP 1.49a). I can provide more info about this upon request. I'd also provide pertinent logs...if I could find anything.
I'm hunting around meta.wikimedia.org and have yet to find anything that addresses. I also have tried several times to search this email list's archive via a google mechanism and have yet to succeed (I can't find any of my old emails from several weeks/months ago when I search for "Matt England"...even though I can find *some* emails...).
Thanks for any help. Like I mentioned, I'm kind of in a tight spot on this one.
-Matt
Matt England wrote:
...so then I did some googling on "mysql errno 145" and found this reference:
http://www.karakas-online.de/forum/viewtopic.php?t=332
...and then followed the recommendations there to do the following stuff, which seems to have cleared up my problem. (*whew*)
Is it fair to assume that database-table corruption (particularly the indeces) can be a common issue, particularly during an ungraceful (in my case 'kill -9') database shutdown?
Error 145 is: 145 = Table was marked as crashed and should be repaired
MySQL's old MyISAM table type is not designed to automatically recover from crashes and other irregular system shutdowns. A table that's not closed regularly is left with a marker indicating it was left open, and the system requires you to run an integrity check when it's back online before continuing.
This may sound familiar from CHKDSK/Scandisk on MS-DOS/Windows or fsck on older Unix/Linux filesystems; it's the same basic way of doing things.
If you switch your tables to the InnoDB table type, they will recover more gracefully and automatically from a crash (except in certain really nasty cases where your hardware is conspiring against you).
Note that the searchindex table must be MyISAM because InnoDB doesn't support the full text indexing it uses, but every other table can be InnoDB.
-- brion vibber (brion @ pobox.com)
If you switch your tables to the InnoDB table type, they will recover more gracefully and automatically from a crash (except in certain really nasty cases where your hardware is conspiring against you).
Excellent. Thanks.
I'll keep this mysql-specific short as I can (and probably follow up with some further questions via an mysql-based community) so as not to be too far off topic, but for now I ask:
These things (below) seem to represent the difference between what I have come to know as "journaled" mechanisms (filesystems/databases) and non-journaled filesystems/databases. (I'm more familiar with the file-system side then the database side). Is this correct?
Assuming I'm "on" with my analogy: I'm a bit stunned that my XAMPP-1.49a-based system doesn't come with full journaling capability (that can allow for auto-data-integrity-rebuilds after ungraceful shutdowns...like NTFS over FAT32, etc) in its databases (in this case Mysql)...nor that Mysql can not (seemingly) support full ungraceful recovery and other journaling features (because some applications like MediaWiki need MyISAM for the text-indexing capabilities that InnoDB does not support?). Is this capability--or lack thereof--traditionally accepted in the open-source database world? Are there other open-source databases that can handle it better? (Or maybe mysql does just fine with converting "most" tables to InnoDB?) Or is one forced to go the mega-$$$ way of products from companies like Oracle?
I'll be digging up more around the web on this (because it applies to the product/service I'm building for my company--outside of things like MediaWiki IT), but if anyone can point me to some other references, it would be truly helpful.
Thanks all for the continued support from this MediaWiki community. I have had quite a good experience thus far since installing my software.
-Matt
At 4/28/2005 06:14 PM, Brion Vibber wrote:
Matt England wrote:
...so then I did some googling on "mysql errno 145" and found this reference:
http://www.karakas-online.de/forum/viewtopic.php?t=332
...and then followed the recommendations there to do the following stuff, which seems to have cleared up my problem. (*whew*)
Is it fair to assume that database-table corruption (particularly the indeces) can be a common issue, particularly during an ungraceful (in my case 'kill -9') database shutdown?
Error 145 is: 145 = Table was marked as crashed and should be repaired
MySQL's old MyISAM table type is not designed to automatically recover from crashes and other irregular system shutdowns. A table that's not closed regularly is left with a marker indicating it was left open, and the system requires you to run an integrity check when it's back online before continuing.
This may sound familiar from CHKDSK/Scandisk on MS-DOS/Windows or fsck on older Unix/Linux filesystems; it's the same basic way of doing things.
If you switch your tables to the InnoDB table type, they will recover more gracefully and automatically from a crash (except in certain really nasty cases where your hardware is conspiring against you).
Note that the searchindex table must be MyISAM because InnoDB doesn't support the full text indexing it uses, but every other table can be InnoDB.
-- brion vibber (brion @ pobox.com)
MediaWiki-l mailing list MediaWiki-l@Wikimedia.org http://mail.wikipedia.org/mailman/listinfo/mediawiki-l
Matt England wrote in news.gmane.org.wikimedia.mediawiki:
[...] Mysql can not (seemingly) support full ungraceful recovery and other journaling features (because some applications like MediaWiki need MyISAM for the text-indexing capabilities that InnoDB does not support?).
MyISAM is MySQL's older database format. as you have found out, it isn't very good; in addition to the lack of recovery, it's lacking some of the locking, transaction and ACID features you would expect in an RDBMS.
MediaWiki doesn't require MyISAM. i would recommend using the Lucene-based search instead, which as well as not dying when something crashes, generally provides better search results than MySQL's fulltext search, and uses less system resources. you can find this in the "lucene-search" module in CVS (http://sourceforge.net/cvs/?group_id=34373).
-Matt
kate.
mediawiki-l@lists.wikimedia.org