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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.