チョットワカル Row-Based Replication・その２

2019/10/30

sejima

こんにちわ。せじまです。今回も replication の話をします。

はじめに

第二回です。

今回の主なお題は、 THD::decide_logging_format() という関数になります。 THD::decide_logging_format() の仕様がわかると、binlog_format が原因で replication 止まる理由が、（そこそこ）わかるようになるでしょう。

また、Row-Based Replication に移行したとき、 THD::decide_logging_format() 以外で replication が停止してしまうケースなどについても、軽くメモ程度に書いておきます。

THD::decide_logging_format() について

（MySQL Internals は微妙に内容が古かったりするんですが）、参考までに、まずは 19.4.1 Determining the Logging Format を読んでみましょう。

At parse time, it is detected if the statement is unsafe to log in statement format (that is, requires row format). If this is the case, the THD::Lex::set_stmt_unsafe() function is called. This must be done prior to the call to THD::decide_logging_format() (that is, prior to lock_tables). As a special case, some types of unsafeness are detected inside THD::decide_logging_format(), before the logging format is decided. Note that statements shall be marked unsafe even if binlog_format!=mixed.

次は、具体的に、 SQLCOM_UPDATE 受けたところから、 THD::decide_logging_format() を見ていきましょう。今回も MySQL 8.0.12 のソースコードをベースに読んでいきます。

  /*
    Locking of tables is done after preparation but before optimization.
    This allows to do better partition pruning and avoid locking unused
    partitions. As a consequence, in such a case, prepare stage can rely only
    on metadata about tables used and not data from them.
  */
  if (!is_empty_query()) {
    if (lock_tables(thd, lex->query_tables, lex->table_count, 0)) goto err;
  }

  // Perform statement-specific execution
  if (execute_inner(thd)) goto err;

543

544

545

546

547

548

549

550

551

552

553

554

Locking of tables is done after preparation but before optimization.

This allows to do better partition pruning and avoid locking unused

partitions. As a consequence, in such a case, prepare stage can rely only

on metadata about tables used and not data from them.

if (!is_empty_query()) {

if (lock_tables(thd, lex->query_tables, lex->table_count, 0)) goto err;

}

// Perform statement-specific execution

if (execute_inner(thd)) goto err;

こんなかんじで statement を実行する前にいったん lock_tables() を実行しておりまして

lock_tables() の中で THD::decide_logging_format() が呼ばれています。

https://github.com/mysql/mysql-server/blob/mysql-8.0.12/sql/sql_base.cc#L6436

THD::decide_logging_format() という関数についてですが、コメントがけっこう充実しています。

ざっくりまとめはコメントのとおりで

  Decision table for logging format
  ---------------------------------

  The following table summarizes how the format and generated
  warning/error depends on the tables' capabilities, the statement
  type, and the current binlog_format.

     Row capable        N NNNNNNNNN YYYYYYYYY YYYYYYYYY
     Statement capable  N YYYYYYYYY NNNNNNNNN YYYYYYYYY

     Statement type     * SSSUUUIII SSSUUUIII SSSUUUIII

     binlog_format      * SMRSMRSMR SMRSMRSMR SMRSMRSMR

     Logged format      - SS-S----- -RR-RR-RR SRRSRR-RR
     Warning/Error      1 --2732444 5--5--6-- ---7--6--

  Legend
  ------

  Row capable:    N - Some table not row-capable, Y - All tables row-capable
  Stmt capable:   N - Some table not stmt-capable, Y - All tables stmt-capable
  Statement type: (S)afe, (U)nsafe, or Row (I)njection
  binlog_format:  (S)TATEMENT, (M)IXED, or (R)OW
  Logged format:  (S)tatement or (R)ow
  Warning/Error:  Warnings and error messages are as follows:

  1. Error: Cannot execute statement: binlogging impossible since both
     row-incapable engines and statement-incapable engines are
     involved.

  2. Error: Cannot execute statement: binlogging impossible since
     BINLOG_FORMAT = ROW and at least one table uses a storage engine
     limited to statement-logging.

  3. Error: Cannot execute statement: binlogging of unsafe statement
     is impossible when storage engine is limited to statement-logging
     and BINLOG_FORMAT = MIXED.

  4. Error: Cannot execute row injection: binlogging impossible since
     at least one table uses a storage engine limited to
     statement-logging.

  5. Error: Cannot execute statement: binlogging impossible since
     BINLOG_FORMAT = STATEMENT and at least one table uses a storage
     engine limited to row-logging.

  6. Error: Cannot execute row injection: binlogging impossible since
     BINLOG_FORMAT = STATEMENT.

  7. Warning: Unsafe statement binlogged in statement format since
     BINLOG_FORMAT = STATEMENT.

  In addition, we can produce the following error (not depending on
  the variables of the decision diagram):

  8. Error: Cannot execute statement: binlogging impossible since more
     than one engine is involved and at least one engine is
     self-logging.

  9. Error: Do not allow users to modify a gtid_executed table
     explicitly by a XA transaction.

  For each error case above, the statement is prevented from being
  logged, we report an error, and roll back the statement.  For
  warnings, we set the thd->binlog_flags variable: the warning will be
  printed only if the statement is successfully logged.

9678

9679

9680

9681

9682

9683

9684

9685

9686

9687

9688

9689

9690

9691

9692

9693

9694

9695

9696

9697

9698

9699

9700

9701

9702

9703

9704

9705

9706

9707

9708

9709

9710

9711

9712

9713

9714

9715

9716

9717

9718

9719

9720

9721

9722

9723

9724

9725

9726

9727

9728

9729

9730

9731

9732

9733

9734

9735

9736

9737

9738

9739

9740

9741

9742

9743

9744

Decision table for logging format

---------------------------------

The following table summarizes how the format and generated

warning/error depends on the tables' capabilities, the statement

type, and the current binlog_format.

Row capable N NNNNNNNNN YYYYYYYYY YYYYYYYYY

Statement capable N YYYYYYYYY NNNNNNNNN YYYYYYYYY

Statement type * SSSUUUIII SSSUUUIII SSSUUUIII

binlog_format * SMRSMRSMR SMRSMRSMR SMRSMRSMR

Logged format - SS-S----- -RR-RR-RR SRRSRR-RR

Warning/Error 1 --2732444 5--5--6-- ---7--6--

Legend

------

Row capable: N - Some table not row-capable, Y - All tables row-capable

Stmt capable: N - Some table not stmt-capable, Y - All tables stmt-capable

Statement type: (S)afe, (U)nsafe, or Row (I)njection

binlog_format: (S)TATEMENT, (M)IXED, or (R)OW

Logged format: (S)tatement or (R)ow

Warning/Error: Warnings and error messages are as follows:

1. Error: Cannot execute statement: binlogging impossible since both

row-incapable engines and statement-incapable engines are

involved.

2. Error: Cannot execute statement: binlogging impossible since

BINLOG_FORMAT = ROW and at least one table uses a storage engine

limited to statement-logging.

3. Error: Cannot execute statement: binlogging of unsafe statement

is impossible when storage engine is limited to statement-logging

and BINLOG_FORMAT = MIXED.

4. Error: Cannot execute row injection: binlogging impossible since

at least one table uses a storage engine limited to

statement-logging.

5. Error: Cannot execute statement: binlogging impossible since

BINLOG_FORMAT = STATEMENT and at least one table uses a storage

engine limited to row-logging.

6. Error: Cannot execute row injection: binlogging impossible since

BINLOG_FORMAT = STATEMENT.

7. Warning: Unsafe statement binlogged in statement format since

BINLOG_FORMAT = STATEMENT.

In addition, we can produce the following error (not depending on

the variables of the decision diagram):

8. Error: Cannot execute statement: binlogging impossible since more

than one engine is involved and at least one engine is

self-logging.

9. Error: Do not allow users to modify a gtid_executed table

explicitly by a XA transaction.

For each error case above, the statement is prevented from being

logged, we report an error, and roll back the statement. For

warnings, we set the thd->binlog_flags variable: the warning will be

printed only if the statement is successfully logged.

Row Injection というのは、 binlog_format=ROW にしたとき binlog に出力される、UPDATE_ROWS_EVENT などのことでしょうね。それらを SQL_Thread などで実行する場合が Statement Type = I になります。

では、順を追って見ていきましょう。

Row capable でも Stmt capable でもない storage engine の例の一つは、 performance schema です。

https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/perfschema/ha_perfschema.h#L73-L97

    return HA_NO_TRANSACTIONS | HA_NO_AUTO_INCREMENT |
           HA_PRIMARY_KEY_REQUIRED_FOR_DELETE | HA_NULL_IN_KEY |
           HA_NULL_PART_KEY;

return HA_NO_TRANSACTIONS | HA_NO_AUTO_INCREMENT |

HA_PRIMARY_KEY_REQUIRED_FOR_DELETE | HA_NULL_IN_KEY |

HA_NULL_PART_KEY;

「間違っても replication できると思うなよ？」ということでしょう。

Stmt capable であり Row capable でない storage engine ですが、ソースコード上ではテストのために存在しているようです。なので、これはいったん忘れても良いでしょう。

https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/example/ha_example.h#L100

  /** @brief
    This is a list of flags that indicate what functionality the storage engine
    implements. The current table flags are documented in handler.h
  */
  ulonglong table_flags() const {
    /*
      We are saying that this engine is just statement capable to have
      an engine that can only handle statement-based logging. This is
      used in testing.
    */
    return HA_BINLOG_STMT_CAPABLE;
  }

100

101

/** @brief

This is a list of flags that indicate what functionality the storage engine

implements. The current table flags are documented in handler.h

ulonglong table_flags() const {

We are saying that this engine is just statement capable to have

an engine that can only handle statement-based logging. This is

used in testing.

return HA_BINLOG_STMT_CAPABLE;

}

Row capable であり Stmt capable でない storage engine ですが、身近なところでいうと、 READ COMMITTED or READ UNCOMMITTED な InnoDB が該当します。InnoDB が Stmt capable であるのは、 Isolation level が REPEATABLE READ か SERIALIZABLE のときに限られるわけです。

https://github.com/mysql/mysql-server/blob/mysql-8.0.12/storage/innobase/handler/ha_innodb.cc#L5215-L5244

handler::Table_flags ha_innobase::table_flags() const {
  THD *thd = ha_thd();
  handler::Table_flags flags = m_int_table_flags;

  /* If querying the table flags when no table_share is given,
  then we must check if the table to be created/checked is partitioned.
  */
  if (table_share == NULL && thd_get_work_part_info(thd) != NULL) {
    /* Currently ha_innopart does not support
    all InnoDB features such as GEOMETRY, FULLTEXT etc. */
    flags &= ~(HA_INNOPART_DISABLED_TABLE_FLAGS);
  }

  /* Temporary table provides accurate record count */
  if (table_share != NULL &&
      table_share->table_category == TABLE_CATEGORY_TEMPORARY) {
    flags |= HA_STATS_RECORDS_IS_EXACT;
  }

  /* Need to use tx_isolation here since table flags is (also)
  called before prebuilt is inited. */

  ulong const tx_isolation = thd_tx_isolation(thd);

  if (tx_isolation <= ISO_READ_COMMITTED) {
    return (flags);
  }

  return (flags | HA_BINLOG_STMT_CAPABLE);
}

5215

5216

5217

5218

5219

5220

5221

5222

5223

5224

5225

5226

5227

5228

5229

5230

5231

5232

5233

5234

5235

5236

5237

5238

5239

5240

5241

5242

5243

5244

handler::Table_flags ha_innobase::table_flags() const {

THD *thd = ha_thd();

handler::Table_flags flags = m_int_table_flags;

/* If querying the table flags when no table_share is given,

then we must check if the table to be created/checked is partitioned.

if (table_share == NULL && thd_get_work_part_info(thd) != NULL) {

/* Currently ha_innopart does not support

all InnoDB features such as GEOMETRY, FULLTEXT etc. */

flags &= ~(HA_INNOPART_DISABLED_TABLE_FLAGS);

}

/* Temporary table provides accurate record count */

if (table_share != NULL &&

table_share->table_category == TABLE_CATEGORY_TEMPORARY) {

flags |= HA_STATS_RECORDS_IS_EXACT;

}

/* Need to use tx_isolation here since table flags is (also)

called before prebuilt is inited. */

ulong const tx_isolation = thd_tx_isolation(thd);

if (tx_isolation <= ISO_READ_COMMITTED) {

return (flags);

}

return (flags | HA_BINLOG_STMT_CAPABLE);

}

といったことを踏まえると、想定すべき現実的な Decision table for logging format は、次のようなものでしょう。

InnoDB で READ COMMITTED or READ UNCOMMITTED なとき


Row capable	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y
Statement capable	N	N	N	N	N	N	N	N	N	Y	Y	Y	Y	Y	Y	Y	Y	Y
Statement type	S	S	S	U	U	U	I	I	I	S	S	S	U	U	U	I	I	I
binlog_format	S	M	R	S	M	R	S	M	R	S	M	R	S	M	R	S	M	R
Logged format	-	R	R	-	R	R	-	R	R	S	R	R	S	R	R	-	R	R
Warning/Error	5	-	-	5	-	-	6	-	-	-	-	-	7	-	-	6	-	-

InnoDB で READ COMMITTED や READ UNCOMMITTED を使用しないとき、あるいは MyISAM や ARCHIVE ENGINE など使うとき


Row capable	Y	Y	Y	Y	Y	Y	Y	Y	Y
Statement capable	Y	Y	Y	Y	Y	Y	Y	Y	Y
Statement type	S	S	S	U	U	U	I	I	I
binlog_format	S	M	R	S	M	R	S	M	R
Logged format	S	R	R	S	R	R	-	R	R
Warning/Error	-	-	-	7	-	-	6	-	-

まぁ、THD::decide_logging_format() の中では THD::is_dml_gtid_compatible() も呼んでいて、 GTID との互換性を確認していたりもするので、将来 GTID 対応にしたいなら、InnoDB 以外は考えないほうがいいでしょうね。

ちなみに decide_logging_format() の中で HA_HAS_OWN_BINLOGGING というフラグが出てきて own_binlogging ってなんだよって話になりますが、 ndbcluster、いわゆる MySQL NDB Cluster のことですね。

Decision table に出てこない次の error についてですが

  In addition, we can produce the following error (not depending on
  the variables of the decision diagram):

  8. Error: Cannot execute statement: binlogging impossible since more
     than one engine is involved and at least one engine is
     self-logging.

  9. Error: Do not allow users to modify a gtid_executed table
     explicitly by a XA transaction.

9731

9732

9733

9734

9735

9736

9737

9738

9739

In addition, we can produce the following error (not depending on

the variables of the decision diagram):

8. Error: Cannot execute statement: binlogging impossible since more

than one engine is involved and at least one engine is

self-logging.

9. Error: Do not allow users to modify a gtid_executed table

explicitly by a XA transaction.

8 は own_binlogging な ndbcluster の話ですね

18.6.3 MySQL Cluster レプリケーションの既知の問題

バイナリロギングを実行していないスレーブのストレージエンジンへの NDB の複製
独自のバイナリロギングを処理しないストレージエンジンを使用するスレーブに MySQL Cluster から複製を試みると、レプリケーションプロセスは次のエラーにより停止します: Binary logging not possible ... Statement cannot be written atomically since more than one engine involved and at least one engine is self-logging (エラー 1595)。次の方法のいずれかで、この問題を回避できます。

9 はここでしょうね

https://github.com/mysql/mysql-server/blob/mysql-8.0.12/sql/binlog.cc#L9911-L9920

      if (table->table->no_replicate) {
        if (!warned_gtid_executed_table) {
          warned_gtid_executed_table =
              gtid_state->warn_or_err_on_modify_gtid_table(this, table);
          /*
            Do not allow users to modify the gtid_executed table
            explicitly by a XA transaction.
          */
          if (warned_gtid_executed_table == 2) DBUG_RETURN(-1);
        }

9911

9912

9913

9914

9915

9916

9917

9918

9919

9920

if (table->table->no_replicate) {

if (!warned_gtid_executed_table) {

warned_gtid_executed_table =

gtid_state->warn_or_err_on_modify_gtid_table(this, table);

Do not allow users to modify the gtid_executed table

explicitly by a XA transaction.

if (warned_gtid_executed_table == 2) DBUG_RETURN(-1);

}

https://github.com/mysql/mysql-server/blob/mysql-8.0.12/sql/rpl_gtid_persist.h#L192-L231

  /**
    Push a warning to client if user is modifying the gtid_executed
    table explicitly by a non-XA transaction. Push an error to client
    if user is modifying it explicitly by a XA transaction.

    @param thd Thread requesting to access the table
    @param table The table is being accessed.

    @retval 0 No warning or error was pushed to the client.
    @retval 1 Push a warning to client.
    @retval 2 Push an error to client.
  */
  int warn_or_err_on_explicit_modification(THD *thd, TABLE_LIST *table) {
    DBUG_ENTER("Gtid_table_persistor::warn_or_err_on_explicit_modification");


    if (!thd->is_operating_gtid_table_implicitly &&
        table->lock_descriptor().type >= TL_WRITE_ALLOW_WRITE &&
        !strcmp(table->table_name, Gtid_table_access_context::TABLE_NAME.str)) {
      if (thd->get_transaction()->xid_state()->has_state(
              XID_STATE::XA_ACTIVE)) {
        /*
          Push an error to client if user is modifying the gtid_executed
          table explicitly by a XA transaction.
        */
        thd->raise_error_printf(ER_ERROR_ON_MODIFYING_GTID_EXECUTED_TABLE,
                                table->table_name);
        DBUG_RETURN(2);
      } else {
        /*
          Push a warning to client if user is modifying the gtid_executed
          table explicitly by a non-XA transaction.
        */
        thd->raise_warning_printf(ER_WARN_ON_MODIFYING_GTID_EXECUTED_TABLE,
                                  table->table_name);
        DBUG_RETURN(1);
      }
    }


    DBUG_RETURN(0);
  }

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

/**

Push a warning to client if user is modifying the gtid_executed

table explicitly by a non-XA transaction. Push an error to client

if user is modifying it explicitly by a XA transaction.

@param thd Thread requesting to access the table

@param table The table is being accessed.

@retval 0 No warning or error was pushed to the client.

@retval 1 Push a warning to client.

@retval 2 Push an error to client.

int warn_or_err_on_explicit_modification(THD *thd, TABLE_LIST *table) {

DBUG_ENTER("Gtid_table_persistor::warn_or_err_on_explicit_modification");

if (!thd->is_operating_gtid_table_implicitly &&

table->lock_descriptor().type >= TL_WRITE_ALLOW_WRITE &&

!strcmp(table->table_name, Gtid_table_access_context::TABLE_NAME.str)) {

if (thd->get_transaction()->xid_state()->has_state(

XID_STATE::XA_ACTIVE)) {

Push an error to client if user is modifying the gtid_executed

table explicitly by a XA transaction.

thd->raise_error_printf(ER_ERROR_ON_MODIFYING_GTID_EXECUTED_TABLE,

table->table_name);

DBUG_RETURN(2);

} else {

Push a warning to client if user is modifying the gtid_executed

table explicitly by a non-XA transaction.

thd->raise_warning_printf(ER_WARN_ON_MODIFYING_GTID_EXECUTED_TABLE,

table->table_name);

DBUG_RETURN(1);

}

DBUG_RETURN(0);

}

補足

Storage Engine のレイヤーで Row capable かどうか判断する必要があるという話は、この bug report が発端のようです。

Bug #23051 READ COMMITTED breaks mixed and statement-based replication

WorkLog 3303 によって、 Row capable や Statement capable という概念がもたらされたようですが

WorkLog 3303 の SETTINGS FOR CURRENT ENGINES と、現状の仕様は異なっているようです。このあたり、実際にコード書いてたら仕様変えたほうが良いとわかったってことかもしれませんね。

THD::decide_logging_format() 以外で Replication が停止してしまうケースなどについて

いちおう軽く触れておきます。すごい雑にまとめると

MySQL 5.6 で SBR から RBR に移行する場合は、割と注意したほうがいい。特に、 SET GLOBAL で binlog_format 変更するのは、よほどその環境を理解していない限り、止めておいた方が無難なのでは。 master と slave で型が違ったり、 TIMESTAMP型がmicrosecondsサポートしてるかしていないかでも、 replication に影響する。
MySQL 5.7 で SBR から RBR に移行する場合、だいぶいろいろと改善されている。ただ、 binlog_rows_query_log_events と、MySQL 5.7 で追加された Multi-Source Replication を組み合わせると、メモリリークするなどのバグが有った。マイナーバージョンが古めの MySQL5.7を使っている場合、 SBR から RBR への移行は、注意が必要。
MySQL 8.0 ではこのあたりのバグなどだいぶ直っているらしい。 MySQL8.0 において、RBRへの移行のハードルはだいぶ下がっている感がある。（ただ、 MySQL 8.0 を使ってる時点で、 SBR はもうやめている気もするし、8.0 に移行すること自体の方が、RBRへの移行よりハードル高い気がする）

時刻型や slave_type_conversions

MySQL5.6 から DATETIME や TIMESTAMP が microseconds に対応しました。

11.3.1 DATE、DATETIME、および TIMESTAMP 型

同じTIMESTAMP型でも、5.5以前と5.6以降では内部的に異なるデータ型となった場合、replication止まるケースがありました。

Bug #70124 Timestamp and datetimes self-incompatible during replication

5.7.5 or 5.6.20 以降でなおったそうです。

Changes in MySQL 5.7.5 (2014-09-25, Milestone 15

Replication: Replication of tables that contained temporal type fields (such as TIMESTAMP, DATETIME, and TIME) from different MySQL versions failed due to incompatible TIMESTAMP types. The fractional TIMESTAMP format added in MySQL 5.6.4 was not being correctly converted. You can now replicate a TIMESTAMP in either format correctly according to the slave_type_conversions variable. (Bug #70124, Bug #17532932)

Changes in MySQL 5.6.20 (2014-07-31, General Availability)

Replication: Replication of tables that contained temporal type fields (such as TIMESTAMP, DATETIME, and TIME) from different MySQL versions failed due to incompatible TIMESTAMP types. The fractional TIMESTAMP format added in MySQL 5.6.4 was not being correctly converted. You can now replicate a TIMESTAMP in either format correctly according to the slave_type_conversions variable. (Bug #70124, Bug #17532932)

具体的にはこの commitですね。

さらに、さいきんのMySQLは型変換するためのパラメータが追加されて、 5.7.2 以降で設定できる値が増えています。

このへんも参考までに

rbr_exec_mode

mysqlbinlog用に、 5.7 から追加されました。詳しくはリファレンスマニュアルを参照してください。

rbr_exec_mode

Bug #85371 Memory leak in multi-source replication when binlog_rows_query_log_events=1

https://bugs.mysql.com/bug.php?id=85371

Multi-Source Replication とbinlog_rows_query_log_events=1 を組み合わせた場合、メモリリークすることがあったそうで、MySQL 5.7.21 と 8.0.4 以降でなおったそうです。

今日はこれまで

decide_logging_format() の Warning/Error は、昔の MySQL より最近の MySQL の方が、 error code が増えていたりします。さいきんの THD::decide_logging_format() を前提に考えた方が、より厳格で安全かもしれません。興味のある方は、むかしの MySQL とさいきんの MySQL で、decide_logging_format() を読み比べてもらうと良いでしょう。

MySQL の replication は、 storage engine と協調動作しながら、その整合性を保つよう、改善され続けてきました。このあたりの経緯などに興味のある方は、今回取り上げた関数やWorkLogなどをいろいろ読んでもらうと、理解が深まるのではないでしょうか。

Row-Based Replication は、 Statement-Based Replicaiton で発生していた不具合を改善するものとして導入された部分もあるのですが、 SBR で動いていたものを RBR に移行するとき、なんらかの副作用が発生する可能性があります。 SET GLOBAL で binlog_format 変更することがリファレンスマニュアルで推奨されていないのは、そのあたりの副作用だったり振る舞いを把握するのが、なかなか難しいという問題があるのでしょう。

次回以降も RBR の話は続きます。