개발 흔적 남기기

기본적으로 하이디와 같은 툴에서 Mysql, Maria DB를 접근하여 쿼리를 날릴 때, 다중으로 delete, update, insert등을 처리 할 수 있다. mysql의 경우에는 구분자 ;(세미콜론)을 추가하여 DML 쿼리를 여러번 처리 할 수 있는데, 당연히 mybatis에서도 처리가 될 것이라고 생각하여 테스트 해봤지만 계속해서 문법 에러가 발생하였다.

확인을 해보니 Mybatis에 mysql연결을 할 때 옵션 설정이 필요하였다.

allowMultiQueries=true

allowMultiQueries 을 true처리해주고 DB를 연결해주면 다중으로 날려도 정상적으로 처리가 된다.

jdbc.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true

*추가적으로 적용할 옵션은 & 추가하여 처리

-Mybatis

<delete id="testDelete" parameterType="java.util.Map">
	DELETE FROM A where id = #{id};
	DELETE FROM B where id = #{id};
</delete>

Mysql에서 대량의 insert쿼리를 처리하는 로직부분이 있는데, 수정 과정을 통해 기존 등록된 데이터를 삭제하고 insert 쿼리가 동작하면서 해당 에러가 발생하였다.

에러가 발생한 서버의 설치된 mysql의 max_allowed값을 확인해보니 4MB였고, 에러는 5MB가 요청되어 발생한 에러였다.

서버에서 max_allowed_packet값 확인하기

show VARIABLES like '%max_allowed_packet%';

설정값 변경하기

mysql 설정 파일에 찾아가 옵션을 변경한다.

# vim /etc/my.cnf

[mysqld]
max_allowed_packet=16M

이후 서비스를 재기동하면, 설정값이 변경된 것을 확인 할 수 있다.

Mysql 상태확인 및 시작, 중지, 재시작 명령어

# service mysql start //시작
# service mysql stop  //중지
# service mysql restart //재시작
# service mysql status //상태확인

CentOS에 MariaDB 설치 후 한글이 깨지는 현상이 발견되었다.

인코딩 변환이 필요하였고, 처리 방법은 아래와 같다.

vim /etc/my.cnf.d/server.cnf

설정파일 열어주고 [mysqld] 아래쪽에 아래와같은 설정을 입력한다.

[mysqld]
init_connect="SET collation_connection=utf8_general_ci"
init_connect="SET NAMES utf8"
character-set-server=utf8
collation-server=utf8_general_ci

재시작을 한다.

systemctl restart mariadb

mariaDB를 이번에 centOS에 올리고 개발서버로 세팅 도중 프로시저만 복원하려고 하면 에러가 발생하였다.

에러 메시지처럼 아래의 명령어를 통해 업그레이드 후  mariaDB를 재부팅하여 해결하였다.

mysql_upgrade -u root -p

...

이후 서비스를 재시작한다.

systemctl restart mariadb

정상적으로 동작한다.

자바 소켓 프로그램 + 스케줄러로 구성된 프로세스를 실서버 운용중에 있었는데, 주말간 문제가 없었는지 확인해보니 주말사이에 스케줄러가 동작할때마다 에러를 뿜어내고 있었습니다.

[2020-07-18 11:00:00] ERROR (utils.SqlSessionUtils - selectList:92) - Exception
org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.apache.ibatis.transaction.TransactionException: Error configuring AutoCommit.  Your driver may not support getAutoCommit() or setAutoCommit(). Requested setting: false.  Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 86,387,538 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
	at kr.or.kisa.ktoaInterlock.utils.SqlSessionUtils.selectList(SqlSessionUtils.java:90)
	at kr.or.kisa.ktoaInterlock.dao.InterlockDao.selectYesterdayReportList(InterlockDao.java:95)
	at kr.or.kisa.ktoaInterlock.scheduler.InterLockScheduler.execute(InterLockScheduler.java:40)
	at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)

와 같은 에러를 출력중이였는데...

DB는 mysql을 사용중이며 해당 스케줄러는 하루에 한번 돌도록 구성되어 있습니다.

에러 내용을 가지고 검색을 해보니, 너무 오랫동안 mysql을 사용하지 않고 있다가 갑자기 사용하려고 하면 발생하는 에러로 mysql에서 오랫동안 사용을 하지 않으니 강제로 종료해버리는 이슈라고 합니다.

POOLED타입의 옵션을 수정할 필요가 있었고, 옵션들을 살펴봤습니다.

https://mybatis.org/mybatis-3/ko/configuration.html

공식문서를 참고하였고, 다른 포스팅 글들과 함께 참고하여 아래의 3옵션을 확인하였습니다.

poolPingQuery - 커넥션이 작업하기 좋은상태인지 요청을 받을 준비가 되었는지 등 체크하기 위해 DB에 핑쿼리를 날리는 문구를 지정합니다.

poolPingEnabled - 핑쿼리를 사용할지 안할지 설정합니다. default - false

poolPingConnectionsNotUsedFor - 핑쿼리를 얼마나 자주 사용할지 지정합니다. 핑쿼리를 사용할 경우에만 해당 옵션이 동작합니다.(ms)

mybatis 설정쪽에 의미없는 쿼리로 핑을 날리듯이 처리를 할 수 있다고하여 옵션을 추가하였고, 지켜보기로 하였습니다.

문제가 해결된다면 해당 포스팅은 여기서 종료하겠습니다.

설정 수정 옵션

<property name="poolPingQuery" value="select 1"/> <!-- 핑쿼리 추가 -->
<property name="poolPingEnabled" value="true"/> <!-- 핑쿼리 추가 -->
<property name="poolPingConnectionsNotUsedFor" value="7200000"/> <!-- 2시간마다 -->

위 내용처럼 ping처리를 할 수 있는 의미없는 쿼리와 사용하겠다는 옵션을 추가하였습니다.

모든 옵션

<environments default="development">
	<environment id="development">
		<transactionManager type="JDBC"/>
		<dataSource type="POOLED">
			<property name="driver" value="${driver}"/>
			<property name="url" value="${url}"/>
			<property name="username" value="${username}"/>
			<property name="password" value="${password}"/>
			<property name="poolPingQuery" value="select 1"/> <!-- 핑쿼리 추가 -->
			<property name="poolPingEnabled" value="true"/> <!-- 핑쿼리 추가 -->
			<property name="poolPingConnectionsNotUsedFor" value="7200000"/> <!-- 핑쿼리 추가 -->
			<property name="poolMaximumActiveConnections" value="100"/> <!-- 주어진 시간에 존재할수 있는 활성화된 커넥션 수 -->
			<property name="poolMaximumIdleConnections" value="100"/>	<!-- 주어진 시간에 존재할 수 있는 유휴 커넥션의 수 -->
			<property name="poolTimeToWait" value="20000"/>				<!-- 풀이 로그 상태를 출력하고 비정상적으로 긴경우 커넥션을 다시얻을려고 시도하는 로우 레벨 셋팅 --> 			
		</dataSource>
	</environment>
</environments>

기존 5버전대 mysql을 쓰다가 해당 구조만 가져올 일이 있어서 그대로 백업한 .sql파일을 복원 하는 과정에서 아래와같은 에러가 발생하였다.

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

함수 생성시 옵션 에러라고 하는데 버전이 올라가면서 기본값이 변경된 것 같다.

발생하는 에러로그를 토대로 검색해보니 함수 생성시 생성 제약처리를 할 수 있었고, 생성할 수 있는 부분이 OFF처리 되어있는걸 볼 수 있었다.

log_bin_trust_function_creators 옵션 확인하기

show global variables like 'log_bin_trust_function_creators';

해결방법
설치한 Mysql은 8버전대였고 아래의 쿼리를 실행 후 동일한 .sql파일 실행시 정상적으로 동작하였다.

SET GLOBAL log_bin_trust_function_creators = 1; //ON
#SET GLOBAL log_bin_trust_function_creators = 0; //OFF

1은 ON처리로 함수 생성을 시킬수 있다.

0은 OFF처리로 정상적으로 처리 후 다시 원복시 사용하거나 다른 사용자가 함수를 추가하지 못하도록 막을 수 있을것이다.

DB 컬럼에 primary key, unique 등을 사용하여 값의 중복을 방지할 수 있는데, 여러개의 컬럼을 동시에 체크하여 중복을 체크해야 하는 경우 여러개를 묶어서 unique 처리를 하면 된다.

ALTER TABLE 테이블명 ADD UNIQUE (컬럼1, 컬럼2, 컬럼3);

동시에 관리할 컬럼들을 UNIQUE() 안에 넣어주면 된다.

사용 예시)

형태로 c1, c2가 모두 중복되는걸 방지하기 위해 UNIQUE 처리하였다.

ALTER TABLE dupli ADD UNIQUE (c1, c2);

아래와 같은 샘플 데이터가 존재하는 상태에서

c1, c2에 1, 5를 각각 입력해보면 아래와 같은 유니크키 에러가 발생하는 것을 볼 수 있다.

특정 문자열에 데이터가 ','를 기준으로 들어가고 개수는 3개까지 제한되어 들어가지만 1개가 들어갈지 2개가 들어갈지 비어있을지 모르는 상황에서 해당 데이터를 쪼개고 JOIN을 하던지 SubQuery를 날리던지 해야하는 상황이였다.

쉽게 설명하자면 위 그림과 같은 상황이였다.

각각 컬럼별로 쪼개어 넣으면 좋았겠지만, 기존 프로젝트 구조 설계도 이런 기능이 지원되지 않는 상황이였고 어거지로 집어넣었기때문에 개수는 3개제한인점을 활용하여 각각 특수문자를 비교하고 서브쿼리를 통해 처리하기로 하였다.

처리한 방법

1. 먼저 구분자값인 ','의 개수가 몇개인지 파악할 필요가 있었다.

SELECT 
    SUM((CHAR_LENGTH(column)-CHAR_LENGTH(REPLACE(column,',',''))+1))
FROM TABLENAME

해당 쿼리를 사용하면 특정 데이터의 특수문자나 문자열등의 개수를 체크할수 있다.

사용예시)

2. 다음 해당 개수에 따른 CASE WHEN문을 통해 비교후 SPLIT 하였다.

각각 SPLIT된 데이터의 이름값을 concat_ws메소드를 통해 문자열을 다시 합쳐주었다.

SELECT
  CASE 
      SUM((CHAR_LENGTH(column)-CHAR_LENGTH(REPLACE(column,',',''))+1))

  #1개라면 특정테이블에서 바로 조회해온다.
  WHEN 1 THEN (SELECT columnName FROM R_FILEUPLOAD    WHERE no = column)

  #2개라면 ,기준으로 1개만 자른값을 비교하여 넣고
  #        2번째까지 가져오고 문자열을 합치다.
  WHEN 2 THEN (SELECT concat_ws(
      ','
      ,(select columnName from R_FILEUPLOAD 
      where no = SUBSTRING_INDEX(column, ',', 1))
      ,(select columnName from R_FILEUPLOAD
      where no = SUBSTRING_INDEX(SUBSTRING_INDEX(column, ',', 2), ',', -1))
  ))

  #3개라면 ,기준으로 1개만 자른값을 비교하여 넣고
  #        2번째까지 가져오고
  #        3번째까지 가져와서 문자열을 합친다.
  WHEN 3 THEN (SELECT concat_ws(
      ','
      ,(select columnName from R_FILEUPLOAD 
      where no = SUBSTRING_INDEX(column, ',', 1))
      ,(select columnName from R_FILEUPLOAD 
      where no = SUBSTRING_INDEX(SUBSTRING_INDEX(column, ',', 2), ',', -1))
      ,(select columnName from R_FILEUPLOAD 
      where no = SUBSTRING_INDEX(SUBSTRING_INDEX(column, ',', 3), ',', -1))
  ))
  END as REAL_NAME;

결과 데이터는 '테스트1,테스트2,테스트3' 의 형태로 처리되었다.

어쩔수 없는 상황에 임시대처로 해당방법을 사용하였고... 어지간하면 컬럼별로 쪼개어서 조인을 하는게 맞는것 같다...

저런형태로되면 나중에 볼때 기억도 안날것같고... 유지보수도 어려울것 같... ㅜㅠ