1. Home
  2. Webデザイン
  3. php/python
  4. MySQLでONLY_FULL_GROUP_BYのエラーが出たときの対策!
AmazonAmazon クレジットカードAmazon DashAmazon PrimeAmazon.co.jpAmazon
Bluetooth対応のスマート体重計・体組成計が送料込み1290円の大特価!

Bluetooth対応のスマート体重計・体組成計が送料込み1290円の大特価!

現在AmazonではBluetooth対応のスマート体重計・体組成計がなんとクーポンコードを使用することで衝撃の1290円で販売されています。スマート体重計・体組成計はスマホやタ...

DearMob iPhoneマネージャーでiTunesなしで簡単に写真を転送する方法!

DearMob iPhoneマネージャーでiTunesなしで簡単に写真を転送する方法!

前回はDearMob iPhoneマネージャーでiPhoneやiPadを簡単にバックアップする方法を紹介しましたが、今回の記事ではiTunesを使用せずにDearMob iPhoneマネージャーで簡単に写...

BOOK☆WALKERで電子書籍が50%還元されるキャンペーン!9/11までに配信された全作品対象!

BOOK☆WALKERで電子書籍が50%還元されるキャンペーン!9/11までに配信された全作品対象!

BOOK☆WALKERでは、BOOK☆WALKERとニコニコ書籍のスマホアプリの統合1周年を記念した「祝!結婚1周年!感謝のコイン最大50%還元キャンペーン」を実施しています。このキャン...

Audio Input FX LEを期間限定でプレゼントするキャンペーンを実施中!

Audio Input FX LEを期間限定でプレゼントするキャンペーンを実施中!

ニコニコ動画などの運営しているドワンゴのニコトクというサービスで、Amazonなどで通常販売価格8000円の音声マルチエフェクトソフトウェア「Audio Input FX LE」が期間限...

無料版あり!DVDFab動画ダウンローダーでYouTube動画を簡単ダウンロード!

無料版あり!DVDFab動画ダウンローダーでYouTube動画を簡単ダウンロード!

前回の記事では「DVDFab 11 Blu-rayコピー」についての紹介を行いましたが、今回はDVDFab動画ダウンローダー(DVDFab Downloader)について紹介したいと思います。DVDFab 11 ...

電子書籍が50%OFFでさらにコイン50%還元のニコニコカドカワ祭り2019!初回は100%還元!

電子書籍が50%OFFでさらにコイン50%還元のニコニコカドカワ祭り2019!初回は100%還元!

現在、電子書籍ストアのBOOK☆WALKERでは、2019年9月4日までに配信されたKADOKAWA作品が50%コイン還元、それに加えて15,000点以上が50%OFFで購入できるニコニコカドカワ祭...

激安10インチ中華タブレットの「ALLDOCUBE iPlay10 Pro」を使ってみた!

激安10インチ中華タブレットの「ALLDOCUBE iPlay10 Pro」を使ってみた!

最近10インチで激安タブレットを探していたのですが、10インチタブレットでなんと価格が1万円ちょっとというALLDOCUBE iPlay10 Proをゲットしたのでレビューを書きたいと思...

「iZotope Ozone 8 Elements」が無料でゲットできるキャンペーンが実施中!

「iZotope Ozone 8 Elements」が無料でゲットできるキャンペーンが実施中!

現在SONICWIREでは、省機能/高性能なマスタリングソフトウェアiZotope ( アイゾトープ )の「Ozone 8 Elements」を無償でプレゼントするキャンペーンを実施中です!Windows/...

Amazon Echoシリーズに「Echo Studio」「Echo Flex」などの新商品が追加に!

Amazon Echoシリーズに「Echo Studio」「Echo Flex」などの新商品が追加に!

AmazonはAlexa搭載のスマートスピーカーのEchoシリーズの新商品を発表しました。新たに追加されたEchoシリースはHi-Fiスマートスピーカー「Echo Studio (エコースタジオ)」...

NetaGear RSS News

Now Loading

Article

MySQLでONLY_FULL_GROUP_BYのエラーが出たときの対策!

Webデザイン > php/python 
Only_full_group_by_000.png

先日のサーバー移転の際にMySQLのバージョンを5.7にしたのですが、ONLY_FULL_GROUP_BYのエラーが出てしまい、今までのソースコードでは正常に動作しなくなってしまいました。そんなわけで今回はMySQL5.7以降でONLY_FULL_GROUP_BYのエラーが出た時の解決方法を記事の残しておきたいと思います。

今回はレンタルサーバーでの普通のPHPとCakePHP2、CakePHP3の対応策とVPSで使える対応策の両方記しておくので、自分の扱っているサーバーの環境に合っている方を試してみてください。

サーバー移転の際にONLY_FULL_GROUP_BYエラーが発生!


Only Full Group By


MySQL5.7以降ではデフォルトでsql_modeにONLY_FULL_GROUP_BYオプションが設定されるため、MySQLのバージョンを上げた際に今まで動いていたプログラムでもエラーが発生してしまうことがあります。今回の私のサーバー移転ではまさにそれで引っかかりました。

Only Full Group By


ONLY_FULL_GROUP_BYとは、sql_modeのオプションの一つで、GROUP BY句で名前が指定されていない非集約カラムを、選択リスト、HAVING 条件、または (MySQL 5.6.5 以降で) ORDER リストが参照するクエリーを拒否します。これがMySQL5.7以降ではデフォルトでオンになっているため、以前よりより厳格なSQL文が要求されるようになっています。つまり古いソースコードで、group byを使って適当にSelectをしていると、MySQLのバージョンを上げるとこの問題が発生します。

ONLY_FULL_GROUP_BYエラーが出た時の対応策



ONLY_FULL_GROUP_BYエラーが出た際には大きく分けると2つの対応策があります。1つ目がSQL文を修正する方法、2つ目がONLY_FULL_GROUP_BYを無効にする方法です。

時間がある場合はSQL文を一つ一つ見て、GROUP BYを使う際に必要なカラムだけを使うように変更することができますが、急ぎの場合はそうは行きません。今回の私の場合も、大量にあるSQL文をすべて確認して修正する時間はなかったので、ONLY_FULL_GROUP_BYを無効にすることにしました。

root権限あり


VPS


VPSのように自分にroot権限がある場合はmy.cnfを編集してMySQLを再起動するだけでONLY_FULL_GROUP_BYを無効にすることができます。すべてのオプションを無効にするのであればsql_mode=''を入れてもOKですが、それ以外のオプションを残したい場合は残したいオプションを入れておきましょう。

[mysqld]
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION


SET sql_mode


Rental Server


root権限などがない場合は先程のmy.cnfを編集する方法は使えません。私も最初はmy.cnfを編集してmysqlを再起動すればいいじゃんと思っていたのですが、私の使っているさくらインターネットのレンタルサーバーだとmysqlを再起動する権限なんてないので使えませんでした。

そこでmy.cnfで設定するのではなく、mysqlに接続する際にSET sql_modeを送ってセッションの設定を書き換えることで対応することができます。PHPでは、最初の接続の段階でこのようにすればすべてのオプションを無効にできます。

$st = $db->prepare("SET sql_mode=''");
※環境によって変数などは異なります。

CakePHP3


CakePHP3


素のPHPならばセッションの接続時にSET sql_mode=''を入れれば良いわけですが、CakePHPなどのフレームワークを使っている場合は先程のようにコードを追加することはできません。CakePHP3の場合はconfig/app.phpのDatabaseの設定のinitからsql_modeの設定をすることができます。Datasourcesのinitに'init' => ["SET sql_mode = ''"]と設定します。initは接続が作成されたときに、データベースサーバーに送信されるクエリーのリストとなります。

config/app.php
'Datasources' => [
'default' => [
'className' => 'Cake\Database\Connection',
'driver' => 'Cake\Database\Driver\Mysql',
'persistent' => false,
'host' => 'localhost',
'username' => 'my_app',
'password' => 'secret',
'database' => 'my_app',
'encoding' => 'utf8',
'timezone' => 'UTC',
'flags' => [],
'cacheMetadata' => true,
'log' => false,
'init' => ["SET sql_mode = 'NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'"],
'url' => env('DATABASE_URL', null),
],


CakePHP2


CakePHP2


CakePHP2の場合は、Config/database.phpのsettingsにキー/値のペアを持つ配列を指定すると、データベースサーバへの 接続が確立された後、これらがSET コマンドとしてサーバに 送られます。これはMySQL, Postgres, SQLserver のみサポートされています。

Config/database.php
public $default = array(
'datasource' => 'Database/Mysql',
'persistent' => false,
'host' => 'localhost',
'login' => 'user',
'password' => 'password',
'database' => 'database_name',
'prefix' => '',
'encoding' => 'utf8',
'settings' => ["sql_mode" => "'NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'"],
);


今回の個人的感想




今回はサーバー移転の際にMySQLのバージョンを5.7以上にバージョンを上げたら発生したONLY_FULL_GROUP_BYのエラーについての対策方法の紹介でした。今回のサーバー移行でCakePHP2とCakePHP3で作っているサイトも移転させたのですが、いくつかこういうエラーが出てしまいました。

ONLY_FULL_GROUP_BYのエラー対策を調べてみるとmy.cnfを編集してMySQLを再起動するというのが一般的な解決方法とされていますが、今回の私の場合はさくらインターネットのレンタルサーバーを使用しておりmysqlを再起動できない環境でした。VPSだったら問題ないのですが、基本的にレンタルサーバーでユーザーがmysqlを再起動するのは無理なので色々と調べたのですが、CakePHPなどのフレームワークを使ったSET sql_modeの方法などについて言及しているブログはなかったので、結局CakePHPのリファレンスを読んで発見しました。

結構調べても情報が少なかったですし、レンタルサーバーでCaKEPHPを使ってONLY_FULL_GROUP_BYのエラーが出て困ってる人はあまりいないんですかね・・・?少ないかもしれませんが、同じようにMySQLのバージョンを5.7に上げたことでONLY_FULL_GROUP_BYのエラーが出て困っている人の助けになれば幸いです。

Related article

COMMENT TO THIS ENTRY

Most Popular

Now loading...

Social Links

Sponsored Link

Popular Article

Access Ranking

Now Loading...

当ブログにリンクしていているサイトの中でアクセス数の多いサイト上位15位が表示されます。今ならリンクするだけで簡単に上位に表示されます。