MySQL BLOB, TEXT size limit

테스트를 진행하던 중에 textarea 값을 DB로 던졌더니 무시무시한 500 에러가 떨어졌다.

내용을 보니 INSERT 시에 컬럼의 제한보다 긴 문자열이 INSERT를 시도해서 발생한 에러였다.

문제가 된 컬럼을 확인하니 데이터 타입은 TEXT.

헐?

도대체 얼마나 긴 문자를 집어 넣었길래 이런 문제가 발생했을까..

입력한 문자들이 HTML형태로 변환되기에 입력 값이 조금 많을 것이라는 예상을 했지만..

TEXT 타입의 제한을 넘겨버릴 줄은 생각도 못했다.

에러를 발생시킨 장본인을 찾아갔더니..

첨부파일 기능이 없어서 PDF의 내용을 모두 복사시킨 후 붙여넣었다고 한다..

그랬더니 이렇게 에러가 떨어졌다고..



 

그래요..예외처리를 하지 않은 개발자 잘못이죠 ㅠㅠ

MySQL의 BLOB과 TEXT는 아래와 같은 사이즈로 저장이 된다.

TINYBLOB, TINYTEXT       L + 1 bytes, where L < 2^8    (255 Bytes) BLOB, TEXT               L + 2 bytes, where L < 2^16   (64 Kilobytes) MEDIUMBLOB, MEDIUMTEXT   L + 3 bytes, where L < 2^24   (16 Megabytes) LONGBLOB, LONGTEXT       L + 4 bytes, where L < 2^32   (4 Gigabytes)

TEXT 타입은 UTF-8이 3바이트씩 저장된다고 할 때, 한 2만자는 넘게 저장할 수 있는 크기다.

그 이상의 길이를 저장하기 위해서는 MEDIUMTEXT 타입으로 수정해야 하지만..

64K의 저장공간에서 16M의 저장공간으로 증가하는 것은 저장공간에 있어서 엄청난 타격을 입게 된다.

신중히 결정해야할 사항이다..

그래서 나는 약 64K 이상의 문자를 입력받으면 문자열 길이에 대한 경고를 출력하도록 수정하였으며, (실제로는 여유있게 약 50K)

첨부파일을 첨부할 수 있도록 변경하였다.

MySQL 문자열 replace

MySQL에서는 문자열을 replace하기 위한 함수를 제공한다.

replace('문자열','찾을 문자','바꿀 문자')

위와 같이 사용한다.

그런데 이미 들어있는 데이터를 바꿔서 넣고 싶을 때는 어떻게 해야할까?

아주 쉽다.

UPDATE문에 replace 함수를 사용하면 된다.

아래는 줄바꿈이 되어있는 텍스트를 HTML 형태의 줄바꿈(BR태크)으로 변경하는 쿼리이다.

mysql> update target_table set text_data = replace(text_data,'\n','<br/>');

MySQL Workbench에서 update 실행 시 1175 에러

MySQL Workbench에서 update 명령 실행 시 Error code 1175가 떨어지는 경우가 있다.

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.

이 에러는 WHERE절에 key(index) 컬럼이 조건으로 들어가 있지 않은 경우에 발생한다.

이를 해결하기 위해서는 WHERE절에 key(index) 컬럼을 조건으로 넣어 주거나 아래 환경변수를 설정해 주면 해결된다.

SET SQL_SAFE_UPDATES=0;

 

MySQL 연동하기

PHP에서 MySQL에 연결해서 쿼리를 보내 결과를 출력하는 예제이다.

자주 쓰지만 쓸 때마다 찾아서 쓰게된다..

이런 기본적인 소스는 저장 해놨다가 꺼내 써야지 ㅋㅋ

<?php
	$mysqli = mysqli_connect("localhost", "root", "passwd", "test", 3306);

	if (mysqli_connect_errno($mysqli)) {
	    echo "Failed to connect to MySQL: " . mysqli_connect_error();
	}

	$res = mysqli_query($mysqli, "SELECT a FROM t");

	while($row = mysqli_fetch_assoc($res)) {
		echo $row['a'];	
	}
?>

 

Kakao DB Team 블로그

카카오의 DB팀에서 MySQL 기술 사례를 공유하는 블로그를 오픈했다.

작년 9월에 오픈해서 10개의 포스팅을 한 것이 전부이지만..

그래도 내용면에서는 참고할만한 것들이 많았다.

내가 MySQL 기술지원을 할 때 겪었던 내용들..

정리를 해놓았다면 어땠을까..하는 생각이 들었다.

아무튼 MySQL, MariaDB를 하시는 분들은 한번쯤 봐도 괜찮을 것 같다.

[ 카카오 DB팀 블로그 ]

데이터베이스 사랑넷이 죽었다ㅠ

MySQL 기술지원을 하면서 많은 정보를 공유했던 데이터베이스 사랑넷이 죽었다..

이제 운영을 안하는 것일까..?

거의 새글이 올라오지 않고 Q&A만 간간히 올라오기에..

조만간 없어질 것 같다는 생각이 들긴했는데..

조금 아쉽다..

내가 데이터베이스 커뮤니티를 한번 운영해 볼까..?ㅋ

GROUP BY 사용 시 최근 값 가져오기

간만에 쿼리 작성을 하던 나는 멘붕에 빠지고 말았다.

 

예전에 GROUP BY ... ORDER BY ~ 를 하여 결과를 출력하면 최근 값을 가지고 올 수 있었던 것으로 기억을 하고 있기 때문이다. (오라클이었나..MSSQL이었나..)

 

허나 내가 작성한 쿼리는 처음 값을 가지고 오는 것이다!

 

검색 결과 MySQL은 처음 값을 가지고 오는 것이 맞다는 것을 알게 되었고..

 

대안으로는 JOIN을 해서 사용 한다는 내용들이 대부분이었다.

 

하지만 나는 아래와 같이 작성.. 어떤 방법이 좋을지는 EXPLAIN을 떠봐야 겠지만.. 귀찮으니까 일단 패스..

 

select * from t1 where timeStamp in (select max(timeStamp) timeStamp from rm_sms_result group by test_id);

Backup 용어 정리

Database backup에 대해 이야기할 때 online backup, warm backup 등 생소한 단어들이 많이 등장한다.

사용되는 용어들을 정리해 보면 다음과 같다.

 

By format

Logical

테이블 구조와 데이터를 dump 형태로 backup 하는 것을 말한다.

느리지만 backup file을 사용자가 읽고 수정할 수 있기 때문에 매우 유용하다.

Physical

Binary file을 저장하는 형태의 backup으로 보통 backup 속도가 빠르다.

이 방식을 사용할 경우 테이블이 corrupt 될 수 있다.

하나의 테이블을 복사하여 테스트 서버에 복사본을 만드는 작업이 가능하다.

 

by interaction with the MySQL server

Online

MySQL 서버가 동작하고 있는 상태에서 backup 하는 것을 말한다.

Offline

MySQL 서버가 동작하지 않는 상태에서 backup 하는 것을 말한다.

 

by interaction with the MySQL server objects

Cold

Backup 중 모든 명령이 허용되지 않는다.

MySQL 서버가 반드시 정지되어 있거나 모든 file들이 수정되는 것(Insert나 Update, Delete 등의 명령)을 막아 놓은 상태이어야 한다.

Backup 방법 중 가장 빠르다는 것이 장점이다.

Warm

 MySQL이 구동 중인 상태에서 backup을 하며, 백업 중엔 몇몇의 object들에 대한 작업이 금지된다.

Backup이 되는 object들에 대해서는 write lock을 적용하여 수정을 막고 이외의 다른 object들에 대해서는 수정이 허용된다.

읽기 작업은 backup 중에 항상 허용된다.

Hot

Online backup 중 가장 빠른 방법이다.

MySQL이 구동 중인 상태에서 backup을 하며 모든 명령이 허용된다.

 

by content

Full

모든 object를 backup하는 것을 말한다.

Incremental

특정시간 이후에 변경된 것들만 backup하는 것을 말한다.

Partial

명시된 object들만 backup하는 것을 말한다.

 

# MySQL Troubleshooting - O'REILLY의 내용을 발로 번역..

MySQL 설치 가이드 (Binary Package)

MySQL 설치 방법에는 RPM, Binary package, Source compile 이렇게 세 가지 방법이 있다. (Redhat Linux 기준)

그 중 권고하는 방법인 Binary package로 설치를 진행해 보겠다.

* Binary package를 권고하는 이유 1. 압축된 파일을 해제하고 간단한 설정만 해주면 되므로 설치 작업이 매우 단순해 진다. 2. 각종 경로 및 기타 설정을 간단하게 할 수 있다. 3. 모든 모듈이 컴파일되어서 포함되어 있기 때문에 설치되어 있지 않은 모듈로 인한 재설치와 같은 번거로움이 없다.

 

1. MySQL 설치파일을 다운로드 받는다.

아래 포스트를 참고하고 다운로드 받으실 때에는 TAR 파일을 다운 받는다.

[다운로드 포스트 보러가기]

 

2. OS 및 아키텍쳐에 맞는 패키지를 다운 받아서 서버에 올려 놓는다.

 

3. MySQL이 사용할  OS유저를 생성한다. (default : mysql)

# useradd mysql

 

4. 다운로드 받은 패키지의 압축을 해제한다.

# tar xfvz mysql-advanced-5.5.29-linux2.6-x86_64.tar.gz mysql-advanced-5.5.29-linux2.6-x86_64/docs/mysql.info mysql-advanced-5.5.29-linux2.6-x86_64/docs/INFO_SRC mysql-advanced-5.5.29-linux2.6-x86_64/docs/INFO_BIN mysql-advanced-5.5.29-linux2.6-x86_64/docs/ChangeLog ...생략... mysql-advanced-5.5.29-linux2.6-x86_64/man/man1/mysqlslap.1 mysql-advanced-5.5.29-linux2.6-x86_64/man/man1/myisampack.1 mysql-advanced-5.5.29-linux2.6-x86_64/man/man8/mysqld.8 #

 

5. MySQL을 설치하고자 하는 경로로 mv 한다.

# mv mysql-advanced-5.5.29-linux2.6-x86_64 /usr/local

 

6. 추후 관리가 용이하도록 심볼릭 링크를 걸어서 사용한다.

# cd /usr/local/ # ln -s mysql-advanced-5.5.29-linux2.6-x86_64/ mysql

 

7. 포함된 예제 환경설정 파일을 이용해 환경을 구성한다.

# cd mysql/ # cp support-files/my-medium.cnf /etc/my.cnf

 

8. /etc/my.cnf를 열어서 MySQL 경로 및 데이터 디렉토리 경로를 설정한다.

* default는 /usr/local/mysql 이며 데이터 디렉토리는 MySQL 경로 아래의 data/ 이다.

[mysqld] basedir=/usr/local/mysql # MySQL 기본 경로 datadir=/usr/local/mysql/data # 데이터 및 로그가 저장될 경로

 

9. MySQL 기본 데이터를 생성한다.

# ./scripts/mysql_install_db --user=mysql Installing MySQL system tables... OK Filling help tables... OK ...생략...

 

10. OS 서비스에 MySQL을 등록한다.

# cp support-files/mysql.server /etc/init.d/mysqld # chkconfig --add mysqld

 

11. MySQL 라이브러리를 등록한다.

# vi /etc/ld.so.conf.d/mysql-x86_64.conf /usr/local/mysql/lib # ldconfig

 

12. MySQL path를 잡아준다.

# vi /etc/profile PATH=/usr/local/mysql/bin:$PATH

 

13. MySQL을 구동시킨다.

--- service 명령을 통해 구동 (root 유저가 아닐 경우 오류 메시지가 출력될 수 있음) # service mysqld start -- mysqld_safe 명령을 통해 구동 # cd /usr/local/mysql # ./bin/mysqld_safe &

Enterprise 버전 다운로드(30일 제한)

MySQL은 Community 버전과 Enterprise 버전으로 나뉘어져 있다.

오늘은 Enterprise 버전을 다운로드 받는 방법에 대해 설명하겠다.

[Community 버전 다운로드]

[Enterprise 버전 다운로드]

MySQL Enterprise는 누구나 다운로드 받아서 30일간 무료로 사용 가능하다.

라이선스를 구입하는 것이 아니라 서브스크립션을 구입해서 사용을 하는 제품이기 때문에 설치 시에 Key를 넣을 필요가 없다.

때문에 30일이 지나도 패키지가 잠겨 버리거나 하는 제약이 없어서 계속 사용할 수 있다.

하지만 서브스크립션을 구입하지 않고 계속해서 운영하다가 Oracle에 발각될 경우 법적인 책임을 물어야 한다.

일단 다운로드를 받기 위해서는 Oracle.com에 가입이 되어 있어야 한다. 가입은 제약이 없으므로 Register 버튼을 클릭하여 가입하도록 한다.

가입 및 다운로드 페이지는 [Enterprise 버전 다운로드]에 접속하면 된다.

다운로드 절차는 아래와 같다.

---

1. Oracle E-delivery 사이트 접속

 

 2. Oracle.com에 가입된 정보로 로그인

 

 

3. Country를 선택하고 약관에 동의

 

 

4. 다운로드 받은 패키지(MySQL Database)와 Platform 선택

(5.5버전 이후로 IBM AIX platform은 지원하지 않음) [지원 platform 확인]

 

 

5. 원하는 패키지의 알맞은 OS 선택 후 다운로드

* 모든 config가 compile되어 압축형태로 배포되는 Binary 패키지(TAR)를 권고

MySQL Query Cache 사용법

MySQL에서는 반복되는 쿼리를 효율적으로 처리하기 위한 캐쉬가 존재한다.

바로 query cache인데 이는 까다로운 조건에 의해 동작하고 pruning 과정에서 meta 정보의 lock이 발생할 수 있어서 조심해서 사용해야 한다.

일단 동작하는 조건은 아래와 같다.

query cache에는 SQL문과 result set이 저장된다. 바로 이 cache에 저장되어 있는 SQL 문과 완전 동일(띄어쓰기까지..)하고 result set이 같은 쿼리가 수행될 때 query cache가 동작하고 result set을 바로 반환한다.

보통 개발자들은 위와 같은 조건을 간과하고 무작정 같은 쿼리를 수행하면 query cache를 쓰는 것으로 알고 사용한다.

그렇게 아무렇게나 쓰면 waiting query cache lock이라는 상태를 가진 프로세스로 인해 MySQL 서버가 hang이 걸릴 것이다...(오랜시간 고쳐지지 않고 있는 버그..)

이러한 상태에 빠지는 것을 방지하기 위해서는 꼭 query cache를 사용해야 하는 쿼리에만 사용하도록 옵션을 주어 사용할 수 있다.

 

query cache는 query_cache_type이라는 옵션을 통해 세 가지 타입을 제공한다.

OFF (0) - Query cache를 사용하지 않는다. ON (1) - Query cache를 사용한다. (SQL_NO_CACHE 힌트를 사용하는 쿼리는 query cache를 사용하지 않는다.) DEMAND (2) - 선택한 쿼리만 query cache를 사용한다. (SQL_CACHE 힌트를 사용하는 쿼리는 query cache를 사용한다.)

 

위와 같은 설정은 my.cnf에서 설정 가능하고 세션 상에서도 설정이 가능하다.

# 세션에서 dynamic하게 설정하는 방법  mysql> set global query_cache_type = 1;   # my.cnf에 설정 (재시작 시 적용) [mysqld] query_cache_type = 1;

설정은 숫자로 설정을 하거나 alias를 통해 설정이 가능하다.

 

query cache를 OFF할 경우에도 query_cache_size만큼 메모리를 할당하므로 해당 설정값을 0으로 설정하는 것을 권고한다.

query cache를 사용할 경우에는 query_cache_size를 1024의 배수로 설정해야 한다. 다른 값으로 설정할 경우 반올림하여 적용된다.

구조상 최소한 40KB 이상으로 설정해야 하며 적은 값으로 설정할 경우 warning이 발생한다.

해당 값은 아래와 같이 설정한다.

# 세션에서 dynamic하게 설정하는 방법 mysql> set global query_cache_size = 16*1024*1024;   # my.cnf에 설정 (재시작 시 적용) [mysqld] query_cache_size = 16M;

MySQL에서 Oracle의 rownum 사용하기

Oracle에서 MySQL로 migration할 때 SQL문에서 확인이 필요한 부분 중 하나인 rownum을 migration하는 방법이다.

Oracle에서는 해당 row의 번호를 가져올 수 있는 rownum이라는 SQL을 제공한다.

#Oracle rownum 사용법 SQL> SELECT ROWNUM, 1 FROM DUAL;

MySQL에서는 Oracle처럼 rownum을 제공하지 않기 때문에 아래와 같이 만들어서 사용할 수 있다.

# MySQL rownum 사용법 SELECT      @ROWNUM := @ROWNUM + 1 AS ROWNUM,     TEST_TABLE.* FROM     TEST_TABLE,     (SELECT @ROWNUM := 0) R

조금은 불편하지만 언젠가는 MySQL에도 rownum이 생기지 않을까?ㅎㅎ

MySQL Timeout 설정

MySQL에서의 timeout은 interactive_timeout과 wait_timeout 이렇게 두 가지가 존재한다.

interactive_timeout은 mysql> 과 같은 콘솔이나 터미널 모드(대화형 클라이언트)에서 mysqld와 client가 연결을 맺은 다음 요청을 기다리는 최대시간이다.
wait_timeout은 API를 이용한 client 프로그램(PHP, JDBC, ODBC...) 상에서 최대 연결시간을 말한다.
설정된 시간 동안 아무 요청이 없으면 연결은 취소되고 다시 요청이 들어오면 자동으로 연결이 맺어진다.
현재 설정된 값을 확인 하시려면 아래와 같은 명령으로 확인 가능하다.

1. Global 설정 확인 mysql> show global variables like ‘%timeout’; 2. Session 설정 확인 mysql> show variables like ‘%timeout’;

Time out 시간을 조절하시려면 아래와 같이 설정한다.

1. Global 설정 mysql> set global interactive_timeout=10; mysql> set global wait_timeout=10; 2. Session 설정 mysql> set interactive_timeout=10; mysql> set wait_timeout=10;

단, 위와 같은 방법은 MySQL 재시작 시 초기 값으로 돌아간다.
MySQL 시작 시 자동으로 설정할 경우 아래와 같이 my.cnf에 설정하면 된다.

[mysqld] interactive_timeout=10 wait_timeout=10

MySQL innodb_flush_log_at_trx_commit 의 설정값 별 설명

MySQL의 InnoDB엔진에는 트랜잭션이 commit될 때 log buffer를 flush하고 disk 연산이 flush되는 시점을 설정하는 파라미터가 있다.

innodb_flush_log_at_trx_commit이라는 파라미터이다.

이 설정값은 0,1,2 이렇게 3가지 모드가 있으며 아래와 같은 차이점들이 있다.

설정값	설명
0	1초에 한번씩 log buffer를 log file에 기록하고 disk 연산에 대한 flush는 log file에서 일어나지만 commit 시점에서는 아무것도 일어나지 않습니다. mysqld 프로세스가 죽으면 마지막 1초간의 트랜잭션이 유실될 수 있습니다.
1	매번 commit이 일어날 때마다 log buffer를 log file에 기록하고 disk 연산에 대한 flush는 log file에서 일어납니다. 따라서 성능이 느려지지만 full ACID를 만족하게 됩니다.
2	매번 commit이 일어날 때마다 log buffer를 log file에 기록하지만 1초에 한번씩 disk 연산에 대한 flush는 log file에서 일어납니다. (프로세스 스케쥴링 이슈로 인해 매번 1초에 한번씩 일어난다는 보장을 할 수 없습니다.) OS가 crash되거나 파워가 나가면 마지막 1초(혹은 그 이상..)의 트랜잭션이 유실될 수 있습니다.

데이터의 유실이 없어야 하는 시스템에서는 1로 설정하여 사용해야 한다.

어느정도 데이터의 유실은 상관없는 서비스에서 성능만으로 테스트 했을 때 0 > 2 > 1 의 순서로 성능차이를 보였다.