Data too long for column 에러 해법 : MySQL의 sql_mode 변경하기

MySQL table에 데이터를 insert하다보면  

Data truncation: Data too long for column '에러가_발생한_필드' at row 1

과 같은 에러를 만나는 경우가 있다.

에러 메시지 자체만 놓고 보면 특정 필드(column)의 선언된 크기보다 insert 될 데이터의 크기가 더 크기 때문으로 보인다.

그런 경우도 있으나 MySQL의 환경 설정상의 원인으로 인한 경우도 있다.

MySQL의 mode가 STRICT인 경우에 위와 같은 에러가 빈번히 발생할수 있다.

MySQL의 설정 파일인 my.cnf에 sql_mode의 값이 STRICT_TRANS_TABLES일 경우 필드에 선언된 대로의 값이 주어지지 않으면 위와같은 에러를 발생시키고 쿼리문을 중단한다.

현재의 sql_mode가 어떤 내용인지를 확인할려면 my.cnf를 열어서 확인할수도 있고 아래와 같이 MySQL에 접속한 상태에서 

# mysql -umysql아이디 -p로 mysql에 접속한 상태에서 select @@global.sql_mode 명령어로 현재의 sql-mode를 확인할수 있다.

mysql> select @@global.sql_mode;

+----------------------------------+

| @@global.sql_mode              |

+----------------------------------+

| NO_ENGINE_SUBSTITUTION    |

+----------------------------------+

sql_mode가 STRICT일 경우는 insert 작업을 까다롭게 관리해서 데이터의 무결성을 위해서는 좋으나 사실은 번거로운 상황이다.

STRICT모드를 변경할려면 my.cnf의 sql_mode의 내용을 변경해 주면 된다.

CentOS의 경우 

# vi /etc/my.cnf 파일을 열어서

# Recommended in standard MySQL setup

sql_mode=NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES

sql_mode에서 STRICT_TRANS_TABLES를 제거하거나 아니면 sql_mode 자체를 주석처리해서 사용하지 않도록 하면된다.

저장 후 mysql 데몬을 재 시작해 준다.

# service mysqld restart

MySQL 한글 insert시 발생하는 ERROR 1366 (HY000): Incorrect string value: 문제

MySQL 테이블의 특정 필드에 한글을 insert하다보면 아래와 같은 에러가 발생하는 경우를 만날수 있다.

ERROR 1366 (HY000): Incorrect string value: ...

kkk라는 테이블이 아래와 같은 형식으로 생성되어 있다고 가정하면,

mysql> show create table kkk;

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                             |

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+

| kkk   | CREATE TABLE `kkk` (

  `bdid` bigint(11) unsigned NOT NULL,

  `name` varchar(80) NOT NULL,

  PRIMARY KEY (`bdid`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

위와 같이 kkk라는 테이블이 있을 경우 DEFAULT CHARSET=latin1으로 되어 있다.

이때 한글을 name 필드에 insert하게 되면 아래와 같은 에러가 발생한다.

mysql> insert into kkk (bdid, name) values (1, '한글입력');

ERROR 1366 (HY000): Incorrect string value: '\xED\x95\x9C\xEA\xB8\x80...' for column 'name' at row 1

이 문제 해결을 위해서는 default character set을 utf8로 변경해 주면 깨끗이 해결된다.

kkk라는 테이블을 생성하는 DDL 문에서 아예 utf8로 설정해서 테이블을 생성해 주면 된다.

다음과 같이

CREATE TABLE `kkk` (

  `bdid` bigint(11) unsigned NOT NULL,

  `name` varchar(80) NOT NULL,

  PRIMARY KEY (`bdid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

MySQL table의 필드 값이 한글일 경우 Java 소스에서 SELECT 실패할 경우

MySQL 필드의 내용이 한글 일 경우 Java 소스코드 상 SELECT문의 비교문에서 인식이 안되는 문제가 있다.

mysql> select * from board;

+------+-----------------+

| bdid | name            |

+------+-----------------+

|    1 | 공지사항        |

|    2 | QandA           |

|    3 | 자유게시판      |

|    4 | menu            |

+------+-----------------+

board라는 테이블의 내용이 위와 같다고 할 경우

select bdid from board where name = '자유게시판';

과 같은 구문에서 bdid의 값 3이 추출되지 않는다.

만일 select bdid from board where name = 'QandA';

와 같이 한글이 아닌 영문일 경우는 정상적으로 bdid의 값 2를 추출할 수 있다.

이 문제는 MySQL의 환경설정 중 character set상에서의 문제이다.

mysql> show variables like '%chara%';

+--------------------------+----------------------------+

| Variable_name            | Value                      |

+--------------------------+----------------------------+

| character_set_client     | utf8                       |

| character_set_connection | utf8                       |

| character_set_database   | latin1                     |

| character_set_filesystem | binary                     |

| character_set_results    | utf8                       |

| character_set_server     | utf8                       |

| character_set_system     | utf8                       |

| character_sets_dir       | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

비록 위와 같이 MySQL variagle들이 utf8로 설정되어 있다하더라도 

/etc/my.cnf라는 설정 파일을 아래와 같이 charater set을 모두 설정해 주어야 한글이 정상적으로 select문에서 작동하게 된다.

[client]

default-character-set = utf8

[mysql]

default-character-set = utf8

[mysqld]

#

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

#

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

character-set-server = utf8

collation-server = utf8_general_ci

init_connect=SET collation_connection = utf8_general_ci

init_connect=set NAMES utf8

character-set-client-handshake = FALSE

skip-character-set-client-handshake

wait_timeout=31536000

[mysqldump]

default-character-set = utf8

# Disabling symbolic-links is recommended to prevent assorted security risks

#symbolic-links=0

# Recommended in standard MySQL setup

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

위와 같이 설정 후 MySQL 데몬을 재가동해 준다.

# service mysqld restart