MySQL5.0で動いていたシステムを、MySQL5.6にバージョンアップした時に、
MySQLの互換性の無い仕様変更に伴うクエリの修正が大量にあったので、まとめた。
問題1:unsigned int に負の値が入った時の、アンダーフローの仕様変更
MySQL5.5 から仕様変更された。
検証テーブル
mysql
CREATE TABLE eudyptes_unsigned_int (count int(11) unsigned) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> 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。
> 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では、エラー。
対策方法1: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となり、互換性が保たれた。
(そもそもアンダーフローしてるのがおかしいというツッコミは無しで。)
対策方法2:signedにcastする
新規でコードを書くなら、計算途中でcast(*** as signed)
でキャストすれば、この問題は解決する。
ってか、新しくコード書くならsql_modeとか考えずに、キャストすれ。
解決出来ない問題
BIG INTのunsignedの足し算によるオーバーフローはMySQL5.1から仕様変更された。
この問題はsql_mode = 'NO_UNSIGNED_SUBTRACTION';
では解決できない。
このSQL_MODEは計算によるオーバーフローは考慮していないようだ。
> 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 |
+-------------------------------------------+
1つ目のクエリがエラー(MySQL5.0のバグなそうです。)
2つ目のクエリがオーバーフロー
> 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)'
1つ目のクエリがワーニング(数値の丸められている)
2つ目のクエリがMySQL5.1ではエラー
問題2:外部キー制約を設けた親テーブルで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));
> 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;
をすれば解決できる。
> 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してね と書いてあります。
問題3: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);
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
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
問題4:lock_wait_timeout のデフォルト値が1年に
lock_wait_timeoutのデフォルト値が1年になったため、短くする必要が有る。
問題5:レプリケーションフォーマットの差異
mysql5.0ではstatementしか存在しない。mixベースで運用しようとすると問題が生じる。
statementベースの場合、以下のようなテンポラリーテーブルを使ったクエリを途中で止めた場合に問題が生じるので、
レプリケーション停止のタイミングは注意が必要。
問題6:スロークエリ化
VersionUpによって、パフォーマンス向上したクエリと、パフォーマンス劣化したクエリがそれぞれある。
- ex. 数百秒かかっていたスロークエリが一掃された。
- ex. 使用するindexが変わり、極端にスロークエリ化して悪化した。
暫定回避としては、FORCE INDEXを利用するといい。(但しFORCE INDEXのご利用は計画的に。。。)