Database | edwin's journey

Weird Exception, “Unsupported startup parameter: extra_float_digits” When Connecting to PostgreSQL

By edwin / 31 May 2016 31 May 2016 / Database, Programming / database, postgresql

Today I’m planning on deploying my application (which runs well on my local laptop), to a production server. But there is a very weird error occur, it says

Caused by: org.postgresql.util.PSQLException: ERROR: Unsupported startup parameter: extra_float_digits
	at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:291)
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:108)
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
	at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
	at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)

Somehow it seems that the error happen because of some wrong PostgreSql JDBC version. But the error stays even if I changed my Postgresql JDBC version several times. So my temporary conclusion is, improper Postgresql configuration.

After debating with several sysadmins and DBA, i found out that my sql connection actually goes to a Connection Pooling called PGBouncer, instead of connecting directly to Postgresql. Weird, because i also using Connection Pooling (Apache DBCP) for my application. So it means that my app is using a double connection pooling 😀

After spending sometime googling, i found out that all i need to do is adding this simple configuration on pgbouncer.ini

ignore_startup_parameters = extra_float_digits

hint :
If your are unable to found the location for pgbouncer.ini, just using a simple ps -ef command on console.

bash-4.1$ ps -ef | grep pgbouncer
500      18927     1  0 May30 ?        00:00:00 /opt/PostgresPlus/pgbouncer-1.6/bin/pgbouncer -d /opt/PostgresPlus/pgbouncer-1.6/share/pgbouncer.ini
500      19045 18913  0 00:11 pts/4    00:00:00 grep pgbouncer

As you can see, the pgbouncer.ini location is in /opt/PostgresPlus/pgbouncer-1.6/share/.

Well, one thing solve, and suddenly i found another error :sigh. So many errors today. Here is my complete stacktrace

 org.postgresql.util.PSQLException: ERROR: No such database: mydatabase
 	at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:291)
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:108)
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
	at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
	at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
	at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:22)
	at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:30)

Somehow i cannot find mydatabase, despite i already created it.

Actually, the answer is simple. Again pgbouncer is the culprit. I cannot connect to a database if the database havent registered yet at pgbouncer.ini. Registering my database solve this issue.

;; database name = connect string
;;
;; connect string params:
;;   dbname= host= port= user= password=
;;   client_encoding= datestyle= timezone=
;;   pool_size= connect_query=
[databases]

; foodb over unix socket
edb = host=127.0.0.1 port=5444
mydatabase = host=127.0.0.1 port=5444

You can see my PostgreSQL’s port is 5444.

Hopefully, my tutorial helps other. Because i spent a ridiculously amount of time solving it 😀

Daftar Kode Provinsi, Kecamatan, Kabupaten dan Desa di Indonesia Menurut Kemendagri

By edwin / 22 October 2015 22 October 2015 / Database, Programming / database, gis, misc

Setelah berkeliaran kesana-kesini, apalagi data kemendagri berupa pdf sehingga menyulitkan diparsing 😛 , akhirnya dapet juga versi sql dari data Kemendagri. Setelah diparsing sehingga mysql-compatible, hasil akhir sql filenya bisa diunduh di —> sini

Database KodePos Seluruh Indonesia

By edwin / 14 August 2015 29 November 2019 / Database, Programming / gis, mysql

Awalnya iseng-iseng mencari data kodepos seluruh Indonesia, akhirnya dapet juga setelah email secara resmi ke PT Pos Indonesia. Namun file yang dikirimkan oleh PT Pos berupa file Excel (xls), walaupun dengan sedikit effort akhirnya bisa juga diimport ke database MySql.

Oh ya, file SQL-nya bisa didownload dari Github gw,

https://github.com/edwin/database-kodepos-seluruh-indonesia

Semoga bisa membantu 🙂

[PostgreSQL] Got Error “FATAL: database databasename does not exist” Despite Database is Exists on PGAdmin

By edwin / 7 July 2015 7 July 2015 / Database, Programming / database, Exception, netbeans

Today i spent a ridiculous amount of time trying to figure out how come suddenly my java application cannot connect to postgresql database. It gives error “FATAL: database databasename does not exist”, and the weird thing is that i can browse and query to my postgresql database from PGAdmin and other database browser.

Here is my application’s complete error stacktrace

Caused by: org.postgresql.util.PSQLException: FATAL: database "geoportal" does not exist
    at org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:684)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:199)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
    at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:127)
    at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:29)
    at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:21)
    at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:41)
    at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)
    at org.postgresql.Driver.makeConnection(Driver.java:414)
    at org.postgresql.Driver.connect(Driver.java:282)

Weird because i can see my db both from PGAdmin and psql console,

When suddenly i realize when im checking my postgresql port, and i can see 2 different postgresql with different PID occupied the same port, 5432. Somehow my PGAdmin and psql connect to the right instance while my application connect to the wrong instance.

After killing my unwanted postgresql PID from taskmanager, suddenly my java application can run well again.

How to Simulate and Finding Connection Leak in Oracle

By edwin / 3 March 2015 3 March 2015 / Database, Programming / Java, oracle, Programming, utility

It’s been a while since the last time i’ve write tutorials in my blog, but i found a very interesting case that perhaps helped a lot of people. Today im trying to create a simple test case to simulate a connection leak and how to search for it.

First is creating a simple unclosed connection query using java,

package com.edw;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Main {

    public static void main(String[] args) throws Exception {
        System.out.println("starting --------");
        
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "USERNAME", "password");
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT * FROM INVALID"); // example table name is INVALID
        
        while(resultSet.next()) {
            System.out.println(resultSet.getString(1));
            System.out.println(resultSet.getString(2));
            System.out.println(resultSet.getString(3));
            System.out.println("=============");
        }
        
        System.out.println("ending --------");
        
        while(true) {
            Thread.sleep(10000);
            System.out.println("testing --------");
        }
    
    }
}

It will create an unclosed query that will hanging on my oracle connection.
And next, is my oracle query to find the hanging queries,

select LAST_CALL_ET, SQL_TEXT, username, machine, to_char(logon_time, 'ddMon hh24:mi') as login, 
    SQL_HASH_VALUE, PREV_HASH_VALUE, status
from v$session, v$sql 
where username='USERNAME' and HASH_VALUE =  PREV_HASH_VALUE
order by last_call_et desc;

it will show result like this,

LAST_CALL_ET SQL_TEXT                USERNAME     MACHINE        LOGIN                SQL_HASH_VALUE PREV_HASH_VALUE STATUS 
------------ ---------------------- ------------- -------------- -------------------- -------------- --------------- --------
          91 SELECT * FROM INVALID   USERNAME     edw      	     03Mar 11:53                       0       621168917 INACTIVE

As you can see the result on the first column, “SELECT * FROM INVALID” query has run for more than 90 seconds.
So i can easily find the java class culprit who run that query.

FYI, this is my Oracle version,

select * from v$version;

BANNER                                                                         
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production              
PL/SQL Release 11.2.0.2.0 - Production                                           
CORE	11.2.0.2.0	Production                                                         
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production                          
NLSRTL Version 11.2.0.2.0 - Production

I Hope this tutorial can help others, have fun 🙂