MySQL High Availability (HA) error - SafeSync for Enterprise

Resolving MySQL High Availability (HA) error in SafeSync for Enterprise

- Updated:
- 4 Nov 2016
- Product/Version:
- SafeSync for Enterprise 3.1
- SafeSync for Enterprise 3.2
- Platform:
- iOS 9.2

Identifying the specific MySQL error

To determine which MySQL HA error you encountered:

Run the following commands in both appliance1 and appliance2:
root@appliance1:~# sudo mysql -uroot -p -e "show slave status\G"
Check the values of these two (2) fields in both appliance1 and appliance2 to find which error occurs.
- Last_IO_Errno:
- Last_IO_Error:
Perform the appropriate solution below based on the error code you got.

Solutions based on error code

EXPAND ALL

Last_IO_Errno: 1236

The detailed error message below appears on the server:

Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysql-bin.003583' at 45997491, the last event read from './mysql-bin.003583' at 4, the last byte read from './mysql-bin.003583' at 4.'

To resolve this error, run the following commands:

root@appliance1:~# sudo mysql -uroot -p
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO master_log_file = 'mysql-bin.003584', master_log_pos = 0;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS \G

Last_IO_Errno: 1060

The detailed error message below appears on the server:

Last_IO_Errno: 1060
Last_IO_Error: Error 'Duplicate column name 'encrypt_all'' on query. Default database: ''. Query: 'ALTER TABLE osdp.policies ADD COLUMN `encrypt_all` tinyint(1) unsigned DEFAULT '0' AFTER allow_download_infected_file'

To resolve this error, run the following commands:

root@appliance1:~# sudo mysql -uroot -p
mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS \G

Alternative solution: Clone MySQL database to another server

You can also clone all MySQL databases from appliance1 to appliance2. Below is a sample script to run on appliance2: fix-mysql-ha-appliance2.sh:

Modify the host name from appliance2 to appliance1 if you want to clone all MySQL database from appliance2 to appliance1.

#!/bin/bash
 
set -x
 
exec 3>&1
exec 1> >(tee --append fix-ha2-$(date | sed --expression='s/ /_/g').log >&3)
exec 2>&1
 
if [ $(hostname) != 'appliance2' ]; then
    echo "This script must be run on appliance2"
    exit 1
fi
 
ha1_ip='appliance1'
SSH="ssh -oBatchMode=yes -oStrictHostKeyChecking=no"
SCP=${SSH/ssh/scp}
 
if ! $SSH root@$ha1_ip 'echo hello'; then
    echo "appliance2 cannot login appliance1 via ssh"
    exit 1
fi
 
# stop all service except mysql
/opt/SingleInstaller/nodeControl/bin/restart_all_services  --mgmtui --no-start
$SSH root@$ha1_ip '/opt/SingleInstaller/nodeControl/bin/restart_all_services  --mgmtui --no-start'
 
$SSH root@$ha1_ip 'MYSQL_PWD=safesync mysql -uroot -e "STOP SLAVE; RESET SLAVE ALL; RESET MASTER;"'
MYSQL_PWD=safesync mysql -uroot -e "STOP SLAVE; RESET SLAVE ALL; RESET MASTER;"
 
/opt/SingleInstaller/nodeControl/bin/clone_mysql_from_appliance1 --yes
 
$SSH root@$ha1_ip 'MYSQL_PWD=safesync mysql -uroot -e "SHOW SLAVE STATUS \G"'
MYSQL_PWD=safesync mysql -uroot -e "SHOW SLAVE STATUS \G"
 
# start all services
$SSH root@$ha1_ip '/opt/SingleInstaller/nodeControl/bin/restart_all_services --all --no-stop'
/opt/SingleInstaller/nodeControl/bin/restart_all_services --all --no-stop
 
echo "Fixing is completed"