MySQL DB is full with ERROR 2002

[Ashutosh Singh]

Hello All,

 

Recently we upgraded our gateway t 9.3 version and OTK to 4.2. However after the upgrade, we ran an endurance test on the environment and seeing that SQL has stopped working.

 

Whenever we try to open MYSQL, we get error like below.

 

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

 

Below is a snippet of df -kh...

 

[root@************ tmp]# df -kh
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg00-lv_root
7.9G 2.0G 5.6G 27% /
tmpfs 16G 0 16G 0% /dev/shm
/dev/sda1 1008M 58M 900M 7% /boot
/dev/mapper/vg00-lv_home
5.0G 222M 4.5G 5% /home
/dev/mapper/vg00-lv_opt
6.9G 3.4G 3.3G 51% /opt
/dev/mapper/vg00-lv_tmp
6.9G 4.4G 2.3G 67% /tmp
/dev/mapper/vg00-lv_var
3.0G 115M 2.7G 4% /var
/dev/mapper/vg00-lv_db
21G 20G 12M 100% /var/lib/mysql
/dev/mapper/vg00-lv_log
2.0G 1.9G 70M 97% /var/log
/dev/mapper/vg00-lv_audit
2.0G 92M 1.8G 5% /var/log/audit

 

And below is the snippet of /var/lib/mysql location where ibdata can be seen as a very huge file.

 

drwxr-xr-x 2 mysql mysql 4096 Dec 14 2016 test
drwx------ 2 mysql mysql 4096 Dec 14 2016 lost@002bfound
-rw-rw---- 1 mysql mysql 1428 Jun 6 2017 localhost-slow.log
-rw-r--r-- 1 mysql mysql 276 Jan 28 20:56 RPM_UPGRADE_MARKER-LAST
-rw-r--r-- 1 mysql mysql 1188 Jan 28 20:56 RPM_UPGRADE_HISTORY
-rw-rw---- 1 mysql mysql 56 Jan 28 20:57 auto.cnf
drwxr-x--- 2 mysql mysql 4096 Jan 28 20:57 performance_schema
drwxr-x--- 2 mysql mysql 12288 Jan 28 20:57 sys
-rw-r--r-- 1 mysql mysql 6 Jan 28 20:57 mysql_upgrade_info
drwx--x--x 2 mysql mysql 4096 Jan 28 20:57 mysql
-rw-r----- 1 mysql mysql 6289 Jan 31 21:59 ib_buffer_pool
-rw------- 1 mysql mysql 6 Feb 1 06:56 mysql.sock.lock
-rw-rw---- 1 mysql mysql 43036 Feb 1 06:56 las2app2448arv-slow.log
-rw-rw---- 1 mysql mysql 16620978176 Feb 1 09:28 ibdata
drwx------ 2 mysql mysql 4096 Feb 1 09:28 ssg
-rw-r----- 1 mysql mysql 16777216 Feb 1 09:28 ib_logfile1
-rw-r----- 1 mysql mysql 16777216 Feb 1 14:31 ib_logfile0

[Cornelia Postma]

Hi,

please have a look at this KB article: https://support.ca.com/us/knowledge-base-articles.TEC0000001057.html 
It describes how to remove existing audit data and shrink the existing ibdata file

[Muthugomu]

Conny 

 

Well, this document can only help if MYSQL is running.  However in the current scenario MYSQL stopped working as /var/lib/mysql filesystem is 100% full.  

 

Question:

1. This gateway is version 9.3. We have audit.managementStrategy set to BYPASS.  As per my understanding, if we have this clusterwide property set to BYPASS, database should never get full?  correct?  If yes, then how the db got full inspite of having audit.managementStrategy flag set to BYPASS?

 

Thanks

muthu

[fazab01]

Hi 

If you are MYSQL partation is 100% full and not working, please follow this to manually remove the file to free up space and the reinitialize your replication since that would be broken at this point.

https://support.ca.com/us/knowledge-base-articles.tec1168083.html

 

Hope this helps

THanks

Abs

[Ashutosh Singh]

This is a stand alone gateway install hence no replication.

 

The /var/lib/mysql file system had exhausted disk space and the mysql process was not able to start up. 

 

The ibdata file was 16 Gb in size which is at its max,

 

What we did to free up some space was move a 'sys' database to /tmp

 

This then allowed mysql to start up successfully.

 

We immediately then went into the mysql interface and truncated and optimized the audit tables via the following:

 

mysql

use ssg;

SET FOREIGN_KEY_CHECKS = 0;

truncate table audit_admin;

optimize table audit_admin;

truncate table audit_detail;

optimize table audit_detail;

truncate table audit_detail_params;

optimize table audit_detail_params;

truncate table audit_main;

optimize table audit_main;

truncate table audit_message;

optimize table audit_message;

truncate table audit_system;

optimize table audit_system;

SET FOREIGN_KEY_CHECKS = 1;

 

The /var/lib/mysql disk usage then dropped from 100% down to 80%

[Charles Lilienkamp]

Ashutosh,

A few follow up things.

1) The ibdata itself wont shrink from just a delete. Assuming a large amount of data was used by audits if you want the file to shrink you would have to follow the instructions on this kb which includes a portion of the above but not the backup-restore which would shrink things down.

Shrinking MySQL ibdata file 

2) If audits are filling your db we have scripts to maintain this regularly you should take a look at,

Configuring and installing the audit record maintenance script 

3) If your disk has space but ibdata is maxed so mysql wont start you can also update

/etc/my.cnf

innodb_data_file_path=ibdata:100M:autoextend:max:15851M

4) You can also on the Vm increase the disk space available to mysql we have a video on this here,

Increasing Disk Space using LVM - YouTube 

 

But besides your deletes if your size of your ibdata grows beyond the max defined in item 3 it will fail to start as well.

Thanks..

[Ashutosh Singh]

Yeah this post is helpful.

 

Best Regards

 

Ashutosh Singh

 

Identity and Access Management Delivery | Technologies

..................................

 

O: 6126788756  | IN: +91124.4179365

M: [phone]

 

Ameriprise Financial

Prestige Corporate Park,

Plot No.14, Sector-18,

Gurgaon, Haryana 122016

 

<http://www.ameriprise.com/>[Facebook]<http://www.facebook.com/ameriprise>[YouTube]<http://www.youtube.com/ameriprise>

 

<http://www.ameriprise.com/>