XF 2.1 Database Crash xf_post.myi

[ASap]

Hi there,

One of our forum databases has crashed

Output when I do mysqlcheck:

db.xf_post
warning  : Table is marked as crashed and last repair failed
warning  : Size of indexfile is: 327299072      Should be: 1024
error    : Found key at page -1 that points to record outside datafile
error    : Corrupt

# myisamchk --force --fast --update-state /var/lib/mysql/db/xf_post.MYI
Info: Using unique option prefix 'key_buffer' is error-prone and can break in the future. Please use the full name 'key_buffer_size' instead.
Info: Using unique option prefix 'sort_buffer' is error-prone and can break in the future. Please use the full name 'sort_buffer_size' instead.
Info: Using unique option prefix 'read_buffer' is error-prone and can break in the future. Please use the full name 'read_buffer_size' instead.
Info: Using unique option prefix 'write_buffer' is error-prone and can break in the future. Please use the full name 'write_buffer_size' instead.
Checking MyISAM file: /var/lib/mysql/forum_db/xf_post.MYI
Data records: 4719563   Deleted blocks:       0
myisamchk: warning: Table is marked as crashed and last repair failed
- check file-size
myisamchk: warning: Size of indexfile is: 327299072      Should be: 1024
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
myisamchk: Unknown error 126
myisamchk: error: Can't read indexpage from filepos: -1
- recovering (with sort) MyISAM-table '/var/lib/mysql/forum_db/xf_post.MYI'
Data records: 4719563
- Fixing index 1
Key 1 - Found wrong stored record at 6458347680
Key 1 - Found wrong stored record at 6458347680
Key 1 - Found wrong stored record at 6458347680
Key 1 - Found wrong stored record at 6458347680
Key 1 - Found wrong stored record at 6458347680
Key 1 - Found wrong stored record at 6458347680
Key 1 - Found wrong stored record at 6458347680
Key 1 - Found wrong stored record at 6458347680
Key 1 - Found wrong stored record at 6458347680
Key 1 - Found wrong stored record at 6458347680
Key 1 - Found wrong stored record at 6458347680
...
Key 1 - Found wrong stored record at 6458347680
myisamchk: Disk is full writing '/var/lib/mysql/db/xf_post.TMD' (Errcode: 28 "No space left on device"). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)
myisamchk: Retry in 60 secs. Message reprinted in 600 secs
^C
root@www:~#

It seems to loop and the .TMD is consuming the whole server disk space.

root@www:/var/lib/mysql/db# ls -alh xf_post.*
-rw-rw---- 1 mysql mysql 4.7K Mar 12  2019 xf_post.frm
-rw-rw---- 1 mysql mysql 6.6G Mar 13 13:08 xf_post.MYD
-rw-rw---- 1 mysql mysql 313M Mar 13 20:02 xf_post.MYI
-rw-r----- 1 root  root  235G Mar 13 20:35 xf_post.TMD
root@www:/var/lib/mysql/db#

Any idea how to recover this?
@Jake Bunce: Can we possibly manually rebuild the XenForo index?

 

[iCafe]

myisamchk: Disk is full writing '/var/lib/mysql/db/xf_post.TMD' (Errcode: 28 "No space left on device"). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)

check you disk now.

 

[ASap]

myisamchk: Disk is full writing '/var/lib/mysql/db/xf_post.TMD' (Errcode: 28 "No space left on device"). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)

check you disk now.

Thanks for your reply, I'm fully aware that my server was running out of disk space while repairing. But, do you think it makes sense if the actual database only 7GB and the repair process took more than 235 GB?

-rw-rw---- 1 mysql mysql 4.7K Mar 12 2019 xf_post.frm
-rw-rw---- 1 mysql mysql 6.6G Mar 13 13:08 xf_post.MYD
-rw-rw---- 1 mysql mysql 313M Mar 13 20:02 xf_post.MYI
-rw-r----- 1 root root 235G Mar 13 20:35 xf_post.TMD

 

[iCafe]

If the previous command does not work, you can try deleting temporary files that may be preventing myisamchk from running correctly.
to use command mysqlcheck -r DATABASE TABLE

 

[ASap]

If the previous command does not work, you can try deleting temporary files that may be preventing myisamchk from running correctly.
to use command mysqlcheck -r DATABASE TABLE

Did this as well, same behaviour, i.e. xf_post.TMD created and growing rapidly until disk space full.

So, I expanded my server disk drive to 4 TB, the repair process still going on (it's been overnight now), now the xf_post.TMD size is 700 GB (it's still creeping up and doesn't make sense to me)

Command used: myisamchk --silent --force --update-state xf_post.MYI, and here's the output:

 

[ASap]

Update: After consumed like 1.5 TB it failed and came with a recommendation, i.e. to use --safe-recover option:



Now it's repairing again, I'll keep updated here.

--

Update: Now it floods me with heaps of Duplicate key 1 for record at X against new record at X:

 

[Kirby]

Erm ... xf_post should be InnoDB, not MyISAM?

 

[ASap]

Erm ... xf_post should be InnoDB, not MyISAM?

What should I use to repair it then?

 

[iCafe]

Erm ... xf_post should be InnoDB, not MyISAM?

in case db is MyISAM

root@www:/var/lib/mysql/db# ls -alh xf_post.*
-rw-rw---- 1 mysql mysql 4.7K Mar 12  2019 xf_post.frm
-rw-rw---- 1 mysql mysql 6.6G Mar 13 13:08 xf_post.MYD
-rw-rw---- 1 mysql mysql 313M Mar 13 20:02 xf_post.MYI
-rw-r----- 1 root  root  235G Mar 13 20:35 xf_post.TMD
root@www:/var/lib/mysql/db#

if inNODB

root@serv1 xxxx]# ls -alh xf_post.*
-rw-rw---- 1 mysql mysql 4.7K Feb 29 18:36 xf_post.frm
-rw-rw---- 1 mysql mysql 772M Mar  4 18:45 xf_post.ibd
[root@serv1 xxxx]#

 

[ASap]

in case db is MyISAM

root@www:/var/lib/mysql/db# ls -alh xf_post.*
-rw-rw---- 1 mysql mysql 4.7K Mar 12  2019 xf_post.frm
-rw-rw---- 1 mysql mysql 6.6G Mar 13 13:08 xf_post.MYD
-rw-rw---- 1 mysql mysql 313M Mar 13 20:02 xf_post.MYI
-rw-r----- 1 root  root  235G Mar 13 20:35 xf_post.TMD
root@www:/var/lib/mysql/db#

if inNODB

root@serv1 xxxx]# ls -alh xf_post.*
-rw-rw---- 1 mysql mysql 4.7K Feb 29 18:36 xf_post.frm
-rw-rw---- 1 mysql mysql 772M Mar  4 18:45 xf_post.ibd
[root@serv1 xxxx]#

Mine is MyISAM then, so I assume I was doing right?

 

[iCafe]

Try running:

myisamchk -r $TABLE_NAME

If that doesn't work, you can try:

myisamchk -r -v -f $TABLE_NAME

 

[ASap]

Try running:

myisamchk -r $TABLE_NAME

If that doesn't work, you can try:

myisamchk -r -v -f $TABLE_NAME

I've done that, the result is similar to my first post, i.e. consumed all disk space.

 

[Kirby]

As posted before, most XF tables really should be InnoDB and not MyISAM as XF uses transactions which do not work in non transactional engines like MyISAM.

You might try -o, but I wouldn't pur too much hope onto that.
Update: Scratch that, you already tried this.

Do you have a recent backup?
Might be a lot easier to restore the table than trying to repair it.

 

[ASap]

As posted before, most XF tables really should be InnoDB and not MyISAM as XF uses transactions which do not work in non transactional engines like MyISAM.

You might try -o, but I wouldn't pur too much hope onto that.
Update: Scratch that, you already tried this.

Do you have a recent backup?
Might be a lot easier to restore the table than trying to repair it.

I probably have to restore from my backup.

If I want to restore from the backup, can I just shut down mysqld, then restore the corrupted table files, which is xf_post.frm, xf_post.MYD, and xf_post.MYI then start the mysqld service back up, or do I need to use the whole mysqldrump restore? (but I don't have the .sql mysqldump backup.)

 

[ASap]

I tried to use the --parallel-recover option and myisamchk always crashes at this point:

Found wrong packed record at 169444
Found link that points at 8243119441998738464 (outside data file) at 169476

 

[Kirby]

FLUSH TABLES WITH READ LOCK, copy the files back and UNLOCK TABLES
You do not have to shutdown MySQL.

This is for MyISAM only, it will not work this way for InnoDB

 

[ASap]

I found this article:

MySQL :: Re: myisamchk and tmd file during repair of table

forums.mysql.com

It says I can delete the .MYI and re-run the repair. Is that true?

another article that supports it:

How to recover or recreate MYI file of a Table in MySQL DB | BaseZap

Hello, Today we will tell you about MyISAM and MYI file and how to recover or recreate MYI file for Table in MySQL Database. MySQL relational database management system prior versions to 5.5 were using MyISAM as a default storage system for databases. MyISAM is old but it has many useful...

www.basezap.com

but when I do it, it says .MYI file doesn't exist.

 

[ASap]

My forum has been down for 3 days now and no XenForo official response so far?

 

[Ozzy47]

Support in the forum is peer support. Sometimes staff replies, sometimes they don't. I would suggest opening a ticket.

 

[Kirby]

As already pointed out by @ozzy47:
If you need support, open a support ticket.
But I somewhat doubt XF staff will be able to help you much as this is clearly a server issue and not a XF issue.