Bug #118841 MYSQL 9.2.0, Facing issue while doing Manual HA promote to source
Submitted: 14 Aug 6:20 Modified: 25 Aug 9:10
Reporter: Ruchi Singh Email Updates:
Status: Need Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:9.2.0 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any

[14 Aug 6:20] Ruchi Singh
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.
[14 Aug 9:19] Ruchi Singh
This also i am using to get the gtids of both source and  replica 
cmd.CommandText = "SHOW REPLICA STATUS";
                data = cmd.ExecuteReader();

                string Retrieved_Gtid_Set = "";
                string sSlaveRunning = "";
                string sHost = "";
                if (data.Read())
                {
                    Retrieved_Gtid_Set = data["Retrieved_Gtid_Set"].ToString();
                    sSlaveRunning = data["Replica_SQL_Running"].ToString();
                    sHost = data["Source_Host"].ToString();
                }

                data.Dispose();

                string GTID_EXECUTED = "";
                cmd.CommandText = "SELECT @@GLOBAL.GTID_EXECUTED;";
                data = cmd.ExecuteReader();
                if (data.Read())
                {
                    GTID_EXECUTED = data.GetString(0);
                }
[18 Aug 10:26] MySQL Verification Team
Hello,

Looking at the report I can see that potential issue can be 
 - The transition from WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS (used in MySQL 8.0.39) to WAIT_FOR_EXECUTED_GTID_SET (MySQL 9.2.0) introduces different behaviors. 
   The semantics, expected usage, and side effects of these functions are not identical.

 - If the GTID set passed to WAIT_FOR_EXECUTED_GTID_SET cannot be applied or does not match the internal state, the function may not return, waiting for transactions that will never be executed.

  
Can you check that the GTID set passed to WAIT_FOR_EXECUTED_GTID_SET is correct, i.e., the set of GTIDs that should be executed on the replica.
        
     
I do not think this is a bug.

> Please provide the guide line or fix.

I think you should contact our MySQL Support team to get a guide how to solve this as I do not believe this is a bug.

I tried reproducing this but the upgrade went without a problem.

Thank you for using MySQL.
[23 Aug 18:52] Ruchi Singh
Hi, How do i validate this that the executed gtids are correct or not , As i am getting it by excuting the command only and it works in similar way earlier. With version 8.0.39. Any way to connect with mysql support team?
[25 Aug 9:09] MySQL Verification Team
MySQL Support: https://dev.mysql.com/support/
You can also get help for free through mysql forums: https://forums.mysql.com/