Koozali.org: home of the SME Server
Contribs.org Forums => Koozali SME Server 10.x => Topic started by: JunkmanNZ 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
-
next step is to read mariadb logs in /va/log/
-
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 ;-)
-
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.
-
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.
systemctl stop mariadb
cp -a /var/lib/mysql /var/lib/mysql.incase
https://wiki.koozali.org/MySQL#MariaDB.2FMySQL_fails_to_start
-
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?
-
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.
-
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.
-
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
-
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?
-
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.
-
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.
-
have you checked and red the lines above the one you tried there…?
-
I think the wiki may be wrong.
This should say 'start' and not 'stop' ?
systemctl stop mariadb
/usr/libexec/mysqld --bootstrap --user=mysql --skip-grant-tables
-
I think the wiki may be wrong.
This should say 'start' and not 'stop' ?
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.
-
Ah it's two separate lines.
Slightly confusing (evidently!!)
-
Each time I do
/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?
-
you need to type other commands in another terminal.
-
you need to type other commands in another terminal.
Tried that, whatever I try returns the message:
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?
-
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.
-
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?
-
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
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)
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
-
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.
-
happy to read that.
thank for the feed back.
yes for the battery !