oranie's blog

旧:iをgに変えると・・・なんだっけ・・・

MySQL5.6で今までのVerでは問題無かったSQL文がエラーになった場合の対処法

追記:記事の文中で5.6のsql_modeデフォルト値について若干実際の挙動と異なる表記をしていました。rpmでinstallすると/usr/my.cnfというのがひょっこりいて、この中に

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

という記述があり、これを/etc/my.cnfと合わせて設定している様です。で、デフォルト値については5.6.6以降はデフォルト値が「The default SQL mode in MySQL 5.6.6 and later is NO_ENGINE_SUBSTITUTION;」でそれ以前のデフォルト値は「MySQL 5.6.5 and earlier, it was empty (no modes set)」となっているようですね。

詳しくは
http://yoku0825.blogspot.jp/2013/03/mysql56sqlmode.html
を見て頂ければ詳細な解説があります。@yoku0825 さん何度も解説ありがとうございました!!

                                                                                                                                                • -

今日同僚がCentOS6系+MySQL5.6でGrowthForecastを構築している時に、なぜかgrowthforecastを起動してもエラーになる問題が発生した。
正しくinstallも出来ており、MySQLの権限とかも問題無い。ただし、なぜか一部のテーブルは作成されているけど、一部のテーブルが作られていなかった。
で、実際に作成するSQLを見て直接流し込んで見ると

CREATE TABLE IF NOT EXISTS graphs (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 service_name VARCHAR(255) NOT NULL COLLATE utf8_bin,
 section_name VARCHAR(255) NOT NULL COLLATE utf8_bin,
 graph_name VARCHAR(255) NOT NULL COLLATE utf8_bin,
 number BIGINT NOT NULL DEFAULT 0,
 mode VARCHAR(255) NOT NULL DEFAULT 'gauge',
 description VARCHAR(255) NOT NULL DEFAULT '',
 sort INT UNSIGNED NOT NULL DEFAULT 0,
 gmode VARCHAR(255) NOT NULL DEFAULT 'gauge',
 color VARCHAR(255) NOT NULL DEFAULT '#00CC00',
 ulimit BIGINT NOT NULL DEFAULT 1000000000,
 llimit BIGINT NOT NULL DEFAULT 0,
 sulimit BIGINT NOT NULL DEFAULT 100000,
 sllimit BIGINT NOT NULL DEFAULT 0,
 type VARCHAR(255) NOT NULL DEFAULT 'AREA',
 stype VARCHAR(255) NOT NULL DEFAULT 'AREA',
 meta TEXT NOT NULL DEFAULT '',
 created_at INT UNSIGNED NOT NULL,
 updated_at INT UNSIGNED NOT NULL,
 PRIMARY KEY (id),
 UNIQUE (service_name, section_name, graph_name)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1101 (42000): BLOB/TEXT column 'meta' can't have a default value

という形で

 meta TEXT NOT NULL DEFAULT '',

の所でデフォルト値が設定されていてエラーになっている。で、同じVerのGrowthForecastをMySQL5.5系でも構築していたので、改めて同じSQLをMySQL5.5系で実行すると問題なくテーブルが作成された。

その後調べると

この挙動により「あーこれ5.6系からBLOB/TEXT型の仕様が変わったんだわーもう間違いないわードキュメントにも書いてあるわー俺くらいのスーパーハカーだから気づけたわー」
http://dev.mysql.com/doc/refman/5.6/en/blob.html にBLOB and TEXT columns cannot have DEFAULT values. という記述があり。)
として意気揚々とTwitterにつぶやく訳ですよ。

で即座にリプライ貰う訳ですね。

という素晴らしい回答貰いました。完璧なピエロでしたね、僕。

要するに

僕の書いた5.6系になった事による仕様変更とかじゃなくて、sql_modeのデフォルト設定が5.5系と5.6系で違う訳ですね。で、実際に確認するとmy.confには明示的にsql_modeを記述していなくても

5.5系

mysql> SELECT @@GLOBAL.sql_mode;
+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

5.6系

mysql> SELECT @@GLOBAL.sql_mode;
+--------------------------------------------+
| @@GLOBAL.sql_mode                          |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

という結果になりました。上記の設定は5.1系ドキュメントですが、
http://dev.mysql.com/doc/refman/5.1/ja/server-sql-mode.html

STRICT_TRANS_TABLES

指定された値をトランザクション テーブルに挿入できない場合、クエリの実行を中断する。非トランザクション テーブルでは、値が 1 行ステートメントの場合、または複数ステートメントの最初の行である場合に、クエリを中断する。詳細は、このセクションでの後述を参照のこと。

NO_ENGINE_SUBSTITUTION

デフォルトのストレージ エンジンの自動置換 (substitution) を防ぐ。これは、CREATE TABLE のようなステートメントが、無効化した、またはコンパイルしたストレージ エンジンを指定するときのこと。エラーで知らせる。

という形で、この為に本来使用できないBLOB/TEXT型でdefalut値をcreate tableの時に設定した為にエラーになりテーブルが作成されず、最終的にアプリでエラーになったという訳です。なので、もし同じように今までは問題無かったSQLが5.6系で問題になった場合は、まず構文として問題が無くても、そのデータ型などでは本当に問題が無い命令文かを確認するなどが必要な訳ですね。


で、最終的には勉強させて頂いた内容を元に、5.6系でもGrowthForecast使えるようにISSUEの報告とpull reqはしました。マージされていれば、エラーにならずに使えるはず。