Mysql - csv파일 불러올 때 발생하는 에러들 해결하기

시작하기 전에.. 터미널에 아래와 같이 입력하면 비밀번호 입력없이 Mysql에 접속할 수 있다.

MYSQL_PWD='{비밀번호 입력}' mysql -u root < {파일명}.sql

 

 

mysql로 csv파일로 불러올 때, 여러 에러메세지들이 발생하였다. 차근차근 해결해보자!

 

에러메세지 1 - 백슬러시

ERROR 1261 (01000) at line 1: Row 13 doesn't contain data for all columns

 

해당 에러는 백슬러시 \ 때문에 발생하는 문제로 데이터에 \가 포함되어 있기 때문이다. 이를 무시하게끔 만드려면 FIELDS TERMINATED BY 옆에 혹은 아래에 ESCAPED BY '' 코드를 추가하면 된다.

ESCAPED BY ''

 

 

 

에러메세지 2 - 인코딩

해당 에러는 인코딩문제로 나타나는 에러인데 여러가지 해결방법이 있지만, 나의 경우에는 CHARACTER SET latin1 을 추가했더니 해결되었다.

 

Try-1) INTO와 FIELDS 사이에 CHARACTER SET utf8mb4 추가

LOAD DATA INFILE '~/data/csv/240717/csv.csv'
INTO TABLE history_db.tmp_cmd_usage
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ESCAPED BY ''
LINES TERMINATED BY '\n';

> 해결되지 않음

 

Try-2) my.cnf 파일에 아래 코드 추가

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

 > 해결되지 않음

 

Try-3) CHARACTER SET utf8 대신 latin1 사용

LOAD DATA INFILE '~/data/csv/240717/csv.csv'
INTO TABLE history_db.cmd_usage
CHARACTER SET latin1
FIELDS TERMINATED BY ',' ESCAPED BY ''
LINES TERMINATED BY '\n';

> 해결됨

> 이유를 찾아보려 했으나 잘 모르겠다..모든 경우의 수를 해보았는데 latin1만 에러가 나지 않았다..

 

 

에러메세지 3 - 구분자

ERROR 1262 (01000) at line 1: Row 3 was truncated; it contained more data than there were input columns

 

위 에러메세지가 발생한 이유는 테이블 안에 Row를 3개 만들고 " , " 기준으로 잘라서 각 Row에 넣겠다.'고 명령을 하였는데 " , " 이 문자가 3개가 되면 만든 Row보다 많아지는 것이기 때문에 Row 에러가 난 것이다. 

2024-07-17,",14
2024-07-17,"\,14
2024-07-17,##b,c를,12
2024-07-17,##ì<83><80><80>,14
2024-07-17,./install.sh,14
2024-07-17,./s00zzang_lotto.py,14
2024-07-17,./sudden.dh,14
2024-07-17,./sudden.sh,14
2024-07-17,./sv,sh,14

 

구분자때문에 에러가 난 것이라 아예 불러올 데이터 자체에 자를 문자들을 ^ 로 감싼 후 처리하였다.

 

데이터 처리 예시)

 

Try-1) 데이터 자체를 '^' 로 감싸고 ENCLOSED BY '^' 추가

mysql -u"$user" -p"$password" "$database" <<EOF
LOAD DATA INFILE '~/data/csv/240717/csv.csv'
-- LOAD DATA INFILE '${CSV_PATH}'
INTO TABLE history_db.tmp_cmd_usage
CHARACTER SET latin1
FIELDS TERMINATED BY ',' ESCAPED BY '' ENCLOSED BY '^'
LINES TERMINATED BY '\n';
EOF

*ENCLOSED BY 는 ~으로부터 감싸져있다. 라는 의미로 내 데이터는 '^로 감싸져 있다~' 라고 명시해준 것이다.

 

에러메세지 4 - Load Local data error

3948 (42000) at line 2: Loading local data is disabled; this must be enabled on both the client and server sides

 

Mysql로 Local 에서 csv 파일 가져오려 한다. 따라서 LOAD DATA LOCAL ~ 을 추가하였더니 해당 메세지가 나타났다.

mysql --local-infile=1 -u"$user" -p"$password" "$database" <<EOF
LOAD DATA LOCAL INFILE '~/data/csv/240717/csv.csv'
INTO TABLE history_db.tmp_cmd_usage
CHARACTER SET latin1
FIELDS TERMINATED BY ',' ESCAPED BY '' ENCLOSED BY '^'
LINES TERMINATED BY '\n';
EOF

 

Try) my.cnf 파일에 아래 코드 추가

local-infile=1

위 코드를 my.cnf 파일에 추가하고 Mysql 을 restart 하면 해결이 된다.

 

에러메세지4를 해결하면서... 별의별짓을 다한...

나는 에러메세지4 를 해결하려다 이상한걸 건드려서 더 복잡해졌다..

 

내가 본 에러메세지는 이거였다.

ERROR 1085 (HY000) at line 3: The file '/usr/local/var/mysql/history_db/' must be in the database directory or be readable by all

 

읽기권한으로 바꿔주라길래... 바꿨는데도 해결이 되지 않아 더 구글링하니 권한소유자를 바꾸라고 하였다.

sudo chown -R mysql:mysql /usr/local/var/mysql/history_db/

그래서 이렇게 바꿨더니만, 더 큰 문제가 생겼다.

내 history_db로 내가 들어갈 수 없게 되었다...ㅋ 당연히 소유자를 바꿨으니.. 내가 소유자가 아니라서 연결이 안되는 상황이 벌어졌고, 이걸 다시 복구하려면 아래 코드를 입력해야 했다.

sudo chown -R {original_user}:{original_group} /usr/local/var/mysql/history_db/

원상복구를 위해 원래 소유자와 그룹을 복구해야 하는데, 이를 위해서는 파일과 디렉터리의 원래 소유자와 그룹을 알아야 한다.

만약 원래 소유자와 그룹을 알고 있다면 다음 명령어를 사용하여 원상복구할 수 있다.

{original_user} 가 원래 소유자 {original_group}이 그룹...

 

근데 나는 그걸 모르는데? 설마 못바꾸나...? 했는데,

다행히 아래 코드로 소유자와 그룹을 확인할 수 있었다.

ls -l /usr/local/var/mysql/

이 코드를 치면, 소유자와 그룹이 보인다! 그럼 그걸 그대로 {original_user}와 {original_group} 자리에 입력하면 된다!

다른 파일들의 소유자와 그룹을 확인하면 알 수 있다. 그리하여, 다시 나의 history_db에 연결할 수 있었다..

 

그럼 위에 에러메세지는?

저건 내가 파일 안에 mysql --local-infile=1 을 추가하지 않았어서 나온 에러메세지였다. 내가 잘못해놓고 에러나서 해결하려다 이렇게 된것이다.. 그래도 여러가지 에러를 만나니 에러해결 스킬이 느는 것 같다.