Top 10 MySQL Database Errors and Their Resolutions

In this document, we are listing common MySQL database error code, cause of error and their resolution. This can help the experienced DBA as well as fresher DBA to fix these errors without spending lot of times.

Error No 1:- Socket Connection Error

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

Cause: - In usual this error reported in error logs or application log when user uses wrong username and password of database. If user is using valid user name and  password then it might happened  because of MySQL instance get crashed or not live.

If MySQL DB server is live and users are not able to authenticate with database then this might happen because of no availability of enough spaces of disks.

Resolution:- First crosscheck the user credentials which are used to connected with db ,if this is Ok then check MySQL instance availability by using below commends

Service mysqld status;

Ps –ef|grep mysqld

Mongoadmin –u root –p status

If DB is live then check free disc space  using below commend

Df –kh

See free space on mount point where MySQL server is keeping data file and Binaries.

Error No 2:- mysql_connect(): Too many connections

Cause:- New Application connection  or DB connection with Non Super Users will not be happen, No effect of existing connections .This is kind of application outage till existing connection got flushed or killed.

Resolution: - Login with the super user and see current value of max_connections variable value using below commend

Show variables like ‘%max_connections%’;

Below is the calculation to get optimal value of max connections

Available RAM = Global Buffers + (Thread Buffers x max_connections)

max_connections = (Available RAM - Global Buffers) / Thread Buffers

Here's a list of the buffers and whether they're Global or Thread:

Global Buffers: key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size, net_buffer_size, query_cache_size

Thread Buffers: sort_buffer_size, myisam_sort_buffer_size, read_buffer_size, join_buffer_size, read_rnd_buffer_size, thread_stack

Below are the steps to change values of max_connections variables

Step1:- edit the configuration file of MySQL ,Default location of configuration file is /etc/mysql/my.cnf or /etc/my.cnf

[mysqld]max_connections = New Value Step2:- Restart MySQL database services using below commendsService mysqld start/etc/init.d/mysqld start Error 3:- MySQL table is marked as crashed and should be repaired (Mysql Error Code 145)
Error No 5:-  Table 'xxx' doesn't exist Error
Error No 6:- Can't create/write to file Error
[mysqld]
tmpdir=/home/ubuntu/temp
Error 7:- Out of memory Error in application Error log or at Client Tool
Error 8:- 21.4.4 Host 'xxx.xx.xx.x ' is blocked Error
Resolution: - You can easily resolve it by executing below command and Unblock  host
 
mysqladmin flush-hosts
 
Or you can start MySQL services with increased limit of connection error
 
shell> safe_mysqld -O max_connect_errors=10000 &
Error No 9:- Out of Space Error due to MySQL Bin Files size and growth
O/P: - ************************** 1. row *************************** 
 
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.132.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000074
 
 
mysql> PURGE BINARY LOGS TO 'mysql-bin.000074';
or
mysql> PURGE BINARY LOGS BEFORE '2016-09-15 00:00:00';
mysql> SET GLOBAL VARIABLE expire_logs_days=X;

                   Table './xxx/xxx' is marked as crashed and should be repaired

Cause: - Often after running out of disk space or a similar unexpected event, some of the database tables may have become corrupt. This often occurs after you experience out of disk space problems on the disk partition where your database resides. In the above example the EXTRNLNKS table became corrupt and needs to be repaired.

Resolution: - Use below steps to recover the tables

Step 1:- check table tablename;

O/P :-

+-------------------------+-------+----------+----------+

| Table                   | Op    | Msg_type | Msg_text |

+-------------------------+-------+----------+----------+

| database.table_name     | check | status   | repair     |

+-------------------------+-------+----------+----------+

1 row in set (0.01 sec)

If  Msg_text  column value is not OK then we need to repair require table

Step 2:- Process to recover corrupted tables

REPAIR TABLE table_name;

In most cases, this will work without any parameters. Sometimes the index file (.MYI) will be missing or too corrupted to repair. In that case, you can use the table definition file to repair it as follows:-

REPAIR TABLE table_name USE_FRM;

If this doesn’t work…and assuming that the database index file exists in the file-system…you’ll have to repair it with an external utility. out of the MySQL client and stop the MySQL server service with: –

service mysqld stop

Once the server has been shut down gracefully, run: –

myisamchk  /var/lib/mysql/mydatabase/mytable.MYI

This assumes MySQL is installed in the default location on your file-system. If you’ve changed the data_dir variable in your “/etc/my.cnf”, point your command parameter there. Obviously, the mydatabase and mytables names are examples :-)

This utility will check the index file for errors. You can also pass several switches to this command: –

If it finds errors, you can repair the index file with: –

myisamchk --recover /var/lib/mysql/mydatabase/mytable.MYI

This make take a while, depending on the extent of the corruption. Once this is done, you can restart the database server: –

service mysqld start

…and all should be well. However, in the most serious cases, your database table may be hosed. Make sure you always have backups, mysqldump at least .

Error 4:- Aborted connection 305628 to db: 'db' user: 'dbuser' host: 'hostname' (Got an error reading communication packets)

Cause: Above error can be reported into error log if  the client slept for longer time then defined wait_timeout in seconds and then MySQL Server aborted that connection or Client is terminated abnormally or fetching very Hugh data from database.

Resolution: - It‘s not easy to diagnosis abort connection error,in general it is related to network or firewall issue  But some of reason can be

- Check to make sure the value of max_allowed_packet is high enough, and that your clients are not receiving a “packet too large” message. This situation aborts the connection without properly closing it.We can resolve it by changing parameter values of max_allowed_packet in configuration file as well as dynamically .

- Another cause for delay in connections is DNS problems. Check if you have skip-name-resolve enabled, and if hosts are authenticated against their IP address instead their hostname.

Cause: - If you get the error Table 'xxx' doesn't exist or can’t find file: ‘xxx’, this means that no table exists in the current database with the name xxx.

Note that as MySQL uses directories and files to store databases and tables, the database and table names are case sensitive! (On Windows the databases and tables names are not case sensitive, but all references to a given table within a query must use the same case!)

You can check which tables you have in the current database with SHOW TABLES;

If you get an error for some queries of type

This means that MySQL can't create a temporary file for the result set in the given temporary directory.

 (The above error is a typical error message on  Windows and Unix Based System)

Resolution:- To fix it first see that enough disk space is available in disk,if enough free space is available then start mysqld with --tmpdir=path or to add to your option file:

Assuming that the  ‘/home/ubuntu/temp’directory exists.

If you issue a query and get something like the following error:

mysql: needed 8136 byte (8k), memory in use: 12481367 bytes (12189k)

ERROR 2008: MySQL client ran out of memory

Cause: - The reason for this error is simply that the client does not have enough memory to store the whole result.

Resolution: - first check that your query is correct. Is it ok that it should return so many rows? If so, you can use mysql --quick, which uses mysql_use_result() to retrieve the result set. This places less of a load on the client (but more on the server).

If you get an error like this:Host 'hostname' is blocked because of many connection errors.

Cause: - This means that mysqld has gotten a lot (max_connect_errors) of connect requests from the host 'hostname' that have been interrupted in the middle. After max_connect_errors failed requests, mysqld assumes that something is wrong (like an attack from a Hacler), and blocks the site from further connections.

By default, mysqld blocks a host after 10 connection errors.

Also it is suggest if you get this error message for a given host, you should first check that there isn't anything wrong with TCP/IP connections from that host. If your TCP/IP connections aren't working, it won't do you any good to increase the value of the max_connect_errors variable!

Cause: - This is happening because of log bin feature enabled for replication feature and incremental backup of database.

Resolution: - The immediate solution to this problem is to purge old and unneeded binary logs generally, very old logs are safe to remove. But, if replication is enabled, you need to ensure that all replication slaves have finished reading the logs before removal. This can be done by querying the replication slave status.

On each replication slave:

mysql> SHOW SLAVE STATUS \G 

Note the binary log file which each replication slave is currently reading. In this case, it is mysql-bin.000074. Since the binary logs are sequential, it is safe to remove any logs created prior to the oldest one being read by replication slaves.

On Master Node:-

Permanent Fix:-

You can add a line to your /etc/my.cnf file which will only keep these files around as long as necessary.

expire_logs_days = X

Or

You can change this parameter in running mode in MySQL Server

Where X is the number of days you’d like to keep them around. I would recommend 10, but this depends on how busy your MySQL server is and how fast these log files grow. Just make sure it is longer than the slowest slave takes to replicate the data from your master.

MySQL binary logs exist as discrete files on disk (usually in /var/lib/mysql, named mysql-bin.000001, etc.),

they should not be removed using conventional means. Doing so may result in MySQL errors or failure due to attempts to enumerate the binary logs at run time.

The discrete log files should only be removed with rm or a similar utility if they are no longer in the list of binary logs maintained by MySQL but still exist on disk.

Error No 10:- Replication process is Broken

Cause: - Sometimes there are invalid MySQL queries which cause the replication to not work anymore.

Resolution:-

Execute below commend and If you found that one of    of Slave_IO_Running or Slave_SQL_Running is set to No, then the replication is broken

mysql> SHOW SLAVE STATUS \G

To fix it execute below commends in  Replication Node of MySQL Server

mysql> STOP SLAVE;

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

This tells the slave to skip one query (which is the invalid one that caused the replication to stop). If you'd like to skip two queries, you'd use SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2;  instead and so on..

mysql> START SLAVE;