header

MySQL can’t open file (pt. 3)

I hadn’t received any comments in the past few months, I figured it was just because I hadn’t put out any new content.

Then I realized that I couldn’t even leave a comment myself. I received an email from a buddy saying he had a similar issue.

So I checked the logs:

WordPress database error Table './{db_name}/wp_comments' 
is marked as crashed and should be repaired for query SELECT * FROM 
wp_comments WHERE comment_approved = '1' ORDER BY 
comment_date_gmt DESC LIMIT 15 made by require, require_once, 
include, get_sidebar, locate_template, load_template, require_once, 
dynamic_sidebar, call_user_func_array, WP_Widget->display_callback, 
WP_Widget_Recent_Comments->widget

Went to the mysql database physical location and did the following to fix it:

# myisamchk wp_comments.MYI
Checking MyISAM file: wp_comments.MYI
Data records:    3914   Deleted blocks:     347
myisamchk: warning: Table is marked as crashed
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
myisamchk: error: Found 3913 keys of 3914
- check record links
myisamchk: error: Keypointers and record positions doesn't match
MyISAM-table 'wp_comments.MYI' is corrupted
Fix it using switch "-r" or "-o"

# myisamchk wp_comments.MYI -r
- recovering (with sort) MyISAM-table 'wp_comments.MYI'
Data records: 3914
- Fixing index 1
- Fixing index 2
- Fixing index 3
- Fixing index 4
- Fixing index 5
- Fixing index 6

Comments (3)

MySQL Can’t Open File (again)

I had another issue with MySQL not being able to open a file again. Same fix did it.

#1016 - Can't open file: 'wp_bad_behavior_log.MYI' (errno: 145) 

# myisamchk wp_bad_behavior_log.MYI
Checking MyISAM file: wp_bad_behavior_log.MYI
Data records:     109   Deleted blocks:     270
myisamchk: warning: Table is marked as crashed
myisamchk: warning: 4 clients are using or haven't closed the table properly
- check file-size
- check record delete-chain
myisamchk: error: Record at pos: 203284 is not remove-marked
myisamchk: error: record delete-link-chain corrupted
- check key delete-chain
- check index reference
- check data record references index: 1
myisamchk: warning: Auto-increment value: 19908 is smaller than max used value: 19909
- check record links
myisamchk: error: Record-count is not ok; is 113          Should be: 109
myisamchk: warning: Found 235456 deleted space.   Should be 236988
myisamchk: warning: Found        419 parts                Should be: 414 parts
MyISAM-table 'wp_bad_behavior_log.MYI' is corrupted
Fix it using switch "-r" or "-o"

# myisamchk -r wp_bad_behavior_log.MYI
- recovering (with sort) MyISAM-table 'wp_bad_behavior_log.MYI'
Data records: 109
- Fixing index 1
Data records: 113

I wonder what causes the data to be corrupted…

Comments (0)

MySQL Can’t Open File

I left on my honeymoon and all sorts of stuff goes wrong!

I was getting the MySQL error message:

#1016 – Can’t open file: ‘wp_comments.MYI’ (errno: 144)

Where wp_comments holds all of the comments for my site. So of course this is a major problem.

I was a little frantic, but it turns out it was an easy fix. I checked the file with the tool ‘myisamchk’:
# myisamchk wp_comments.MYI
Checking MyISAM file: wp_comments.MYI
Data records: 8385 Deleted blocks: 0
myisamchk: warning: Table is marked as crashed and last repair failed
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
myisamchk: error: Found 8384 keys of 8385
- check record links
myisamchk: error: Found wrong record at 38231804
MyISAM-table 'wp_comments.MYI' is corrupted
Fix it using switch "-r" or "-o"
[root@sam]#

So I simply did what it said, brought down MySQL, repaired, brought MySQL back up and it worked, whala.

[root@sam]# /usr/local/etc/rc.d/mysql-server.sh stop
Stopping mysql.
Waiting for PIDS: 889, 889, 889.
[root@sam]# myisamchk -r wp_comments.MYI
- recovering (with sort) MyISAM-table 'wp_comments.MYI'
Data records: 8385
- Fixing index 1
Key 1 - Found wrong stored record at 38231804
- Fixing index 2
- Fixing index 3
- Fixing index 4
Data records: 8384
[root@sam]# /usr/local/etc/rc.d/mysql-server.sh start
Starting mysql.
[root@sam]#

And it works now…

It’s a good thing too, because I didn’t have a very recent backup… 🙂

Comments (5)

Mysql Version Compatibility Issues

I was exporting a Mysql database from a 4.1.x build to a 3.x build on another machine. When importing, I was getting the message

“ERROR 1064 at line 20: You have an error in your SQL syntax near ‘DEFAULT CHARSET=latin1’ at line 6”

Line 6 of my exported database .sql file was

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

If I delete the DEFAULT CHARSET=latin1 portion, it still has an error about the ENGINE=MyISAM portion. Mysql 4.0.x or 3.x were expecting instead

) TYPE=MyISAM;

I initially thought to look in the configuration files to change the post-table output, but couldn’t find anything. I then tried looking for a table-output-template, but wasn’t successful there either. I eventually just uninstalled 4.1.x and installed 4.0.x so that I wouldn’t have to worry about compatibility.

How annoying…

Edit 9/13:
You may or may not have to delete /var/db prior to reinstalling… if I did not I got an error something similar to

/usr/local/libexec/mysqld: Can’t open file: ‘host.MYI’. (errno: 142)
Fatal error: Can’t open privelege tables: File ‘/usr/local/share/mysql/charsets/?.conf’ not found (Errcode: 2)
Aborting

Also, if you use PHP or anything else that depends on the mysql build, you’ll of course have to rebuild those (including php4-mysql which is managed by php4-extensions). I had quite a hassle with my mysql.so file from php4-mysql… maybe you won’t, maybe you will.

Comments (3)

Mysql install on FreeBSD

I’ve had enough problems with this to know that I need to write down what I need to do to install mysql on FreeBSD. Here are the steps I took to do it successfully.

cd /usr/ports/databases/mysql41-server/
make install clean
/usr/local/bin/mysql_install_db
chown -R mysql /var/db/mysql/
chgrp -R mysql /var/db/mysql/
/usr/local/bin/mysqld_safe –user=mysql &
/usr/local/bin/mysqladmin -u root password newpassword

Edit 8/17:
{edit /etc/rc.conf and add}
mysql_enable="YES"
Thanks lucas

That works.

Comments (60)


blogtimes