JDBC에서 LOAD DATA INFILE 오류발생

Workbench나 mysql console에서는 잘 되는데 JDBC로 접속해 "LOAD DATA INFILE"을 하면 안될 때가 있다. 컬럼 개수가 안 맞는다거나 형태를 바꿀 수 없다는 오류 메시지만 계속 나올 뿐이다.

이 문제는 지정한 데이터 파일 내 빈 필드값이 있을 때 간혹(?) 발생한다. 그렇니까, 다음과 같이 구분자가 연속으로 나오는 경우이다.

COL1, COL2, COL3
1,A,B
2,,C
3,D,

해결방법은 데이터 파일의 빈필드에 문자열 '\N'을 명시적으로 넣어 NULL임을 알려 주는 것이다.

COL1, COL2, COL3
1,A,B
2,\N,\NC
3,D,\N

이 방법이 여의치 않다면 "LOAD DATA INFILE" 명령을 다음과 같이 실행하여 해결할 수도 있다.

LOAD DATA INFILE 'data file path'
REPLACE INTO TABLE_NAME
FIELD TERMINATED BY ','
LINES TERMINATED BY '\n'
(@V1, @V2, @V3)
SET COL1 = NULLIF(@V1, '')
  , COL2 = NULLIF(@V2, '')
  , COL3 = NULLIF(@V3, '')
;

데이터를 넣을 테이블에는 컬럼 COL1, COL2, COL3가 있으며, 해결책의 키-포인트는 "SET"을 이용한 부분이다.

MySQL 재시작 방법

RedHat 계열의 Linux에 설치된 MySQL을 재시작하려면

# service mysqld restart
or
# /etc/init.d/mysqld restart

비슷하게 시작, 종료하려면,

# service mysqld [start | stop]
or
# /etc/init.d/mysqld [start | stop]

MySQL 한글이 안될 때

한글이 안 되는 경우

MySQL 사용 시 한글이 안 될 경우 세 부분을 의심해 보아야 한다.

1. MySQL 서버 characterset 설정.
2. 사용하고 있는 툴의 문자 인코딩 지원 종류.
3. 쿼리를 저장한 파일 혹은 데이터 파일의 문자 인코딩 방식.

한글을 사용하려면 문자 인코딩 방식을 euc-kr나 Unicode를 지원해야 한다. 요즘 쿼리 툴은 대부분 Unicode 중 utf-8을 지원하기 때문에 2번은 크게 문제가 되지 않는다. 물론 확인은 해 봐야 한다. 3번도 무료로 사용할 수 있는 Sublime Text 등을 이용하면 쉽게 확인할 수 있다. 문제는 1번이다.

MySQL을 설치하고 utf-8을 기본으로 제공해 주면 좋은데 그렇지가 않다. 다음은 설치 후 MySQL의 상태를 확인한 화면이다.

mysql> status
--------------
mysql  Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (x86_64) using readline 5.1
 
Connection id:          20
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.95 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 6 days 5 hours 2 min 54 sec
 
Threads: 1  Questions: 41  Slow queries: 0  Opens: 13  Flush tables: 1  Open tables: 6  Queries per second avg: 0.000
--------------

mysql> 

문자 인코딩 방식이 latin1이다. 이러니 한글이 안되지... utf-8으로 바꿔보자.

MySQL Characterset 바꾸기

문자셋을 바꾸려면 /etc/my.cnf 파일의 해당 부분에 다음 내용을 추가한다.

# vi /etc/my.cnf
 
[mysqld]
...
default-character-set=utf8
collation-server=utf8_unicode_ci
character-set-server=utf8
character-set-client-handshake=FALSE
...

[client]
default-character-set=utf8
...    

그리고 나서 재시작. 다시 상태를 확인해 보면,

mysql> status
--------------
mysql  Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (x86_64) using readline 5.1
 
Connection id:          2
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.95 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 22 sec
 
Threads: 1  Questions: 5  Slow queries: 0  Opens: 12  Flush tables: 1  Open tables: 6  Queries per second avg: 0.227
--------------

mysql> 

이제 한글을 사용할 수 있다.

MySQL / LOAD DATA INFILE 실행 시 ERROR 13

구분자로 구분되어 있는 텍스트 파일에서 데이터를 빠르게 올리기 위하여 LOAD DATA INFILE 구문을 사용한다. 예전에 Sybase ASIQ에서도 비슷한 걸 사용한 적이 있는데 INSERT 문을 건건이 사용하는 것보다 비교가 안 될 만큼 빨랐다. MySQL도 마찬가지다. 그런데 다음과 같은 오류가 날 때가 있다.

ERROR 13 (HY0000): Can't get stat of '/home/~' (Errcode: 13)

파일('/home/~')의 stat(상태? 통계? 뭘까?)을 얻을(get) 수 없다니... 이 메시지만 봐서는 무슨 오류인지 알 수가 없다.

알고 보니, 지정한 파일을 읽을 수 없는 경우에 나타나는 오류 메시지이다. 그리고 읽을 수 없는 경우는 다음과 같은 것들이 있다.

• Others에 읽기 권한이 없는 경우.
• Others가 파일경로 내의 디렉토리에 접근할 수 없는 경우.
• 지정한 파일이 MySQL 서버 내에 없는 경우.
• "LOAD DATA INFILE ~" 구문을 실행 권한이 없는 경우.

위와 같은 문제들을 해결하면 아주 잘~ 된다.

마지막 문제는 mysql 쿼리 환경에서

mysql> show variables like 'local%';

문장을 실행하여 나오는 값 중 local_infile이 ON이어야 한다. 만약, ON이 아니라면 mysql을 실행할 때 다음과 같이 "-local-infile=1"옵션을 주면 된다.

$ mysql -h 'host' -u 'userName' -p -local-infile=1

끝!