「MySQL」カテゴリーアーカイブ

MySQL, PHP, Ruby

UTF-8の4バイト文字のバリデーション

コメントする

MySQLの文字セットがutf8の場合、utf-8で符号化すると4バイトになる文字（😁のような絵文字など）をセットすると、SQLモード（sql_mode）が厳密モード（STRICT_ALL_TABLES または STRICT_TRANS_TABLES のいずれかが有効）でない場合、その文字以降が切り捨てられてしまう。（警告は発生する）

4バイトUTF-8文字に対応するためには、CHARACTER SET に utf8mb4（COLLATE に utf8mb4_unicode_520_ci など utf8mb4_xxx） を指定したカラムを使用し、接続文字セットも utf8mb4 を使用する必要がある。

ちなみにRailsでは、MySQLのカラムの文字セットがutf8の場合に4バイトUTF-8文字をセットしようとすると、以下のようなエラーが発生するので、気付かないうちに文字列が切り捨てられてしまうことはない。

An ActiveRecord::StatementInvalid occurred in news#update:

Mysql2::Error: Incorrect string value: '\xF0\x9F\x98\x80\x0D\x0A' for column 'description' at row 1: UPDATE `news` SET `description` = '😀\r\n' WHERE `news`.`id` = 2
app/controllers/news_controller.rb:98:in `update'

これは特に指定していない場合、AbstractMysqlAdapter#configure_connection で、STRICT_ALL_TABLES がセッションのSQL_MODEに追加されているから。（NO_AUTO_VALUE_ON_ZERO も追加される。）

これは以下のようにして確認できる。

  • mysqlクライアントで確認 
    mysql> show variables like 'sql_mode';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| sql_mode      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 row in set (0.00 sec)
  • 同じデータベースに対して、rails consoleで確認 
    > con = ActiveRecord::Base.connection
> con.select_all("SHOW VARIABLES LIKE 'sql_mode'")
   (0.8ms)  SHOW VARIABLES LIKE 'sql_mode'
 => #<ActiveRecord::Result:0x00007fc6ca533728 @columns=["Variable_name", "Value"], @rows=[["sql_mode", "NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION"]], @hash_rows=nil, @column_types={}>

対処方法

カラムの CHARACTER SET を utf8mb4 、COLLATE を utf8mb4_xxx に変換して、接続文字セットに utf8mb4 を使用すればよいが、何らかの事情でカラムを utf8mb4 に変換できない場合は、黙って4バイトUTF-8文字以降が切り捨てられるといろいろとまずいので、4バイトのUTF-8文字をバリデーションではじくことになるだろう。

UTF-8にエンコードすると4バイトになるUnicode文字の範囲は、U+10000からU+10FFFFである。

PHPでの例

if (preg_match('/[\x{10000}-\x{10FFFF}]/u', $s) { /* ... */ }

if (preg_match('/[\xF0-\xF7][\x80-\xBF][\x80-\xBF][\x80-\xBF]/', $s)) { /* ... */ }

preg_match_all('/[\x{10000}-\x{10FFFF}]/u', $s, $matches);
// $matches[0]に4バイトutf-8の文字の配列が格納される。

Rubyでの例

if /[\u{10000}-\u{10FFFF}]/ =~ s
  # ...
end

chars = s.scan(/[\u{10000}-\u{10FFFF}]/)
# charsに4バイトutf-8の文字の配列が格納される。
MySQL

MySQLのスロークエリログを取得する

コメントする
  • my.cnf（MySQL 5.1） 
    [mysqld]
# スロークエリログの出力先
log_slow_queries = /var/log/mysql-slow.log
# これより実行に時間がかかる SQL ステートメントをロギング(秒)
long_query_time = 2
# インデックスを使用しないクエリをロギング
#log-queries-not-using-indexes
# 管理ステートメントもロギング
#log-slow-admin-statements
  • my.cnf（MySQL 5.5以降） 
    [mysqld]
# スロークエリログを有効にする
slow_query_log=1
# スロークエリログの出力先
slow_query_log_file=/var/log/mysql-slow.log
# これより実行に時間がかかる SQL ステートメントをロギング(秒)
long_query_time = 2
# インデックスを使用しないクエリをロギング
#log-queries-not-using-indexes
# 管理ステートメントもロギング
#log-slow-admin-statements
  • 空のログファイルを作成し、パーミッションを設定後、mysqldを再起動する。 
    # touch /var/log/mysql-slow.log
# chown mysql:mysql /var/log/mysql-slow.log
# service mysqld restart
  • ログをクリアする場合は、 
    # :> /var/log/mysql-slow.log
MySQL

MySQLのクエリキャッシュ

コメントする

クエリキャッシュの設定

my.cnf

[mysqld]
query_cache_size=64M
query_cache_type=2

query_cache_type には、以下のいずれかを指定できる。
0: (OFF) キャッシュを行わない、または キャッシュした結果の読み出しを行わない。
1: (ON) SELECT SQL_NO_CACHE で始まるステートメント以外のキャッシュ。
2: (DEMAND) SELECT SQL_CACHE で始まるステートメントだけのキャッシュ。

クエリキャッシュのステータスを確認

mysql> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        | 
| Qcache_free_memory      | 66674696 | 
| Qcache_hits             | 8        | 
| Qcache_inserts          | 41       | 
| Qcache_lowmem_prunes    | 0        | 
| Qcache_not_cached       | 1145     | 
| Qcache_queries_in_cache | 41       | 
| Qcache_total_blocks     | 88       | 
+-------------------------+----------+
8 rows in set (0.00 sec)

クエリキャッシュのデフラグメント

mysql> FLUSH QUERY CACHE;

クエリキャッシュからクエリ結果を削除

mysql> RESET QUERY CACHE;

MySQL :: MySQL 5.1 リファレンスマニュアル :: 4.13 MySQL クエリ キャッシュ

MySQL

innodb_log_file_sizeの変更

1件のコメント

innodb_log_file_size を変更する場合は、ログファイルをいったん削除して再作成する必要がある。
具体的には以下のような手順になる。

my.cnfを編集

/etc/my.cnf

[mysqld]
innodb_buffer_pool_size=512M
innodb_log_file_size=128M

シャットダウンと同時にテーブルスペースへデータを反映させるよう設定

$ mysql -u root -p
mysql> SET GLOBAL innodb_fast_shutdown=0;

mysqldを停止

# /etc/init.d/mysqld stop

ログをリネーム

# cd /var/lib/mysql/
# mv ib_logfile0 ib_logfile0.bak
# mv ib_logfile1 ib_logfile1.bak

mysqldを開始

# /etc/init.d/mysqld start

MySQLの「innodb_buffer_pool_size」と「innodb_log_file_size」の設定 - FlatLabs

MySQL

mysqldumpで出力したファイルのインポートで error at line xxx: unknown command '\z'

コメントする

mysqldumpで出力したダンプファイルをmysqlでインポート中に、

error at line 301: unknown command '\z'

というエラーになった。
エラーに出ているダンプファイルの301行目は、longblobのカラムを持つテーブルへのデータのINSERT。
mysqldumpで --hex-blob オプションを付けて出力するようにすると、エラーを回避できた。
ただしダンプしたファイルのサイズは大きくなる。
Mysqldump and Error "Unknown command '\0′" | Mister Nerd's Blog
MySQL :: MySQL 5.1 リファレンスマニュアル :: 7.12 mysqldump -- データベースバックアッププログラム

--hex-blob
16進変換表記法を使用しているバイナリカラムをダンプします(例えば、'abc'は0x616263となります)。影響を受けるデータタイプはBINARY、VARBINARY、BLOB、そしてBITになります。

MySQL, PHP

MacPortsでphp5−mysqlをインストール

コメントする 
$ sudo port install php5-mysql
Password:
--->  Computing dependencies for php5-mysql
--->  Fetching php5-mysql
--->  Verifying checksum(s) for php5-mysql
--->  Extracting php5-mysql
--->  Configuring php5-mysql
--->  Building php5-mysql
--->  Staging php5-mysql into destroot
--->  Installing php5-mysql @5.3.0_0+mysqlnd
To use mysqlnd with a local MySQL server, edit /opt/local/etc/php5/php.ini and set
mysql.default_socket, mysqli.default_socket and pdo_mysql.default_socket
to /opt/local/var/run/mysql5/mysqld.sock
--->  Activating php5-mysql @5.3.0_0+mysqlnd
--->  Cleaning php5-mysql

　表示されているメッセージにあるように、php.iniにdefault_socketを設定するのを忘れないこと。

To use mysqlnd with a local MySQL server, edit /opt/local/etc/php5/php.ini and set
mysql.default_socket, mysqli.default_socket and pdo_mysql.default_socket
to /opt/local/var/run/mysql5/mysqld.sock