nullable로 선언한 deleted_at에 저장하자마자 삭제 시각이 찍힌 이유 — explicit_defaults_for_timestamp

문제 발생

소프트 삭제를 위해 deleted_at을 nullable TIMESTAMP로 두고, 모든 목록 쿼리에 deleted_at IS NULL을 걸었습니다. 로컬에서는 멀쩡했는데 운영에 배포하자 새로 쓴 글이 저장 직후 목록에서 사라졌습니다. 저장 뒤 id로 조회하는 리다이렉트는 성공하니 "저장은 됐는데 페이지를 찾을 수 없다"로 보였습니다.

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  deleted_at TIMESTAMP,
  created_at TIMESTAMP NOT NULL
);

운영 서버에서 SHOW CREATE TABLE posts를 보니 선언과 다른 정의가 들어 있었습니다.

`deleted_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

원인 분석

서버 변수 하나가 컬럼 정의를 바꿨습니다. MariaDB 문서는 explicit_defaults_for_timestamp를 이렇게 설명합니다 — 이 옵션이 있으면 CREATE TABLE이 모든 TIMESTAMP 컬럼을 DEFAULT NULL인 NULL 허용 컬럼으로 만들고, 없으면 TIMESTAMP 컬럼은 NOT NULL이 되고 암묵적인 DEFAULT 절을 갖습니다.

그중 테이블의 첫 TIMESTAMP 컬럼은 특별합니다. 문서 그대로, 이 변수가 설정되지 않았을 때 첫 TIMESTAMP 컬럼에는 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP가 자동으로 붙습니다. INSERT나 UPDATE에서 그 컬럼에 값을 주지 않으면 현재 시각으로 초기화된다는 뜻입니다.

그래서 deleted_at이 첫 TIMESTAMP인 테이블은 INSERT하는 순간 삭제 시각이 찍혔고, UPDATE할 때마다 다시 찍혔습니다. 세션 테이블의 expires_at이 첫 TIMESTAMP였다면 세션을 갱신할 때마다 만료 시각이 "지금"이 되어 로그인이 그 자리에서 풀립니다.

로컬과 운영이 달랐던 이유는 기본값이 버전에 따라 다르기 때문입니다 — 문서 기준 MariaDB 10.10 이상은 ON, 10.9 이하는 OFF입니다. 운영 서버의 설정 파일이 이 값을 끄고 있어도 같은 일이 납니다.

해결 방안

1. 컬럼 정의가 스스로 말하게 합니다. nullable이면 NULL DEFAULT NULL을, 값을 항상 직접 넣는 NOT NULL 컬럼이면 DEFAULT CURRENT_TIMESTAMP를 명시합니다. 명시된 DEFAULT가 있으면 자동 ON UPDATE가 붙지 않습니다.

ALTER TABLE posts
  MODIFY COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL,
  MODIFY COLUMN created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

2. 서버 변수를 켜는 것으로 대신하지 않습니다. 설정에 기대면 다음 서버에서 같은 일이 또 납니다. 마이그레이션이 만드는 정의가 어느 서버에서든 같은 결과를 내야 합니다.

3. 만들어진 뒤의 정의를 검사합니다. 마이그레이션 SQL을 읽어서는 드러나지 않습니다. information_schema에서 ON UPDATE가 붙은 TIMESTAMP를 찾는 테스트를 두면 새 테이블을 추가할 때 빠뜨린 것이 잡힙니다.

SELECT TABLE_NAME, COLUMN_NAME, IS_NULLABLE, COLUMN_DEFAULT, EXTRA
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND DATA_TYPE = 'timestamp'
  AND EXTRA LIKE '%on update%';

4. NULL을 넣는 것도 조심합니다. 문서에 따르면 TIMESTAMP 컬럼에 NULL을 대입하면 현재 시각으로 초기화하는 별도 동작이 있습니다. NULL DEFAULT NULL로 선언한 컬럼은 NULL을 그대로 받으므로, 1번을 먼저 해야 이 동작도 사라집니다.

공식 문서

• MariaDB: TIMESTAMP — Automatic Values
• MariaDB: Server System Variables — explicit_defaults_for_timestamp