RSS
 

Archive for the ‘Database’ Category

Merubah Database Template PostgreSQL

20 Jul

Jika database template pada PostgreSQL menggunakan SQL_ASCII, saat kita ingin membuat database baru dengan jenis encoding yang lain akan muncul error “new encoding incompatible with the encoding of the template database (SQL_ASCII).”. Untuk memperbaiki masalah ini, kita harus merubah encoding database template itu sendiri menjadi, misalnya UTF8 (Unicode). Encoding SQL_ASCII ini menurut saya tidak berguna karena menurut saya, encoding SQL_ASCII artinya “tidak peduli dengan jenis data dan encoding apapun”.

Cara untuk merubah default encoding, berikut perintah SQL yang harus dijalankan.

Enable connection pada database template0.

1
postgres=# UPDATE pg_database SET datallowconn = TRUE WHERE datname ='template0';

Masuk ke database template0

1
postgres=# \c template0;

Ubah column “datistemplate” pada template1 agar kita bisa menghapusnya.

1
postgres=# UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';

Hapus database template1

1
postgres=# DROP DATABASE template1;

Buat database template1 yang baru dengan encoding Unicode (UTF8).

1
postgres=# create database template1 with template = template0 encoding = 'UTF8';

Set database baru tersebut sebagai database template.

1
postgres=# UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';

Masuk ke database template1

1
postgres=# \c template1

Disable koneksi ke template0

1
postgres=# UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template0';

Setelah semua selesai, sekarang kita bisa membuat database baru dengan jenis encoding apapun yang kita inginkan :-)

 
No Comments

Posted in Database, Tutorial

 

MySQL Replica Duplicate Entry Error

25 Jun

In normal condition, MySQL replication will stop whenever it encountered and error while executing slave queries. This is done so we can analyze the error produced and fix the problem which cause it to ensure data consistency between the master server and the replica. However, there’s a situation where we want to skip this bad query and continue to the next one. Although this is not recommended, but as long as we know well what’s that query is all about and we are very sure it won’t cause data inconsistency, these tricks I will show you is feasible.

For example, we can skip a single query (which we consider as broken) and continue to the next query. Execute this on MySQL console :

mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

We can ignore more than 1 query at a time, by simply change the number variable for “SQL_SLAVE_SKIP_COUNTER” to any number we want to skip.

There’s another way of doing this, we can tell MySQL to ignore all error with specific error codes. As example, we  can tell MySQL to skip all error with 1062 error code, which is “duplicate entry” error number.

slave-skip-errors = 1062

Beside error with 1062 error code,we can skip more than one error code by putting them in my.cnf “slave-skip-errors” option, put all error codes we want to skip, separated by comma (,). The complete list of MySQL error codes can be found here.

Note : I re-wroted this entry from Indonesian to English, since some people tell me it was confusing

 
No Comments

Posted in Database, Tutorial