Description:
I am migrating my manual HA Controller service from mysql version 8.0.39 to 9.2.0.
I am using Manual HA, Promoting to source its by .net code.
To promote replica to master,
First i am fetching the gtids of source and then of replica and then using the GTID_SUBTRACT() to get the missing gtids on replica. for reference
bool bRet1 = GetAllTransactionsGTIDS(sProposedMasterIP, ref sProposedMasterTx, false);
bool bRet2 = GetAllTransactionsGTIDS(sOldMasterIP, ref sOldMasterTx, true);
string sMissingTx = "";
using (DbCommand gtidCmd = proposedMasterConn.CreateCommand())
{
gtidCmd.CommandText = $"SELECT GTID_SUBTRACT('{sOldMasterTx}', '{sProposedMasterTx}')";
object result = gtidCmd.ExecuteScalar();
if (result != null && result != DBNull.Value)
{
sMissingTx = result.ToString();
}
}
if (!string.IsNullOrEmpty(sMissingTx))
{
_logger.Info("GTID SUBTRACTED GTID HERE" + sMissingTx);
WaitUntilTransactionDone(cmd, sMissingTx);
}
Then i am trying to execute those gtids on replica but the command which i am using now for that got hanged while performing operation.
cmd.CommandText = $"SELECT WAIT_FOR_EXECUTED_GTID_SET('{GTIDTransaction}');";
cmd.CommandTimeout = 5;
data = cmd.ExecuteReader();
Its not coming back with the response, and manual promote to master got failed.
On Replica machine i am using Ini info like this:
######## START ##########
#require_secure_transport=ON
ssl-ca="C:/ProgramData/MySQL/MySQL Server 9.2/Data/ca.pem"
ssl-cert="C:/ProgramData/MySQL/MySQL Server 9.2/Data/server-cert.pem"
ssl-key="C:/ProgramData/MySQL/MySQL Server 9.2/Data/server-key.pem"
#group_replication_ssl_mode=required
binlog_format = ROW
log_replica_updates=ON
enforce_gtid_consistency = ON
gtid_mode = ON
sync_source_info=1
##datadir=/home/billy/mysql/data1
server-id=307
log-bin=utilBINLog-bin.log
relay-log=RELAYLog.log
report-host=172.23.160.**
binlog-do-db= BlackR
replicate-do-db= BlackR
########## End ##########
On Master:
######## START ##########
; require_secure_transport=ON
ssl-ca="C:/ProgramData/MySQL/MySQL Server 9.2/Data/ca.pem"
ssl-cert="C:/ProgramData/MySQL/MySQL Server 9.2/Data/server-cert.pem"
ssl-key="C:/ProgramData/MySQL/MySQL Server 9.2/Data/server-key.pem"
binlog_format = ROW
log_replica_updates=ON
enforce_gtid_consistency = ON
gtid_mode = ON
sync_source_info=1
server-id=207
log-bin=utilBINLog-bin.log
relay-log=RELAYLog.log
report-host=172.23.160.**
binlog-do-db= BlackR
replicate-do-db= BlackR
########## End ##########
connection string will be like this:
Server=172.23.160.**;
User ID=ReplicationUser;
Password=********;
Port=3306;
Database=mysql;
Connection Timeout=15;
DefaultCommandTimeout=10;
SslMode=Preferred;
AllowPublicKeyRetrieval=True;
I am not sure why it is happening but with the old command SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('" + GTIDTransaction + "'); with mysql lower versions 8.0.39 it was working fine
Also earlier we were using mysql_native_password but now caching_sha_password as the mysql_native_password was removed and deperecated.
How to repeat:
am migrating my manual HA Controller service from mysql version 8.0.39 to 9.2.0.
I am using Manual HA, Promoting to source its by .net code.
To promote replica to master,
First i am fetching the gtids of source and then of replica and then using the GTID_SUBTRACT() to get the missing gtids on replica. for reference
bool bRet1 = GetAllTransactionsGTIDS(sProposedMasterIP, ref sProposedMasterTx, false);
bool bRet2 = GetAllTransactionsGTIDS(sOldMasterIP, ref sOldMasterTx, true);
string sMissingTx = "";
using (DbCommand gtidCmd = proposedMasterConn.CreateCommand())
{
gtidCmd.CommandText = $"SELECT GTID_SUBTRACT('{sOldMasterTx}', '{sProposedMasterTx}')";
object result = gtidCmd.ExecuteScalar();
if (result != null && result != DBNull.Value)
{
sMissingTx = result.ToString();
}
}
if (!string.IsNullOrEmpty(sMissingTx))
{
_logger.Info("GTID SUBTRACTED GTID HERE" + sMissingTx);
WaitUntilTransactionDone(cmd, sMissingTx);
}
Then i am trying to execute those gtids on replica but the command which i am using now for that got hanged while performing operation.
cmd.CommandText = $"SELECT WAIT_FOR_EXECUTED_GTID_SET('{GTIDTransaction}');";
cmd.CommandTimeout = 5;
data = cmd.ExecuteReader();
Its not coming back with the response, and manual promote to master got failed.
On Replica machine i am using Ini info like this:
######## START ##########
#require_secure_transport=ON
ssl-ca="C:/ProgramData/MySQL/MySQL Server 9.2/Data/ca.pem"
ssl-cert="C:/ProgramData/MySQL/MySQL Server 9.2/Data/server-cert.pem"
ssl-key="C:/ProgramData/MySQL/MySQL Server 9.2/Data/server-key.pem"
#group_replication_ssl_mode=required
binlog_format = ROW
log_replica_updates=ON
enforce_gtid_consistency = ON
gtid_mode = ON
sync_source_info=1
##datadir=/home/billy/mysql/data1
server-id=307
log-bin=utilBINLog-bin.log
relay-log=RELAYLog.log
report-host=172.23.160.**
binlog-do-db= BlackR
replicate-do-db= BlackR
########## End ##########
On Master:
######## START ##########
; require_secure_transport=ON
ssl-ca="C:/ProgramData/MySQL/MySQL Server 9.2/Data/ca.pem"
ssl-cert="C:/ProgramData/MySQL/MySQL Server 9.2/Data/server-cert.pem"
ssl-key="C:/ProgramData/MySQL/MySQL Server 9.2/Data/server-key.pem"
binlog_format = ROW
log_replica_updates=ON
enforce_gtid_consistency = ON
gtid_mode = ON
sync_source_info=1
server-id=207
log-bin=utilBINLog-bin.log
relay-log=RELAYLog.log
report-host=172.23.160.**
binlog-do-db= BlackR
replicate-do-db= BlackR
########## End ##########
connection string will be like this:
Server=172.23.160.**;
User ID=ReplicationUser;
Password=********;
Port=3306;
Database=mysql;
Connection Timeout=15;
DefaultCommandTimeout=10;
SslMode=Preferred;
AllowPublicKeyRetrieval=True;
I am not sure why it is happening but with the old command SELECT WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS('" + GTIDTransaction + "'); with mysql lower versions 8.0.39 it was working fine
Also earlier we were using mysql_native_password but now caching_sha_password as the mysql_native_password was removed and deperecated.
Suggested fix:
I should work in similar way.
Please provide the guide line or fix.