생각하는 족족 고.따.구 냐..

Posted
Filed under About Knowledge/DB_MySQL
[서버 설정 변경]
character_set_client     = utf8mb4           
character_set_connection = utf8mb4           
character_set_database   = utf8mb4           
character_set_filesystem = binary            
character_set_results    = utf8mb4           
character_set_server     = utf8mb4           
character_set_system     = utf8              
collation_connection     = utf8mb4_unicode_ci
collation_database       = utf8mb4_unicode_ci
collation_server         = utf8mb4_unicode_ci

[테이블/컬럼 설정 변경]
ALTER DATABASE homestead CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
2018/11/23 16:13 2018/11/23 16:13
Posted
Filed under About Knowledge/DB_MySQL
Milliseconds 입력 방법

mysql or mariadb를 이용하며 milliseconds를 입력하지 못하는현상을 개선하지 못하고 사용하였으나 최근 사용하는 library ( or Framework )셋을 변경하고 개선된 환경을 만들었습니다.



사용된 Frameset
ORM  - Hibernate 5.2.12
Connection Pool - HikariCP 2.6.3
Mariadb Connector/J  - 2.2.3

Mariadb or MySQL 설정

DATETIME  == DATETIME(0)  : fractional seconds rounded
DATETIME  ->
DATETIME(3)  : support milliseconds

DATETIME  -> DATETIME(6)  : support nano seconds


설정

@Bean
@Primary
public PlatformTransactionManager transactionManager() {

JpaTransactionManager transactionManager
= new JpaTransactionManager();
transactionManager.setEntityManagerFactory(
entityManagerFactory().getObject());
return transactionManager;
}

@Bean
@Primary
public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
LocalContainerEntityManagerFactoryBean em
= new LocalContainerEntityManagerFactoryBean();
em.setDataSource(dataSource());
em.setPackagesToScan(
new String[] { "{entitiy classes ex=> com.entity.Entity1}" });
HibernateJpaVendorAdapter vendorAdapter
= new HibernateJpaVendorAdapter();
em.setJpaVendorAdapter(vendorAdapter);
HashMap<String, Object> properties = new HashMap<>();
properties.put("hibernate.hbm2ddl.auto", "validate" );
properties.put("hibernate.dialect", "org.hibernate.dialect.MariaDB53Dialect" );
properties.put("hibernate.show_sql", "true" );
properties.put("hibernate.implicit_naming_strategy", "org.hibernate.boot.model.naming.ImplicitNamingStrategyJpaCompliantImpl" );

// for spring data compatibility
properties.put("hibernate.physical_naming_strategy", "org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy" );
em.setJpaPropertyMap(properties);
return em;
}

@Primary
@Bean
public DataSource dataSource() {
HikariDataSource ds = new HikariDataSource();
ds.setDataSourceProperties(this.getProperties());

ds.setPoolName("my-pool");
ds.setConnectionTestQuery("select now() from dual");

// DataSource Class Name for MariaDB
ds.setDataSourceClassName( "org.mariadb.jdbc.MariaDbDataSource");
//ds.setIdleTimeout(datasourceProperties.getIdleTimeout());
ds.setMaximumPoolSize( 10 );
ds.setConnectionTimeout(5000);
//ds.setMinimumIdle(minIdle);
ds.setConnectionTestQuery("SELECT 1 FROM DUAL");
return ds;
}

private Properties getProperties()
{
Properties prop = new Properties();
prop.put("serverName", "${HOST IP}");
prop.put("databaseName", "${DATA BASE NAME}");
prop.put("user", "${Account ID}");
prop.put("password", "${Account Password}");


// Mariadb Clinet Do Not User Below
// prop.put("cachePrepStmts", "true");
// prop.put("prepStmtCacheSize", "250");
// prop.put("prepStmtCacheSqlLimit", "2048");
// prop.put("encoding", "UTF-8");
return prop;
}





2018/03/26 12:29 2018/03/26 12:29
Posted
Filed under About Knowledge/DB_MySQL

Mariadb java client does not support useServerPrepStmts


  • Epic Name:
    Use server prepareStatement
  • Sprint:
    Sprint connector/j 1.3.0



To enable prepared statements on the server side, MySQL Connector/J provides useServerPrepStmts parameter.
It doesn't work for MariaDB client. If there is a way to get prepared statements work through MariaDB client, I haven't yet found it.

The provided test case checks the global value of Prepared_stmt_count status variable, then attempts to prepare a statement on a connection with useServerPrepStmts, then checks the variable value again.

Output with MariaDB client library 1.1.0:

Prepared_stmt_count before prepare: 0
Prepared_stmt_count after prepare: 0

With MySQL Connector/J 5.1.23:

Prepared_stmt_count before prepare: 0
Prepared_stmt_count after prepare: 1


exmaples

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


public class BugPreparedStatement
{
  public static void main (String argv[])  
  {
    try {

      Statement status = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","").createStatement();

      ResultSet rs = status.executeQuery("show global status like 'Prepared_stmt_count'");
      if (rs.first()) {
        System.out.println("Prepared_stmt_count before prepare: " + rs.getInt(2));  
      }

      PreparedStatement pst = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useServerPrepStmts=true","root","").prepareStatement("select 1");

      rs = status.executeQuery("show global status like 'Prepared_stmt_count'");
      if (rs.first()) {
        System.out.println("Prepared_stmt_count after prepare: " + rs.getInt(2));  
      }
    }
    catch (Exception e)
    {
      System.out.println("Exception: " + e + "\n");
      e.printStackTrace();
    }
  } 
}

Refered From : https://mariadb.atlassian.net/browse/CONJ-22

 



2018/03/26 10:49 2018/03/26 10:49
Posted
Filed under About Knowledge/DB_MySQL
USE one option !

==================
--single-transaction
ex> mysqldump --user=root --password
                               --single-transaction    /*하나의 트랜잭션을 이용하여 Dump진행*/
                               --routines                        /*프로시저포함*/
                               --no-create-info           
/*테이블 생성스크립트 제외*/
                               --no-data
                        /*데이터 제외*/
                               --no-create-db             
/*DB 생성스크립트 제외*/
                              --triggers                        /*트리거백업*/
                                --skip-opt                     /*그룹옵션 실행 방지*/
                               [데이터베이스명]  >  `hostname`_`date '+%y%m%d'`.dump.sql

==================
2017/02/03 16:04 2017/02/03 16:04
Posted
Filed under About Knowledge/DB_MySQL
<로그인 할 사용자 생성>

mysql>insert into user(host,user,password) values('접속허용할 호스트네임 or IP','root',password('패스워드'));

 

<권한주기>

mysql> grant all privileges on *.* to 'root'@'%' identified by '암호' with grant option;
mysql> flush privileges
2016/10/12 14:42 2016/10/12 14:42