MySQL v5.0からv5.6へバージョンアップした時の互換性の無いクエリ仕様変更まとめ

MySQL5.0で動いていたシステムを、MySQL5.6にバージョンアップした時に、
MySQLの互換性の無い仕様変更に伴うクエリの修正が大量にあったので、まとめた。

問題１：unsigned int に負の値が入った時の、アンダーフローの仕様変更

MySQL5.5 から仕様変更された。

検証テーブル
mysql
CREATE TABLE eudyptes_unsigned_int (count int(11) unsigned) ENGINE=InnoDB DEFAULT CHARSET=utf8;


MySQL5.5

> set @@sql_mode = '';

> insert into eudyptes_unsigned_int(count) values (0);
Query OK, 1 row affected (0.00 sec)

> update eudyptes_unsigned_int set count = count - 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1
Warning (Code 1264): Out of range value adjusted for column 'count' at row 1

> select * from eudyptes_unsigned_int;
+------------+
| count      |
+------------+
| 4294967295 |
+------------+
1 row in set (0.00 sec)

> select cast(0 as unsigned) - 1;
+-------------------------+
| cast(0 as unsigned - 1 |
+-------------------------+
|    18446744073709551615 |
+-------------------------+
1 row in set (0.01 sec)

mysql5.5では成功。　但し、-1ではなく、オーバーフローして4294967295。

MySQL5.6

> set @@sql_mode = '';

> insert into eudyptes_unsigned_int(count) values (0);
Query OK, 1 row affected (0.01 sec)

> update eudyptes_unsigned_int set count = count - 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`db`.`eudyptes_unsigned_int`.`count` - 1)'

> select cast(0 as unsigned) - 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'

> select cast(18446744073709551615 as unsigned) +1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(18446744073709551615 as unsigned) + 1)'

=> MySQL5.6では、エラー。

対策方法１:set sql_mode = 'NO_UNSIGNED_SUBTRACTION';

MySQL5.5 MySQL5.6 共に、

> set sql_mode = 'NO_UNSIGNED_SUBTRACTION';

> insert into eudyptes_unsigned_int(count) values (0);

> update eudyptes_unsigned_int set count = count - 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1
Warning (Code 1264): Out of range value adjusted for column 'count' at row 1

> select * from eudyptes_unsigned_int;
+-------+
| count |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

片方はアンダーフロー、片方はエラーになっていたのが、両方0となり、互換性が保たれた。
（そもそもアンダーフローしてるのがおかしいというツッコミは無しで。）

対策方法２：signedにcastする

新規でコードを書くなら、計算途中でcast(*** as signed)でキャストすれば、この問題は解決する。
ってか、新しくコード書くならsql_modeとか考えずに、キャストすれ。

解決出来ない問題

BIG INTのunsignedの足し算によるオーバーフローはMySQL5.1から仕様変更された。
この問題はsql_mode = 'NO_UNSIGNED_SUBTRACTION'; では解決できない。
このSQL_MODEは計算によるオーバーフローは考慮していないようだ。

MySQL5.0

> select cast(18446744073709551626 as unsigned);
+----------------------------------------+
| cast(18446744073709551626 as unsigned) |
+----------------------------------------+
|                   18446744073709551615 |
+----------------------------------------+
1 row in set, 1 warning (0.00 sec)
Error (Code 1292): Truncated incorrect DECIMAL value: ''

> select cast(18446744073709551615+1 as unsigned);
+-------------------------------------------+
| cast(18446744073709551615+1 as unsigned) |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+

１つ目のクエリがエラー（MySQL5.0のバグなそうです。）
２つ目のクエリがオーバーフロー

mysql5.1

> select cast(18446744073709551626 as unsigned);
+----------------------------------------+
| cast(18446744073709551626 as unsigned) |
+----------------------------------------+
|                   18446744073709551615 |
+----------------------------------------+
1 row in set, 1 warning (0.01 sec)
Warning (Code 1292): Truncated incorrect DECIMAL value: ''

> select cast(18446744073709551615+1 as unsigned);
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(18446744073709551615 + 1)'

１つ目のクエリがワーニング(数値の丸められている)
２つ目のクエリがMySQL5.1ではエラー

問題２:外部キー制約を設けた親テーブルでtruncate不可能

• mysql5.5.6は、子テーブルの参照が無ければ親テーブルのtruncateは可能
• mysql5.6は、子テーブルの参照があるないに関わらず、truncateは不可能

どうやら、mysql5.5.7から仕様が変わったらしい。　⇒　http://bugs.mysql.com/bug.php?id=58788

検証テーブル

create table eudyptes_truncate1 (id int, index(id));
create table eudyptes_truncate2 (id3 int, foreign key (id3) references eudyptes_truncate1(id));

MySQL5.6

> select * from eudyptes_truncate_test1;
Empty set (0.00 sec)

> select * from eudyptes_truncate_test2;
Empty set (0.01 sec)

> truncate eudyptes_truncate_test1;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`db`.`eudyptes_truncate_test2`, CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`id3`) REFERENCES `db`.`eudyptes_truncate_test1` (`id`))

=> 例えtest2から空だったとしても、test1に対してtruncateがmysql5.6では不可能！

どうしてもmysql5.6で外部キー制約を設けた親テーブルに対してtruncateをしたければ、set foreign_key_checks=0;をすれば解決できる。

MySQL5.6

> set foreign_key_checks=0;                                                                                                                                                          
Query OK, 0 rows affected (0.01 sec)

> truncate eudyptes_truncate1;
Query OK, 0 rows affected (0.02 sec)

> set foreign_key_checks=1;
Query OK, 0 rows affected (0.00 sec)

但し、foreign_key_checks=0にすると、外部キー制約はDBレベルで無効化されるので、重要な環境ではやめましょう。
delete tableして下さい。

• 参考サイト：http://bugs.mysql.com/bug.php?id=54678 外部キー参照テーブルは、truncate出来ないからdeleteしてね　と書いてあります。

問題３：timestampに暗黙のデフォルト値がつかなくなる

mysql5.6では、timestampの暗黙的な属性追加がされなくなる。
mysql5.5では、属性を付けなくても暗黙的に付与される。

対策としては、

• NULL or NOTNULL を必ずつける
• 使用有無に問わず、default値を必ずつける。（CURRENT_TIMESTAMPでOK。）

なお、MySQL5.6では、explicit_defaults_for_timestamp というオプションがあり、
今まで通り補完をすることも可能ですが、暗黙的な属性値はそのうち廃止されるようで、非推奨オプションとなっている。

検証テーブル

create table eudyptes1 ( time timestamp);
create table eudyptes2 ( time timestamp NULL);
create table eudyptes3 ( time timestamp NOT NULL);
create table eudyptes4 ( time timestamp NULL default CURRENT_TIMESTAMP);
create table eudyptes5 ( time timestamp NOT NULL default CURRENT_TIMESTAMP);

MySQL5.5のdump結果

CREATE TABLE `eudyptes1` (`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `eudyptes2` (`time` timestamp NULL default NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `eudyptes3` (`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `eudyptes4` (`time` timestamp NULL default CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `eudyptes5` (`time` timestamp NOT NULL default CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8

MySQL5.6のdump結果

CREATE TABLE `eudyptes1` (`time` timestamp NULL DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `eudyptes2` (`time` timestamp NULL DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `eudyptes3` (`time` timestamp NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `eudyptes4` (`time` timestamp NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `eudyptes5` (`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8

問題４：lock_wait_timeout　のデフォルト値が１年に

lock_wait_timeoutのデフォルト値が１年になったため、短くする必要が有る。

問題５：レプリケーションフォーマットの差異

mysql5.0ではstatementしか存在しない。mixベースで運用しようとすると問題が生じる。
statementベースの場合、以下のようなテンポラリーテーブルを使ったクエリを途中で止めた場合に問題が生じるので、
レプリケーション停止のタイミングは注意が必要。

• 参考：http://yoku0825.blogspot.jp/2013/06/create-temporary-table.html

問題６：スロークエリ化

VersionUpによって、パフォーマンス向上したクエリと、パフォーマンス劣化したクエリがそれぞれある。
- ex. 数百秒かかっていたスロークエリが一掃された。
- ex. 使用するindexが変わり、極端にスロークエリ化して悪化した。

暫定回避としては、FORCE INDEXを利用するといい。（但しFORCE INDEXのご利用は計画的に。。。）