かみぽわーる

kamipo's blog

MySQL と寿司ビール問題

MySQL と Unicode Collation Algorithm (UCA) - かみぽわーる に関連するトピックで、 MySQL には寿司ビール問題というのがある。

これはどういう問題かというと、 MySQLUnicode では binary collation にしてコードポイントで比較しないと🍣と🍺に限らず絵文字が同値判定されるという問題です。

なぜこんな挙動にしたのか、どういうケースのときにこの挙動だとうれしいのか全く理解が及ばないが、残念ながらこの挙動はドキュメントに明記されており、仕様である。

以下に引用しますが、ざっくりいうと

  • collating weight がある文字はそれを使う。ない場合は以下に従う。
  • BMP文字で general collation (xxx_general_ci) の場合、コードポイントを使う。
  • BMP文字で unicode collation (xxx_unicode_ci) の場合、なんかいい感じの計算式で導出する。
  • SMP文字(絵文字とか)の場合、0xfffd REPLACEMENT CHARACTER と同じ weight になる。

とのこと(死)。

For all Unicode collations except the “binary” (xxx_bin) collations, MySQL performs a table lookup to find a character's collating weight. This weight can be displayed using the WEIGHT_STRING() function. (See Section 12.5, “String Functions”.) If a character is not in the table (for example, because it is a “new” character), collating weight determination becomes more complex:

  • For BMP characters in general collations (xxx_general_ci), weight = code point.
  • For BMP characters in UCA collations (for example, xxx_unicode_ci and language-specific collations), the following algorithm applies:
    (snip)
  • For supplementary characters in general collations, the weight is the weight for 0xfffd REPLACEMENT CHARACTER. For supplementary characters in UCA 4.0.0 collations, their collating weight is 0xfffd. That is, to MySQL, all supplementary characters are equal to each other, and greater than almost all BMP characters.

http://dev.mysql.com/doc/refman/5.6/en/charset-unicode-sets.html

なので、絵文字を検索で区別する必要がある場合は utf8mb4_bin にするしかないんですが、'ハハ' = 'パパ' 問題を気にしなければもうひとつ方法があることをドキュメント読んでて知りました。

xxx_unicode_ci というのは UCA 4.0.0 というかなり古いと思われる仕様に対する実装で、より新しい UCA 5.2.0 を実装した xxx_unicode_520_ci だとSMP文字にも weight を持っており、 weight がなくてもいい感じの計算式で導出するので 0xfffd REPLACEMENT CHARACTER と同じ weight にはならないと書いてある。

  • For supplementary characters based on UCA versions later than 4.0.0 (for example, xxx_unicode_520_ci), supplementary characters do not necessarily all have the same collation weight. Some have explicit weights from the UCA allkeys.txt file. Others have weights calculated from this algorithm:
    (snip)

http://dev.mysql.com/doc/refman/5.6/en/charset-unicode-sets.html

実際試してみるとこんな感じ。まず区別できない例。

SET NAMES utf8mb4;

DROP TABLE IF EXISTS wishlist_general;
CREATE TABLE wishlist_general (
  item varchar(1)
) COLLATE utf8mb4_general_ci;

DROP TABLE IF EXISTS wishlist_unicode;
CREATE TABLE wishlist_unicode (
  item varchar(1)
) COLLATE utf8mb4_unicode_ci;

INSERT INTO wishlist_general VALUES ('🍣'), ('🍺');
INSERT INTO wishlist_unicode VALUES ('🍣'), ('🍺');

SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_general WHERE item = '🍣';
+------+--------------------------+
| item | HEX(WEIGHT_STRING(item)) |
+------+--------------------------+
| 🍣     | FFFD                     |
| 🍺     | FFFD                     |
+------+--------------------------+

SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_unicode WHERE item = '🍣';
+------+--------------------------+
| item | HEX(WEIGHT_STRING(item)) |
+------+--------------------------+
| 🍣     | FFFD                     |
| 🍺     | FFFD                     |
+------+--------------------------+

区別できる例。

SET NAMES utf8mb4;

DROP TABLE IF EXISTS wishlist_unicode_520;
CREATE TABLE wishlist_unicode_520 (
  item varchar(1)
) COLLATE utf8mb4_unicode_520_ci;

DROP TABLE IF EXISTS wishlist_bin;
CREATE TABLE wishlist_bin (
  item varchar(1)
) COLLATE utf8mb4_bin;

INSERT INTO wishlist_unicode_520 VALUES ('🍣'), ('🍺');
INSERT INTO wishlist_bin VALUES ('🍣'), ('🍺');

SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_unicode_520 WHERE item = '🍣';
+------+--------------------------+
| item | HEX(WEIGHT_STRING(item)) |
+------+--------------------------+
| 🍣     | FBC3F363                 |
+------+--------------------------+

SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_unicode_520;
+------+--------------------------+
| item | HEX(WEIGHT_STRING(item)) |
+------+--------------------------+
| 🍣     | FBC3F363                 |
| 🍺     | FBC3F37A                 |
+------+--------------------------+

SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_bin WHERE item = '🍣';
+------+--------------------------+
| item | HEX(WEIGHT_STRING(item)) |
+------+--------------------------+
| 🍣     | 01F363                   |
+------+--------------------------+

SELECT item, HEX(WEIGHT_STRING(item)) FROM wishlist_bin;
+------+--------------------------+
| item | HEX(WEIGHT_STRING(item)) |
+------+--------------------------+
| 🍣     | 01F363                   |
| 🍺     | 01F37A                   |
+------+--------------------------+

とりあえず現状これが仕様だというのは分かったけど、どう考えてもSMP文字を 0xfffd REPLACEMENT CHARACTER と同じ weight にするの現世においてデメリットしかないと思うんで、これがちゃんと区別されるようになるのを願ってやまないです。

MySQL と Unicode Collation Algorithm (UCA)

utf8_unicode_ci に対する日本の開発者の見解 - かみぽわーる で、日本語が分かる人には utf8_unicode_ci のヤバさを感じてもらえたと思うんですけど、この挙動はドキュメントによると UCA というアルゴリズムによるものらしい。

MySQL implements the xxx_unicode_ci collations according to the Unicode Collation Algorithm (UCA) described at http://www.unicode.org/reports/tr10/. The collation uses the version-4.0.0 UCA weight keys: http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt. Currently, the xxx_unicode_ci collations have only partial support for the Unicode Collation Algorithm. Some characters are not supported yet. Also, combining marks are not fully supported. This affects primarily Vietnamese, Yoruba, and some smaller languages such as Navajo. http://dev.mysql.com/doc/refman/5.6/en/charset-unicode-sets.html

これもしかして、 Unicode って日本語のこと分かってない人たちによって作られてて日本語の検索とかなんも考慮されてないんとちゃうんかと思って調べてみたら、 そんなことはまったくなくて MySQL が UCA を部分的にしか実装していないということだった。

UTS #10: Unicode Collation Algorithm によると、 UCA では Multi-Level Comparison といって複数レベルに分けて文字列の比較を行い、各レベルの比較で文字列が一致した場合(tie-breaking level)、次のレベルで比較を行うを繰り返して順序を決定する。L1は大文字小文字アクセント無視(Base Charaters)、L2はアクセントを無視しない(Accents)、L3は大文字小文字を無視しない(Case/Variants)、のような感じである。

MySQL が UCA を部分的にしか実装していないとはどういうことかというと、 allkeys.txt でいうところの Primary weight range (L1比較のためのweight) しか実装していないので、アクセントを区別しないと常用に適さない言語(日本語とか)殺しな挙動になっているということだった。

mysql-5.7.6/strings/ctype-uca.c#L17-L31 からコメントを引用する。

/* 
   UCA (Unicode Collation Algorithm) support. 
   Written by Alexander Barkov <bar@mysql.com>
   
   Currently supports only subset of the full UCA:
   - Only Primary level key comparison
   - Basic Latin letters contraction is implemented
   - Variable weighting is done for Non-ignorable option
   
   Features that are not implemented yet:
   - No Normalization From D is done
     + No decomposition is done
     + No Thai/Lao orderding is done
   - No combining marks processing is done
*/

アクセントを区別しないと常用に適さない言語が日本語以外にどれぐらいあるのか言語に詳しくないので分からないけど、これを理由に以下のコミットをrevertできたりしないですかね。

参考

utf8_unicode_ci に対する日本の開発者の見解

MySQLにうるう秒は保存できるのか試した

結果、保存できなかった😢

SET sql_mode = STRICT_ALL_TABLES;

CREATE TABLE time_leap (
  dt datetime,
  ts timestamp
) ENGINE=InnoDB;

INSERT INTO time_leap VALUES ('2012-06-30 23:59:59', '2012-06-30 23:59:59');
-- Query OK, 1 row affected (0.02 sec)

INSERT INTO time_leap VALUES ('2012-06-30 23:59:60', '2012-06-30 23:59:60');
-- ERROR 1292 (22007): Incorrect datetime value: '2012-06-30 23:59:60' for column 'dt' at row 1

これを試そうと思ったのは、timestamp型は内部表現がepoch秒なのでうるう秒保存するの無理だろうけどdatetime型は秒の精度に6bit割いてるので[0, 64)の範囲の値を保存できるだけの精度を持ってると思ったんですけど、よく見たらドキュメントに6 bits second (0-59)って書いてたしどこかでバリデーションしてるんでしょうねきっと。

InnoDBの制限とファイルフォーマットAntelopeとBarracudaの違い

この投稿はMySQL Casual Advent Calendar 2014の5日目の記事です。

先週ツイッターInnoDBのことを質問されまして、せっかくなのでアドカレのネタにしようと思いますってことでInnoDBのファイルフォーマット毎の違いをカジュアルに説明しようと思います。

InnoDBのファイルフォーマットBarracudaと新機能

InnoDBにはファイルフォーマットとして昔からあるAntelopeと新しいフォーマット(5年も前からあるので新しくはないが)のBarracudaがあって、新しいフォーマットのBarracudaにはいろいろ改良や新機能が追加されています。

例えば、ROW_FORMAT=COMPRESSEDとかは分かりやすい新機能で、上のCOMPRESSEDではなくDYNAMICを選んでいる理由を質問されたときには、とりいそぎ圧縮展開するため最大スループットが落ちますという回答をしたんですけど、COMPRESSEDの性能については@さんのエントリが参考になります。

他には、innodb_large_prefixによるインデックスサイズ制限の拡張もBarracudaの新機能です。

もうひとつ、見た目地味だけどドキュメントには書いてある大きな違いがあって、それは可変長カラム(VARBINARY, VARCHAR, BLOB, TEXT)の扱いについてです。

可変長カラム(VARBINARY, VARCHAR, BLOB, TEXT)の扱いの違い

ざっくりいうと、従来のAntelopeでは可変長カラムの値は先頭768バイトはローカルページに保存して残りの部分を外部のオーバーフローページに保存するけど、Barracudaは可変長カラムの値をすべて外部のページに保存してローカルページにはそのページへの20バイトのポインタだけ保存します。

これは実際どういう効果が期待できるかという話なんですけど、サイズの大きい可変長カラムをクラスタインデックスから追い出して行のサイズを小さく保てるということは、ページあたりより多くの行を詰め込めるようになるということで、バッファプールにより多くの行を乗せられるようになることを期待できると思います。

また、InnoDBには1つの行の最大サイズはページサイズの半分(innodb_page_sizeのデフォルトは16KBなので約8000バイト)までという制限があるので、Antelopeだと例えばutf8でvarchar(255)のカラムが11個あるテーブルに最大サイズまでデータを入れようとするとエラーになるんですけど、Barracudaだとローカルページにはポインタしか保持してないので余裕で保存できたりします。

CREATE TABLE tbl_compact (
    col01 varchar(255),
    col02 varchar(255),
    col03 varchar(255),
    col04 varchar(255),
    col05 varchar(255),
    col06 varchar(255),
    col07 varchar(255),
    col08 varchar(255),
    col09 varchar(255),
    col10 varchar(255),
    col11 varchar(255)
) CHARACTER SET utf8 ROW_FORMAT=COMPACT;

CREATE TABLE tbl_dynamic (
    col01 varchar(255),
    col02 varchar(255),
    col03 varchar(255),
    col04 varchar(255),
    col05 varchar(255),
    col06 varchar(255),
    col07 varchar(255),
    col08 varchar(255),
    col09 varchar(255),
    col10 varchar(255),
    col11 varchar(255)
) CHARACTER SET utf8 ROW_FORMAT=DYNAMIC;
# error
$ perl -le 'print "INSERT INTO tbl_compact VALUES (",join(q{,},map{q{"}.("あ"x255).q{"}}1..11),")"' | mysql -u root test
ERROR 1118 (42000) at line 1: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
Error (Code 1118): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
Error (Code 1030): Got error 139 from storage engine

# ok
$ perl -le 'print "INSERT INTO tbl_dynamic VALUES (",join(q{,},map{q{"}.("あ"x255).q{"}}1..11),")"' | mysql -u root test
参考

Rails複数DB Casual TalksでMySQLとActiveRecordの話をしてきた

Rails複数DB Casual Talks - connpass お疲れさまでした。

当日は準備不足で資料がただのリンク集だったのを公開用に当社比800%ぐらい加筆したので、当日参加できなかった人が見ても内容がわかるようになってると思います。

MySQLとActiveRecord

@さんのツイートふぁぼってたらみんなが複数DBの話するなかでひとりだけMySQLの話するとか公開処刑なのでは!?と思ったけど、ちょうどみんなestablish_connectionの話でおなかいっぱいでしたからね、楽しんでもらえたようでよかったです。

Rails 複数DB Casual Talks #mdb_casual - Togetterまとめ

MySQLユーザーのためのPostgreSQL対応表

毎回わからなくなってググってるから今度からここに追記していく。

MySQL PostgreSQL
show databases; \l
use dbname \c dbname
show tables; \dt
SELECT * FROM tblname\G \x on
SELECT * FROM tblname;

table / column の情報

MySQL PostgreSQL
show table status from dbname; わからん
show create table [dbname.]tblname; わからん
show full columns from [dbname.]tblname; \d+ tblname もしくは↓で代替
SELECT * FROM information_schema.columns WHERE table_schema = 'dbname' AND table_name = 'tblname' ORDER BY ordinal_position; SELECT * FROM information_schema.columns WHERE table_catalog = 'dbname' AND table_name = 'tblname' ORDER BY ordinal_position;

dump / restore

MySQL PostgreSQL
$ mysqldump --no-data dbname [tblname1 tblname2 ...] > schema.sql $ pg_dump --schema-only [-t tblname1] [-t tblname2] dbname > schema.sql
$ mysqldump --single-transaction dbname > backup.sql $ pg_dump dbname > backup.sql
$ mysql dbname < backup.sql $ psql dbname < backup.sql