ip2longとMySQLとアーキテクチャとわたし

時代は移り変わりアーキテクチャは32bitから64bitになり、MySQLは5.1から5.6に進化し、ついでにPHPも6すっとばして7.0になる始末。

バージョンアップ、コードそのまま、環境混在、何も起きないはずもなく。

ことの発端

わりと昔からIPアドレスをip2longで整数値にしてDBに保存していたけど、MySQL5.6にしてみたらエラーが出てデータが入らなかったのでよく見てみたらちょっと前からちゃんと入ってないんじゃね疑惑。

調査

環境

Dockerでぽちぽち。

1. 32bit + PHP 5.6 環境
   • 32bit/debian のイメージに apt-get install php5
2. 64bit + PHP 5.6 環境
   • php:5.6 のイメージをそのまま利用 (debian8.6)
3. 64bit + PHP 7.0 環境
   • ubuntu:16.04 のイメージに apt-get install php (PHP7が入る)

便利な世の中になったものだ。

int型の範囲のおさらい

PHP

32bit では -2147483648 - 2147483647
64bit では -9223372036854775808 - 9223372036854775807

MySQL

SIGNED で -2147483648 - 2147483647
UNSIGNED で 0 - 4294967295

ip2longの結果が違う問題

IPアドレスを整数表現した際に、64bitではいくらでも表現してくれますが、32bitアーキテクチャでは最大値が足りずに負の数値に手を出し始めます。

ip2long.php

#!/usr/bin/env php
<?php
$ips = array('0.0.0.0', '127.0.0.1', '127.255.255.255', '128.0.0.0', '192.168.0.1', '255.255.255.255');
foreach ($ips as $ip) {
    var_dump(ip2long($ip));
}

$ip	32bit PHP5.6	64bit PHP5.6	64bit PHP7.0
0.0.0.0	int(0)	int(0)	int(0)
127.0.0.1	int(2130706433)	int(2130706433)	int(2130706433)
127.255.255.255	int(2147483647)	int(2147483647)	int(2147483647)
128.0.0.0	int(-2147483648)	int(2147483648)	int(2147483648)
192.168.0.1	int(-1062731775)	int(3232235521)	int(3232235521)
255.255.255.255	int(-1)	int(4294967295)	int(4294967295)

PHPでは数値がintの最大値を超えた場合に勝手にfloat扱いになってくれる小さな親切余計な(ryがありますが、ip2longさんは頑なにintを返してきます。

MySQLへ数値を入れる時の問題

MySQLを5.5以前から5.6へ上げたときの悲痛な叫びはググればいろいろ出てきますが、叫ぶことができただけマシではないかということも多々あります。

MySQL5.6では整数値範囲外のデータをINSERTしようとすると(デフォルト設定では)エラーになりますが、それ以前ではしれっと丸められて成功します。(一応INSERTしたときにWARNINGは出てるけど、普段からWARNINGを意識したプログラムを書く人がいるだろうか、いやいない。)

sample.sql

DROP TABLE IF EXISTS IntTest;
CREATE TABLE `IntTest` (`inserted_value` varchar(20) NOT NULL, `id_unsigned` int(10) unsigned NOT NULL, `id_signed` int(11) NOT NULL) ENGINE=InnoDB;
SET sql_mode="NO_ENGINE_SUBSTITUTION";
INSERT INTO IntTest VALUES ("-2147483648", -2147483648, -2147483648);
INSERT INTO IntTest VALUES ("0", 0, 0);
INSERT INTO IntTest VALUES ("2147483647", 2147483647, 2147483647);
INSERT INTO IntTest VALUES ("4294967295", 4294967295, 4294967295);

こちらのSQLをMySQL5.1系で実行するとこんなかんじ。

＠MySQL5.1

mysql> select * from IntTest;
+----------------+-------------+-------------+
| inserted_value | id_unsigned | id_signed   |
+----------------+-------------+-------------+
| -2147483648    |           0 | -2147483648 | # INT UNSIGNED にマイナスは入らない
| 0              |           0 |           0 |
| 2147483647     |  2147483647 |  2147483647 |
| 4294967295     |  4294967295 |  2147483647 | # INT (SIGNED) の最大超えた。
+----------------+-------------+-------------+
4 rows in set (0.00 sec)

それぞれ範囲を超えた数値は近い数字になってます。
WARNING辿ればこんなかんじのがちゃんとでてます。

Out of range value for column 'id_signed'

やってしまいましたなぁ。

対策

というわけで今後も何があるのかわからないので、環境が混在していてもちゃんとデータ入るようにしたい所存。

SQLでINET_ATON()を利用する

プログラム上ではip2long()を利用しないでおき、最終的にSQLにするときに INET_ATON を利用すればPHP依存はなくなりそう。MySQL依存はしそう。
この場合はカラムの型を INT UNSIGNED にする必要がある。

ただ、よくあるORMではsave()するとプロパティの値をそのまま保存してしまうため、生のSQLを流してるでもない限りMySQL関数を利用したINSERTやUPDATEは難しいかも。

SQL生成サンプル

$sql = sprintf(
  'INSERT INTO Record (ip_address, created_at) VALUES (INET_ATON("%s"), NOW());',
  $_SERVER['REMOTE_ADDR']
);

printfの%uを利用してみる

＠32bit+PHP5.6

~# php -r "var_dump(ip2long('192.168.0.1'));"
int(-1062731775)
~# php -r "var_dump(sprintf('%u', -1062731775));"
string(10) "3232235521"

これで符号なし整数値を指定できる(型は文字列だけど)。
この場合、64bitの方では最初から3232235521を取得できるのでsprintfする必要はないけど、環境に左右されないコードにするならば、

$record->ip_address = sprintf('%u', ip2long($_SERVER['REMOTE_ADDR']));

こんなかんじでしょうか。

BIGINTにしてしまう

MySQL側のカラムをBIGINT (SIGNEDね) にしてしまえば多少の投げやり感はあるけどまぁデータ破損とかはなくなる。
ただし、環境が混在した際に同一のIPアドレスを示す値が２パターン存在してしまうため、特定のIPを抜き出したいとかIPごとにGROUP BYしたいとかあったときに小細工が必要になったりするので注意が必要。

あとなんかすごく容量もったいない感ある。

文字列最強説

ここまでいろいろ書きましたが、数値に変換とかしないで VARCHAR(15) でそのまま入れたら解決やんけという説を私は強く推します。

今更ですが、 127.0.0.1 みたいなドット区切りの形式のことをドットアドレスって言うそうです。

さらに手を掛けるなら・・。

どうしても数値で保存しなければならない場合でも、実際のプログラム上では文字列の方を利用する機会が多いでしょうし、実装まで手を入れられるのであれば、ゲッタ・セッタ等を介して処理を表側から意識しなくて良くするとみんなハッピー。

/**
 * @property ...
 * @property int unsigned $ip_address
 * @property ...
 */
class Record extends SomeORM
{
  :

  public function getIpAddress()
  {
    return long2ip($this->ip_address);
  }

  /**
   * @param string $ip_address '127.0.0.1' とか
   */
  public function setIpAddress($ip_address)
  {
    $this->ip_address = spritnf('%u', ip2long($ip_address));
  }

  :
}

こうしてプログラムの各所からはセッタを介して代入しておけば、仮にまた何かの環境依存系の問題が発生したときでも、setIpAddress() 内で場合分けしたりごにょごにょすればすぐ解決できます。

更に深読みしてIPv6が飛んでくるようになったりしたらとか考えると、ip2long() ではなくて inet_pton() 等を利用してDBのカラムを BLOB 形式にするとかで対応できそうな気もします。

既存データや本番で稼働しているデータの移行みたいな話になってくるとまた別の話に(日記はここで終わっている)