Mengatasi MySQL Query Group By Gagal di Eksekusi

Bila anda sedang mencari informasi tentang judul diatas, berarti anda adalah seorang programmer backend khususnya pada Database MySQL. Selamat datang!, diartikel ini kita akan belajar sama-sama.

Sebagaimana yang kita ketahui bahwa sejak MySQL distribusi 5.7 atau lebih baru terdapat banyak fitur baru yang ditambahkan oleh Oracle. Contohnya seperti virtual columns, virtual index, Kolom dengan tipe data JSON yang bisa langsung anda manfaatkan setelah menginstall database MySQL di Komputer anda.

Namun sejak distribusi 5.7.5 selain tambahan fitur diatas anda akan menemukan beberapa perubahan yang biasanya bisa anda lakukan dengan lancar di versi MySQL sebelumnya, menjadi tidak bisa atau gagal dijalankan. Salah satunya adalah penggunaan perintah Group by. Bila anda sudah membuktikannya kemudian baru menemukan tulisan ini maka anda tidak perlu stress, kami akan membantu anda untuk memecahkan masalah tersebut.

Munculnya Eror Group By

Pada pembahasan ini kita akan menggunakan sebuah contoh yang biasanya akan dengan mudah dilakukan group by pada versi MySQL sebelum 5.7. Tetapi setelah versi 5.7 atau lebih baru malah terjadi eror. 

Perintah group by merupakan perintah yang sering digunakan untuk menghasilkan data yang dipilih dari sebuah tabel ataupun lebih (tabel relasi). Data yang dipilih dikelompokkan berdasarkan field atau kolom yang ditunjuk dengan perintah group by tersebut. Bila anda sudah terbiasa melakukan perhitungan seperti sum, count dan lain sebagainya tentu anda sudah sering atau setidaknya pernah menggunakan perintah group by ini.

Penyebab utama gagalnya perintah group by selain karena salah ketik adalah ada setting default yaitu ONLY_FULL_GROUP_BY dalam keadaan on. Seperti pada versi MySQL yang saya instal versi 14.14 distribusi 5.7.25 perintah group by gagal dijalankan. 

Contohnya pada tabel sederhana berikut:

CREATE TABLE `in_gambar` (
  `gambar_id` int(11) NOT NULL,
  `gambar_name` varchar(100) DEFAULT NULL,
  `gambar_category` varchar(25) NOT NULL,
  `gambar_file` varchar(100) DEFAULT NULL,
  `gambar_alt` varchar(100) DEFAULT NULL,
  `gambar_date` datetime DEFAULT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Bila tabel diatas diisi dengan data misalnya seperti berikut:
INSERT INTO `in_gambar` (`gambar_id`, `gambar_name`, `gambar_category`, `gambar_file`, `gambar_alt`, `gambar_date`) VALUES
(1, 'Budi', '9', 'WhatsApp_Image_1_12_58_PM.jpg', 'Kelas 1', '2019-08-20 00:00:00'),

(2, 'Joko', '9', 'WhatsApp_Image_at_1_14_44_PM.jpg', 'kelas 3', '2019-08-20 00:00:00'),

(3, 'Wati', '10', 'WhatsApp_Image_at_1_11_40_PM.jpg', 'kelas 6', '2019-08-20 00:00:00');

Tabel diisi dengan 3 baris data dengan dua jenis kategori yang berbeda. Kemudian perintah group dilakukan untuk menampilkan semua kolom dengan pengelompokan berdasarkan gambar_kategori,. Perintah pada query gagal disebabkan MySQL tidak mau memilih salah satu data dari kategori yang berbeda kemudian dikumpulkan dalam kategori. contohnya seperti berikut:

SELECT * FROM `in_gambar` group by gambar_category

Maka akan tampil eror:
#1055 - Expression
#1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'in_gambar.gambar_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by.

Cara Mengatasi Eror ONLY_FULL_GROUP_BY

Dari eror tersebut tampil incompatible with sql_mode=only_full_group_by.
Oleh sebab itu maka agar perintah group diatas dapat dijalankan anda bisa memilih salah satu dari beberapa cara. Diantaranya sebagai berikut:

1. Menonaktifkan mode Only Full Group By:

Untuk menonaktifkan anda bisa memilih dari beberapa cara berikut:
a. Dengan menggunakan perintah melalui mysql console:

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Dengan cara ini, anda akan langsung dapat menggunakan perintah group by seperti biasanya, tetapi ketika server database di restart maka setting Only full group by akan kembali aktif. Atau dengan kata lain, perintah ini hanya akan berlaku secara temporary selama server database berjalan saat itu.

b. Dengan menggunakan setting variabel database:
Agar memudahkan setting variabel, Anda bisa menggunakan PHPMyadmin untuk melakukannya:
- Buka PHPMyAdmin
- Pilih Variable
- Cari bagian SQL Mode
- Hapus opsi ONLY_FULL_GROUP_BY

Seperti pada cara di poin sebelumnya, dengan cara ini anda akan langsung dapat menggunakan perintah group by seperti biasanya, tetapi perubahan setting ini hanya akan dapat anda nikmati secara temporary selama server database berjalan saat itu saja.

c. Bisa juga dengan melakukan edit pada file my.cnf

Cara yang ketiga ini merupakan cara yang permanen. Artinya sekali anda melakukan perubahan setting, maka hasilnya akan terus bisa anda nikmati walaupun server di restart berkali-kali. Yang perlu anda lakukan adalah setting langsung di file my.cnf. Bila anda pengguna windows anda bisa menemukannya di antara folder mysql, tetapi bila anda pengguna linux anda bisa menemukan setting MySQL ini di salah satu lokasi berikut:

/etc/my.cnf
/etc/mysql/my.cnf
$MYSQL_HOME/my.cnf
[datadir]/my.cnf
~/.my.cnf

kemudian tambahkan baris berikut:
[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

kemudian restart service mysql dengan perintah berikut:
$ mysql service restart

Setelah restart anda akan dapat menjalankan perintah group seperti biasanya.

2. Menggunakan Sub Query

Kita akan menggunakan cara Sub query yang akan langsung di praktekkan pada contoh dibawah ini tanpa mengubah setting sama sekali. Bila anda menggunakan sub query, maka anda akan dapat memahami lebih jelas mengenai penyebab erornya group by ketika setting only_full_group_by aktif.

Mengapa ONLY_FULL_GROUP_BY ketika aktif malah membuat eror perintah group by?

Sebenarnya tidak ada yang salah dengan setting ONLY_FULL_GROUP_BY aktif. Setting ini sebenarnya bermaksud baik, tetapi karena kita sudah terbiasa dengan versi MySQL sebelumnya yang sering menggunakan group by dan hasilnya seperti yang kita ketahui biasanya. 

Ketika setting ini aktif, apa yang biasa kita lakukan hasilnya tidak seperti biasanya akhirnya membuat kita menjadi merasa tidak nyaman. ONLY_FULL_GROUP_BY menjaga agar MySQL menampilkan hasil yang sesuai perintah saja. Tidak otomatis seperti group by pada versi MySQL sebelumnya. Contohnya adalah seperti berikut:

Contoh 1
SELECT gambar_category,count(*) FROM in_gambar group by gambar_category
Perintah diatas sukses dijalankan, maka hasilnya seperti berikut:

gambar_category	count(*)
9	2
10	1

Namun ketika kita tambahkan sebuah field lain misalkan gambar_nama didepan count maka akan terjadi eror:

Contoh 2
SELECT gambar_nama,gambar_category,count(*) FROM in_gambar group_by gambar_category

Penjelasan

ONLY_FULL_GROUP_BY tidak mau bekerja secara otomatis memilih secara random kolom yang ditampilkan untuk data pendamping. Padahal kita tahu pada contoh diatas, baris yang memiliki kategori sama ada dua. Pada kategori 9 ada gambar dengan nama Budi dan Joko, dengan setting aktif maka MYSQL menolak memilih salah satu dari data tersebut.

Perintah diatas bila dijalankan pada setting ONLY_FULL_GROUP_BY tidak aktif maka akan menampilkan hasil seperti versi MySQL sebelumnya. Namun yang akan terjadi sebaliknya apabila setting aktif. Yang terjadi pada ONLY_FULL_GROUP_BY adalah pada kolom yang di group by tersebut tidak ada masalah bila tidak menunjuk suatu kolom tertentu seperti pada contoh 1. 

Maka agar kita mendapat hasil seperti perintah diatas kita dapat menggunakan sub query seperti berikut:
SELECT gambar_name, gambar_category, (select count(*) FROM `in_gambar` where gambar_category= g.gambar_category) as jumlah  from in_gambar g
Jadi yang dihasilkan adalah  nama tetap tampil semua tetapi jumlahnya hasil dari perhitungan kategori yang sama.

Hasil sub query diatas tidak dikelompokkan hasilnya kemudian menunjuk ke salah satu baris data secara random, tetapi hasilnya ditampilkan dengan hasil perhitungan count yang dikelompokkan. Bila anda memang membutuhkan tampilan yang dikelompokkan maka anda perlu merubah query diatas menjadi seperti ini:

SELECT (select gambar_name from in_gambar where gambar_category=g.gambar_category limit 1) as nama, gambar_category, count(*) as jumlah from in_gambar g group BY gambar_category

Perintah diatas sukses dijalankan, maka hasilnya:

nama	gambar_category	jumlah
Budi	9	2
Wati	10	1

Jadi lebih panjang ya dari query group by MySQL yang versi sebelumnya.

Itulah tadi cara mengatasi MySQL Query Group By yang bermasalah akibat aturan Only Full Group By. Demikian tulisan kali ini, semoga bermanfaat.