[CentOS] Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND

Tue Jun 13 15:12:26 UTC 2023
Kaushal Shriyan <kaushalshriyan at gmail.com>

Hi,

I am running MySQL DB server 8.0.31
(mysql-community-server-8.0.31-1.el7.x86_64) on the CentOS Linux release
7.9.2009 (Core) operating system and have enabled replication between
Master and Standby.

I am currently encountering the error Error_code: 1032; handler error
HA_ERR_KEY_NOT_FOUND as found in mysqld.log file on Standby server.

cat mysqld.log file
##################################################################################################################################
2023-06-12T07:34:49.698300Z 0 [System] [MY-010116] [Server]
/usr/sbin/mysqld (mysqld 8.0.31) starting as process 9942
2023-06-12T07:34:49.716114Z 1 [System] [MY-013576] [InnoDB] InnoDB
initialization has started.
2023-06-12T07:34:50.203380Z 1 [System] [MY-013577] [InnoDB] InnoDB
initialization has ended.
2023-06-12T07:34:50.560267Z 0 [Warning] [MY-010068] [Server] CA certificate
ca.pem is self signed.
2023-06-12T07:34:50.560375Z 0 [System] [MY-013602] [Server] Channel
mysql_main configured to support TLS. Encrypted connections are now
supported for this channel.
2023-06-12T07:34:50.565553Z 0 [Warning] [MY-011810] [Server] Insecure
configuration for --pid-file: Location '/data' in the path is accessible to
all OS users. Consider choosing a different directory.
2023-06-12T07:34:50.592935Z 0 [Warning] [MY-010604] [Repl] Neither
--relay-log nor --relay-log-index were used; so replication may break when
this MySQL server acts as a slave and has his hostname changed!! Please use
'--relay-log=devportal2-relay-bin' to avoid this problem.
2023-06-12T07:34:50.608947Z 5 [Warning] [MY-010897] [Repl] Storing MySQL
user name or password information in the master info repository is not
secure and is therefore not recommended. Please consider using the USER and
PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax'
in the MySQL Manual for more information.
2023-06-12T07:34:50.611076Z 5 [System] [MY-010562] [Repl] Slave I/O thread
for channel '': connected to master 'replicauser at 10.1.12.4:3306',replication
started in log 'mysql-bin.000007' at position 97065730
2023-06-12T07:34:50.618090Z 0 [System] [MY-010931] [Server]
/usr/sbin/mysqld: ready for connections. Version: '8.0.31' socket:
'/data/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
2023-06-12T07:34:50.618355Z 0 [System] [MY-011323] [Server] X Plugin ready
for connections. Bind-address: '::' port: 33060, socket:
/var/run/mysqld/mysqlx.sock
2023-06-12T07:41:32.562350Z 8 [ERROR] [MY-010584] [Repl] Slave SQL for
channel '': Worker 1 failed executing transaction 'ANONYMOUS' at master log
mysql-bin.000007, end_log_pos 97155197; Could not execute Update_rows event
on table mb.cache_apigee_edge_entity; Can't find record in
'cache_apigee_edge_entity', Error_code: 1032; handler error
HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000007, end_log_pos
97155197, Error_code: MY-001032
2023-06-12T07:41:32.563449Z 6 [Warning] [MY-010584] [Repl] Slave SQL for
channel '': ... The slave coordinator and worker threads are stopped,
possibly leaving data in inconsistent state. A restart should restore
consistency automatically, although using non-transactional storage for
data or info tables or DDL queries could lead to problems. In such cases
you have to examine your data (see documentation for details). Error_code:
MY-001756
##################################################################################################################################

I am sharing the /etc/my.cnf file for both Master and Standby MySQL DB
server.

Master -> /etc/my.cnf file
--------------------------------------------------------------------------------------------------------------

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for
default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
#
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
bind-address=192.168.1.10
server-id=1
log_bin = /data/mysql/logs/mysql-bin.log
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/data/mysql/logs/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
--------------------------------------------------------------------------------------------------------------

Standby -> /etc/my.cnf file

--------------------------------------------------------------------------------------------------------------
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for
default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
#
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

server-id=2
read_only = 1
max_binlog_size = 500M
log_bin = /data/mysql/logs/mysql-bin.log
datadir=/data/mysql
socket=/data/mysql/mysql.sock

log-error=/data/mysql/logs/mysqld.log
pid-file=/data/mysql/mysqld.pid
--------------------------------------------------------------------------------------------------------------

Please guide me. Thanks in Advance.

Best Regards,

Kaushal