データベースを移行するとプリペアードステートメントを利用できなくなってしまいました

Question

###前提、問題点
GCP（Compute Engine + SQL）でWEBサイトを構築しているのですが、
Cloud SQLインスタンスを別のものに移行すると一部のプリペアードステートメントを利用しているクエリが実行できなくなってしまいました。

原因を特定したいのですが、まったく掴めないためお力をお借りしたいです。

###問題勃発までの経緯
新しいSQLインスタンスに変更したいと考え、既存のインスタンスから必要なデータベース情報をエクスポートし、新なSQLインスタンスにインポートさせました。
データの移行は正常に行われたように見えましたが、一部のWEBページ以下のエラー文が表示されました。

Fatal error: Call to a member function bind_param() on boolean

このエラーが発生している箇所のクエリは以下の通りです。

$query = "SELECT test_item.id, GROUP_CONCAT(test_tag.id SEPARATOR ','), GROUP_CONCAT(test_tag.name SEPARATOR ',') FROM test_item LEFT OUTER JOIN test_tag ON test_item.id = test_tag.itemnum WHERE test_item.id = ? LIMIT 1";
$stmt = $mysqli->prepare($query);
$stmt->bind_param('s', $company);
$company = 4;
$stmt->execute();
$stmt->bind_result($t1, $t2, $t3);
while($stmt->fetch()){
}

テーブル構造は以下の通りです。

MySQL [database]> desc test_item;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

MySQL [atabase]> desc test_tag;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(255) | YES  |     | NULL    |                |
| itemnum | int(11)      | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

###不明点①
プリペアードステートメントを利用しているクエリのすべてが同じ状況ではありません。
むしろごく一部のクエリのみです。

// エラーになるクエリ
$query = "SELECT test_item.id, GROUP_CONCAT(test_tag.id SEPARATOR ','), GROUP_CONCAT(test_tag.name SEPARATOR ',') FROM test_item LEFT OUTER JOIN test_tag ON test_item.id = test_tag.itemnum WHERE test_item.id = ? LIMIT 1";

// エラーがでないクエリ
$query = "SELECT test_item.id FROM test_item LEFT OUTER JOIN test_tag ON test_item.id = test_tag.itemnum WHERE test_item.id = ? LIMIT 1";
$query = "SELECT GROUP_CONCAT(test_tag.id SEPARATOR ','), GROUP_CONCAT(test_tag.name SEPARATOR ',') FROM test_item LEFT OUTER JOIN test_tag ON test_item.id = test_tag.itemnum WHERE test_item.id = ? LIMIT 1";

###不明点②
エクスポート元のSQLインスタンスに戻すとエラーが発生しません。
問題なくすべてのページでプリぺアードステートメントを実行できます。
つまり、エクスポート元SQLインスタンスとインポート先SQLインスタンスのデータベースで相違があるということになると思います。

###不明点の検証
データベース構造を新旧インスタンス上で比較するとRow_formatがCompactとDynamicの違いはありましたがその他は見つけられませんでした。
テーブル構造も同じく比較しましたがまったく同じでした。
PHP上のクエリも構文チェックを何度もしましたが問題が見当たりませんでした。これは不明点①からもわかると思います。
問題原因を探ろうと手は尽くしてみたのですが自分の力だけでは解決できませんでした。

Answer 1

$stmt = $mysqli->prepare($query);

の直後に以下の1行を追加してみてください。

echo $mysqli->error;

おそらく、

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

と、表示されるはずです。

これは、MySQL のSQLモードに ONLY_FULL_GROUP_BY が含まれていて、かつ SELECT句に集約関数と非集約カラムが混在しているときに発生するエラーです。
https://dev.mysql.com/doc/refman/5.6/ja/sql-mode.html

ONLY_FULL_GROUP_BY

（中略）
クエリーに集約関数があって GROUP BY 句がない場合、ONLY_FULL_GROUP_BY が有効なときに、クエリーは選択リストまたは ORDER BY リストに非集約カラムを含めることができません。

実際、エラーになるクエリは SELECT 句に集約関数 (GROUP_CONCAT) と非集約カラム (test_item.id) が混在しており、GROUP BY 句がありません。

---

エクスポート元の MySQL とインポート先の MySQL のバージョンは、それぞれ

• エクスポート元 : 5.6 か、それ以前
• インポート先 : 5.7

になっていませんか？

MySQL 5.6 とそれ以前のバージョンの SQLモードは、デフォルトで ONLY_FULL_GROUP_BY を含んでおらず、
MySQL 5.7 では、デフォルトでそれを含んでいます。
https://dev.mysql.com/doc/refman/5.6/ja/faqs-sql-modes.html#idm140652876272144

MySQL 5.6.6 の時点では、デフォルトの SQL モードは NO_ENGINE_SUBSTITUTION です。5.6.6 より前は、デフォルトのモードは空でした (どのモードも有効にされません)。

https://dev.mysql.com/doc/refman/5.7/en/faqs-sql-modes.html#faq-mysql-what-default-mode

The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

---

もし、MySQL のバージョンが上述の通りであれば、インポート先の MySQL の SQLモードをエクスポート元のそれに合わせてやるのがよいでしょう。

Cloud SQL では、SQLモードの確認および変更は、いずれもコンソール画面から実行できるようです。

以下のリンクの
"Setting a MySQL flag"
および
"Viewing current values of MySQL flags"
という項を参照してください。
https://cloud.google.com/sql/docs/mysql-flags

SQLモードは、sql_modeという名称のフラグのようです。

追記

$stmt = $mysqli->prepare($query);
の直後に
echo $mysqli->error;
を設置しても何も表示されませんでした。

では、 echo $mysqli->error; の代わりに以下2行を追加すると、どのように表示されるか確認していただけますか？

var_dump($mysqli);
exit(1);

$stmt->bind_param('s', $company);が実行された行で

Fatal error: Call to a member function bind_param() on boolean

と表示されるのは、$mysqli->prepare($query) の実行に失敗し、変数 $stmt に boolean型の FALSE が代入されているからです。
http://php.net/manual/ja/mysqli.prepare.php

返り値
mysqli_prepare() はステートメントオブジェクトを返します。 エラー時には FALSE を返します。

その場合、$mysqli->error というプロパティに何らかのエラーメッセージが格納されているはずです。

---

MySQL5.6で新たにSQLインスタンスを作成してみましたが、結果は変わりませんでした。。。

もしかすると ONLY_FULL_GROUP_BY とは別の問題の可能性もありますが、
いずれにせよ、エラーメッセージを確認しないことには調査を進めることができません。

頑張ってください。

Comments

[RyuichiTani]

前回に引き続きご回答ありがとうございます。
$stmt = $mysqli->prepare($query);
の直後に
echo $mysqli->error;
を設置しても何も表示されませんでした。
クエリ事態には問題がないようです。
$stmt->bind_param('s', $company);
が実行された行でエラーが発生しています。

ただ、MySQLのバージョンを確認したのですが、エクスポート元は5.6、インポート先は5.7でした。
新たに5.6でSQLインスタンスを作って実行できるか試してみたいと思います。

[RyuichiTani]

MySQL5.6で新たにSQLインスタンスを作成してみましたが、結果は変わりませんでした。。。

[RyuichiTani]

すみません！確認みすでした。
MySQLのバージョンを同じにしたら正しく動作するようになりました！ありがとうございました！！！

[KiyoshiMotoki]

解決できたようで、何よりです。

入れ違いになりましたが、回答を追記させていただきました。
参考がてら、確認してみてください。