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;
```

```mysql: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。 

```mysql: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 共に、

```mysql
> 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は計算によるオーバーフローは考慮していないようだ。
 
```mysql: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のバグなそうです。）
２つ目のクエリがオーバーフロー
 
```mysql: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
 
検証テーブル

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

```mysql: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;`をすれば解決できる。

```mysql: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);
```

```mysql: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
```

```mysql: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のご利用は計画的に。。。）