Bug #111277 MySQL execute add_pke when running on COMMIT_ORDER mode

• Submitted: 5 Jun 2023 10:42
• Modified: 6 Jun 2023 12:31
• Reporter: xiaoyu bai
• Status: Unsupported
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.7.29 8.0.23
• OS: Any
• CPU Architecture: Any

[5 Jun 2023 10:42] xiaoyu bai

Description:
when set binlog_transaction_dependency_tracking = COMMIT_ORDER, mysql still execute add_pke

How to repeat:
read source code

Suggested fix:
add mysql_bin_log.m_dependency_tracker.m_opt_tracking_mode != DEPENDENCY_TRACKING_COMMIT_ORDER 
to below if condition in binlog_log_row method.

source code:
if (thd->variables.transaction_write_set_extraction != HASH_ALGORITHM_OFF) {
      try {
        if (before_record && after_record) {
          /* capture both images pke */
          if (add_pke(table, thd, table->record[0]) ||
              add_pke(table, thd, table->record[1])) {
            return HA_ERR_RBR_LOGGING_FAILED;
          }
        } else {
          if (add_pke(table, thd, table->record[0])) {
            return HA_ERR_RBR_LOGGING_FAILED;
          }
        }
      } catch (const std::bad_alloc &) {
        my_error(ER_OUT_OF_RESOURCES, MYF(0));
        return HA_ERR_RBR_LOGGING_FAILED;
      }
    }

fixed code :
 if ( mysql_bin_log.m_dependency_tracker.m_opt_tracking_mode !=
          DEPENDENCY_TRACKING_COMMIT_ORDER && thd->variables.transaction_write_set_extraction != HASH_ALGORITHM_OFF) {
      try {
        if (before_record && after_record) {
          /* capture both images pke */
          if (add_pke(table, thd, table->record[0]) ||
              add_pke(table, thd, table->record[1])) {
            return HA_ERR_RBR_LOGGING_FAILED;
          }
        } else {
          if (add_pke(table, thd, table->record[0])) {
            return HA_ERR_RBR_LOGGING_FAILED;
          }
        }
      } catch (const std::bad_alloc &) {
        my_error(ER_OUT_OF_RESOURCES, MYF(0));
        return HA_ERR_RBR_LOGGING_FAILED;
      }
    }

[6 Jun 2023 3:30] xiaoyu bai

The default value of transaction_write_set_extraction is XXHASH64 in 8.0, and it's  default value is OFF in 5.7 ,so when using commit_order in 8.0 , add_pke will be executed and performance will drop unless change transaction_write_set_extraction to OFF.

[6 Jun 2023 12:31] MySQL Verification Team

Hi Mr. bai,

Thank you, very much, for your bug report.

We would like you to provide us with the code analysis based on our latest release of 8.0 only. That is on 8.0.33. Lot's of time has passed since the release of 8.0.23 and many changes have been made in that part of the code.

Hence, we are VERY interested in your analysis, but please base it on 8.0.33. Do provide us with that info and we shall change the status of this report. 

Next, 5.7 now receives only crashing and security bugs.

Do note that our server supports the following options now:

  --binlog-transaction-dependency-history-size=#
  --binlog-transaction-dependency-tracking=name
                      Selects the source of dependency information from which
                      binlog_transaction_dependency_tracking.
binlog-transaction-dependency-history-size                   25000
binlog-transaction-dependency-tracking                       COMMIT_ORDER

So, we guess that your are writing only about the last option. Is that true ?????

Unsupported.