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. 

Offline ReetP

  • *
  • 3,722
  • +5/-0
Re: After power outage the SQL service seems to be down.
« Reply #15 on: July 06, 2022, 01:11:38 PM »
Ah it's two separate lines.

Slightly confusing (evidently!!)
...
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 JunkmanNZ

  • 10
  • +0/-0
Re: After power outage the SQL service seems to be down.
« Reply #16 on: July 07, 2022, 03:10:15 AM »
Each time I do
Code: [Select]
/usr/libexec/mysqld --bootstrap --user=mysql --skip-grant-tablesI seem to drop out of the command prompt system and the server seems to be waiting for me to enter something, as soon as I enter any line it goes and does some thinking, then drops me back to command prompt.

I've been trying to do the steps in https://wiki.koozali.org/MySQL#MariaDB.2FMySQL_fails_to_start and getting confused with the loss of the command prompt.

Any hints?

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 #17 on: July 07, 2022, 04:03:40 AM »
you need to type other commands in another terminal. 

Offline JunkmanNZ

  • 10
  • +0/-0
Re: After power outage the SQL service seems to be down.
« Reply #18 on: July 07, 2022, 04:26:21 AM »
you need to type other commands in another terminal.

Tried that, whatever I try returns the message:
Quote
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock
WebSearching talked about editing the my.cnf, which I did, but to me it all looks like it should be. Plus it has a comment at the top saying "!!DO NOT MODIFY THIS FILE!!"
 
Do I need to reset the sock somehow? Or is there something else I should be looking at?

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 #19 on: July 07, 2022, 01:50:38 PM »
have you checked at the log file when trying to start the service ?

there are high chances it fails because of the remaining sock file or something similar.

Offline JunkmanNZ

  • 10
  • +0/-0
Re: After power outage the SQL service seems to be down.
« Reply #20 on: July 08, 2022, 04:55:24 AM »
have you checked at the log file when trying to start the service ?

there are high chances it fails because of the remaining sock file or something similar.

I've had a look at a few log files, but I've not been able to locate anything that seems helpful, it might be that I am looking at the wrong logs.

Is there a way to reset all the sock files?

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 #21 on: July 08, 2022, 05:47:39 AM »
I've had a look at a few log files, but I've not been able to locate anything that seems helpful, it might be that I am looking at the wrong logs.

Is there a way to reset all the sock files?

just rewrote the command should do the job now
Code: [Select]
systemctl stop mariadb.service
/usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql  --user=mysql --skip-grant-tables


then you should be able to do the final steps (considering your table dump is in a file called  /home/e-smith/db/mysql/mysql.user.dump)
Code: [Select]
mysql mysql < /home/e-smith/db/mysql/mysql.user.dump
expand-template /var/service/mysqld/set.password
mysql mysql < /var/service/mysqld/set.password
mysqladmin shutdown
systemctl start mariadb



in any case when a daemon in charge of a socket has crashed and the socket is still there, check it is not running anymore and you can delete it, see :
https://www.ibm.com/support/pages/mysql-does-not-start-after-power-outage
« Last Edit: July 08, 2022, 05:50:17 AM by Jean-Philippe Pialasse »

Offline JunkmanNZ

  • 10
  • +0/-0
Re: After power outage the SQL service seems to be down.
« Reply #22 on: July 09, 2022, 04:23:09 AM »
Thanks Jean-Philippe,

That seems to have fixed the issue, I had another power cut last night (must get the battery replaced in the UPS) and everything seems to be up and running again.


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 #23 on: July 09, 2022, 06:18:44 AM »
happy to read that. 

thank for the feed back. 

yes for the battery !