Koozali.org: home of the SME Server

After power outage the SQL service seems to be down.

Offline JunkmanNZ

  • 10
  • +0/-0
After power outage the SQL service seems to be down.
« on: July 04, 2022, 02:54:12 AM »
We had a power outage and the UPS didn't last long enough, and hence the server lost power.  On restarting everything this seemed to be working ok until we tried our web site, which showed a HTML 500 error. I have searched the forum and found a few hints, but still I am off-line. The backup I have is a bit older then I would like (Yes I know I know)

I did a systemctl status mariadb -l and got the following response:

● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
  Drop-In: /usr/lib/systemd/system/mariadb.service.d
           └─50koozali.conf
   Active: failed (Result: exit-code) since Fri 2022-07-01 17:09:03 NZST; 2 days ago

Jul 01 17:08:54 creakings2.creakings.net systemd[1]: Starting MariaDB database server...
Jul 01 17:08:57 creakings2.creakings.net mariadb-initialize[1849]: 220701 17:08:57 [Note] /usr/libexec/mysqld (mysqld 5.5.68-MariaDB) starting as process 1853 ...
Jul 01 17:09:03 creakings2.creakings.net systemd[1]: mariadb.service: control process exited, code=exited status=1
Jul 01 17:09:03 creakings2.creakings.net systemd[1]: Failed to start MariaDB database server.
Jul 01 17:09:03 creakings2.creakings.net systemd[1]: Unit mariadb.service entered failed state.
Jul 01 17:09:03 creakings2.creakings.net systemd[1]: mariadb.service failed.


Ideally I just want to get the service up and running again without loosing any data  :-)

Thanks

Offline Jean-Philippe Pialasse

  • *
  • 2,747
  • +11/-0
  • aka Unnilennium
    • http://smeserver.pialasse.com
Re: After power outage the SQL service seems to be down.
« Reply #1 on: July 04, 2022, 02:58:06 AM »
next step is to read mariadb logs in /va/log/

Offline JunkmanNZ

  • 10
  • +0/-0
Re: After power outage the SQL service seems to be down.
« Reply #2 on: July 04, 2022, 03:14:41 AM »
This is what I have found in one of the latest logs:

220701 17:08:57 InnoDB: The InnoDB memory heap is disabled
220701 17:08:57 InnoDB: Mutexes and rw_locks use GCC atomic builtins
220701 17:08:57 InnoDB: Compressed tables use zlib 1.2.7
220701 17:08:57 InnoDB: Using Linux native AIO
220701 17:08:57 InnoDB: Initializing buffer pool, size = 128.0M
220701 17:08:57 InnoDB: Completed initialization of buffer pool
220701 17:08:57 InnoDB: highest supported file format is Barracuda.
220701 17:08:57  InnoDB: Waiting for the background threads to start
220701 17:08:58 Percona XtraDB (http://www.percona.com) 5.5.61-MariaDB-38.13 started; log sequence number 3991745554
220701 17:08:58 [Note] Plugin 'FEEDBACK' is disabled.
ERROR: 130  Incorrect file format 'user'
220701 17:08:58 [ERROR] Aborting


Here I have to admit I can get around linux, but no way am I an expert ;-)

Offline ReetP

  • *
  • 3,722
  • +5/-0
Re: After power outage the SQL service seems to be down.
« Reply #3 on: July 04, 2022, 09:20:20 AM »
If you search the interwebs for your error it looks like a mangled table.

Restore is clearly the quick solution. (I run automysqlbackup frequently for this reason)

There are some utilities that do not require maria DB to be running.

Eg

https://mariadb.com/kb/en/myisamchk/
https://mariadb.com/kb/en/clients-utilities/

Make sure you take a backup of all the MYI/MYD files before you mess with this - it's dangerous stuff.

If the tables are innodb and not myisam I'm not sure they are repairable. Others may know.

I think the corrupted table is

Db mysql
Table user

That holds info on users & permissions, which is core.

No idea if you can restore JUST that table. Might be a last ditch attempt if all else fails.
...
1. Read the Manual
2. Read the Wiki
3. Don't ask for support on Unsupported versions of software
4. I have a job, wife, and kids and do this in my spare time. If you want something fixed, please help.

Bugs are easier than you think: http://wiki.contribs.org/Bugzilla_Help

If you love SME and don't want to lose it, join in: http://wiki.contribs.org/Koozali_Foundation

Offline Jean-Philippe Pialasse

  • *
  • 2,747
  • +11/-0
  • aka Unnilennium
    • http://smeserver.pialasse.com
Re: After power outage the SQL service seems to be down.
« Reply #4 on: July 04, 2022, 09:08:06 PM »
this should do pretty much what you need, step by step, with progressive response.

Hint: do backup before proceeding, if mariadb is not running you can backup it by simply copying /var/lib/mysql somewhere else.
Code: [Select]
systemctl stop mariadb
cp -a /var/lib/mysql  /var/lib/mysql.incase

https://wiki.koozali.org/MySQL#MariaDB.2FMySQL_fails_to_start

Offline JunkmanNZ

  • 10
  • +0/-0
Re: After power outage the SQL service seems to be down.
« Reply #5 on: July 04, 2022, 10:16:18 PM »
With the old backup that I have, and no local backup, it seems like I'm a little "stuffed".

Is there a way to at least create an empty(ish) user files so that I can try to work from there to rebuild the MySQL structure?

Offline Jean-Philippe Pialasse

  • *
  • 2,747
  • +11/-0
  • aka Unnilennium
    • http://smeserver.pialasse.com
Re: After power outage the SQL service seems to be down.
« Reply #6 on: July 04, 2022, 11:10:32 PM »
even old you can use this backup. 

have you checked the link i provided and worked on for you? there are actual steps to do before getting at the step where you need a backup to restore from.


Offline JunkmanNZ

  • 10
  • +0/-0
Re: After power outage the SQL service seems to be down.
« Reply #7 on: July 04, 2022, 11:32:39 PM »
Have you checked the link i provided and worked on for you? there are actual steps to do before getting at the step where you need a backup to restore from.

Yes, I looked at the link you sent me, and the issue is that the two User files are of zero length.  I tried what was suggested, but that didn't help to rebuild the user files, so stuck with no user info.

The backup that I have is the full SMEServer>>USB Hard drive one, I'll look to see if there is any info on how to extract certain files from it.


Offline Jean-Philippe Pialasse

  • *
  • 2,747
  • +11/-0
  • aka Unnilennium
    • http://smeserver.pialasse.com
Re: After power outage the SQL service seems to be down.
« Reply #8 on: July 04, 2022, 11:43:59 PM »
if console backup then the file is smeserver.tgz

see the manual to know how to extract specfific files from tar archive

man tar


another hint the dump you want will be in
home/e-smith/db/mysql/mysql.dump

Offline JunkmanNZ

  • 10
  • +0/-0
Re: After power outage the SQL service seems to be down.
« Reply #9 on: July 06, 2022, 04:04:43 AM »
I've managed to recover the SQL setting for the user file, but the console backup does the backup as a SQL Dump, so I have a chicken/egg problem,  I need to have the SQL running to run the code to create the user files, but I need the user's files created to make the SQL run.

I can try to do the creation on a different version of MySQL, but I have no idea if that will work out or not, and it's a little tricky because I would need to set up MySQL on a machine to process the files.

Is there anybody who would like to offer to create the user files and send them to me if I provide the SQL dump for that one file?

Offline Jean-Philippe Pialasse

  • *
  • 2,747
  • +11/-0
  • aka Unnilennium
    • http://smeserver.pialasse.com
Re: After power outage the SQL service seems to be down.
« Reply #10 on: July 06, 2022, 06:27:06 AM »
yes and AGAIN in the link i provided, there is the procedure to start the service without auth so you can restore the auth table mysql.user table. 

Offline JunkmanNZ

  • 10
  • +0/-0
Re: After power outage the SQL service seems to be down.
« Reply #11 on: July 06, 2022, 11:15:05 AM »
yes and AGAIN in the link i provided, there is the procedure to start the service without auth so you can restore the auth table mysql.user table.

Good point, so I tried again what I think you were pointing me to and I got these errors:

[root@creakings2 ~]# mysqlcheck mysql
mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) when trying to connect
[root@creakings2 ~]# mysqlcheck mysql --repair
mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) when trying to connect
[root@creakings2 ~]# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)


Looks like there s something wrong with the mysql.sock  I did some searching for this and first checked the permissions, which look ok:

[root@creakings2 ~]# stat /var/lib/mysql/mysql.sock
  File: ‘/var/lib/mysql/mysql.sock’
  Size: 0            Blocks: 0          IO Block: 4096   socket
Device: fd00h/64768d   Inode: 1075774300  Links: 1
Access: (0777/srwxrwxrwx)  Uid: (   27/   mysql)   Gid: (   27/   mysql)
Access: 2022-07-01 15:52:52.659993589 +1200
Modify: 2022-07-01 15:52:51.726020838 +1200
Change: 2022-07-01 15:52:51.726020838 +1200
 Birth: -


So I might have another problem as well as the user SQL data issue.

I am trying to do what I can to work it out, and I do appreciate your time in helping me.

Offline Jean-Philippe Pialasse

  • *
  • 2,747
  • +11/-0
  • aka Unnilennium
    • http://smeserver.pialasse.com
Re: After power outage the SQL service seems to be down.
« Reply #12 on: July 06, 2022, 12:20:53 PM »
have you checked and red the lines above the one you tried there…?

Offline ReetP

  • *
  • 3,722
  • +5/-0
Re: After power outage the SQL service seems to be down.
« Reply #13 on: July 06, 2022, 12:53:31 PM »
I think the wiki may be wrong.

This should say 'start' and not 'stop' ?

Code: [Select]
systemctl stop mariadb
/usr/libexec/mysqld --bootstrap --user=mysql --skip-grant-tables

...
1. Read the Manual
2. Read the Wiki
3. Don't ask for support on Unsupported versions of software
4. I have a job, wife, and kids and do this in my spare time. If you want something fixed, please help.

Bugs are easier than you think: http://wiki.contribs.org/Bugzilla_Help

If you love SME and don't want to lose it, join in: http://wiki.contribs.org/Koozali_Foundation

Offline Jean-Philippe Pialasse

  • *
  • 2,747
  • +11/-0
  • aka Unnilennium
    • http://smeserver.pialasse.com
Re: After power outage the SQL service seems to be down.
« Reply #14 on: July 06, 2022, 01:00:09 PM »
I think the wiki may be wrong.

This should say 'start' and not 'stop' ?

Code: [Select]
systemctl stop mariadb
/usr/libexec/mysqld --bootstrap --user=mysql --skip-grant-tables

YOU are indeed WRONG. ;)


systemd tries in loop to start mariadb. you have to prevent it to do so by stopping it if you want to have the chance your command win the pid file. 


also for OP, have you seen that your socket file is older than your initial post. this is because of the power outage it has not been removed.