Ksetyadi's [B]Log

It’s been a long time since I visit my old website which still uses MySQL as its database. I’ve been moving to PostgreSQL since years ago and never look back. PostgreSQL is undoubtedly one of the best RDBMS out there when it comes to enterprise-level databases. MySQL is good but I found it hard to tweak and optimize it in a way that it becomes a robust database. So I choose the lazy part: switching to PostgreSQL totally and it goes well so far.

Back to the problem I had with my old website. It’s broken. I never touched it but it’s broken. I don’t have any idea what’s wrong but I’m willing to dig down deep to fix it. Here’s how.

I ssh’ed into my VM and check whether requests are still coming through. I check the Nginx access log and it is still coming through, but it has a problem (noticed that I took out the web information).

162.158.162.187 ----------.com - [25/Apr/2022:00:07:02 +0700] "GET / HTTP/1.1" 500 263 "-" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.67 Safari/537.36" "3.96.243.128" 80 448 "text/html; charset=utf-8" ----------.com "" "-"
162.158.162.187 ----------.com - [25/Apr/2022:00:07:54 +0700] "HEAD / HTTP/1.1" 500 0 "-" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.67 Safari/537.36" "54.76.137.83" 80 200 "text/html; charset=utf-8" ----------.com "" "-"
162.158.162.107 ----------.com - [25/Apr/2022:00:07:54 +0700] "GET / HTTP/1.1" 500 263 "-" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.67 Safari/537.36" "54.76.137.83" 80 448 "text/html; charset=utf-8" ----------.com "" "-"
162.158.162.107 ----------.com - [25/Apr/2022:00:08:03 +0700] "HEAD / HTTP/1.1" 500 0 "-" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.67 Safari/537.36" "35.177.175.106" 80 200 "text/html; charset=utf-8" ----------.com "" "-"

From here, I knew that there was something wrong with the PHP engine, so I checked the PHP log and found nothing. Hm, it’s weird. The requests were indeed coming but not reaching the PHP, at least not yet, and it was dropped.

I’m curious and I checked the database log and finally, I found these errors:

2022-05-06T20:01:34.778651Z 8 [ERROR] [MY-013134] [Server] Table ./mydatabase/table_options is marked as crashed and should be repaired

So, one of the MySQL tables is crashed and should be repaired.

So here’s what I do.

First, I stopped the MySQL service by using this command:

$ sudo service mysql stop

After the service is stopped, then I go to MySQL directory:

$ cd /var/lib/mysql

Then I go to the database directory:

$ cd mydatabase

After which I then find the table name table_options.

Finally, I run this command to fix the table:

$ sudo myisamchk -r -v -f table_options

Here’s the result:

- recovering (with sort) MyISAM-table table_options
Data records: 3424
- Fixing index 1
- Searching for keys, allocating buffer for 78663 keys
Wrong block with wrong total length starting at 237508
Wrong bytesec: 251-  0-  0 at     237512; Skipped
Wrong bytesec:  95-116-114 at     237532; Skipped
Wrong bytesec: 105-108-101 at     237552; Skipped
Wrong bytesec:  52- 55- 58 at     237572; Skipped
Wrong bytesec: 101- 57-100 at     237592; Skipped
Wrong bytesec:  54- 97- 34 at     237612; Skipped
Wrong bytesec:  59-115- 58 at     237632; Skipped
Wrong bytesec:  71-114- 97 at     237652; Skipped
Wrong bytesec: 105-112-116 at     237672; Skipped
Wrong bytesec: 121-111-117 at     237692; Skipped
Wrong bytesec:  44- 32- 97 at     237712; Skipped
Wrong bytesec:  34-106-117 at     237732; Skipped
Wrong bytesec:  58- 34- 34 at     237752; Skipped
Wrong bytesec:  54- 34- 59 at     237772; Skipped
Wrong bytesec: 105-111-110 at     237792; Skipped
Wrong bytesec:  49- 48- 58 at     237812; Skipped
Wrong bytesec:  34- 49- 46 at     237832; Skipped
Wrong bytesec: 115- 58- 48 at     237852; Skipped
...
...
Wrong bytesec:  53-102- 52 at    1463624; Skipped
Wrong bytesec:  31-  0-  0 at     284760; Skipped
Wrong bytesec: 106-112-115 at     284780; Skipped
- Dumping 3413 keys
- Fixing index 2
- Searching for keys, allocating buffer for 2681 keys
- Last merge and dumping keys
Data records: 3413

Done. My database is normal again and when I started the service:

$ sudo service mysql start

my old website can be accessed again.