IN SELECT plus EXISTS

Environment

1. MySQL 5.7.27
2. Centos 6.10

mysql> DELETE FROM CSV_DATA
WHERE FILE_ID IN ( SELECT FILE_ID FROM CSV_FILE WHERE FILE_STATUS = 7)
LIMIT 100000

129 sec

Nampak-nya seluruh row akan di-scan dan nilai FILE_ID pada setiap setiap row akan dibandingkan dengan hasil dari IN SELECT .

mysql> DELETE FROM CSV_DATA
WHERE EXISTS ( SELECT FILE_ID FROM CSV_FILE WHERE FILE_STATUS = 7 )
  AND FILE_ID IN ( SELECT FILE_ID FROM CSV_FILE WHERE FILE_STATUS = 7 )
LIMIT 100000

0 sec

Nilai dari EXIST akan di-evaluate dan bila row tidak ditemukan maka perbandingan selanjutkan tidak di-evaluate.

Dalam case ini, LIMIT digunakan untuk mempersingkat waktu locking terhadap table CSV_DATA. Statement JOIN tidak dapat digunakan karena tidak compatible dengan LIMIT .

Remove MySQL root password

Environment

1. Operation System: Ubuntu 18.04.3 LTS
2. MySQL Server: 5.7.28

Use on your own risk !

$ sudo service mysql stop
$ sudo rm -rf /var/lib/mysql
$ sudo rm -rf /var/run/mysqld
$ sudo mkdir /var/run/mysqld
$ sudo chown -R mysql:mysql /var/run/mysqld
$ sudo /usr/lib/mysqld_safe --user=mysql --initialize-insecure
$ sudo service mysql start

Re-install MySQL Server setelah remove MariaDB

Abis install MariaDB 10 di Ubuntu 12.04 ternyata tiada cara yang mudah membuat sambungan ke php. Tak berguna sudo apt-get install php-mariabdb. Extensi musti di-compile ulang dari source dan itu pula tak berhasil.

Keterangan dari /var/log/mysql/error.log:

Fatal error: Can't open and lock privilege tables: 
Incorrect information in file: './mysql/proxies_priv.frm'

Akhirnya, coba lah re-build mysql database:

# cd /var/lib
# mv mysql mysql.bak
# mysql_install_db --user=mysql \
#     --basedir=/usr --datadir=/var/lib/mysql
# service mysql start 

Done.

Trigger BEFORE dan Trigger AFTER

Untuk beberapa operasi database, trigger dapat membantu pengembangan aplikasi dengan mengeksekusi beberapa perintah untuk setiap SQL Statement yang dikirimkan ke database. Beberapa perintah tersebut dapat dilakukan pada trigger BEFORE dan trigger AFTER. Namun penggunaannya secara umum, berbeda.

"Trigger BEFORE biasanya digunakan untuk melakukan validasi sebelum melakukan perubahan terhadap database. Trigger BEFORE dijalankan sebelum ada perubahan apapun terhadap database. Sebagai ilustrasi, di dalam database bank terdapat tabel rekening dan tabel transaksi. Jika nasabah melakukan penarikan dari rekeningnya, nasabah harus memiliki saldo yang cukup dalam rekeningnya untuk melakukan penarikan. Trigger BEFORE dapat melakukan validasi tersebut sehingga transaksi penarikan yang melebihi saldo dapat dicegah.

Trigger AFTER biasanya digunakan ketika ada informasi yang perlu ditambahkan dalam tabel lain setelah melakukan perubahan terhadap database. Trigger AFTER dijalankan setelah perubahan terhadap database dilakukan. Dalam ilustrasi sebelumnya, setelah transaksi penerikan yang dilakukan nasabah berhasil disimpan ke dalam database, perlu ada penyesuaian terhadap data saldo dalam tabel rekening. Hal ini dapat dilakukan dengan menggunakan Trigger AFTER." StackOverflow

MySQL 5.5
Dimulai dengan versi 5.5, MySQL menyediakan perintah "SIGNAL" yang dapat mendeklarasikan error. Perintah ini dapat digunakan untuk memberikan informasi perihal error code dan error description dari error yang dideklarasikan.


MySQL < 5.5
Untuk MySQL versi < 5.5 ada workaround untuk menggantikan perintah SIGNAL, ada cara untuk membuat perintah INSERT tidak dilakukan oleh database. Dengan menggunakan perintah SELECT terhadap nama field yang tidak ada dalam tabel, MySQL akan menampilkan error yang memberikan informasi bahwa nama field yang diminta tidak ada. Untuk menampilkan deskripsi error, nama field tersebut berisi pesan error yang akan kita sampaikan. Cara ini memang tidak elegan namun dapat dilakukan.

CREATE TRIGGER myTrigger
BEFORE INSERT
    FOR EACH ROW
BEGIN
    IF (<TheConditionIsNotvalid>) THEN
        SELECT "<YourMeaningfullErrorMessageGoesHere>"
        FROM myTable
    END IF;
END;

Trigger Or No Trigger
Penggunaan trigger bukan tanpa konsekuensi, ada beberapa dampak negatif yang dapat timbul. Dalam mendefinisikan sebuah trigger, dapat digunakan stored procedure. Bahasa yang digunakan dalam stored procedure seringkali jauh berbeda antar vendor database. Bahkan dalam vendor yang sama ada kemungkinan penambahan fitur dalam versi yang lebih tinggi. Sedangkan upgrade ke versi yang lebih tinggi belum tentu merupakan solusi yang efisien dalam pengembangan aplikasi yang kompleks. Bila diperlukan migrasi database, maka hampir pasti setiap stored procedure yang ada perlu disesuaikan.