2020/07/02

MySQLで CURDATE() - 1 は「昨日の日付」を返さない

TL;DR

  • 意図したことをMySQLでやるには CURDATE() - INTERVAL 1 DAY
  • というか俺はむしろDATE型から数値を引くことに違和感があるんですがこれってOracleの書き方なんでしたっけ?

昨日の朝、こんなのを見た。

昨日の夜、このブログを読んだ。

けんつさんのブログは「文字列とDATE型」だけれど、バグレポートの方は「数値とDATE型」に起因する。
折角なのでちょっと書いておこうかと思った。


まず、DATE型と数値型を演算しようとするとDATE型が数値型にキャストされて戻り値も数値型になる。
DATE型から数値型へのキャストは 年 * 1000000 + 月 * 1000 + 日 で行われる(DATETIME型の場合は時分秒を格納するために更に桁数が増える)。逆の操作で有効な日付が生成できれば、数値型からDATE型へのキャストもできる。

mysql80 13> SELECT CURDATE(), CAST(CURDATE() AS SIGNED), CAST(20200702 AS DATE);
+------------+---------------------------+------------------------+
| CURDATE()  | CAST(CURDATE() AS SIGNED) | CAST(20200702 AS DATE) |
+------------+---------------------------+------------------------+
| 2020-07-02 |                  20200702 | 2020-07-02             |
+------------+---------------------------+------------------------+
1 row in set (0.00 sec)

よって(2020/7/2から)1を引くと20200701になるし、これはDATE型にキャストし返せる。 - INTERVAL 1 DAY にしておけば最初からDATE型で返ってくる。

mysql80 13> SELECT CURDATE() - 1, CAST(CURDATE() - 1 AS DATE), CURDATE() - INTERVAL 1 DAY;
+---------------+-----------------------------+----------------------------+
| CURDATE() - 1 | CAST(CURDATE() - 1 AS DATE) | CURDATE() - INTERVAL 1 DAY |
+---------------+-----------------------------+----------------------------+
|      20200701 | 2020-07-01                  | 2020-07-01                 |
+---------------+-----------------------------+----------------------------+
1 row in set (0.00 sec)

これが月をまたぐと雲行きが怪しくなってくる。

mysql80 13> SELECT CURDATE() - 2, CAST(CURDATE() - 2 AS DATE), CURDATE() - INTERVAL 2 DAY;
+---------------+-----------------------------+----------------------------+
| CURDATE() - 2 | CAST(CURDATE() - 2 AS DATE) | CURDATE() - INTERVAL 2 DAY |
+---------------+-----------------------------+----------------------------+
|      20200700 | 2020-07-00                  | 2020-06-30                 |
+---------------+-----------------------------+----------------------------+
1 row in set (0.00 sec)

mysql80 13> SELECT CURDATE() - 3, CAST(CURDATE() - 3 AS DATE), CURDATE() - INTERVAL 3 DAY;
+---------------+-----------------------------+----------------------------+
| CURDATE() - 3 | CAST(CURDATE() - 3 AS DATE) | CURDATE() - INTERVAL 3 DAY |
+---------------+-----------------------------+----------------------------+
|      20200699 | NULL                        | 2020-06-29                 |
+---------------+-----------------------------+----------------------------+
1 row in set, 1 warning (0.00 sec)

閑話休題。今年1年分の日付だけをDATE型で突っ込んだ cal というテーブルを用意した。そういえば今年は閏年だったのか。

mysql80 15> SELECT * FROM cal;
+------------+
| _date      |
+------------+
| 2020-01-01 |
| 2020-01-02 |
| 2020-01-03 |

..
| 2020-12-29 |
| 2020-12-30 |
| 2020-12-31 |
+------------+
366 rows in set (0.00 sec)

これで >= を使って比較してみる(「n日前から今日まで」みたいなイメージ)。
やはり月をまたぐとおかしくなるが、「本当に6月分のレコードが存在しなければこういう結果セットでもおかしくないよね」みたいな結果セットであるところがにくい。

mysql80 15> SELECT MIN(_date), CURDATE() - 1, CURDATE() - INTERVAL 1 DAY FROM cal WHERE _date >= CURDATE() - 1;
+------------+---------------+----------------------------+
| MIN(_date) | CURDATE() - 1 | CURDATE() - INTERVAL 1 DAY |
+------------+---------------+----------------------------+
| 2020-07-01 |      20200701 | 2020-07-01                 |
+------------+---------------+----------------------------+
1 row in set (0.00 sec)

mysql80 15> SELECT MIN(_date), CURDATE() - 2, CURDATE() - INTERVAL 2 DAY FROM cal WHERE _date >= CURDATE() - 2;
+------------+---------------+----------------------------+
| MIN(_date) | CURDATE() - 2 | CURDATE() - INTERVAL 2 DAY |
+------------+---------------+----------------------------+
| 2020-07-01 |      20200700 | 2020-06-30                 |
+------------+---------------+----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql80 15> SELECT MIN(_date), CURDATE() - 3, CURDATE() - INTERVAL 3 DAY FROM cal WHERE _date >= CURDATE() - 3;
+------------+---------------+----------------------------+
| MIN(_date) | CURDATE() - 3 | CURDATE() - INTERVAL 3 DAY |
+------------+---------------+----------------------------+
| 2020-07-01 |      20200699 | 2020-06-29                 |
+------------+---------------+----------------------------+
1 row in set, 1 warning (0.00 sec)

なおこのパターン、体感として「30日」か「90日」を超えたあたりで気が付く人が多い。

30日以上過去の日付は MONTH(_date) の値が変わることが多くの場合期待されるが、数値型になって30を引かれても100の位は変わらないから、先月ぶんはまるまる引っ掛からない。これに違和感を覚えるのが「30日」のパターン。

mysql80 15> SELECT MIN(_date), CURDATE() - 60, CURDATE() - INTERVAL 60 DAY FROM cal WHERE _date >= CURDATE() - 60;
+------------+----------------+-----------------------------+
| MIN(_date) | CURDATE() - 60 | CURDATE() - INTERVAL 60 DAY |
+------------+----------------+-----------------------------+
| 2020-07-01 |       20200642 | 2020-05-03                  |
+------------+----------------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

90日以上過去の日付を数値型にキャストするとだいたい 再びDATE型にキャスト可能な10の位の範囲 に戻ってきて、到底期待されない日付として扱われる。これに違和感をおぼえるのが「90日」のパターン。

mysql80 15> SELECT MIN(_date), CURDATE() - 90, CURDATE() - INTERVAL 90 DAY FROM cal WHERE _date >= CURDATE() - 90; -- 7/1で70だと上手くいかないので90にしたけど

+------------+----------------+-----------------------------+
| MIN(_date) | CURDATE() - 90 | CURDATE() - INTERVAL 90 DAY |
+------------+----------------+-----------------------------+
| 2020-06-12 |       20200612 | 2020-04-03                  |
+------------+----------------+-----------------------------+
1 row in set (0.00 sec)

こっちはワーニングすら出ないので、仕様を知らないと混乱するかも。

90日以外のパターンは SHOW WARNINGS でも拾える。
8.0なら performance_schema.events_errors_* でそのワーニングが発生しているかどうかも多少はあたりがつけられる(文字列を数値型にキャストしようとした時にも出るワーニングだから100%とは言えないけれども…

mysql80 15> SHOW WARNINGS;
+---------+------+--------------------------------------------------------------+
| Level   | Code | Message                                                      |
+---------+------+--------------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '20200699' for column '_date' at row 1 |
+---------+------+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql80 15> SELECT * FROM performance_schema.events_errors_summary_global_by_error WHERE error_number= 1292;
+--------------+--------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| ERROR_NUMBER | ERROR_NAME               | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN          | LAST_SEEN           |
+--------------+--------------------------+-----------+------------------+-------------------+---------------------+---------------------+
|         1292 | ER_TRUNCATED_WRONG_VALUE | 22007     |             1110 |                 0 | 2020-07-02 11:56:07 | 2020-07-02 14:15:55 |
+--------------+--------------------------+-----------+------------------+-------------------+---------------------+---------------------+
1 row in set (0.00 sec)

ワーニングは拾った方が後々楽だと思うけれど、自作しないとなかなか挟むのムズカシイんだよなぁ。。

2020/07/01

MySQL徹底入門 第4版の執筆に参加しました

TL;DR


掲題の通り、MySQL徹底入門の執筆に参加させていただきました。

第3版 を「読んで勉強していた」のが、第4版を書く側として声をかけていただいて、人生何があるかわからないものだなあ。

飽くまで「入門」なので、「実践ハイパフォーマンスMySQL」のような玄人向け成分はほぼありません。たぶん。

俺の担当は 6章(運用), 8章(レプリケーション), 9章(バックアップとリストア), 10章(プログラミングのうちPerl), 14章(逆引きMySQL辞典の一部) となっています。

6章(運用)

  • いわゆる MySQLを運用しましょう 的な内容ではない
  • 「運用するにはこれくらいの基礎知識は必要だよね?」な内容が詰まっています
    • SET GLOBAL で即時有効にならないパラメーターの話とか
    • my.cnfの読み込まれる順番の話とか
    • MySQLが「インデックスをどう使うからWHEREやORDER BY .. LIMITが速くなるのか」とか
      • 一部の方にご好評いただいている「トランプを使ったたとえ」が初めて文書化されています :)
    • レプリケーションを使ったローリングアップグレードの仕組みの話とか

8章(レプリケーション)

  • (非同期)レプリケーションとグループレプリケーションの「仕組み」に言及しています
    • 非同期レプリケーションではBinlog Dumpスレッド、I/Oスレッド、SQLスレッド、じゃあグループレプリケーションは? :)
      • というくらいのレベル感です
  • レプリケーション関連のオプションのオススメ設定や、設定時に考えることが割と列挙されています
  • なんとレプリケーションの構築手順が 全てMySQL Shellから構築する方法しか紹介されていません よってgtid_mode=ON も前提です。
    • 8.0のこの時代に「入門」する人は、もうMySQL Shellからの方法だけおぼえればいいんじゃないかな…

9章(バックアップとリストア)

  • 目新しいところは Cloneステートメントを使ったサンプルがあるところと、 mysqlbinlog --read-from-remote-server --stop-never --raw を使ったサンプルがあるところでしょうか
  • GTIDを前提に解説しているのでGTIDに不慣れな人には良い鴨

10章(プログラミング - Perl)

  • たぶん中括弧と代入の使い方がキモチワルイと思うんですが、これは(CにできてPerlにできない書き方を除いて)「 昔の MySQLのコーディング規約」に毒された俺の末路です。

14章(逆引きMySQL辞典)

  • 本編(?)とは違ったテンションでお送りする一問一答…とは限らないQ&Aっぽいコーナー
  • ここ書いてて本当に楽しかった章で、読み返しの時に「俺が書いたような気がしたものが別は他の人が書いた部分」「この人が書いただろうなと思ったものを書いたのは実は俺」みたいな錯覚がありました

参加できたことを嬉しく思いますし、全体通してもなかなか面白く読み応えのある本になっていますので、是非お手に取っていただければと。

俺が一番読んでて面白かったのは第11章の「文字コードと日本語環境」です :D

2020/06/29

performance_schema.data_locks.ENGINE_LOCK_ID is 何

TL;DR

  • MySQL 8.0.20のInnoDBにおいては row->lock_trx_immutable_id ":" row->lock_space ":" row->lock_page ":" row->lock_rec ":" row->lock_immutable_id らしい
  • ちなみにこのENGINE_LOCK_ID(実体は pk_pos_data_lock::m_engine_lock_id ?)を真面目に実装しているのはInnoDBだけっぽく見える
    • NDBCLUSTERは読んでない

そう思って見てみると、行単位でどこのページに乗っかってるのかとか調べられたりするかなあと思ったり思わなかったり。

mysql80 18> SELECT SUBSTRING_INDEX(engine_lock_id, ':', 1) AS lock_trx_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 2), ':', -1) AS space_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 3), ':', -1) AS page_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 4), ':', -1) AS record_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 5), ':', -1) AS lock_id, lock_data FR
OM data_locks WHERE lock_type = 'RECORD';
+-----------------+----------+---------+-----------+-----------------+------------------------+
| lock_trx_id     | space_id | page_id | record_id | lock_id         | lock_data              |
+-----------------+----------+---------+-----------+-----------------+------------------------+
| 140293334355320 | 166      | 4       | 1         | 140293227750848 | supremum pseudo-record |
| 140293334355320 | 166      | 4       | 2         | 140293227750848 | 1                      |
| 140293334355320 | 166      | 4       | 3         | 140293227750848 | 2                      |
| 140293334355320 | 166      | 4       | 4         | 140293227750848 | 3                      |
| 140293334355320 | 166      | 4       | 5         | 140293227750848 | 4                      |
| 140293334355320 | 166      | 4       | 6         | 140293227750848 | 5                      |
| 140293334355320 | 166      | 4       | 7         | 140293227750848 | 6                      |
+-----------------+----------+---------+-----------+-----------------+------------------------+
7 rows in set (0.00 sec)

mysql80 19> OPTIMIZE TABLE t1; -- これで↓のspace_idが変わった

mysql80 18> SELECT SUBSTRING_INDEX(engine_lock_id, ':', 1) AS lock_trx_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 2), ':', -1) AS space_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 3), ':', -1) AS page_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 4), ':', -1) AS record_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 5), ':', -1) AS lock_id, lock_data FROM data_locks WHERE lock_type = 'RECORD';
+-----------------+----------+---------+-----------+-----------------+------------------------+
| lock_trx_id     | space_id | page_id | record_id | lock_id         | lock_data              |
+-----------------+----------+---------+-----------+-----------------+------------------------+
| 140293334355320 | 167      | 4       | 1         | 140293227750848 | supremum pseudo-record |
| 140293334355320 | 167      | 4       | 2         | 140293227750848 | 1                      |
| 140293334355320 | 167      | 4       | 3         | 140293227750848 | 2                      |
| 140293334355320 | 167      | 4       | 4         | 140293227750848 | 3                      |
| 140293334355320 | 167      | 4       | 5         | 140293227750848 | 4                      |
| 140293334355320 | 167      | 4       | 6         | 140293227750848 | 5                      |
| 140293334355320 | 167      | 4       | 7         | 140293227750848 | 6                      |
+-----------------+----------+---------+-----------+-----------------+------------------------+
7 rows in set (0.00 sec)

↓i_sほにゃららのくせにp_sから呼ばれている図

(gdb) bt
+bt
#0  trx_i_s_create_lock_id (row=row@entry=0x7f4918052850,
    lock_id=lock_id@entry=0x7f4918052970 "139951965544648:1248:139951844968944", lock_id_size=lock_id_size@entry=105)
    at /home/yoku0825/mysql-8.0.20/storage/innobase/trx/trx0i_s.cc:1154
#1  0x000000000205a373 in print_record_lock_id (lock=lock@entry=0x7f4914013dd0, heap_no=heap_no@entry=2,
    lock_id=lock_id@entry=0x7f4918052970 "139951965544648:1248:139951844968944", lock_id_size=lock_id_size@entry=105)
    at /home/yoku0825/mysql-8.0.20/storage/innobase/handler/p_s.cc:519
#2  0x000000000205ac5a in Innodb_data_lock_iterator::scan_trx (this=this@entry=0x7f48c400ff40,
    container=container@entry=0x7f48c42ca828, with_lock_data=with_lock_data@entry=true, trx=trx@entry=0x7f491b3120c8,
    with_filter=with_filter@entry=false, filter_lock_immutable_id=filter_lock_immutable_id@entry=0, filter_heap_id=0)
    at /home/yoku0825/mysql-8.0.20/storage/innobase/handler/p_s.cc:804
#3  0x000000000205ae8f in Innodb_data_lock_iterator::scan_trx_list (this=this@entry=0x7f48c400ff40,
    container=container@entry=0x7f48c42ca828, with_lock_data=with_lock_data@entry=true, read_write=read_write@entry=true,
    trx_list=<optimized out>) at /home/yoku0825/mysql-8.0.20/storage/innobase/handler/p_s.cc:684
#4  0x000000000205b76a in scan (with_lock_data=true, container=0x7f48c42ca828, this=0x7f48c400ff40)
    at /home/yoku0825/mysql-8.0.20/storage/innobase/handler/p_s.cc:593
#5  Innodb_data_lock_iterator::scan (this=0x7f48c400ff40, container=0x7f48c42ca828, with_lock_data=<optimized out>)
    at /home/yoku0825/mysql-8.0.20/storage/innobase/handler/p_s.cc:572
#6  0x000000000249252b in table_data_locks::rnd_next() () at /home/yoku0825/mysql-8.0.20/storage/perfschema/table_data_locks.cc:191
#7  0x000000000243fc6e in ha_perfschema::rnd_next (this=0x7f48c42bf838, buf=0x7f48c42d8e38 "\377\377")
    at /home/yoku0825/mysql-8.0.20/storage/perfschema/ha_perfschema.cc:1641
#8  0x00000000010df114 in handler::ha_rnd_next (this=0x7f48c42bf838, buf=0x7f48c42d8e38 "\377\377")
    at /home/yoku0825/mysql-8.0.20/sql/handler.cc:2966
#9  0x0000000000e0d72d in TableScanIterator::Read (this=0x7f48c42d8390) at /home/yoku0825/mysql-8.0.20/sql/row_iterator.h:275
#10 0x0000000000f702b3 in SELECT_LEX_UNIT::ExecuteIteratorQuery(THD*) () at /home/yoku0825/mysql-8.0.20/sql/sql_union.cc:1183
#11 0x0000000000f7047c in SELECT_LEX_UNIT::execute(THD*) () at /home/yoku0825/mysql-8.0.20/sql/sql_union.cc:1235
#12 0x0000000000f0245b in Sql_cmd_dml::execute_inner(THD*) () at /home/yoku0825/mysql-8.0.20/sql/sql_select.cc:945
#13 0x0000000000f0c449 in Sql_cmd_dml::execute(THD*) () at /home/yoku0825/mysql-8.0.20/sql/sql_select.cc:725
#14 0x0000000000eb6435 in mysql_execute_command(THD*, bool) () at /home/yoku0825/mysql-8.0.20/sql/sql_parse.cc:4489
#15 0x0000000000eb8378 in mysql_parse (thd=thd@entry=0x7f48c4010140, parser_state=parser_state@entry=0x7f49180544d0)
    at /home/yoku0825/mysql-8.0.20/sql/sql_parse.cc:5306
#16 0x0000000000eba4d5 in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
    at /home/yoku0825/mysql-8.0.20/sql/sql_parse.cc:1776
#17 0x0000000000ebb314 in do_command (thd=thd@entry=0x7f48c4010140) at /home/yoku0825/mysql-8.0.20/sql/sql_parse.cc:1274
#18 0x0000000000fccc70 in handle_connection (arg=arg@entry=0x757af30)
    at /home/yoku0825/mysql-8.0.20/sql/conn_handler/connection_handler_per_thread.cc:302
#19 0x000000000244315c in pfs_spawn_thread (arg=0x55f8070) at /home/yoku0825/mysql-8.0.20/storage/perfschema/pfs.cc:2854
#20 0x00007f49276caea5 in start_thread (arg=0x7f4918055700) at pthread_create.c:307
#21 0x00007f49258538dd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111
(gdb) frame 14
+frame 14
#14 0x0000000000eb6435 in mysql_execute_command(THD*, bool) () at /home/yoku0825/mysql-8.0.20/sql/sql_parse.cc:4489
4489          res = lex->m_sql_cmd->execute(thd);
(gdb) p thd->m_query_string
+p thd->m_query_string
$3 = {
  str = 0x7f48c42d4608 "SELECT * FROM performance_schema.data_locks",
  length = 43
}

2020/06/26

最近のMySQLにテンポラリーなファイル/ディレクトリを指定するオプションが多い気がする件

TL;DR


mysql80 9> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.20    |
+-----------+
1 row in set (0.00 sec)

mysql80 9> SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE '%tmp%' OR variable_name LIKE '%temp%';
+---------------------------------+-----------------------+
| variable_name                   | variable_value        |
+---------------------------------+-----------------------+
| avoid_temporal_upgrade          | OFF                   |
| default_tmp_storage_engine      | InnoDB                |
| innodb_temp_data_file_path      | ibtmp1:12M:autoextend |
| innodb_temp_tablespaces_dir     | ./#innodb_temp/       |
| innodb_tmpdir                   |                       |
| internal_tmp_mem_storage_engine | TempTable             |
| show_old_temporals              | OFF                   |
| slave_load_tmpdir               | /tmp                  |
| temptable_max_ram               | 1073741824            |
| temptable_use_mmap              | ON                    |
| tmp_table_size                  | 16777216              |
| tmpdir                          | /tmp                  |
+---------------------------------+-----------------------+
12 rows in set (0.00 sec)
mysql57 6> SELECT @@version;
+------------+
| @@version  |
+------------+
| 5.7.30-log |
+------------+
1 row in set (0.00 sec)

mysql57 6> SELECT variable_name, variable_value FROM performance_schema.global_variables WHERE variable_name LIKE '%tmp%' OR variable_name LIKE '%temp%';
+----------------------------------+-----------------------+
| variable_name                    | variable_value        |
+----------------------------------+-----------------------+
| avoid_temporal_upgrade           | OFF                   |
| default_tmp_storage_engine       | InnoDB                |
| innodb_temp_data_file_path       | ibtmp1:12M:autoextend |
| innodb_tmpdir                    |                       |
| internal_tmp_disk_storage_engine | InnoDB                |
| max_tmp_tables                   | 32                    |
| show_old_temporals               | OFF                   |
| slave_load_tmpdir                | /tmp                  |
| tmp_table_size                   | 16777216              |
| tmpdir                           | /tmp                  |
+----------------------------------+-----------------------+
10 rows in set (0.00 sec)
  • 5.6は performance_schema ではなくて information_schema
mysql56> SELECT @@version;
+------------+
| @@version  |
+------------+
| 5.6.48-log |
+------------+
1 row in set (0.00 sec)

mysql56> SELECT variable_name, variable_value FROM information_schema.global_variables WHERE variable_name LIKE '%tmp%' OR variable_name LIKE '%temp%';
+----------------------------+----------------+
| variable_name              | variable_value |
+----------------------------+----------------+
| INNODB_TMPDIR              |                |
| SHOW_OLD_TEMPORALS         | OFF            |
| TMPDIR                     | /tmp           |
| MAX_TMP_TABLES             | 32             |
| DEFAULT_TMP_STORAGE_ENGINE | InnoDB         |
| AVOID_TEMPORAL_UPGRADE     | OFF            |
| TMP_TABLE_SIZE             | 16777216       |
| SLAVE_LOAD_TMPDIR          | /tmp           |
+----------------------------+----------------+
8 rows in set (0.00 sec)

2020/06/23

utf8mb3なマスターに絵文字を突っ込んだ時にバイナリログってどうなるんだっけ

TL;DR

  • マスター上で、3バイトUTF-8なカラムとコネクションのcharsetの対応(いずれもsql_mode = ‘STRICT_TRANS_TABLES’ でない )
SET NAMES utf8 SET NAMES utf8mb4
utf8なカラム 絵文字から後ろが切れる 絵文字が ‘?’ になる
utf8mb4なカラム 絵文字が ‘????’ になる 絵文字が入る
  • マスターが3バイトutf8でスレーブが4バイトutf8(utf8mb4)の場合と↑の対応
ROW STATEMENT
マスターでは後ろが切れた スレーブでも後ろが切れる スレーブでは絵文字が入る
マスターでは ‘?’ になった スレーブでも ‘?’ になる スレーブでは絵文字が入る
  • 「後ろが全部切れる」のが「’?’ に変換される」のはまだインパクト少なそう(絵文字を入れたら後ろが全部切れるのを前提にしているエンドユーザーはきっといない)
  • binlog_format=ROW の方が安牌
    • マスター切り替え前後でエンドユーザーに見える値は変わらない
    • スレーブの CONVERT TO CHARSET utf8mb4 後すぐにマスターを切り替えるなら binlog_format= STATEMENT にしたくなる気持ちはわからなくもない(が、やりたくない)

以下、ログ

binlog_format = ROW

CREATE TABLE .. CHARACTER SET utf8mb3

mysql80 54> CREATE TABLE t1 (num int, val varchar(32)) CHARSET utf8mb3;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql80 54> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                     |
+---------+------+---------------------------------------------------------------------------------------------+
| Warning | 1287 | 'utf8mb3' is deprecated and will be removed in a future release. Please use utf8mb4 instead |
+---------+------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql80 54> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int DEFAULT NULL,
  `val` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

SET NAMES utf8mb3

mysql80 54> INSERT INTO t1 VALUES (1, 'utf8mb3による🍣だよ');
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql80 54> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------------+
| Level   | Code | Message                                                                         |
+---------+------+---------------------------------------------------------------------------------+
| Warning | 1300 | Invalid utf8 character string: 'F09F8D'                                         |
| Warning | 1366 | Incorrect string value: '\xF0\x9F\x8D\xA3\xE3\x81...' for column 'val' at row 1 |
+---------+------+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql80 54> SELECT * FROM t1;
+------+------------------+
| num  | val              |
+------+------------------+
|    1 | utf8mb3による    |
+------+------------------+
1 row in set (0.00 sec)

# at 772
#200623 13:53:33 server id 1080  end_log_pos 851 CRC32 0x9517e90e       Rows_query
# INSERT INTO t1 VALUES (1, 'utf8mb3による🍣だよ')
### INSERT INTO `d11`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='utf8mb3による' /* VARSTRING(96) meta=96 nullable=1 is_null=0 */

SET NAMES utf8mb4

mysql80 54> INSERT INTO t1 VALUES (1, 'utf8mb4による🍣だよ');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql80 54> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------------+
| Level   | Code | Message                                                                         |
+---------+------+---------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xF0\x9F\x8D\xA3\xE3\x81...' for column 'val' at row 1 |
+---------+------+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql80 54> SELECT * FROM t1;
+------+-------------------------+
| num  | val                     |
+------+-------------------------+
|    1 | utf8mb3による           |
|    1 | utf8mb4による?だよ      |
+------+-------------------------+
2 rows in set (0.00 sec)

# at 1145
#200623 13:54:54 server id 1080  end_log_pos 1224 CRC32 0x4357ac1b      Rows_query
# INSERT INTO t1 VALUES (1, 'utf8mb4による🍣だよ')
### INSERT INTO `d11`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='utf8mb4による?だよ' /* VARSTRING(96) meta=96 nullable=1 is_null=0 */

CREATE TABLE .. CHARSET utf8mb4

mysql80 54> CREATE TABLE t1 (num int, val varchar(32)) CHARSET utf8mb4;
Query OK, 0 rows affected (0.04 sec)

mysql80 54> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int DEFAULT NULL,
  `val` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

SET NAMES utf8mb3

mysql80 54> INSERT INTO t1 VALUES (1, 'utf8mb3による🍺だよ');
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql80 54> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------------+
| Level   | Code | Message                                                                         |
+---------+------+---------------------------------------------------------------------------------+
| Warning | 1300 | Invalid utf8 character string: 'F09F8D'                                         |
| Warning | 1366 | Incorrect string value: '\xF0\x9F\x8D\xBA\xE3\x81...' for column 'val' at row 1 |
+---------+------+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql80 54> SELECT * FROM t1;
+------+----------------------------+
| num  | val                        |
+------+----------------------------+
|    1 | utf8mb3による????だよ      |
+------+----------------------------+
1 row in set (0.00 sec)

# at 1948
#200623 13:57:06 server id 1080  end_log_pos 2027 CRC32 0xccae4843      Rows_query
# INSERT INTO t1 VALUES (1, 'utf8mb3による🍺だよ')
### INSERT INTO `d11`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='utf8mb3による????だよ' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */

SET NAMES utf8mb4

mysql80 54> INSERT INTO t1 VALUES (1, 'utf8mb4による🍺だよ');
Query OK, 1 row affected (0.01 sec)

mysql80 54> SELECT * FROM t1;
+------+----------------------------+
| num  | val                        |
+------+----------------------------+
|    1 | utf8mb3による????だよ      |
|    1 | utf8mb4による🍺だよ          |
+------+----------------------------+
2 rows in set (0.00 sec)

# at 2333
#200623 13:58:52 server id 1080  end_log_pos 2412 CRC32 0xbc81cf0a      Rows_query
# INSERT INTO t1 VALUES (1, 'utf8mb4による🍺だよ')
### INSERT INTO `d11`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='utf8mb4による🍺だよ' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */

binlog_format = STATEMENT

CREATE TABLE .. CHARACTER SET utf8mb3

SET NAMES utf8mb3

mysql80 57> INSERT INTO t1 VALUES (1, 'utf8mb3による🍣だよ');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql80 57> SELECT * FROM t1;
+------+------------------+
| num  | val              |
+------+------------------+
|    1 | utf8mb3による    |
+------+------------------+
1 row in set (0.00 sec)

# at 1584
#200623 14:05:34 server id 1080  end_log_pos 1714 CRC32 0xb7219e87      Query   thread_id=57    exec_time=0     error_code=0
SET TIMESTAMP=1592888734/*!*/;
INSERT INTO t1 VALUES (1, 'utf8mb3による🍣だよ')

SET NAMES utf8mb4

mysql80 57> INSERT INTO t1 VALUES (1, 'utf8mb4による🍣だよ');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql80 57> SELECT * FROM t1;
+------+-------------------------+
| num  | val                     |
+------+-------------------------+
|    1 | utf8mb3による           |
|    1 | utf8mb4による?だよ      |
+------+-------------------------+
2 rows in set (0.00 sec)

# at 1904
#200623 14:06:17 server id 1080  end_log_pos 2034 CRC32 0xae911ee8      Query   thread_id=57    exec_time=0     error_code=0
SET TIMESTAMP=1592888777/*!*/;
INSERT INTO t1 VALUES (1, 'utf8mb4による🍣だよ')

CREATE TABLE .. CHARACTER SET utf8mb4

SET NAMES utf8mb3

mysql80 57> INSERT INTO t1 VALUES (1, 'utf8mb3による🍺だよ');
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql80 57> SELECT * FROM t1;
+------+----------------------------+
| num  | val                        |
+------+----------------------------+
|    1 | utf8mb3による????だよ      |
+------+----------------------------+
1 row in set (0.00 sec)

# at 2647
#200623 14:07:40 server id 1080  end_log_pos 2777 CRC32 0xf21371cd      Query   thread_id=57    exec_time=0     error_code=0
SET TIMESTAMP=1592888860/*!*/;
INSERT INTO t1 VALUES (1, 'utf8mb3による🍺だよ')
/*!*/;

SET NAMES utf8mb4

mysql80 57> INSERT INTO t1 VALUES (1, 'utf8mb4による🍺だよ');
Query OK, 1 row affected (0.01 sec)

mysql80 57> SELECT * FROM t1;
+------+----------------------------+
| num  | val                        |
+------+----------------------------+
|    1 | utf8mb3による????だよ      |
|    1 | utf8mb4による🍺だよ          |
+------+----------------------------+
2 rows in set (0.00 sec)

# at 2967
#200623 14:08:25 server id 1080  end_log_pos 3097 CRC32 0x29b3d896      Query   thread_id=57    exec_time=0     error_code=0
SET TIMESTAMP=1592888905/*!*/;
INSERT INTO t1 VALUES (1, 'utf8mb4による🍺だよ')
/*!*/;