MySQL Foreign Key 1215 error

얼마전 담당하는 MySQL 서비스의 복제본 한벌을 더 추가 구성하게 되었습니다.
single-transaction + dump-slave 혹은 single-transaction + master-data 옵션으로 mysqldump 를 받은 뒤
새 서버에 반영해주고 dumpfile에 dump-slave / master-data 옵션으로 인해 딸려온 change master to 구문을 확인하여
replication을 맺어주면 되는 아주 간단한 작업인데요

그런데 실패했습니다…

replication error log

show slave status\G;

.
.
    
Last_Errno: 1146
Last_Error: Error 'Table 'xxxxx.xxxx' doesn't exist' on query. Default database: 'xxxxxxxxxxxx'. Query: 'UPDATE xxxx SET lastused = NOW(), lingertime = 7 WHERE siteid = 'xxxxxxxxxxx''
.
.
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1146
Last_SQL_Error: Error 'Table 'xxxxxxxx.xxxxx' doesn't exist' on query. Default database:'xxxxxxx'. Query: 'UPDATE xxxxx SET lastused = NOW(), lingertime = 7 WHERE siteid = 'xxxxxxxxxx''
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1

이전에도 종종 해본 작업이고 mysqldump 를 그대로 import 하고 백업받을 때 master의 binary log position 도 정확히 잘 찍어놔서
데이터가 유실될 일은 없을 터인데..
dump 복원 과정에서 무려 테이블이 유실되어 당혹스러웠는데요
Import 에러 로그부터 dump file의 내용까지 천천히 확인해보니 이유를 알 수 있었습니다.

import error log

line 5048  ERROR 1215 (HY000): Cannot add foreign key constraint

정확하진 않지만 대략 위 에러문구였습니다.
mysqldump file을 반영하다가 line 5048번에서 FK 생성 에러가 발생했습니다.
보통 mysqldump을 별다른 옵션없이 수행하면 아래처럼 FK check 옵션을 끄고 테이블 생성 & 데이터 적재 후 모든작업을 마치고
마지막에 다시 FK 옵션을 원복해줍니다.(이전에 off였든 on이었든)

### dumpfile 시작부분
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

### dumpfile 끝부분
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

이 FOREIGN_KEY_CHECKS=0 옵션 덕에 parent table 보다 child table이 먼저 생성되는 경우나
데이터 dml 순서도 FK제한없이 처리될 수 있습니다.
그런데도 왜 FK에러가 났느냐?
위 import error log 에러에서 나온 mysqldump file의 5048 line을 살펴보니 아래와 같은 테이블 생성구문이었습니다.

생성 실패한 table

CREATE TABLE `tb_test_child` (
  `id` varchar(64) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `column_fk` varchar(64) DEFAULT NULL,
  `col2` tinyint(1) NOT NULL,
  `col3` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx1` (`name`),
  KEY `idx2` (`column_fk`),
  CONSTRAINT `tb_test_child_fk1` FOREIGN KEY (`column_fk`) REFERENCES `tb_test_parent` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

=> 에러로그에서 FK를 생성하는데 실패했다고 했기 때문에 tb_test_parent 테이블의 id 컬럼을 reference 하여 FK 생성에 실패한 것이 되는데요
그래서 tb_test_parent 테이블의 생성구문을 확인해봤습니다.

parent table

CREATE TABLE `tb_test_parent` (
  `id` varchar(64) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
  `name` varchar(255) DEFAULT NULL,
.
.
  `create_time` datetime DEFAULT NULL,
  `deleted_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tb_test_parent_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

=> tb_test_child의 tb_test_child_fk1 FK는 tb_test_parent의 id를 refernce 합니다.
이때 FK 생성 조건에는 아래와 같은 조건 등이 있습니다.

참조하는 컬럼이 FK 컬럼과 동일한 타입, length를 가져야한다
참조하는 컬럼은 해당 테이블에서 어떤 종류던 key여야한다
parent , child의 해당 컬럼의 charset과 collation은 동일해야한다

이번 에러는 어떤 이유에서인지 dumpfile에서 FK 컬럼의 charset과 collation이 parent / child 간 상이했습니다.

# parent
`id` varchar(64) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',

# child 
`column_fk` varchar(64) DEFAULT NULL,
CONSTRAINT `tb_test_child_fk1` FOREIGN KEY (`column_fk`) REFERENCES `tb_test_parent` (`id`)

그래서 child 테이블의 column_fk 컬럼을 parent의 id컬럼과 동일하게 CHARACTER SET utf8mb4로 맞춰 생성 후 정상화 되었습니다.

MySQL Foreign Key 1215 error#

MySQL Foreign Key 1215 error