Data Science Lab

MySQL/MariaDB 파일 읽기/쓰기시 발생할 수 있는 secure-file-priv 오류

·       Version : MySQL 5.7

MySQL에서 LOAD DATA 및 SELECT.. INTO OUTFILE, LOAD_FILE() 함수를 사용할 경우, 아래와 같은 오류와 함께 실행이 실패되는 경우가 있다.

이러한 오류는 MySQL 시스템 설정 문제로, 보안과 관련이 있다. 즉, MySQL이 허용하는 경로의 위치에서만 파일을 읽고 쓸 수 있도록 지정된 것이며, 아래 스크립트를 통해서 현재 사용할 수 있는 경로를 확인할 수 있다.

결과가 NULL 일 경우 어떠한 경로도 지정되어 있지 않다는 뜻이며,  디렉터리 경로가 표시될 경우 해당 위치에서는 파일을 읽고 쓸수 있다는 뜻이다. 또한 아무 값이 표시되지 않으면, 어떠한 위치에서도 파일을 읽고 쓸 수 있다.

해당 값을 수정하기 위해서는 설정 파일에 아래 스크립트를 참고하며, 서비스 재시작이 필요하다. my.cnf 또는 my.ini의 mysqld 항목에 추가한다.

[참고자료]

https://dev.mysql.com/doc/refman/8.0/en/server-options.html#option_mysqld_secure-file-priv

2019-07-23 / 강성욱 / http://sungwookkang.com

MySQL, MySQL security, secure_file_priv, LOAD DATA, SELECT INTO OUTFILE, LOAD_FILE()

MySQL/MariaDB ROW Size Limit

·       Version : MySQL 5.7

SQL Server에서 사용하던 일부 데이터를 MySQL 서버로 마이그레이션 작업 진행중, 컬럼수가 많은 (또는 컬럼의 길이가 큰 테이블) 테이블이 MySQL에서는 아래와 같은 오류와 함께 테이블이 생성되지 않는 문제가 발생하였다.

해당 원인을 찾아본 결과, SQL Server의 경우 한 페이지의 크기가 8K로 고정이 되어있고, Row의 최대 사이즈는  8K 이다. 하지만  MySQL의 경우 InnoDB를 사용할 경우 페이지 사이즈를 사용자가 설정 할 수 있으며, 페이지에서 사용할 수 있는 최대 ROW크기는 페이지 크기의 약 절반보다 약간 작다. 예를들어 MySQL의 기본 Page 크기는 16K 인데, 최대 행 크기는 8K 보다 약간 작다.

·       Limits on InnoDB Tables :  https://dev.mysql.com/doc/refman/8.0/en/innodb-restrictions.html

·       Limits on Table Column Count and Row Size : https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html

또한 BLOB이나 TEXT의 경우 LOB 영역을 사용한다고 하더라도, 9~12바이트의 포인터 값을 가지고 있다.

최근 SSD의 보급으로 SSD 최적화된 아키텍처를 제공하기 위해 일부 서비스에서는 페이지의 크기로 4K를 할당하는 경우가 있다.  이 경우 한 로우에 저장할 수 있는 데이터의 크기가 작아지므로 미리 데이터를 설계할때 주의가 필요하다.

[참고자료]

·       Limits on InnoDB Tables :  https://dev.mysql.com/doc/refman/8.0/en/innodb-restrictions.html

·       Limits on Table Column Count and Row Size : https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html

2019-07-19 / 강성욱 / http://sungwookkang.com

MySQL, MySQL column limit, MySQL row size, MySQL Innodb, page size, Row size limit, MariaDB

MySQL/MariaDB sql_mode 설정 변경으로 NULL 데이터 처리하기

·       Version : MySQL 5.7

MS SQL Server에서 BCP를 이용하여 데이터를 탭 구분형식의 텍스트 파일로 내려받고 MySQL에서는 Bulk load(LOAD DATA INFILE)를 사용하여 데이터를 복원할때 아래와 같은 에러가 지속적으로 발생하였다.

이 문제는 Decimal 컬럼에 NULL값을 입력할때 MySQL의 유효성 문제로 에러가 발생한 것이다. 해당 컬럼은 NULL 허용 컬럼임에도 불구하고 지속적으로 동일한 문제가 발생하여, 해결 방법을 찾아본 결과 아래와 같은 sql_mode 변경으로 해결할 수 있었다.

Server SQL Mode 는 MySQL이 지원해야 하는 SQL 구문과 수행해야하는 데이터의 유효성 검사의 종류를 정의한다. MySQL 서버는 이러한 모드를 클라이언트에 개별적으로 적용할 수 있다. 운영중에 모드를 변경하려는 경우, SET 명령을 사용할 수있으며, GLOBAL 또는 SESSION  변수를 사용할 수 있다. GLOBAL 변수의 경우 SUPER 권한이 필요하며, 설정 이후 모든 클라이언트의 작동에 영향을 준다. SESSION은 현재 클라이언트에만 영항이 있다. 현재 설정된 sql_mode 값을 확인하려면 아래 스크립트를 실행한다.

. MySQL 5.7 경우 기본SQL 모드는 아래와 같다.

이번 해결은 유효성 검사를 비활성화 하여 해결하였지만 Sql_mode 변경은 시스템 환경을 변경하는것으로 반드시 잘 알고 사용해야 하며 다른 서비스에 영향이 없는지 반드시 검토가 필요하다.  자세한 내용은 아래 링크를 참고 한다.

·       Server SQL Mode : https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

 [참고자료]

·       Server SQL Mode : https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

·       FAQ : Server SQL Mode : https://dev.mysql.com/doc/refman/5.7/en/faqs-sql-modes.html

2019-07-05 / 강성욱 / http://sungwookkang.com

MySQL, MySQL sql_mode, 유효성 검사, 벌크로드, NULL Exception, NULL 값처리, 데이터 마이그레이션, LOAD DATA INFILE

MySQL/MariaDB Timezone 설정 및 확인

·       Version : MySQL 5.7, Docker

MySQL/MariaDB를 운영할때, 타임존에 대해서 알아본다. 아래 스크립트는 현재 데이터베이스의 타임존을 확인한다. 타임존이 SYSTEM 으로 표시되면 별도의 타임존 설정이 되어 있지 않다는 뜻이며 시스템의 타임존을 사용하겠다는 뜻이다.

데이터베이스 설정에서 default-time-zone 변경 방법은 my.ini (my.cnf)에서 [mysqld] 영역에 타임존을 추가한다. 아래 스크립트는KST로 타임존을 설정하였으며 스크립트 적용 후MySQL 서비스 재시작이 필요하다.

다른 방법으로는 현재 서비스 상태에서 글로벌, 또는 세션 변수의 값을 명시적으로 설정할 수 있다. 이 방법은 서비스를 재시작 하지 않아도 된다.

어플리케이션에서 연결시에도 타임존을 지정할 수 있다. 이 경우 세션으로 작동한다. JDBC URL에 추가하는 방법은 아래 코드를 참고 한다.

Timezone의 값은 여러 가지 포맷으로 사용할 수 있으며 대소문자 구분이 없다.

·       SYSTEM은 MySQL이 시스템의 타임존과 동일한 설정을 사용

·       ‘+9:00’ 또는 ‘-6:00’과 같이 GTM/UTC 기준 Offset 사용

·       US/Eastern, Asia/Seoul 과 같은 named timezone 사용

named timezone 형태의 값을 사용하려면  “mysql” 데이터베이스에 “time_zone”, “time_zone_name”, “time_zone_transition”, “time_zone_transition_type”, “time_zone_leap_second” 테이블을 참조하는데 실제 이 테이블은 빈테이블이기 때문에 사용자가 데이터를 입력해주어야 한다. 타임존 데이터는 아래 링크에서 정보를 다운로드 받아 스크립트를 실행한다. 스크립트를 살펴보면 각 타임존 정보를 확인할 수 있다.

·       https://dev.mysql.com/downloads/timezones.html

[참고자료]

·       https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

2019-06-12 / 강성욱 / http://sungwookkang.com

MySQL, MySQL Timezone, 타임존, DB time, time_zone, time_zone_name, time_zone_transition, time_zone_transition_type, time_zone_leap_second, named timezone