Storage Anda penuh ketika menggunakan MySQL/MariaDB ?

Jika Storage Anda penuh ketika menggunakan MySQL/MariaDB, ada kemungkinan binary logs adalah biang masalahnya, untuk development, fitur ini tidak bisa digunakan, purge binary logs, lalu non aktifkan fitur ini, penggunaan storage Anda akan menjadi lapang kembali. 

mysql> purge binary logs before '2025-01-01'

Disable log binary, edit my.cnf, lalu pada bagian [mysqld], tambahkan : disable-log-bin=1, restart mysql 

Melihat dan Mengubah Engine Tabel-Tabel di Database dengan CLI

Melihat engine yang digunakan pada setiap tabel di sebuah database : 

SELECT TABLE_NAME,
ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'dbname';

 Mengubah Engine :

ALTER TABLE tbl_name ENGINE=InnoDB;

 Jika mengalami error seperti https://gist.github.com/wildanm/c082ac468a6b9056ceeec5b0c2058c11

Coba naikkan nilai : 

net_read_timeout dan net_write_timeout

The total number of locks exceeds the lock table size

Jika  menemukan error seperti ini, yang bisa kita lakukan adalah memperbesar ukuran innodb_buffer_pool_size , maksimalkan sesuai dengan kemampuan mesin kita. 

Jika sudah, dan masih terjadi error yang sama, maka bisa dicoba cara berikut : 

SET @@AUTOCOMMIT=0;

SQL statements disini ….

UNLOCK TABLES;

MySQL Infusion UDF : A MySQL functionality enhancement UD

...

Referensi

1. A MySQL functionality enhancement UDF, https://github.com/infusion/udf_infusion

MySQL GROUP BY

Perhatikan Query berikut :

mysql> select id,nomor, kode   from jawaban group by nomor, kode  having count(*) > 1 ;
+--------+------------+------+
| id     | nomor      | kode |
+--------+------------+------+
| 123673 | 2020800548 | 333  |
|  82207 | 2022800062 | 435  |
|  89616 | 2024200252 | 332  |
| 139145 | 2024801290 | 334  |
|  69294 | 2063702348 | 013  |
|  69100 | 2063703029 | 011  |
|  70004 | 2083700083 |      |
+--------+------------+------+
7 rows in set (0.17 sec)

Lalu :

mysql> select MIN(id),nomor, kode   from jawaban group by nomor, kode  having count(*) > 1 ;
+---------+------------+------+
| MIN(id) | nomor      | kode |
+---------+------------+------+
|  123673 | 2020800548 | 333  |
|   82207 | 2022800062 | 435  |
|   89616 | 2024200252 | 332  |
|  139145 | 2024801290 | 334  |
|   69294 | 2063702348 | 013  |
|   69100 | 2063703029 | 011  |
|   70004 | 2083700083 |      |
+---------+------------+------+
7 rows in set (0.17 sec)

Kemudian :

mysql> select MAX(id),nomor, kode   from jawaban group by nomor, kode  having count(*) > 1 ;
+---------+------------+------+
| MAX(id) | nomor      | kode |
+---------+------------+------+
|  124862 | 2020800548 | 333  |
|   82346 | 2022800062 | 435  |
|   90074 | 2024200252 | 332  |
|  140915 | 2024801290 | 334  |
|   77558 | 2063702348 | 013  |
|   77380 | 2063703029 | 011  |
|   78377 | 2083700083 |      |
+---------+------------+------+
7 rows in set (0.19 sec)

Kita bisa menentukan, dalam member group yg duplikat, data mana yang ingin kita ambil secara default.

Memilih Nilai Ke-n Pada Data Yang Dipisahkan Oleh Pembatas

Status : Draft

Dikutip dari [1] :

In general, to select the N-th value from a string VALUES that contains values separated by delimiter DELIM, you have to use: 

SUBSTRING_INDEX( SUBSTRING_INDEX( VALUES, DELIM, N ), DELIM, -1 )

Referensi

1. MySQL: select the n-th element from a delimiter separated value set, http://www.giannistsakiris.com/2015/01/23/mysql-select-the-n-th-element-from-a-delimiter-separated-value-set/

Membuang Semua Karakter Numerik dari Data

Alhamdulillah, sudah ada yang memberikan solusinya di [1] :

 DELIMITER $$

 DROP FUNCTION IF EXISTS `numericOnly`$$

 CREATE FUNCTION `numericOnly`(as_val VARCHAR(65535))
 RETURNS VARCHAR(65535)
 DETERMINISTIC
 BEGIN
   DECLARE retval VARCHAR(65535);
   DECLARE i INT;
   DECLARE strlen INT;
   -- shortcut exit for special cases
   IF as_val IS NULL OR as_val = '' THEN
     RETURN as_val;
   END IF;
   -- initialize for loop
   SET retval = '';
   SET i = 1;
   SET strlen = CHAR_LENGTH(as_val);
 do_loop:
   LOOP
     IF i > strlen THEN
       LEAVE do_loop;
     END IF;
     IF SUBSTR(as_val,i,1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
       SET retval = CONCAT(retval,SUBSTR(as_val,i,1));
     END IF;
     SET i = i + 1;
   END LOOP do_loop;
   RETURN retval;
 END$$

 DELIMITER ;

Referensi

1. Remove all non-numeric characters from a field, https://stackoverflow.com/questions/31146942/remove-all-non-numeric-characters-from-a-field/31147842

Alter Table Add Column If Not Exists

Terkadang kita ingin melakukan proses penambahan kolom pada sekumpulan database yang memiliki struktur tabel yang sama, tapi ada beberapa yang sudah ditambahkan dan ada yang belum, tapi jika melihatnya satu persatu akan memakan waktu. 

Jadi, bagaimana cara menambahkan kolom baru pada suatu tabel, hanya jika kolom tersebut belum ada ?

Begini caranya :

SET @s = (SELECT IF(
   (SELECT COUNT(*)
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE table_name = 'nama_tabel'
       AND table_schema = DATABASE()
       AND column_name = 'nama_kolom'
   ) > 0,
   'SELECT 1',
   'ALTER TABLE nama_tabel ADD nama_kolom INT(9) NULL DEFAULT 0 AFTER nama_kolom_yg_sudah_ada;'
));
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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