MariaDBとMySQLの初期設定(mysql.conf)の違いかもしれないが、ハマったのでメモ
プリペアードステートメントのバインドパラメーターにnow()などの関数を渡した場合
MySQLは実行でき、MariaDBは実行できないという珍現象に遭遇
プリペアードステートメント
SELECT h.follower, h.updated_at
from twitter_status_histories as h,
(SELECT id FROM bases WHERE twitter_account = 'usagi_anime' order by id desc limit 1) b
where h.bases_id = b.id AND h.updated_at < ? order by h.updated_at desc limit 100
実際のクエリ
SELECT h.follower, h.updated_at
from twitter_status_histories as h,
(SELECT id FROM bases WHERE twitter_account = 'usagi_anime' order by id desc limit 1) b
where h.bases_id = b.id AND h.updated_at < 'now()' order by h.updated_at desc limit 100
MySQL 5.6 結果
follower updated_at
51345 2014-09-23 18:53:27
50606 2014-09-14 14:55:02
50607 2014-09-14 14:50:04
46350 2014-08-11 16:44:05
MariaDB 5.5.37 結果
Empty set, 1 warning (0.00 sec)
nowのクオーテーションをはずすと
SELECT h.follower, h.updated_at
from twitter_status_histories as h,
(SELECT id FROM bases WHERE twitter_account = 'usagi_anime' order by id desc limit 1) b
where h.bases_id = b.id AND h.updated_at < now() order by h.updated_at desc limit 100
結果
| 133727 | 2015-11-07 12:00:06 |
| 133717 | 2015-11-07 11:30:07 |
| 133707 | 2015-11-07 11:00:07 |
| 133701 | 2015-11-07 10:30:06 |
うーんこの・・