Cannot Access MySQL because Alfresco’s log consumed space storage

By Posted on

MySQL Database cannot be accessed because the Server’s storage is exhausted.

One of the application crashed and was displaying error messages. One of the most highlighted error message is that the application itself cannot connect to MySQL server.

This is step-by-step actions taken to resolve the problem :

  1. To be able to resolve the problem, we have to check it first by logging in to the database server which is represented by the IP Address of the database server itself.

Connect with the following command : 

ssh username@IP_Address_Database_Server
  1. Try to log in to MySQL Console by executing the following command :
mysql -uroot -p

There is an error when we are trying to connect to MySQL console which the error itself can be specified as follows : 

Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (38)

It means that we cannot connect to MySQL Server but before we go further, we have to check the status of MySQL service as follows : 

systemctl status mysql.service

It can be shown as follows : 

[root@hostname ~]# systemctl status mysql.service
mysql.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysql)
   Active: active (running) since Sat xxxx-xx-xx xx:xx:xx WIB; x day xxh ago
  Process: 12776 ExecStop=/etc/rc.d/init.d/mysql stop (code=exited, status=0/SUCCESS)
 Process: 12783 ExecStart=/etc/rc.d/init.d/mysql start (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/mysql.service
           ├─12788 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/xxx.xxx.xxx.xx.xx.pid
           └─12969 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/xxx.xxx.xxx.xxx.err --pid-file=/var/lib...
Jul 09 xx:xx:xx xxx.xxx.xxx.xx.xx mysql[12783]: Starting MySQL.... SUCCESS!
Jul 09 xx:xx:xx xxx.xxx.xxx.xx.xx systemd[1]: Started LSB: start and stop MySQL.
[root@hostname ~]#
  1. Check whether the file exist by checking it as follows in the following folder by typing the following command :
pwd
[root@hostname mysql]# pwd
/var/lib/mysql
[root@hostname mysql]#

Check the content of the folder itself whether or not mysql.sock file exists by typing the following command : 

ls -al
[root@hostname mysql]# ls -al
total 176384
drwxr-xr-x.  9 mysql mysql     4096 Jul 11 16:55 .
drwxr-xr-x. 29 root  root      4096 Jul 11 03:37 ..
drwx------.  2 mysql mysql     8192 Apr 26 08:24 alfresco
drwx------.  2 mysql mysql     8192 Jul  2 03:00 alfresco_community
-rw-rw----.  1 mysql mysql       56 Nov 20  2015 auto.cnf
-rw-------.  1 mysql mysql        6 Feb  2 19:31 .bash_history
-rw-r-----.  1 mysql root    135072 Jul  9 18:30 dws.xxxx.xxxxxxxxx.xx.xx.err
-rw-rw----.  1 mysql mysql        6 Jul  9 18:30 xxx.xxxx.xxxxxxxxx.xx.xx.pid
-rw-rw----.  1 mysql mysql 79691776 Jul 11 16:55 ibdata1
-rw-rw----.  1 mysql mysql 50331648 Jul 11 16:55 ib_logfile0
-rw-rw----.  1 mysql mysql 50331648 Jul 11 16:55 ib_logfile1
drwx------.  2 mysql mysql     4096 Jun 22 11:57 xxxxxx
-rw-r-----.  1 mysql root     43159 Apr 19 16:06 xxxxxx.err
-rw-rw----.  1 mysql mysql        6 Apr 19 16:06 xxxxxx.pid
rwx--x--x.  2 mysql mysql     4096 Nov 20  2015 mysql
srwxrwxrwx.  1 mysql mysql        0 Jul  9 18:30 mysql.sock
drwx------.  2 mysql mysql     4096 Feb 22 17:55 xxxxxxxxxxxxx
drwx------.  2 mysql mysql     4096 Nov 20  2015 performance_schema
-rw-r--r--.  1 root  root       115 Nov 20  2015 RPM_UPGRADE_HISTORY
-rw-r--r--.  1 mysql mysql      115 Nov 20  2015 RPM_UPGRADE_MARKER-LAST
drwx------.  2 mysql mysql     4096 Apr 25 11:01 xxxxxx
[root@hostname mysql]# pwd
/var/lib/mysql
[root@hostname mysql]#
  1. As we check the log file in mysql there is an error define that the cannot be created, the file is  :
xxx.xxxx.xxxxxxxx.xx.xx.err

Unfortunately, the space storage which is 100% consumed cannot be shown in this article which is becoming the main cause MySQL crashed. It cannot further generate any files since there aren’t any available space exists. Below is the content of the file previewing a state that MySQL service cannot further more write in to file /var/lib/mysql/xxx.xxxxx-test as follows : 

2016-07-05 07:21:15 98588 [Warning] Can't create test file /var/lib/mysql/xxx.xxxxx-test
2016-07-05 07:21:15 98588 [Warning] Can't create test file /var/lib/mysql/xxx.xxxxx-test
160705 07:21:15 mysqld_safe WSREP: Failed to recover position:
160709 18:30:15 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160709 18:30:15 mysqld_safe WSREP: Running position recovery with --log_error='/var/lib/mysql/wsrep_recovery.wciHrr' --pid-file='/var/lib/mysql/xxx.xxxx.xxxxxxxx.xx.xx-recover.pid'
2016-07-09 18:30:15 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-07-09 18:30:15 0 [Note] /usr/sbin/mysqld (mysqld 5.6.27) starting as process 12931 ...
160709 18:30:18 mysqld_safe WSREP: Recovered position 00000000-0000-0000-0000-000000000000:-1
2016-07-09 18:30:18 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-07-09 18:30:18 0 [Note] /usr/sbin/mysqld (mysqld 5.6.27) starting as process 12969 ...
  1. There is a need to check the following file in alfresco since the storage space is exhausted. Check the following content but first of all type the following command :
pwd

It can be shown as follows : 

[root@hostname logs]# pwd
/opt/alfresco-community/tomcat/logs
[root@hostname logs]#

Check the content of the folder by typing the following command : 

ls -al
[root@hostname logs]# ls -al
total 7420548
drwxr-xr-x.  2 root root       4096 Jul 11 00:00 .
drwxr-xr-x. 12 root root       4096 Apr 29 09:57 ..
-rw-r--r--.  1 root root      19045 Apr 29 17:06 catalina.2016-04-29.log
-rw-r--r--.  1 root root        210 May  9 11:03 catalina.2016-05-09.log
-rw-r--r--.  1 root root      34020 May 17 05:52 catalina.2016-05-17.log
-rw-r--r--.  1 root root      12141 May 18 10:54 catalina.2016-05-18.log
-rw-r--r--.  1 root root        220 Jun  8 21:34 catalina.2016-06-08.log
-rw-r--r--.  1 root root       5379 Jun 13 09:30 catalina.2016-06-13.log
-rw-r--r--.  1 root root   64627268 Jul  9 18:30 catalina.out
-rw-r--r--.  1 root root          0 Apr 29 09:58 host-manager.2016-04-29.log
-rw-r--r--.  1 root root          0 May 17 04:18 host-manager.2016-05-17.log
-rw-r--r--.  1 root root          0 May 18 10:51 host-manager.2016-05-18.log
-rw-r--r--.  1 root root          0 Jun 13 09:29 host-manager.2016-06-13.log
-rw-r--r--.  1 root root          0 Apr 29 09:58 localhost.2016-04-29.log
-rw-r--r--.  1 root root          0 May 17 04:18 localhost.2016-05-17.log
-rw-r--r--.  1 root root          0 May 18 10:51 localhost.2016-05-18.log
-rw-r--r--.  1 root root          0 Jun 13 09:29 localhost.2016-06-13.log
.
.
.
.
.
.
.
-rw-r--r--.  1 root root 4059771517 Jul 10 23:59 localhost_access_log2016-xx-xx.txt
-rw-r--r--.  1 root root 2959267746 Jul 11 16:57 localhost_access_log2016-xx-xx.txt
-rw-r--r--.  1 root root          0 Apr 29 09:58 manager.2016-04-29.log
-rw-r--r--.  1 root root          0 May 17 04:18 manager.2016-05-17.log
-rw-r--r--.  1 root root          0 May 18 10:51 manager.2016-05-18.log
-rw-r--r--.  1 root root          0 Jun 13 09:29 manager.2016-06-13.log
-rw-r--r--.  1 root root          0 Feb 17  2015 NOTEMPTY

[root@hostname logs]# pwd

The above shown that lots of log files have been produced by alfresco. It is the one that affect the storage to be exhausted. One way or another we have to erase several log files so that we can restart MySQL service as follows : 

[root@hostname logs]# rm -rf localhost_access_logxxxx-x*

The removal process is done with the command above which is already calculated as files with large size which exists in the folder : 

/opt/alfresco-community/tomcat/logs

It is done based on the conclusion from counting the file size on the logs folder based on the command executed as follows : 

du -a  . | sort -n -r | head -n 10
[root@hostname logs]# du -a  . | sort -n -r | head -n 10

The above  command can be used in order to count the size of files.

As soon as several files removed in the above step we can claim some spaces which is needed for MySQL service to operate again.

  1. Restart MySQL service by executing the following command :
[root@hostname logs]# systemctl restart mysql.service
[root@hostname logs]#
  1. After finishing on restart MySQL service, we can log in to MySQL Console by typing the command as follows : 
[root@hostname logs]# mysql -uroot -p experiment
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5983
Server version: 5.6.27 MySQL Community Server (GPL), wsrep_25.12

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Leave a Reply