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

 

·       Version : MySQL 5.7

 

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

Error Code: 1366. Incorrect decimal value …

 

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

SET SESSION sql_mode = ''

 

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

SELECT @@GLOBAL.sql_mode;

SELECT @@SESSION.sql_mode;

 

 

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

ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION

 

이번 해결은 유효성 검사를 비활성화 하여 해결하였지만 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 으로 표시되면 별도의 타임존 설정이 되어 있지 않다는 뜻이며 시스템의 타임존을 사용하겠다는 뜻이다.

SELECT @@GLOBAL.time_zone, @@SESSION.time_zone, @@system_time_zone;

 

 

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

[mysqld]

default-time-zone='+9:00'

 

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

SET GLOBAL time_zone='timezone;

SET time_zone='timezone;

 

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

<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">

  <property name="driverClassName" value="com.mysql.jdbc.Driver"/>

  <property name="url" value="jdbc:mysql://127.0.0.1:3306/ db?serverTimezone=UTC" />

  <property name="username" value="admin"/>

  <property name="password" value="admin"/>

</bean>

 

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

 

SQL Server MySQL PostgreSQL 비교

 

·         Version : SQL Server MySQL PostgreSQL

 

RDBMS 많이 사용하는 SQL Server MySQL 그리고 PostgreSQL 특징 차이점에 대해서 살펴본다. 데이터베이스 기능이 너무나 많기 때문에 모두 다루지는 못하며 대표적인 내용 몇가지만 다루도록 한다.

·         MySQL PostgreSQL, SQL Server 데이터베이스 모두 ACID 완벽하게 지원하며 많은 양의 데이터와 높은 수준의 쿼리 동시성을 처리할 있다.

·         PostgreSQL 기능이 풍부하고 확장성이 뛰어나다

·         MySQL 많은 곳에서 사용하고 있으며 애플리케이션과 전자상거래 프로젝트에 적합하며, PostgreSQL 비해 블로그, 지원, 문서가 훨씬 많다

·         PostgreSQL 경우 연결마다 자체 메모리가 있기 때문에 연결수가 많은 환경의 경우 많은 메모리가 필요할수 있다. 그러나 PgBouncers 외부 연결 풀을 사용하여 이러한 문제를 해결할 있는 솔루션이 있다.

·         PostgreSQL 수동 파티션 관리는 너무 많은 오버헤드와 파티션에서 다른 파티션으로 행을 이동시키는 업데이트가 필요하다.

·         MySQL에는 쿼리당 1개의 CPU 사용하는 중첩루프 조인 알고리즘만 있어 MySQL 데이터웨어우스 시스템에는 적합하지 않다.

·         Check 제약 기능이 중요한 서비스라면 MySQL 적합하지 않다.

 

[MySQL, PostgreSQL, SQL Server일반 정보]

 

MySQL

PostgreSQL

SQL Server

Maturity

1995 릴리즈

1989 릴리즈

1989 MSMS OS/2 SQL Server릴리즈(Sybase 함께)

1995 SQL Server 6.0릴리즈

Language

C (일부 C++)

C

C++

Cost

오픈소스 / 오라클 소유의 유료버전

완전 무료 / 오픈소스

SQL Server Express 무료버전과 외의 유료버전

 

[MySQL, PostgreSQL, SQL Server 데이터 변경]

 

MySQL

PostgreSQL

SQL Server

Row Update

업데이트가 수행되고 변경된 데이터가 롤백 세그먼트로 복사. Vacuum 인덱스 압축이 매우 효율적. MySQL 읽기에는 속도가 느리지만 쓰기는 원자적이며 보조 인덱스의 열이 변경되어도 모든 인덱스를 변경할 필요가 없음

업데이트는 인서트 + 삭제 표시로 구현된다. 모든 색인에는 행의 실제 ID 대한 링크가 있다. 열이 업데이트되면 새로운 물리적ID 있는 행이 만들어지고 모든 행이 새로운 행의 실제ID 대한 포인터를 얻기 위해 변경된 열을 참조하지 않는 경우에도 모든 인덱스가  업데이트 되어야 하기 때문에 업데이트 오버헤드가 발생

Row-Store 데이터베이스 엔진 :

 

인메모리 데이터베이스 엔진 : 업데이트는 인서트 + 삭제 표시로 구현. 가비지 컬렉터는 블럭킹 병렬로 작업

 

Columnstore 데이터베이스 엔진 : in-place 업데이트

Vacuum / Defragmentation

Vacuum 인덱스 압축은 매우 효율적

Vacuum 전체 테이블 스캔을 수행하여 삭제 행을 찾는다. 프로세스/사용자의 작업에 오버헤드를 있다.

메모리 가비지 컬렉턴는 최대 15% 오버헤드가 발생할 있다.

 

[MySQL, PostgreSQL, SQL Server 데이터 쿼리]

 

MySQL

PostgreSQL

SQL Server

쿼리 요청에 대한 Buffer Pool / Cache

MySQL 캐시는사용자 쿼리를 버퍼풀이라고 한다. 캐시는 필요에 따라 크기로 설정할 있으므로 서버의 다른 프로세스에 충분한 메모리만 남겨둔다. 버퍼 풀을 여러 부분으로 분할하여 메모리 구조에 대한 경합을 최소화하고 테이블을 버퍼 풀에 고정할 있다. 테이블 스캔 또는 mysqldump 이전 데이터를 제거한다.

PostgreSQL 데이터 페이지를 위한 공유 메모리를 유지한다. 프로세스 기반 시스템이기 때문에 연결은 고유한 고유  OS 프로세스를 가지며 자체 메모리를 가지고 있다. 프로세스는 실행이 끝난 메모리를 해제 한다. 따라서 많은 연결을 확장하는데 문제가 발생할 있다.

SQL Server 메모리는 버퍼풀이라고 하며 크기는 필요에 따라 크게 설정할   있으며 여러 버퍼 풀을 설정하는 옵션은 없다.

제약 조건 지원

기본키, 외래키, not null 제약 조건, 고유 제약조건, 기본 제약조건을 지원. CHECK 제약 조건을 지원하지 않는다.

기본 , 외래키, not null 제약 조건, 체크 제약 조건, 유니크 제약 조건, 기본 제약 조건을 지원한다.

기본 , 외래키, not null 제약 조건, check 제약 조건, 고유 제약 조건, 기본 제약 조건을 지원한다.

임시 테이블

CTE 지원하고 전역 임시 테이블 테이블 변수를 지원하지 않음.

동일한 쿼리에서 TEMPORARY 테이블을 번이상 참조할 없음. ( : select * from tbl_temp t1 join tbl_temp as t2)

CTE, 전역 로컬 임시 테이블 테이블 변수를 지원.

동일한 이름을 가진 개의 테이블을 만들면 다른 하나는 일반 테이블이다.

Creatae temp table x (..)

Create table x (…)

Select * from X 항상 임시테이블에서 데이터를 가져옴

CTE, 전역 로컬 임시테이블 테이블 변수 지원

Windows /Analytical 함수

CUME_DIST, FIRST_VALUE, LAG, LAST_VALUE, LEAD, PERCENT_RANK, ROW_NUMBER, RANK, DENSE_RANK, NTILE, NTH_VALUE PERCENTILE_CONT, PERCENTILE_DISC

CUME_DIST, FIRST_VALUE, LAG, LAST_VALUE, LEAD, PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK, ROW_NUMBER, RANK, DENSE_RANK, NTILE, NTH_VALUE

CUME_DIST, FIRST_VALUE, LAG, LAST_VALUE, LEAD, PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK, ROW_NUMBER, RANK, DENSE_RANK, NTILE

병렬 쿼리 실행

MySQL 일반적으로 쿼리당 1개의 CPU 사용

쿼리 계획은 여러 CPU 활용할 있음

쿼리 계획은 여러 CPU 활용할 있음

인덱스

인덱스 구성 테이블을 지원 클러스터 인덱스.

지속된 인덱스/구체화된 뷰를 지원하지 않음

인덱스 구성 테이블 미지원.

지속된 인덱스 / 구체화된 지원

인덱스 구성 테이블을 지원 클러스터 인덱스

단일 쿼리에서 다중 인덱스 사용

단일 쿼리에 다중 인덱스 사용 가능

단일 쿼리에 다중 인덱스 사용 가능. Xy 별도 인덱스가 있는 경우 WHERE x=5 and y=6 같은 쿼리를 구현할 있는 방법 하나는 적절한 쿼리 절과 함께 인덱스를 사용한 다음 인덱스 결과를 AND 결합하여 결과 행을 식별하는 것이다.

단일 쿼리에 다중 인덱스 사용 불가능

다중 컬럼 인덱스

다중 인덱스에는 최대 16개의 열을 포함할 있음

다중 인덱스에는 최대 32개의 열을 포함할 있음

다중 인덱스에는 최대 16개의 열을 포함할 있음

부분 인덱스

부분 인덱스를 지원하지 않음

부분 인덱스 지원

부분 인덱스 지원

JOIN 알고리즘

MySQL 중첩 루프 알고리즘 또는 변형을 사용하여 테이블간의 조인을 실행

중첩 루프 조인, 해시 조인 병합 조인 알고리즘 지원

중첩 루푸 조인, 해시 조인 병합 조인 알고리즘 지원

쿼리 실행 계획 재사용

준비된 명령문 저장된 프로그램에 대한 캐시를 세션별로 유지. 세션에 대해 캐시된 명령문은 다른 세션에서 액세스할 없음

준비된 문이 열려있는 동안에만 쿼리 계획을 캐시함. 쿼리 계획은 준비된 문이 클로즈때 해제됨.

쿼리가 실행계획을 다시 사용할 있도록 공유 실행계획 캐시가 있음

통계

지속성 지속성 통계 유지 (서버 재시작시 지워짐)

 

 

메모리 최적화 테이블

MySQL 테이블을 메모리에 저장할 있다. 메모리에 작성된 테이블은 트랜잭션을 지원하지 않으므로 데이터가 손상될수 있다. 이러한 테이블은 임시 영역 또는 읽기 전용 캐시로 사용해야한다.

메모리 엔진을 제공하지 않음

메모리 OLTP SQL Server 데이터베이스 엔진에 통합되어 있음

Columnstore 또는 저장소

MariaDB 최근에 여러 서버가 있는 환경에서 대규모 병렬 데이터베이스로 설계된 MySQL 저장소 엔진을 추시. InnoDB 스토리지 엔진 대신 사용 가능

저장소, 컬럼형 스토리지 엔진을 제공하지 않음

SQL Server 테이블을 쿼리하기 위해 저장소 인덱스를 제공

 

[MySQL, PostgreSQL, SQL Server JSON 데이터 유형]

 

MySQL

PostgreSQL

SQL Server

JSON 데이터 유형

MySQL JSON 데이터 형식을 지원하며 전체 문서를 바꾸는 대신 JSON 통한 부분 업데이트를 지원. 그러나 많은 제한이 있다. JSON 대한 인덱스 생성은 지원하지 않음

PostgreSQL JSON 데이터 유형을 지원하며 부분 업데이트를 지원한다.

SQL Server JSON 데이터 형식을 지원하며 부분 업데이트를 지원

고급 데이터 형식

지형 공간 데이터 유형을 지원한다. 사용자 정의 유형이 없다.

지형 공간 다차원 배열, 사용자 정의 형식 등과 같은 많은 고급 데이터 형식을 지원

지형 공간 데이터 유형, 계층 데이터 지원

 

[MySQL, PostgreSQL, SQL Server 샤딩/파티셔닝/복제]

 

MySQL

PostgreSQL

SQL Server

파이셔닝 지원

HASH 파이셔닝(모든 컬럼에서 HASH 함수를 사용하여 테이블을 N개의 파티션으로 분할), 여러 컬럼을 기반으로 하는 RANGE 또는 LIST 파티셔닝, HASH 유사한 KEY 파티셔닝(자동생성된 숫자 기반) 지원

RANGE LIST 파티셔닝을 지원하지만 파티션 인덱스는 수동으로 생성해야하며 테이블 상속을 통한 구식 파티셔닝이 필요(부모 테이블을 쿼리할때 모든 하위 테이블도 쿼리가 될때 하위 테이블은 파티셔닝 컬럼에 제약이 있음)

하위 테이블에는 하위 테이블에 부모 테이블과 인덱스를 별도로 적용해야하는 열이 많을 있음

RANGE 파이셔닝을 지원

샤딩 지원

공유를 구현하지 못함(MySQL Cluster 많은 제한 사항 때문에 거의 배포되지 않음)

공유를 구현하는 Postgres 포크는 수십가지가 있지만 아직 커뮤니티 출시에 추가된 것은 없음

표준 공유 구현이 없음

복제

명령문 또는 변경된 행을 기반으로 하는 마스터-슬레이브 복제.

그룹 복제는 마스터 서버에서 자동으로 복제

변경된 로그 전달을 기반으로하는 마스터-슬레이브 복제

데이터베이스 수준 : 가용성 그룹의 마스터-여러 슬레이브

로그전달

On Data level : 마스터-슬레이브 /양방향 마스터-슬레이브 / 마스터-마스터(병합) 복제

 

[참고자료]

https://www.mssqltips.com/sqlservertip/5745/compare-sql-server-mysql-and-postgresql-features/

 

 

2018-10-30 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, MSSQL, MySQL, PostgreSQL


ETL data error with MariaDB ODBC 3.0 (from Aurora to MS SQL)

 

·      Version : SQL Server 2016, AWS Aurora, Maria ODBC 3.0

 

최근 다양한 DB환경이 구성되면서 DW(Data Warehouse) 구축할 , ETL 작업 또한 이기종 DB (데이터베이스가 다른 종류간의 ETL) 작업이 증가하는 추세이다. 필자는 현재 다양한 데이터베이스를 구축 운영하고 있으며 MySQL 에서 MS SQL ETL과정중 예기치 못한 데이터 오류가 많이 발생 하였다. AWS 있는 Aurora (MySQL 5.6) 데이터 원본에서MS SQL Server ETL하는 과정에서 Decimal값이 정상적으로 표시되지 않고 /100 값으로 변환되어 출력되어지는 문제점이 발견되었다.


 

ETL방식은 SQL Server에서 MariaDB ODBC 3.0 Driver 사용하여 AWS Aurora 데이터베이스 데이터를 오픈 쿼리 방식으로 가져온다. 모든 데이터에 대해서 /100 으로 변환되지는 않으며 decimal 데이터 타입에서만 변환되는것을 확인하였다. 구글에서 Maria odbc decimal error 검색해보니 이미 많은 사용자들이 해당 이슈를 보고 하였고 관련 솔루션도 소개되어 있다.


 

다양한 해결 방법중에 MariaDB ODBC 3.03 Driver 에서는 해당 이슈가 해결되었다는 글이 있어 드라이버를 업그레이드 진행 하였다. 그리고 테스트 결과 정상적으로 값이 출력됨을 확인할 있었다.


 

 

각종  ODBC Driver 대한 버전은 ODBC Data Source Administrator에서 Drivers 탭에서 확인할 있다.


 

·       MariaDB ODBC Driver 설치 또는 업그레이드시 시스템 재부팅이 요구되므로 주의한다.

 

 

2018-05-30 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

Aurora My5.6, ETL, MariaDB ODBC driver error, ODBC decimal error, ETL, ODBC driver, MySQL, MariaDB, MSSQL


  1. 악의 꽃 다시보기 2020.07.30 11:56

    잘 보고 갑니다~~

+ Recent posts