Backup & Restore MySQL

Tips & Trick 

Pada versi MySQL terbaru, character set default yang digunakan adalah utf8mb4, agar compatible direstore ke MySQL versi lawas, kita harus mengubahnya menjadi utf8.

sed 's/utf8mb4_unicode_520_ci/utf8_unicode_ci/g' original-mysql-data.sql > updated-mysql-data.sql sed 's/utf8mb4/utf8/g' original-mysql-data.sql > updated-mysql-data.sql

Restore Backup yang Memiliki Kolom dengan Tipe GENERATED ALWAYS AS

Error ini terjadi jika kita merestore backup dari MariaDB ke MySQL, solusi pragmatisnya adalah dengan mengubah tipe kolom nya : 

sed  's/GENERATED ALWAYS AS .* VIRTUAL/NOT NULL/' backup.sql > backup_new.sql  

Hapus kolom yg kita ubah sementara tadi, lalu regenerate kembali : 

alter table tbl_penjualan drop column cnofaktur_tanpa_jenisbayar ;

alter table tbl_penjualan add column  `cnofaktur_tanpa_jenisbayar` varchar(14) GENERATED ALWAYS AS (concat(left(`cnofaktur`,3),right(`cnofaktur`,octet_length(`cnofaktur`) - 4))) VIRTUAL;

Referensi

1. Continuous MySQL backup validation: Restoring backups, https://code.facebook.com/posts/1007323976059780/continuous-mysql-backup-validation-restoring-backups

Import File CSV Berukuran Besar dengan csvkit

Status : Draft

Untuk melakukan import file CVS besar banyak cara yang bisa dilakukan, diantaranya dapat menggunakan tool bawaa MySQL, yaitu mysqlimport atau LOAD DATA atau menggunakan tool pihak ke tiga, seperti Pentaho Data Integration (PDI). Tapi kali ini, kita akan menggunaka tool pihak ke tiga yang lain, yaitu CSVKit [1].

Instalasi 

Untuk instalasi di Ubuntu, cukup dengan perintah : sudo apt-get install  python3-csvkit . Untuk platform lain, silahkan lihat bagian instalasi di [1].

Import Data 

Untuk melakukan import data, jalankan perintah berikut :

csvsql --dialect mysql --snifflimit 0   bigfile.csv > bigfile.sql

Jika ketika menjalankan perintah ini, Anda menemui error seperti berikut :

Your file is not "utf-8" encoded. Please specify the correct encoding with the -e flag. Use the -v flag to see the complete error.

Itu artinya, file Anda disimpan tidak dengan format utf-8. File Anda harus dikonversi ke utf-8, di Ubuntu Anda dapat melakukannya dengan menjalankan perintah berikut : 

iconv -f ISO8859-15 -t utf-8 bigfile.csv -o bigfile-utf8.csv

Kemudian, jalankan perintah csvsql kembali : 

csvsql --dialect mysql --snifflimit 0   bigfile-utf8.csv > bigfile.sql

Jika sudah, import file bigfile.sql seperti biasa.

Atau jika ingin langsung di-insert ke database, bisa menggunakan perintah berikut :

csvsql --db  mysql://username:password@localhost:3306/databasename --tables table_name --insert bigfile.csv

Ada kemungkinan, jika pustaka python untuk mengakses MySQL belum ada, Anda harus menginstall nya terlebih dahulu :

sudo apt-get install python3-pymysql

Referensi

1. CSVKit, https://csvkit.readthedocs.io

MySQL Vulnerability, CVE-2016-6662, Remote Root Code Execution / Privilege Escalation

Status : Draft

Silahkan baca [1]

Referensi

1. MySQL Exploit Remote-Root Code-Execution Privesc CVE 2016-6662, http://legalhackers.com/advisories/MySQL-Exploit-Remote-Root-Code-Execution-Privesc-CVE-2016-6662.html

Hati-Hati, UNION membuang row data yang duplikat

UNION di MySQL menghilangkan row data yang duplikat, perhatikan contoh berikut :

Query 1 :

SELECT '0' corder, ckode_coa, '' cnofaktur, DATE_FORMAT(CONCAT('2016','/','06','/','01'),'%d/%m/%Y') dtanggal,
'SALDO AWAL PERIODE' cketerangan, ndebit, ncredit FROM tbl_saldo WHERE cperiode=DATE_FORMAT(DATE_ADD(STR_TO_DATE(CONCAT('01/','06','/','2016'),'%d/%m/%Y'), INTERVAL -1 MONTH),'%Y%m') AND ckode_coa LIKE '1112019' AND substr(ckode_coa,length(ckode_coa),1)<>'0 ';

Output :

Query 2 :

SELECT '1' corder, d.ckode_coa, h.cnofaktur, h.dtanggal, h.cketerangan, d.ndebit, d.ncredit FROM tbl_gl_h h, tbl_gl_d d
WHERE dtanggal
BETWEEN '2016-06-30' AND '2016-06-30' AND ckode_coa LIKE '1112019' AND h.cnofaktur=d.cnofaktur
AND substr(ckode_coa,length(ckode_coa),1)<>'0'

Output :

Query 3 :

SELECT '0' corder, ckode_coa, '' cnofaktur,
DATE_FORMAT(CONCAT('2016','/','06','/','01'),'%d/%m/%Y') dtanggal,
'SALDO AWAL PERIODE' cketerangan, ndebit, ncredit FROM tbl_saldo
WHERE cperiode=DATE_FORMAT(DATE_ADD(STR_TO_DATE(CONCAT('01/','06','/','2016'),'%d/%m/%Y'), INTERVAL -1 MONTH),'%Y%m')
AND ckode_coa LIKE '1112019' AND substr(ckode_coa,length(ckode_coa),1)<>'0 '
UNION
SELECT '1' corder, d.ckode_coa, h.cnofaktur, h.dtanggal, h.cketerangan, d.ndebit, d.ncredit FROM tbl_gl_h h, tbl_gl_d d
WHERE dtanggal
BETWEEN '2016-06-30' AND '2016-06-30' AND ckode_coa LIKE '1112019' AND h.cnofaktur=d.cnofaktur
AND substr(ckode_coa,length(ckode_coa),1)<>'0'

Output :

Solusinya adalah ganti UNION dengan  UNION ALL, dengan UNION ALL, row data yang duplikat tidak akan dibuang. 

Referensi

1. Using UNION to Append Result Sets, http://www.geeksengine.com/database/multiple-table-select/using-union.php

ERROR 1093 : Can't specify target table for update in FROM clause

SQL :

UPDATE tbl_sales_d_pabrik set nprice = 5000 WHERE cnofaktur IN(
SELECT tsd.cnofaktur FROM tbl_sales_d_pabrik tsd, tbl_sales_h_pabrik tsh where tsh.cnofaktur=tsd.cnofaktur
  AND tsh.ckode_customer='B20DEP00001'  AND tsd.ckode_produk='1402010'
  AND tsh.dtanggal BETWEEN '2016-04-15' AND '2016-04-30'
);

Jika dijalankan akan terjadi error berikut : 

ERROR 1093 (HY000): You can't specify target table 'tbl_sales_d_pabrik' for update in FROM clause

Solusinya : 

UPDATE tbl_sales_d_pabrik tsd LEFT JOIN tbl_sales_h_pabrik tsh ON tsh.cnofaktur=tsd.cnofaktur
  SET nprice = 5000
  WHERE tsh.ckode_customer='B20DEP00001'  AND tsd.ckode_produk='1402010'
  AND tsh.dtanggal BETWEEN '2016-04-15' AND '2016-04-30'  

Referensi

1. MySQL Error 1093 - Can't specify target table for update in FROM clause, http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause

sql_mode=only_full_group_by

Status : Draft

Error :

 SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tirtaerp2012.t.ckode_customer' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by. Failing Query: "SELECT t.ckode_customer AS t__ckode_customer, t.cpayment AS t__0, t2.ckode_jenisbayar AS t2__ckode_jenisbayar, t2.cketerangan AS t2__1, t.cpayment AS t__0, t2.cketerangan AS t2__1 FROM tbl_customer t LEFT JOIN tbl_jenisbayar t2 ON t.cpayment = t2.ckode_jenisbayar GROUP BY t.cpayment"

Referensi

1. MySQL 5.7.5: GROUP BY respects functional dependencies!, http://rpbouman.blogspot.co.id/2014/09/mysql-575-group-by-respects-functional.html
2. Proposal to enable sql mode ONLY_FULL_GROUP_BY by default, http://www.tocker.ca/2014/01/24/proposal-to-enable-sql-mode-only-full-group-by-by-default.html

Optimalisasi MySQL

Status : Draft

Default di Ubuntu 18.04 :

Referensi

1. linux memory management for servers, https://dom.as/2016/05/13/linux-memory-management-for-servers/