database Posts

Fixing MySql’s Error, “Cant create test file /folder/data/servername.lower-test” on RedHat OS

Had a very weird error starting my MySql after im moving MySql’s data folder from /var/lib/mysql to /folder/data on my RHEL 7 Server,

2017-03-23 11:50:52 2119 [Warning] Can't create test file /folder/data/servername.lower-test
2017-03-23 11:50:52 2119 [Warning] Can't create test file /folder/data/servername.lower-test
2017-03-23 11:50:52 2119 [ERROR] /usr/sbin/mysqld: Can't create/write to file 
    '/folder/data/servername.lower-test' (Errcode: 13 - Permission denied)

very very weird because im already change the ownership of my /folder data to mysql user, and still not working. I even chmod it to 777 for testing purpose, yet still no positive result. After a while i found out that my SELinux is blocking it, here is the command to unblock it.

[root@servername ~]# setenforce 0
[root@servername ~]# getenforce
Permissive

but the commands above only configure your SELinux until reboot. If you want to make it permanent, you could use this command,

[root@servername ~]# vi /etc/selinux/config 
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=permissive
# SELINUXTYPE= can take one of these two values:
#     targeted - Targeted processes are protected,
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted  

Hope it helps others, cheers

Google+

[Java] org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (ORA-00923: FROM keyword not found where expected

Had this weird error, when connecting to Oracle Database

### The error occurred while executing a query
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; 
nested exception is org.apache.commons.dbcp.SQLNestedException: 
Cannot create PoolableConnectionFactory (ORA-00923: FROM keyword not found where expected
)

Somehow it never happens on my MySql Configuration. Here is my configuration,

<bean id="dataSourceOracle" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"
          p:driverClassName="oracle.jdbc.driver.OracleDriver" p:url="${db.url.oracle}"
          p:username="${db.username.oracle}" p:password="${db.password.oracle}"
          p:initialSize="2"
          p:maxActive="30"
          p:maxIdle="10"
          p:minIdle="3"
          p:maxWait="30000"
          p:removeAbandoned="true"
          p:removeAbandonedTimeout="30"
          p:validationQuery="SELECT 1" />

After investigating for a while, i found out that it happens because i use MySql’s specific validationQuery, “SELECT 1″. Changing it into “SELECT 1 FROM DUAL” make the error go away. :-P

Google+

[Java] Cara Cache Data Hasil Query dengan SpringMVC

Pada tulisan kali ini, saya coba membuat suatu metode caching untuk menyimpan data-data master kedalam memory, sehingga tidak perlu query ke database lagi. Hal ini cocok digunakan untuk menyimpan data-data master yang sangat jarang berubah, seperti kode provinsi ataupun kode cabang.

Okay, di tutorial ini saya menggunakan Spring MVC 4, Hibernate dan ehCache sebagai cache provider. Berikut adalah pom.xml yang digunakan,

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.edw</groupId>
    <artifactId>mavenproject5</artifactId>
    <version>1</version>
    <packaging>war</packaging>

    <name>mavenproject5</name>

    <properties>
        <endorsed.dir>${project.build.directory}/endorsed</endorsed.dir>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>
    
    <dependencies>
        <dependency>
            <groupId>javax</groupId>
            <artifactId>javaee-web-api</artifactId>
            <version>6.0</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>
        
        
        <!-- Spring 4 dependencies -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>4.1.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>4.1.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>4.1.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>4.1.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>4.1.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>cglib</groupId>
            <artifactId>cglib</artifactId>
            <version>2.2</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context-support</artifactId>
            <version>4.1.2.RELEASE</version>
         </dependency>
         
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        
        
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.2.2</version>
        </dependency>
        
        <!--ehcache-->
        <dependency>
            <groupId>net.sf.ehcache</groupId>
            <artifactId>ehcache</artifactId>
            <version>2.7.0</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.5</version>
        </dependency>
        
        <!--hibernate-->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>4.3.8.Final</version>
        </dependency>
        
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.1</version>
                <configuration>
                    <source>1.7</source>
                    <target>1.7</target>
                    <compilerArguments>
                        <endorseddirs>${endorsed.dir}</endorseddirs>
                    </compilerArguments>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-war-plugin</artifactId>
                <version>2.3</version>
                <configuration>
                    <failOnMissingWebXml>false</failOnMissingWebXml>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-dependency-plugin</artifactId>
                <version>2.6</version>
                <executions>
                    <execution>
                        <phase>validate</phase>
                        <goals>
                            <goal>copy</goal>
                        </goals>
                        <configuration>
                            <outputDirectory>${endorsed.dir}</outputDirectory>
                            <silent>true</silent>
                            <artifactItems>
                                <artifactItem>
                                    <groupId>javax</groupId>
                                    <artifactId>javaee-endorsed-api</artifactId>
                                    <version>7.0</version>
                                    <type>jar</type>
                                </artifactItem>
                            </artifactItems>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

Kemudian konfigurasi standard yang digunakan di SpringMVC, applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:cache="http://www.springframework.org/schema/cache"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
        http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.0.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
        http://www.springframework.org/schema/cache http://www.springframework.org/schema/cache/spring-cache.xsd">

    <context:annotation-config/>
    
    <tx:annotation-driven/>
    
    <cache:annotation-driven />
    
    <context:component-scan base-package="com.edw.mavenproject5.service"/>
    
     <!-- datasource  -->
    <bean id="ds" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close"
          p:driverClassName="com.mysql.jdbc.Driver" p:url="jdbc:mysql://localhost/test"
          p:username="root" p:password=""
          p:initialSize="10"
          p:maxActive="50"
          p:maxIdle="10"
          p:minIdle="3"
          p:maxWait="30000"
          p:removeAbandoned="true"
          p:removeAbandonedTimeout="30"
          p:validationQuery="SELECT 1" />
    
    <bean id="sessionFactory"
        class="org.springframework.orm.hibernate4.LocalSessionFactoryBean"
        p:dataSource-ref="ds" p:configLocations="classpath:hibernate.cfg.xml" depends-on="ds">
        <property name="hibernateProperties">
            <value>
                hibernate.dialect=org.hibernate.dialect.MySQLDialect
                hibernate.query.substitutions=true
                hibernate.show_sql=true
                hibernate.enable_lazy_load_no_trans=true
            </value>
        </property>
    </bean> 
    
    <bean id="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
        <property name="sessionFactory" ref="sessionFactory" />
    </bean>
    
    <bean id="cacheManager" 
          class="org.springframework.cache.ehcache.EhCacheCacheManager" 
          p:cache-manager-ref="ehcache"/>
    <bean id="ehcache" class="org.springframework.cache.ehcache.EhCacheManagerFactoryBean"
          p:config-location="classpath:ehcache.xml"/>
    
</beans>

dan dispatcher-servlet.xml,

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:p="http://www.springframework.org/schema/p"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">


    <context:component-scan base-package="com.edw.mavenproject5.controller" />
	
    <mvc:annotation-driven />
    <mvc:default-servlet-handler />

    <bean id="viewResolver"
          class="org.springframework.web.servlet.view.InternalResourceViewResolver"
          p:prefix="/WEB-INF/jsp/"
          p:suffix=".jsp" />
</beans>

yang kemudian dipanggil dari web.xml,

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.0" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
    <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>/WEB-INF/applicationContext.xml</param-value>
    </context-param>
    <listener>
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>
    <servlet>
        <servlet-name>dispatcher</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <load-on-startup>2</load-on-startup>
    </servlet>
    <servlet-mapping>
        <servlet-name>dispatcher</servlet-name>
        <url-pattern>/</url-pattern>
    </servlet-mapping>
</web-app>

Nah, ada satu konfigurasi xml yang penting dalam membuat cache. Yaitu ehcache.xml, disana disimpan nama cache serta lama waktu hidupnya

<?xml version="1.0" encoding="UTF-8"?>
<!--
    caching configuration
-->
<ehcache>
    
    <defaultCache
        maxElementsInMemory="5000"
        eternal="true"
        overflowToDisk="false"
        memoryStoreEvictionPolicy="LRU"
    />
    
    <!--2menit-->
    <cache name="duaMenit"
           maxElementsInMemory="10000"
           timeToLiveSeconds="120"
           memoryStoreEvictionPolicy="LRU" 
    />
    
    <!--1menit-->
    <cache name="satuMenit"
           maxElementsInMemory="10000"
           timeToLiveSeconds="60"
           memoryStoreEvictionPolicy="LRU" 
    />
</ehcache>

Nah sekarang kita buat class java-nya, dimulai dari service layer. Perhatikan annotation Cacheable, konfigurasi tersebut adalah konfigurasi yang menandakan lokasi method yang kita cache.

package com.edw.mavenproject5.service;

import com.edw.mavenproject5.bean.Dosen;
import java.util.List;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
@Transactional
public class DosenService {

    @Autowired
    private SessionFactory sessionFactory;
    
    @Cacheable(key = "#root.methodName", value = "duaMenit")
    public List<Dosen> getDosens() {
        return sessionFactory.getCurrentSession().createCriteria(Dosen.class).list();
    }
    
    @Cacheable(key = "#root.methodName + #idDosen", value = "satuMenit")
    public Dosen getDosen(String idDosen) {
        return (Dosen) sessionFactory.getCurrentSession().get(Dosen.class, idDosen);
    }
    
}

Yang kemudian dipanggil dari controller,

package com.edw.mavenproject5.controller;

import com.edw.mavenproject5.service.DosenService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

@Controller
public class IndexController {
    
    @Autowired
    private DosenService dosenService;
    
    @RequestMapping(value = "/", method = RequestMethod.GET)
    public String index(ModelMap modelMap){
        modelMap.put("dosens", dosenService.getDosens());
        return "index";
    }
    
    @RequestMapping(value = "/get", method = RequestMethod.GET)
    public String get(ModelMap modelMap, String id){
        modelMap.put("dosen", dosenService.getDosen(id));
        return "index";
    }
}

Dan berikut adalah ui yang digunakan untuk menampilkan hasil query didatabase,

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <h1>Hello World!</h1>
        
        <c:if test="${not empty dosens}">
        <table class="table table-hover table-striped">
            <thead>
                <tr>
                    <th>ID Dosen</th>
                    <th>Nama Dosen</th>
                </tr>
            </thead>
            <tbody>
            <c:forEach items="${dosens}" var="dosen">
                <tr>
                    <td>${dosen.iddosen}</td>
                    <td>${dosen.namadosen}</td>
                </tr>
            </c:forEach>
            </tbody>
        </table>
        </c:if>
        
        <c:if test="${not empty dosen}">
            <h1>Hello Dosen ${dosen.namadosen}!</h1>
        </c:if>
    </body>
</html>

Hasil akhirnya adalah sebagai berikut, data dengan kode 02 dan 321 sudah diubah secara langsung didatabase, namun walaupun browser di-refresh berulang kali, tetap menampilkan data yang lama, yaitu “Dodol” dan “Testing 123 123″.
capture1

Untuk sourcecode lengkapnya bisa diunduh di github, https://github.com/edwinkun/CachingWithSpringMVC. (*)

Google+

[MySQL Error] Got a packet bigger than ‘max_allowed_packet’ bytes

Recently i got an error while importing my data into a new mysql database, this is the complete stacktrace

[Err] 1153 - Got a packet bigger than 'max_allowed_packet' bytes

The problem is gone after i run these command on my mysql console,

set global net_buffer_length=1000000; 
set global max_allowed_packet=1000000000;

(*) (*)

Google+

Menghapus Ratusan Juta Record dari Oracle Table

Baru kemarin ketemu case yang lumayan menarik, aplikasi yang dibuat tahun lalu kena timeout ketika delete seluruh isi table yang berisi paling tidak 200juta record. Berikut error lengkapnya, fyi framework yang dipakai adalah MyBatis dan Spring Framework dan database yang dipakai adalah Oracle,

<2016-10-31 02:15:01,723>,[http-/0.0.0.0:8080-16]>>[INFO]start deleting TABLE_NAME Data
<2016-10-31 02:18:02,426>,[http-/0.0.0.0:8080-16]>>[ERROR]
### Error updating database.  Cause: java.sql.SQLException: ORA-01013: user requested cancel of current operation

### The error may involve id.edwin.service.delete-Inline
### The error occurred while setting parameters
### SQL: delete from TABLE_NAME
### Cause: java.sql.SQLException: ORA-01013: user requested cancel of current operation

; uncategorized SQLException for SQL []; SQL state [72000]; error code [1013]; ORA-01013: user requested cancel of current operation
; nested exception is java.sql.SQLException: ORA-01013: user requested cancel of current operation

sepertinya sudah timeout duluan (3menit timeout), padahal belum semua data terhapus. :-(

Setelah googling bentar, sepertinya ada dua solusi yaitu menggunakan TRUNCATE dan CTAS (CREATE TABLE AS SELECT). Setelah diskusi panjang lebar dengan kuncen (admin) Database, opsi terakhir (sepertinya) jauh lebih cepat, drop table tersebut kemudian di re-create ulang. Berikut adalah query-nya

CREATE TABLE TABLE_NAME_NEW AS
        SELECT * FROM TABLE_NAME WHERE ROWNUM = 1 ;
Rename TABLE_NAME to TABLE_NAME_OLD ;
Rename TABLE_NAME_NEW to TABLE_NAME;
drop table TABLE_NAME_OLD ;

Setelah itu baru didelete isi table TABLE_NAME, lebih cepat karena isi datanya hanya 1 row. Satu-satunya kekurangan adalah, tidak bisa replicate Primary Key dan Index dari table yang sebelumnya di drop, yang mana itu bukanlah masalah bagi saya :-D

Google+