Common Mysql Problems & Solutions

Here are few common Mysql Replication Errors & their solutions

Problem - 1   

Last_Errno: 1594
Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

Solution

Run Show slave status & Check these 2 positions
Relay_Master_Log_File: db1-bin.002183
Exec_Master_Log_Pos: 9866809

Stop Slave & Reset the same & provide the position you have noted earlier
mysql> stop slave;
mysql> reset slave;
mysql> change master to master_log_file='db1-bin.002183', master_log_pos=9866809;
mysql> start slave;

It's done now. Let the slave recover it's data with Master

#####################################

PROBLEM - 2

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository




This error comes when you are creating new Mysql slave & just started the replication with Master

Solition:

Reset slave;
Stop Mysql
Delete mysqld-relay-bin.index in Mysql folder
Start Mysql
Run Change Master to Master command
Start Slave;


#################################

PROBLEM - 3

Last_SQL_Errno: 1305
Last_SQL_Error: Error 'FUNCTION DB.SPLIT_STR does not exist' on query. Default database: 'DB'. Query: 'insert into tablename(folderid, subuid, company, title, summary, descr, keyskills, job_type, minsal, maxsal, minsal_channel,

Solution -
It is related with functions as the above function is not available at slave server
 
Take backup of functions of DB from destination server
mysqldump -u root -p --routines --no-create-info --no-data --no-create-db --skip-opt  DBName > /home/bhagwat/functions.sql

Restore with below command on Slave
mysql --defaults-file=/root/.my.cnf.root DBNAME < /home/bhsingh/functions.sql


##############################

Problem - 4

ERROR 1235 (42000) at line 21: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

Solution -
Add --skip-triggers in command & take backup of functions

mysqldump --defaults-file=/root/.my.cnf.root --routines --no-create-info --no-data --no-create-db --skip-opt --skip-triggers bazooka > /home/bhsingh/bazooka1.sql

##############################

Problem - 5

Last_SQL_Error: Error 'Out of resources when opening file


Solution:

Increase Open files limit on server to fix this  issue

Check current limit with below command
[root@server ~]# mysql -u root -p -e 'show variables;' | grep open
have_openssl    DISABLED
innodb_open_files    300
open_files_limit    8000
table_open_cache    2048


Increase the same with below command

[root@server ~]# cat /etc/security/limits.conf |grep mysql
mysql         soft      nofile     12000
mysql         hard      nofile     16000

For Immediate effect change in below file
 vim /etc/security/limits.d/90-nproc.conf
*          soft    nproc     1024
mysql      soft    nofile    12000
mysql      hard    nproc     16000
root       soft    nproc     unlimited

##########################