Topic: To delete and recreate often crashing, possibly corrupt personal_messages table

[qureshi]

Hi there,

The personal_messages table crashes often, a few times a week.
Repairs through PhpMyadmin fixes it, but not permanently.
It has been an issue for a while, and the host offers only temporary solutions.
The crashes return, and it's always "personal_messages is marked as crashed".

I've researched a bit, and the cause seems to be corruption.
The solution then is to truncate the table, export the SQL, drop the table, and then restore it.

However, I suspect there is more to it than this.

Please, do give me short pointers on what I should do and watch out for.

Thanks,

Nadeem

[pkrack]

A.o.A nadeem , if you need to drop the table then just copy its data and delete it and when create a new one then add the data to it. No need to drop whole database. Also check if your users are doing some thing wrong intentionally or unintentionally

[qureshi]

A.o.A pkcrack.

Thanks for the swift reply. I have a few questions.

1. Wouldn't copying the data preserve the corruption?

2. What are the things users could be doing wrong?

Thanks,

Nadeem

[ziycon]

Try running the below SQL command first and post the response here.

Code: [Select]

REPAIR TABLE {prefix}personal_messages USE_FRM;

[qureshi]

@ziycon,

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'smf_personal_messages USE_FRM' at line 1

Thanks for joining in.

[qureshi]

Mysql Version:  5.0.91-community-cll

[ziycon]

Sorry, typo on my part, it should have been.

Code: [Select]

REPAIR TABLE {prefix}personal_messages USE_FRM;

[qureshi]

Table   Op   Msg_type   Msg_text
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 10064452
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 13203012
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 16570528
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 16741172
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 16774840
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 16822488
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 16869872
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 16899020
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 16912600
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 16916120
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 16933316
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 16973868
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 17149704
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 17350932
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 17357636
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 17359756
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 17484032
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 17540592
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 17550924
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 17591260
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 17631276
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 17644644
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 17659100
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 18730028
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 18731068
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 18740792
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 18754208
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 18835032
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 18840560
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 18945052
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 19125332
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 19562440
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 19752240
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 19769104
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 19774496
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 19778492
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 19788160
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 19887892
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 19938544
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 19949680
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 19950916
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 20066500
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 20197112
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 20200232
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 20275988
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 20322668
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 20342288
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 20359280
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 20487512
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 20501220
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 20505124
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 20534524
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 20537376
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 20539544
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 20573096
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 20609220
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 20614180
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 20666176
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 20684616
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 20736608
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 20796540
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 20999388
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 21319484
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 21512476
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 21928624
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 22125288
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 22176680
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 22178060
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 22401136
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 22571944
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 22636744
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 22659200
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 22710376
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 22752852
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 22771612
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 22808688
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 22816492
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 22859788
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 22943748
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 22944644
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 22963996
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 22968020
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 23017632
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 23053604
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 23102292
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 23145720
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 29747908
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 30356056
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 30755428
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 30878316
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 30902768
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 31299988
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 32992292
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 34554848
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 35806052
*********_forum.smf_personal_messages   repair   info   Delete link points outside datafile at 36440544
*********_forum.smf_personal_messages   repair   warning   Number of rows changed from 0 to 50285
*********_forum.smf_personal_messages   repair   status   OK

What does this mean?

[pkrack]

I think thats all the errors and your table had 0 virtual rows which were fixed to the number given

[ziycon]

The last line is the one you want to worry about, its giving a status OK, can you tell us if the table is stable now? If so keep an eye on the table and let us know how you get on.

[qureshi]

Hi Guys,

At the time I ran ziycon's repair command, the table was not marked as crashed. I'd repaired it a few days ago, and it has not crashed since.

I think I'll try pkcracks suggestion, and also I will post in a while with a status update.

Thanks for the help, guys.

Regards,

Nadeem

[pkrack]

Nadeem have you installed any mod adding some feature to personal message

[qureshi]

No, pkrack, I haven't.

Is there any benefit in converting the table from MyIsam to something else?

[Aleksi "Lex" Kilpinen]

If any smf table keeps crashing more than once, I would advice you to turn to your host and have them investigate it. Mysql tables should not just crash.
I run a fairly huge forum, and I've only once or twice had to repair a table in the past 6 or 7  years I've been an admin...

[qureshi]

Thanks for your comments Aleksi "Lex" Kilpinen.

We have asked our host to investigate, but if memory serves me right, that turned into a blame game.

It would be very helpful if you could share some specific points that should be investigated.
Then I will bring the matter up with our host for furher clarification.

[ziycon]

Hi Guys,

At the time I ran ziycon's repair command, the table was not marked as crashed. I'd repaired it a few days ago, and it has not crashed since.

I think I'll try pkcracks suggestion, and also I will post in a while with a status update.

Thanks for the help, guys.

Regards,
Nadeem

Based on the reponse that you posted there were some issues with the .MYI headers being corrupted which have been fixed by running the provied SQL statement, this statement is more then just a repair. It rebuilds the .MYI file from the .FRM files and replaces the existing .MYI file.

I would say just to keep an eye on the table for the next week or two and if you have any problems post back here.

Tables crash for many reasons, unexpected system crash, improper shutdown of a system, malware to name a few.

[pkrack]

Regular crash of a specific table is spoting some thing more then accidental crash caused by server or any thing else. It may be due to three reasons
your table structure is stored in a file with some thing wrong or get wrong due to a bunch of reason

the data in the table may be causing the problem

some one is experimenting with your forum.

That was just my approach to the problem. Also contacting the host will be fruitfull . May be they have some thing crashed in their sql engine