꿈꾸는 개발자, DBA 커뮤니티 구루비

db createdAt 동기화 오류
by 쉐리 [SQL Query] [2022.11.20 09:52:18]
안녕하세요.
dev에 있는 db를 prod db로 동기화 과정에서 ERROR: Error 1067: Invalid default value for 'createdAt' 이러한 에러가 발생하여 질문드립니다.
mysql workbench로 동기화 진행하며, mysql 8버전입니다.
구글링해서 찾아본 결과 sql_mode를 바꾸면 된다고 하여 바꿔서 했는데도 똑같이 에러가 발생했습니다.
createdAt에서 어디가 잘못되었는지 잘 모르겠습니다. 알려주시면 감사겠습니다.
sql문은 다음과 같습니다.
-- MySQL Workbench Synchronization
-- Generated: 2022-11-20 09:48
-- Model: New Model
-- Version: 1.0
-- Project: Name of the project
-- Author: choyo

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

CREATE TABLE IF NOT EXISTS `personal`.`Administrator` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) NOT NULL COMMENT '관리자 이름',
  `email` VARCHAR(100) NOT NULL COMMENT '관리자 이메일',
  `salt` VARCHAR(255) NOT NULL COMMENT 'salt',
  `password` VARCHAR(255) NOT NULL COMMENT '관리자 비밀번호',
  `roles` ENUM('master', 'viewer') NOT NULL DEFAULT 'viewer' COMMENT '권한 상태',
  `createdAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE INDEX `IDX_f8dfb4b01a8faddf6f571faed2` (`email` ASC) VISIBLE)
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_bin;

CREATE TABLE IF NOT EXISTS `personal`.`BlogCategory` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(30) NOT NULL,
  `createdAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `mpath` VARCHAR(255) NULL DEFAULT '',
  `parentId` INT(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `IDX_5e009c02c0dbb92e6f1c5d51b2` (`name` ASC) VISIBLE,
  INDEX `FK_e4ef0b8f52d58c769587c4bda18` (`parentId` ASC) VISIBLE,
  CONSTRAINT `FK_e4ef0b8f52d58c769587c4bda18`
    FOREIGN KEY (`parentId`)
    REFERENCES `personal`.`BlogCategory` (`id`)
    ON DELETE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_bin;

CREATE TABLE IF NOT EXISTS `personal`.`BlogComment` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(60) NOT NULL,
  `email` VARCHAR(255) NULL DEFAULT NULL,
  `message` TEXT NOT NULL,
  `createdAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `postId` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `FK_d83da0effc75096d0a9d0100858` (`postId` ASC) VISIBLE,
  CONSTRAINT `FK_d83da0effc75096d0a9d0100858`
    FOREIGN KEY (`postId`)
    REFERENCES `personal`.`BlogPost` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_bin;

CREATE TABLE IF NOT EXISTS `personal`.`BlogCommentReply` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(60) NOT NULL,
  `email` VARCHAR(255) NULL DEFAULT NULL,
  `message` TEXT NOT NULL,
  `createdAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `blogCommentId` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `FK_4a7be66de1fd00316c05c520a02` (`blogCommentId` ASC) VISIBLE,
  CONSTRAINT `FK_4a7be66de1fd00316c05c520a02`
    FOREIGN KEY (`blogCommentId`)
    REFERENCES `personal`.`BlogComment` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_bin;

CREATE TABLE IF NOT EXISTS `personal`.`BlogPost` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(60) NOT NULL,
  `titleImage` VARCHAR(300) NULL DEFAULT NULL,
  `content` TEXT NOT NULL,
  `hidden` TINYINT(4) NULL DEFAULT '0',
  `createdAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `categoryId` INT(11) NOT NULL,
  `description` VARCHAR(160) NOT NULL,
  `visitCount` INT(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  INDEX `FK_5b4d18df7e7ec41e4dcf85c23fe` (`categoryId` ASC) VISIBLE,
  CONSTRAINT `FK_5b4d18df7e7ec41e4dcf85c23fe`
    FOREIGN KEY (`categoryId`)
    REFERENCES `personal`.`BlogCategory` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_bin;

CREATE TABLE IF NOT EXISTS `personal`.`BlogPostVisitLog` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `ip` VARCHAR(255) NOT NULL,
  `createdAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `blogPostId` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `FK_b67a6aa70dd538364a1d7e9bb93` (`blogPostId` ASC) VISIBLE,
  CONSTRAINT `FK_b67a6aa70dd538364a1d7e9bb93`
    FOREIGN KEY (`blogPostId`)
    REFERENCES `personal`.`BlogPost` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_bin;

CREATE TABLE IF NOT EXISTS `personal`.`BlogPosts-BlogTags` (
  `blogPostId` INT(11) NOT NULL,
  `blogTagId` INT(11) NOT NULL,
  PRIMARY KEY (`blogPostId`, `blogTagId`),
  INDEX `IDX_4ff4c4e5038518846e6601ad3a` (`blogPostId` ASC) VISIBLE,
  INDEX `IDX_e20c6491e76cd0c49d7c4d0e1f` (`blogTagId` ASC) VISIBLE,
  CONSTRAINT `FK_4ff4c4e5038518846e6601ad3ac`
    FOREIGN KEY (`blogPostId`)
    REFERENCES `personal`.`BlogPost` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `FK_e20c6491e76cd0c49d7c4d0e1fa`
    FOREIGN KEY (`blogTagId`)
    REFERENCES `personal`.`BlogTag` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_bin;

CREATE TABLE IF NOT EXISTS `personal`.`BlogTag` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) NOT NULL,
  `createdAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE INDEX `IDX_8c401971204471fc14c3b5e69b` (`name` ASC) VISIBLE)
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_bin;

CREATE TABLE IF NOT EXISTS `personal`.`Career` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `position` VARCHAR(255) NOT NULL,
  `grade` VARCHAR(255) NOT NULL,
  `startDate` DATE NOT NULL,
  `endDate` DATE NULL DEFAULT NULL,
  `hidden` TINYINT(4) NULL DEFAULT '0',
  `createdAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `companyName` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_bin;

CREATE TABLE IF NOT EXISTS `personal`.`CareerDetail` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(100) NOT NULL,
  `intro` VARCHAR(255) NOT NULL,
  `position` VARCHAR(255) NULL DEFAULT NULL,
  `performance` VARCHAR(255) NULL DEFAULT NULL,
  `team` VARCHAR(255) NULL DEFAULT NULL,
  `skill` VARCHAR(255) NULL DEFAULT NULL,
  `startDate` DATE NOT NULL,
  `endDate` DATE NULL DEFAULT NULL,
  `hidden` TINYINT(4) NULL DEFAULT '0',
  `createdAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `portfolioId` INT(11) NULL DEFAULT NULL,
  `careerId` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `FK_84e45bde57cbc7b5ece6b19e3dd` (`portfolioId` ASC) VISIBLE,
  INDEX `FK_a1be150548d28a34dbfb1226af6` (`careerId` ASC) VISIBLE,
  CONSTRAINT `FK_84e45bde57cbc7b5ece6b19e3dd`
    FOREIGN KEY (`portfolioId`)
    REFERENCES `personal`.`Portfolio` (`id`)
    ON UPDATE CASCADE,
  CONSTRAINT `FK_a1be150548d28a34dbfb1226af6`
    FOREIGN KEY (`careerId`)
    REFERENCES `personal`.`Career` (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_bin;

CREATE TABLE IF NOT EXISTS `personal`.`Portfolio` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `titleImage` VARCHAR(255) NULL DEFAULT NULL,
  `title` VARCHAR(60) NOT NULL,
  `intro` VARCHAR(160) NOT NULL,
  `content` TEXT NOT NULL,
  `clientName` VARCHAR(255) NULL DEFAULT NULL,
  `url` VARCHAR(255) NULL DEFAULT NULL,
  `team` VARCHAR(255) NOT NULL,
  `technology` VARCHAR(255) NOT NULL,
  `type` ENUM('company', 'personal') NOT NULL,
  `feature` JSON NULL DEFAULT NULL,
  `endDate` DATE NULL DEFAULT NULL,
  `hidden` TINYINT(4) NULL DEFAULT '0',
  `createdAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `startDate` DATE NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_bin;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(
) 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입
데이터베이스 질문과 답변
