Need help with MYSQL table crash

Hello Experts,

I had a issue with a MYSQL Table. Here's my scenario.
CentOS Linux 6.4
MySQL version 5.1.69

When trying to run a script to update some tables the Mysql shows a warning at the EventData table

I first check the table

[root@myserver]# myisamchk EventData.MYI --check
Checking MyISAM file: EventData.MYI
Data records: 46215316   Deleted blocks:       0
myisamchk: warning: 3 clients are using or haven't closed the table properly
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check record links
myisamchk: error: Found wrong record at 7619297220
MyISAM-table 'EventData.MYI' is corrupted
Fix it using switch "-r" or "-o"

Then I try to use the -r option however it did not repair
myisamchk -r /var/lib/mysql/gts/EventData.MYI

I did try to run the same code around 2 - 3 times but it did not work. I lookup on the web and found this code below however it do not work and stop with the message Segmentation fault (core dumped) as you can see below.

[root@myserver]# myisamchk --silent --force --fast --update-state /var/lib/mysql/gts/*.MYI
myisamchk: MyISAM file /var/lib/mysql/gts/Driver.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: MyISAM file /var/lib/mysql/gts/EventData.MYI
myisamchk: warning: Table is marked as crashed and last repair failed
myisamchk: error: Found wrong record at 7619297220
Segmentation fault (core dumped)

Is there anyway that record could be deleted or replaced? I now I might loose data but data but in worst case scenario I think if the repair do not work I could give it a try.

Any help will be appreciated.
prsnAsked:
Who is Participating?
 
Mohamed MagdyCTOCommented:
Did you try using mysqlcheck?

mysqlcheck -r DBNAME TABLENAME

Open in new window

To repair one table

or

mysqlcheck -r DBNAME

Open in new window

To repair the whole database
0
 
prsnAuthor Commented:
I'm running the command at this moment. I post my progress.
0
 
Mohamed MagdyCTOCommented:
Hope it get fixed.

What are the sizes of the database, corrupted table and also how much is your RAM?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
prsnAuthor Commented:
DB size is around 15 GB . The command is running at this moment and I'm looking that /tmp directory is increasing every second
# df -h

/dev/md1              2.0G  730M  1.2G  39% /tmp

/dev/md1              2.0G  928M  986M  49% /tmp

/dev/md1              2.0G  990M  924M  52% /tmp

I guess this has something to do with the final error I always get
Segmentation fault (core dumped)
0
 
Mohamed MagdyCTOCommented:
Yes, your tmp size is small so that MySQL throws an error.

Did you try to repair the corrupted table rather than repairing the whole db?

I guess the corrupted table is EventData, so you can issue this command:
mysqlcheck -r DBNAME EventData

Open in new window


replace DBNAME with your Database Name.
0
 
prsnAuthor Commented:
Yes that's exactly what I did.
mysqlcheck -r mydbname EventData -u root -p

At this point there's no space left
/dev/md1              2.0G  1.9G  8.0K 100% /tmp

Is there a way to tell mysql to use other directory or partition that has bigger space?

Filesystem            Size  Used Avail Use% Mounted on
/dev/md2              913G  147G  721G  17% /
tmpfs                 3.5G     0  3.5G   0% /dev/shm
/dev/md0               97M   28M   64M  31% /boot
/dev/md1              2.0G  1.9G  8.0K 100% /tmp
0
 
Mohamed MagdyCTOCommented:
Yes,

You can create a folder inside, let's say /home/mysqltmp, give the right permission, maybe you could try 777.

Then add the path to mysql configuration file "/etc/my.cnf", like this:
tmpdir = /home/mysqltmp

Open in new window


Save the file, restart mysql.

You can check if you assigned it right and mysql can write in it by doing this:

login into mysql server
mysql -u root -p PASSWORD

Open in new window


then issue this command:
SHOW VARIABLES LIKE 'tmpdir';

Open in new window


If you get the result of /home/mysqltmp then you are good to go and ready to repair the table.
0
 
prsnAuthor Commented:
working on this solution right away.
0
 
Mohamed MagdyCTOCommented:
Note: in /etc/my.cnf, put tmpdir under [mysqld]
0
 
prsnAuthor Commented:
Did all the steps and restarted the mysqld . I run the command  and shows the correct tmpdir

mysql> SHOW VARIABLES LIKE 'tmpdir';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tmpdir        | /home/mysqltmp |
+---------------+----------------+
1 row in set (0.00 sec)

mysql> exit

Open in new window


When I run top -M I see that mysql is performing something
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
  438 mysql     20   0  497m  26m 3212 S 94.4  0.4   4:48.47 mysqld

Open in new window


I can't really see that directory is been filled with data or space increasing . I
drwxrwxrwx   2 mysql         mysql         4096 Apr 19 22:49 mysqltmp

Open in new window


I will wait to see if I get any progress. Should that directory be increasing on size?
0
 
Mohamed MagdyCTOCommented:
You should not see /tmp size increase? Is it increasing now?
0
 
prsnAuthor Commented:
what about the new directory?  it has been 5 minutes now and only 4.0k

du -hs mysqltmp/
4.0K    mysqltmp/

Open in new window

0
 
Mohamed MagdyCTOCommented:
What is the result of this command

[code]ps fux | grep mysql[/code]
0
 
prsnAuthor Commented:
root       816  0.0  0.0 103244   824 pts/2    S+   23:04   0:00      \_ grep mysql
root       555  0.0  0.0  50188  1704 pts/1    S+   22:51   0:00      \_ mysqlcheck -r gts EventData -u root -p
root       326  0.0  0.0 108200  1460 pts/2    S    22:48   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql

Open in new window

0
 
Mohamed MagdyCTOCommented:
Did you assign the permission correctly?
Is /tmp increasing while you are repairing now?
0
 
prsnAuthor Commented:
Directory /tmp is no longer increasing after changes.

# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/md2              913G  148G  720G  17% /
tmpfs                 3.5G     0  3.5G   0% /dev/shm
/dev/md0               97M   28M   64M  31% /boot
/dev/md1              2.0G   36M  1.9G   2% /tmp

Open in new window


Here are the permissions for the new folder. I did chmod 777. I changed the owner and group from root to mysql . Is this correct?
drwxrwxrwx   2 mysql         mysql         4096 Apr 19 23:11 mysqltmp

Open in new window


It has been 23 minutes now that command is running. Top -M shows that mysql is running
 PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
  438 mysql     20   0  628m  26m 3540 S 88.4  0.4  23:59.74 mysqld

Open in new window


No error so far. I think I have to wait. Just a Little concern that size on the new directory is not increasing.
0
 
Mohamed MagdyCTOCommented:
Yes, this happened to my before but after a while it starts to fill up.

You can monitor the repair process by this command:
mysqladmin processlist

Open in new window


about chowning the folder, yes, that is correct.
0
 
prsnAuthor Commented:
Thanks I really appreciate your time on providing assistance. I try to run the command this is what it shows

# mysqladmin processlist
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

Open in new window


Do I need to open a second terminal and log into mysql to run that command?
0
 
Mohamed MagdyCTOCommented:
No worries, I hope you could solve the issue.

Yes you may open a second terminal and the command after logging into mysql will be:
show processlist;

Open in new window

0
 
prsnAuthor Commented:
Thanks! show processlist;
mysql> show processlist;
+----+------+-----------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host            | db   | Command | Time | State             | Info                                                                                                 |
+----+------+-----------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
|  5 | root | localhost       | gts  | Query   | 2488 | Repair by sorting | REPAIR TABLE `EventData`                                                                             |
| 10 | gts  | localhost:54443 | gts  | Query   | 1973 | Waiting for table | SELECT * FROM EventData WHERE ( (accountID='-account') AND (deviceID='2443') ) ORDER BY timestamp DE |
| 11 | gts  | localhost:54444 | gts  | Sleep   | 1973 |                   | NULL                                                                                                 |
| 29 | root | localhost       | NULL | Query   |    0 | NULL              | show processlist                                                                                     |
+----+------+-----------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+

Open in new window


After 2 minutes I ran the same code and its showing
mysql> show processlist;
+----+------+-----------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host            | db   | Command | Time | State             | Info                                                                                                 |
+----+------+-----------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
|  5 | root | localhost       | gts  | Query   | 2243 | Repair by sorting | REPAIR TABLE `EventData`                                                                             |
| 10 | gts  | localhost:54443 | gts  | Query   | 1728 | Waiting for table | SELECT * FROM EventData WHERE ( (accountID='-account') AND (deviceID='2443') ) ORDER BY timestamp DE |
| 11 | gts  | localhost:54444 | gts  | Sleep   | 1728 |                   | NULL                                                                                                 |
| 29 | root | localhost       | NULL | Query   |    0 | NULL              | show processlist                                                                                     |
+----+------+-----------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Open in new window


I'm not sure if the deviceID='2443' should be changing
Hope the process is not freeze or hang. It wouldn't be fun waiting 3 hours and no results :P
0
 
Mohamed MagdyCTOCommented:
Check the logs to see if there were any errors.

You can find them in /var/lib/mysql, file name will be your-hostname.err

The repair is in Query mode then It's almost working.

Unfortunately, I don't know anyway that you can check if its working or frozen.

Back in days, I was repairing a 24GB database and it took about 8 hours and I didn't believe when its finished because I was 100% sure it was frozen.

I'm sorry but I've to get some sleep now as its 6AM here and my work starts at 10AM, I hope its done by then.
0
 
prsnAuthor Commented:
Once again Thanks! have a great day!
0
 
Mohamed MagdyCTOCommented:
You are most welcome, you too.

Check this out to monitor query:
http://www.tecmint.com/mysql-performance-monitoring/

See you in 4-5 hours.
0
 
prsnAuthor Commented:
modserv

Just wanted to thank you for your support. I cancelled the process . I was more that 1hr and I didn't see any changes however db seems to be repaired . I where able to turn on the mysql service .
0
 
prsnAuthor Commented:
Excellent Expert and Outstanding Support!!
0
 
Mohamed MagdyCTOCommented:
No worries at all prsn, It was really nice working with you and I'm happy that the database got fixed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.