'Database/Mysql'에 해당하는 글 27건

Row size too large

Database/Mysql 2015. 10. 29. 10:54

Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help.
In current row format, BLOB prefix of 768 bytes is stored inline

 

Mysql 에서 글을 겁나 길게 썼더니 발생한 오류이다.
InnoDB 는 한 레코드 삽입에 있어서 8000 byte 의 크기 제한이 있다. 그 크기를 넘어섰다고 경고를 하는 것이다.
해당 컬럼 타입을 TEXT 나 BLOB 으로 변경하거나 ROW_FORMAT 을 DYNAMIC 이나 COMPRESSED 로 변경하란다.
이미 BLOB 으로 되어 있고 COMPRESSED 로 포맷을 변경하였는데도 동일한 에러가 발생하였다.

 

여러 삽질 끝에 테이블 row_format을 compressed 로 변경하기 위해서는,
my.inf 나 AWS 의 Parameter Groups 에서 innodb_file_format 을 Barracuda 로 설정해야 한다는 것을 알았다.
Barracuda 는 DYNAMIC 과 COMPRESSED 을 지원하는 InnoDB 파일 형식이다.

 

 

먼저 file_format 을 바꾸고,

 

innodb_file_format = Barracuda
( > SET GLOBAL innodb_file_format = barracuda; )

 

row_format 을 바꾼다.

 

ALTER TABLE mytable
    ENGINE=InnoDB
    ROW_FORMAT=COMPRESSED
    KEY_BLOCK_SIZE=8;

 

 

그리고 정상적으로 변경이 되었는지 확인한다.

 

> show variables like "%innodb_file%";
innodb_file_format        Barracuda
innodb_file_format_check  ON       
innodb_file_format_max    Barracuda
innodb_file_per_table     ON       

 

> show table status like 'mytable';
Row_format : Compressed

 

 


WRITTEN BY
손가락귀신
정신 못차리면, 벌 받는다.

,

 

 

 

아주 오래전, 기억이 나지 않는 어떤 이유로 워크벤치는 사용하지 않았다. dbforge가 가볍고 좋았다;
주변에서 워크벤치에 대한 질문들이 간혹있어 깔아봤는데 시작부터 장애가 ㅋㅋ

 

특정 테이블의 데이터를 직접 수정하려니 불가능하다.
Read Only 라고 쓰여 있는 곳에 마우스를 올리니,

 

The table has no unique row identifier (primary key or a NOT NULL unique index)

 

Primary key 가 필요없는 테이블이니 Primary key 를 넣지 않았을 것이고,
unique index 도 넣을 수 없으니 넣지 않았을 것이고...

 

Not unique index 하나 넣으니 Read Only 가 사라졌음. ^^
primary key 나 index 가 없다는 이유로 Read Only 를 거는 이유가 뭐지?
오히려 primary key 나 index 걸린 데이터를 수정하는게 더 위험한거 아닌가???

 

 


WRITTEN BY
손가락귀신
정신 못차리면, 벌 받는다.

,

단 하나 알고 있는 계정으로 들어갔는데 프로시저를 변경할 수가 없다. 콜은 되고, 내용은 볼 수 없고...ㅜ
프로시저 생성자 패스워드도 모르고, 결국 mysql root 패스워드 초기화하여 작업 준비.

- 1분 작업 예상하고 스피드하게 Start

# /etc/init.d/mysqld stop
# mysqld_safe --skip-grant &
[1] 1613
# Starting mysqld daemon with databases from /var/lib/mysql
# mysql

> use mysql;
> update user set password=password('#abcdefg$') where user='root';
> flush privileges;
> quit

# /etc/init.d/mysqld restart


WRITTEN BY
손가락귀신
정신 못차리면, 벌 받는다.

,
EXPLAIN 사용

  1. 테이블의 어느 곳에 인덱스를 추가해야 성능이 향상되는지 알 수 있음.
  2. 옵티마이저가 최적의 순서로 테이블을 조인할 수 있는 지 여부 검사.


EXPLAIN 결과 컬럼

  1. id : 쿼리 안의 SELECT 순차 번호
  2. select_type : SELECT 타입
    SIMPLE - 단순 SELECT (UNION 이나 서브쿼리를 사용하지 않음)
    PRIMARY - 가장 외곽의 SELECT
    UNION - UNION의 두번째 혹은 그 이후의 SELECT
    DEPENDENT UNION - UNION의 두번째 혹은 그 이후의 SELECT, 외곽 쿼리에 의존적
    UNION RESULT - UNION의 결과
    SUBQUERY - 서브쿼리의 첫번째 SELECT
    DEPENDENT SUBQUERY - 서브쿼리의 첫번째 SELECT, 외곽 쿼리에 의존적
    DERIVED - SELECT로 추출된 테이블 (FROM 절의 서브쿼리)
    UNCACHEABLE SUBQUERY - 결과가 캐시될 수 없고 외곽 쿼리의 각 행에 대해 재평가되어야만 하는 서브쿼리
    UNCACHEABLE UNION - 캐시될 수 없는 서브쿼리에 속하는 UNION의 두번째 혹은 그 이후의 SELECT
  3. table : 결과 열이 참조하는 테이블
  4. type : 조인(join) 타입
    system - 하나의 열만 가지는 테이블(=시스템 테이블)
    const - primary key나 unique 인덱스의 모든 부분을 상수값과 비교. 하나의 열만 존재하기 때문에 매우 빠름.
    SELECT * FROM tbl_name WHERE primary_key=1;
    eq_ref - = 연산자를 사용해서 비교되는 인덱스된 컬럼용으로 사용.
    SELECT * FROM ref_table, other_table WHERE ref_table.key_column = other_table.column;
    ref - = 또는 <=> 연산자를 사용해서 비교되는 인덱스된 컬럼에 대해 사용
    SELECT * FROM ref_table WHERE key_column = expr;
    ref_or_null - ref 와 유사하지만, null 값을 가지고 있는 열에 대해서도 검색. 서브 쿼리를 해석할 때 자주 사용
    SELECT * FROM ref_table WHERE key_column = expr OR key_column IS NULL;
    index_merge -  인덱스 병합 최적화가 사용되었음.
    unique_subquery - IN 서브 쿼리 (subqueries)에 대해서 ref를 대체하는 인덱스 lookup 함수임.
    index_subquery - unique_subquery와 유사하지만 non-unique 인덱스에 대해서도 동작함.
    range - 주어진 범위에 들어 있는 열만을 추출하며, 열 선택은 인덱스를 사용함. 키 컬럼이 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, 또는 IN 연산자를 사용하는 상수 (constant)와 비교할 때 사용할 수 있음.
    index - ALL과 동일하지만 인덱스 트리만을 스캔함.
    ALL - 전체 테이블 스캔.
  5. possible_keys : 테이블에서 열을 찾기 위해 선택한 인덱스.
  6. key : 실제 사용할 예정인 키(인덱스)
  7. key_len : MySQL이 사용하기로 결정한 키의 길이.
  8. ref : 테이블에서 열을 선택하기 위해 인덱스를 어떤 컬럼 또는 상수(constant)와 비교하는지를 표시.
  9. rows : 쿼리를 실행하기 위해 조사해야 하는 열의 숫자를 가리킨다.
  10. filtered : 테이블 정의문이 필터링하는 테이블 열을 추정한 비율. rows는 조사된 열의 추정 숫자이며, rows × filtered / 100은 조인될 열의 숫자임. EXPLAIN EXTENDED를 사용하게 되면, 이 컬럼이 출력.
  11. Extra : 쿼리의 추가 정보
    Distinct - 명확한 값을 찾게 되며 매칭되는 열을 찾게 되면 검색을 중단.
    Full scan on NULL key - 인덱스 룩업(index-lookup) 접속을 할 수 없고 펄백(fallback) 방식으로 서브 쿼리 최적화를 할 때.
    Impossible WHERE noticed after reading const tables - 모든 const(system) 테이블 값을 읽었으며, WHERE 구문이 항상 거짓(false)일 때.
    No table - FROM 구문이 없거나, FROM DUAL 구문이 있을 때.
    Not exists - LEFT JOIN 최적화를 실행 했으며, 이 최적화와 매치되는 열을 찾은 후에는 이전 열 조합 검색 중단.
    SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
    range checked for each record (index map: N) : 사용하기에 좋은 인덱스를 찾지 못했으나, 컬럼값을 찾고 난 후에는 사용할만한 인덱스를 찾았음.
    Select tables optimized away : 인덱스, 또는 MyISAM용 COUNT(*)을 사용하되 GROUP BY 구문은 사용하지 않은 채로 처리된 집단 함수(MIN(), MAX())만을 가지고 있음.
    Using filesort : 정렬(sort)은 조인(join) 타입과 정렬 키 and WHERE 구문과 매치가 되는 모든 열에 대한 열 포인터(pointer)를 사용해서 모든 열에 걸쳐 진행.
    Using index : 인덱스 트리에 있는 정보만을 가지고 테이블에서 컬럼 정보를 추출.
    Using join cache : 테이블을 부분적으로 읽어온 후에, 읽어 온 열을 사용해서 조인을 실행.
    Using temporary : 결과를 저장할 임시 테이블을 하나 생성해야 함. GROUP BY and ORDER BY 구문을 가지고 있는 경우 나타남.
    Using where : 테이블에 대한 열 매치 (match) 또는 클라이언트에 보내지는 열을 제한하기 위해 사용.
    Using sort_union(...), Using union(...), Using intersect(...) : 인덱스 스캔이 어떻게 index_merge 조인 타입과 병합(merge)이 되는지 출력.
    Using index for group-by : 실제 테이블을 추가 접속하지 않은 채로 GROUP BY 또는 DISTINCT 쿼리의 모든 컬럼을 추출할 때 사용할 수 있는 인덱스.
    Using where with pushed condition : 테이블을 추가 검색하지 않고도, GROUP BY 또는 DISTINCT 쿼리의 모든 컬럼을 추출하기 위해 사용될 인덱스를 찾았음.

WRITTEN BY
손가락귀신
정신 못차리면, 벌 받는다.

,
1. 우편번호 원본 다운로드 - http://www.epost114.co.kr/

2. 테이블 생성 예)

CREATE TABLE db1.zipcode(
  zipcode CHAR(7) NOT NULL COMMENT '우편번호',
  sido VARCHAR(12) DEFAULT NULL COMMENT '시도',
  gugun VARCHAR(12) DEFAULT NULL COMMENT '시군구',
  dong VARCHAR(255) DEFAULT NULL COMMENT '동',
  ri VARCHAR(255) DEFAULT NULL COMMENT '리',
  bunji VARCHAR(255) DEFAULT NULL COMMENT '번지',
  building VARCHAR(255) DEFAULT NULL COMMENT '건물명'
)
ENGINE = MYISAM
CHARACTER SET utf8
COLLATE utf8_general_ci;

3. 다운받은 csv 파일의 field를 db 순서에 맞춤.


번거로운 방법

4-1. 귀찮게 에디터에서 정규식을 사용하여 insert into zipcode ~~~ 자동 변환. sql 로 저장

4-2. utf-8 DB 를 사용중이라면 # iconv -f euc-kr -t utf-8 zipcode.sql > zipcode_utf8.sql

4-3. # mysql -u username -p db1 < zipcode_utf8.sql


그나마 편한 방법

4-1. utf-8 DB 를 사용중이라면 # iconv -f euc-kr -t utf-8 zip.csv > zip_utf8.csv

4-2. mysql> load data local infile 'zip_utf8.csv' into table db1.zipcode fields terminated by ',';

둘 다 잘들어감 ^^

WRITTEN BY
손가락귀신
정신 못차리면, 벌 받는다.

,