mysql 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+

[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+

Database KodePos Seluruh Indonesia

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/edwinkun/database-kodepos-seluruh-indonesia

Semoga bisa membantu :-)

Google+

How to Inserting 0 To an AutoIncreament Field on MySql

Let say i have this table,

CREATE TABLE `refproductscale` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `scale` varchar(255) NOT NULL,
  `del` set('y','n') NOT NULL DEFAULT 'n',
  PRIMARY KEY (`id`)
)

I dont know why, but everytime im inserting ‘zero’ as its id, suddenly it changes into an increament number.
But with this script, im able to insert ‘zero’ as its id. This is my script,

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
insert into refproductscale values(0, 'none', 'n');

And this is the result,

All i have to do is use NO_AUTO_VALUE_ON_ZERO before i do insert query. ;-)

Google+

A Simple Caching Example on MyBatis using EhCache

Today i will show a simple example on how to combine ehcache caching framework with MyBatis ORM. I use Maven as my build tool, and Netbeans as my IDE.

Okay, here is my pom.xml,

<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.ehcache</groupId>
    <artifactId>MyBatisEhCache</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>MyBatisEhCache</name>
    <url>http://maven.apache.org</url>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>
    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.1</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.2.2</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.caches</groupId>
            <artifactId>mybatis-ehcache</artifactId>
            <version>1.0.2</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>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>     
    </dependencies>
</project>

And i create a simple mysql table,

CREATE TABLE `testing` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL DEFAULT '',
  `address` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`Id`),
  UNIQUE KEY `ix` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

and a simple bean and xml representation from my table,

package com.edw.mybatisehcache.bean;

import java.io.Serializable;

public class Testing implements Serializable  {

    private Integer id;
    private String name;
    private String address;

    // setter and getter

    @Override
    public String toString() {
        return "testing{" + "id=" + id + ", name=" + name + ", address=" + address + '}';
    }            
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.edw.mybatisehcache.mapper.TestingMapper" >
  
     <cache type="org.mybatis.caches.ehcache.EhcacheCache"/>
    
    <resultMap id="Testings" type="com.edw.mybatisehcache.bean.Testing" >
        <id column="id" property="id" jdbcType="BIGINT" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="address" property="address" jdbcType="VARCHAR" />
    </resultMap>  

    <select id="select" resultMap="Testings">
        select 
        *
        from testing    
    </select>   
</mapper>

An xml configuration to load my database connection,

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
       <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="UNPOOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost/test"/>
                <property name="username" value="root"/>
                <property name="password" value=""/>
            </dataSource>
        </environment>          
    </environments>
     <mappers>        		
        <mapper resource="com/edw/mybatisehcache/xml/TestingMapper.xml" />  
    </mappers>              		
</configuration>

A java code to load my xml configuration,

package com.edw.mybatisehcache.config;

import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBatisSqlSessionFactory {

    private static final SqlSessionFactory FACTORY;

    static {
        try {
            Reader reader = Resources.getResourceAsReader("com/edw/mybatisehcache/xml/Configuration.xml");
            FACTORY = new SqlSessionFactoryBuilder().build(reader);
        } catch (Exception e){
            throw new RuntimeException("Fatal Error.  Cause: " + e, e);
        }
    }

    public static SqlSessionFactory getSqlSessionFactory() {
        return FACTORY;
    }
}

A java interface to do handle queries,

package com.edw.mybatisehcache.mapper;

import com.edw.mybatisehcache.bean.Testing;
import java.util.List;

public interface TestingMapper {
    public List<Testing> select();    
}

And this is my ehcache.xml configuration,

<?xml version="1.0" encoding="UTF-8"?>
<!--
    caching configuration
-->
<ehcache>
    
    <diskStore path="F:\\cache" />
    
    <defaultCache eternal="true" maxElementsInMemory="1000"
                   overflowToDisk="true" diskPersistent="true" timeToIdleSeconds="0"
                   timeToLiveSeconds="0" memoryStoreEvictionPolicy="LRU" statistics="true" />
</ehcache>

This is my main java class, as you can see i try to do a repeated simple select queries,

package com.edw.mybatisehcache.main;

import com.edw.mybatisehcache.bean.Testing;
import com.edw.mybatisehcache.config.MyBatisSqlSessionFactory;
import com.edw.mybatisehcache.mapper.TestingMapper;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.log4j.Logger;

public class Main {

    private static Logger logger = Logger.getLogger(Main.class);

    public static void main(String[] args) {

        for (int i = 0; i < 3; i++) {
            SqlSessionFactory sqlSessionFactory = MyBatisSqlSessionFactory.getSqlSessionFactory();
            SqlSession sqlSession = sqlSessionFactory.openSession();
            TestingMapper testingMapper = sqlSession.getMapper(TestingMapper.class);

            List<Testing> testings = testingMapper.select();
            for (Testing testing : testings) {
                logger.debug(testing);
            }
            sqlSession.close();

            try {
                Thread.sleep(3000);
            } catch (Exception e) {
                logger.error(e, e);
            }
        }
    }
}

This is what is written on my netbeans’ console,

2013-07-25 15:30:10,648 [Segment] DEBUG net.sf.ehcache.store.disk.Segment:779 - fault removed 0 from heap
2013-07-25 15:30:10,648 [Segment] DEBUG net.sf.ehcache.store.disk.Segment:796 - fault added 0 on disk
2013-07-25 15:30:13,722 [Cache] DEBUG net.sf.ehcache.Cache:1970 - Cache: com.edw.mybatisehcache.mapper.TestingMapper store hit for 2026218237:1652924294:com.edw.mybatisehcache.mapper.TestingMapper.select:0:2147483647:select 
        *
        from testing
2013-07-25 15:30:13,722 [Main] DEBUG com.edw.mybatisehcache.main.Main:24 - testing{id=1, name=edw, address=Ciledug}
2013-07-25 15:30:13,722 [Main] DEBUG com.edw.mybatisehcache.main.Main:24 - testing{id=2, name=kamplenk, address=Cikokol}
2013-07-25 15:30:13,722 [Main] DEBUG com.edw.mybatisehcache.main.Main:24 - testing{id=3, name=nugie, address=Pamulang}
2013-07-25 15:30:13,722 [Main] DEBUG com.edw.mybatisehcache.main.Main:24 - testing{id=4, name=tebek, address=Karawaci}

Here is my Netbeans project structure

Have fun (K)

Google+