Topic: Problem with mysql

[Skalviai]

SmeServer 10.
Mysql does not work after updating the server with "yum update" and restarting the server. When you try mysql:

[root@server ~]# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)

How to restore mysql.

[ReetP]

How to restore mysql.

You have got backups? Just in case?

Can you please read the thread on how to report issues effectively.

https://forums.koozali.org/index.php/topic,54724.0.html

Then give us some server history - new clean install or upgrade etc etc.

Is this totally new or related to your previous posts as you opened this a while ago and seemed to be hacking about with the database so I wonder if this is self inflicted?

https://forums.koozali.org/index.php/topic,54775.msg287780.html

Ah and you have been playing with MSSql as well:

https://forums.koozali.org/index.php/topic,54726.msg287430.html

And last - did you buy a UPS?

https://forums.koozali.org/index.php/topic,54633.msg286653.html


Paste the results of these:

Code: [Select]

/sbin/e-smith/audittools/newrpms
/sbin/e-smith/audittools/templates
ll /var/lib/mysql/


[Jean-Philippe Pialasse]

and check your logs


journalctl -u mariadb -f


tail /var/log/mariadb/mariadb.log

[Skalviai]

1. Backup - YES.
2. clean install - YES
3.  MSSql  - NO.
4. UPS - YES.

[root@server ~]# /sbin/e-smith/audittools/newrpms
Loaded plugins: fastestmirror, post-transaction-actions, priorities, smeserver
Loading mirror speeds from cached hostfile
 * base: mirror.cloudhosting.lv
 * smeaddons: ibsgaarden.dk
 * smeos: ibsgaarden.dk
 * smeupdates: ibsgaarden.dk
 * updates: centos2.hti.pl
Extra Packages
GeoIP.x86_64                            1.6.12-9.el7.sme            @smecontribs
GeoIP-GeoLite-data.noarch               2018.06-7.el7.sme           @smecontribs
GeoIP-GeoLite-data-extra.noarch         2018.06-7.el7.sme           @smecontribs
libicu69.x86_64                         69.1-2.el7.remi             @remi-safe
php81-php.x86_64                        8.1.4-1.el7.remi            @remi-safe
php81-php-bcmath.x86_64                 8.1.4-1.el7.remi            @remi-safe
php81-php-cli.x86_64                    8.1.4-1.el7.remi            @remi-safe
php81-php-common.x86_64                 8.1.4-1.el7.remi            @remi-safe
php81-php-enchant.x86_64                8.1.4-1.el7.remi            @remi-safe
php81-php-fpm.x86_64                    8.1.4-1.el7.remi            @remi-safe
php81-php-gd.x86_64                     8.1.4-1.el7.remi            @remi-safe
php81-php-imap.x86_64                   8.1.4-1.el7.remi            @remi-safe
php81-php-intl.x86_64                   8.1.4-1.el7.remi            @remi-safe
php81-php-ldap.x86_64                   8.1.4-1.el7.remi            @remi-safe
php81-php-mbstring.x86_64               8.1.4-1.el7.remi            @remi-safe
php81-php-mysqlnd.x86_64                8.1.4-1.el7.remi            @remi-safe
php81-php-opcache.x86_64                8.1.4-1.el7.remi            @remi-safe
php81-php-pdo.x86_64                    8.1.4-1.el7.remi            @remi-safe
php81-php-pear.noarch                   1:1.10.13-1.el7.remi        @remi-safe
php81-php-pecl-xmlrpc.x86_64            1.0.0~rc3-1.el7.remi        @remi-safe
php81-php-pecl-zip.x86_64               1.20.0-1.el7.remi           @remi-safe
php81-php-process.x86_64                8.1.4-1.el7.remi            @remi-safe
php81-php-snmp.x86_64                   8.1.4-1.el7.remi            @remi-safe
php81-php-soap.x86_64                   8.1.4-1.el7.remi            @remi-safe
php81-php-sodium.x86_64                 8.1.4-1.el7.remi            @remi-safe
php81-php-tidy.x86_64                   8.1.4-1.el7.remi            @remi-safe
php81-php-xml.x86_64                    8.1.4-1.el7.remi            @remi-safe
php81-runtime.x86_64                    8.1-1.el7.remi              @remi-safe
phpMyAdmin.noarch                       5.1.0-1.el7.sme             @smecontribs
smeserver-mod_dav.noarch                1.1-7.el7.sme               @smecontribs
smeserver-phpmyadmin.noarch             4.0.10.2-11.el7.sme         @smecontribs
smeserver-remoteuseraccess.noarch       1.3-6.el7.sme               @smecontribs
smeserver-shared-folders.noarch         0.3-15.el7.sme              @smecontribs

[root@server ~]# /sbin/e-smith/audittools/templates
/etc/e-smith/templates-custom/etc/smb.conf/ibays/20browseable: MANUALLY_ADDED, ADDITION
/etc/e-smith/templates-custom/etc/crontab/katalogas: MANUALLY_ADDED, ADDITION
/etc/e-smith/templates/etc/smb.conf/50userhomes-admin: MANUALLY_ADDED

[root@server ~]# ll /var/lib/mysql/
total 28728
-rw-rw---- 1 mysql mysql    16384 Apr  6 19:09 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Apr  6 19:09 aria_log_control
drwx------ 2 mysql mysql    12288 Feb 23 18:26 horde
-rw-rw---- 1 mysql mysql 18874368 Apr  6 19:09 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Apr  6 19:09 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Feb 23 18:26 ib_logfile1
drwx------ 2 mysql mysql      131 Feb 24 12:17 katalogas
drwx------ 2 mysql mysql      131 Mar  4 21:35 katalogastest
drwx------ 2 mysql mysql     4096 Apr  6 09:20 mysql
srwxrwxrwx 1 mysql mysql        0 Apr  6 09:20 mysql.sock
-rw-r--r-- 1 root  root        14 Feb 23 18:26 mysql_upgrade_info
drwx------ 2 mysql mysql     4096 Feb 23 18:26 performance_schema
drwx------ 2 mysql mysql     4096 Feb 23 19:43 phpmyadmin
-r-------- 1 root  root       258 Feb 23 18:25 set.password

[Skalviai]

and check your logs


journalctl -u mariadb -f


tail /var/log/mariadb/mariadb.log

[root@server mariadb]# tail mariadb.log
220405  7:26:38 [Note] Hostname 'zg-0308e-74.stretchoid.com' does not resolve to '192.241.225.151'.
220405  7:26:38 [Note] Hostname 'zg-0308e-74.stretchoid.com' has the following IP addresses:
220405  7:26:38 [Note]  - 127.0.0.1

220405 14:24:20 [Warning] IP address '81.69.36.46' could not be resolved: Name or service not known
220405 14:24:20 [Warning] IP address '81.69.36.46' could not be resolved: Name or service not known
220405 16:34:49 [Warning] IP address '31.111.58.170' could not be resolved: Name or service not known
220405 16:34:49 [Warning] IP address '31.111.58.170' could not be resolved: Name or service not known
220405 20:50:35 [Warning] IP address '34.96.130.28' has been resolved to the host name '28.130.96.34.bc.googleusercontent.com', which resembles IPv4-address itself.
220406  1:57:08 [Note] Hostname 'zg-0308b-59.stretchoid.com' does not resolve to '192.241.214.46'.
220406  1:57:08 [Note] Hostname 'zg-0308b-59.stretchoid.com' has the following IP addresses:
220406  1:57:08 [Note]  - 127.0.0.1
220406  5:23:11 [Warning] IP address '167.94.146.57' could not be resolved: Name or service not known
220406  9:21:30 InnoDB: The InnoDB memory heap is disabled
220406  9:21:30 InnoDB: Mutexes and rw_locks use GCC atomic builtins
220406  9:21:30 InnoDB: Compressed tables use zlib 1.2.7
220406  9:21:30 InnoDB: Using Linux native AIO
220406  9:21:30 InnoDB: Initializing buffer pool, size = 128.0M
220406  9:21:30 InnoDB: Completed initialization of buffer pool
220406  9:21:30 InnoDB: highest supported file format is Barracuda.
220406  9:21:30  InnoDB: Waiting for the background threads to start
220406  9:21:31 Percona XtraDB (http://www.percona.com) 5.5.61-MariaDB-38.13 started; log sequence number 4996527
220406  9:21:31 [Note] Plugin 'FEEDBACK' is disabled.
ERROR: 130  Incorrect file format 'user'
220406  9:21:31 [ERROR] Aborting

220406  9:21:31  InnoDB: Starting shutdown...
220406  9:21:35  InnoDB: Shutdown completed; log sequence number 4996527
220406  9:21:35 [Note] /usr/libexec/mysqld: Shutdown complete

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

220406 13:09:41  InnoDB: Starting shutdown...
220406 13:09:45  InnoDB: Shutdown completed; log sequence number 4996527
220406 13:09:45 [Note] /usr/libexec/mysqld: Shutdown complete

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

220406 13:17:04  InnoDB: Starting shutdown...
220406 13:17:08  InnoDB: Shutdown completed; log sequence number 4996527
220406 13:17:08 [Note] /usr/libexec/mysqld: Shutdown complete

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

220406 14:10:20  InnoDB: Starting shutdown...
220406 14:10:24  InnoDB: Shutdown completed; log sequence number 4996527
220406 14:10:24 [Note] /usr/libexec/mysqld: Shutdown complete

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

220406 14:15:46  InnoDB: Starting shutdown...
220406 14:15:50  InnoDB: Shutdown completed; log sequence number 4996527
220406 14:15:50 [Note] /usr/libexec/mysqld: Shutdown complete

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

220406 19:09:01  InnoDB: Starting shutdown...
220406 19:09:05  InnoDB: Shutdown completed; log sequence number 4996527
220406 19:09:05 [Note] /usr/libexec/mysqld: Shutdown complete

[root@server ~]# journalctl -u mariadb -f
-- Logs begin at Wed 2022-02-23 20:30:35 EET. --
Apr 06 14:15:50 server.****.local systemd[1]: mariadb.service: control process exited, code=exited status=1
Apr 06 14:15:50 server.****.local systemd[1]: Failed to start MariaDB database server.
Apr 06 14:15:50 server.****.local systemd[1]: Unit mariadb.service entered failed state.
Apr 06 14:15:50 server.****.local systemd[1]: mariadb.service failed.
-- Reboot --
Apr 06 19:08:59 server.****..local systemd[1]: Starting MariaDB database server...
Apr 06 19:09:00 server.****..local mariadb-initialize[1397]: 220406 19:09:00 [Note] /usr/libexec/mysqld (mysqld 5.5.68-MariaDB) starting as process 1399 ...
Apr 06 19:09:05 server.****..local systemd[1]: mariadb.service: control process exited, code=exited status=1
Apr 06 19:09:05 server.****..local systemd[1]: Failed to start MariaDB database server.
Apr 06 19:09:05 server.****..local systemd[1]: Unit mariadb.service entered failed state.
Apr 06 19:09:05 server.****..local systemd[1]: mariadb.service failed.

Thanks for the ideas and help.
In the LOG file I noticed that
user.MYD and user.MYI - file size is 0.
There are no copies of these files.
How to restore / create these files?

[Skalviai]

Problem SOLVED.

I copied the user.MYD and user.MYI files from another running server. Clearly needed to re-create users ...

Thanks everyone for the help.

Maybe there is another better way?

[ReetP]

Problem SOLVED.

I copied the user.MYD and user.MYI files from another running server. Clearly needed to re-create users ...

How on earth did you lose them? Clearly something you haven't told us (which makes helping you difficult)

Maybe there is another better way?

Yes. Restore from a known good backup.

You have possibly introduced all sorts of errors. Another server may well not have all the same information.

https://stackoverflow.com/questions/879176/how-to-recover-mysql-database-from-myd-myi-frm-files#879268

The next question is likely to be:.

"Why can't I do X or Y in my database"

You are quite possibly compounding your original mistake and creating your own XY problem.

Restore from backup is the only certain answer here.

[Skalviai]

How on earth did you lose them? Clearly something you haven't told us (which makes helping you difficult)

How those files became 0 size, I don't know. Why mysql doesn't start is only clear after analyzing the log file /var/log/mariadb/mariadb.log.
Thanks for the advice to view this log file.

[Jean-Philippe Pialasse]

where is the server ?
what says

uname -r


I have seen that once on a server hosted where the the provider support thought a good idea to reboot the server on its own kernel version. 
Also had issues with qpsmtpd failing to start with insufficient memory available abd probably few other issues i did overlook.

Alternative would have been to manually start mariadb without auth, restore the table from a recent dump and restart the service. 

[Skalviai]

what says
uname -r

[root@server ~]# uname -r
3.10.0-1160.59.1.el7.x86_64