Move Tables workflow errors out for source tables that have a column of datetime type

[bharathramaprasad-okta]

Overview of the Issue

Move tables workflow throws the following error : Move Tables Error: rpc error: code = Unknown desc = Table Manager.Apply Schema on zone1- error: /usr/bin/mysql: exit status 1, output: ERROR 1067 (42000) at line 45: Invalid default value for 'eventTimestamp'
Each source table that has a eventTImestamp of datetime type throws this error.

desc table of one of the tables for which movetable fails is show in the image below.

Reproduction Steps

Steps to reproduce this issue :

1. Use the latest vitess 8 version
2. Follow example scripts for docker local setup.
3. Deploy the below schema :

CREATE TABLE Events (
id bigint(20) NOT NULL AUTO_INCREMENT,
eventId char(25) NOT NULL,
eventTimestamp datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
message varchar(2048) NOT NULL,
PRIMARY KEY (id,eventTimestamp),
UNIQUE KEY idx_eventId_timestamp (eventId,eventTimestamp),
KEY eventtimestamp (eventTimestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (EXTRACT(YEAR_MONTH FROM eventTimestamp))
(PARTITION m202101 VALUES IN (202101) ENGINE = InnoDB,
PARTITION m202102 VALUES IN (202102) ENGINE = InnoDB,
PARTITION m202103 VALUES IN (202103) ENGINE = InnoDB,
PARTITION m202104 VALUES IN (202104) ENGINE = InnoDB,
PARTITION m202105 VALUES IN (202105) ENGINE = InnoDB,
PARTITION m202106 VALUES IN (202106) ENGINE = InnoDB,
PARTITION m202107 VALUES IN (202107) ENGINE = InnoDB,
PARTITION m202108 VALUES IN (202108) ENGINE = InnoDB,
PARTITION m202109 VALUES IN (202109) ENGINE = InnoDB,
PARTITION m202110 VALUES IN (202110) ENGINE = InnoDB,
PARTITION m202111 VALUES IN (202111) ENGINE = InnoDB,
PARTITION m202112 VALUES IN (202112) ENGINE = InnoDB,
PARTITION m202201 VALUES IN (202201) ENGINE = InnoDB,
PARTITION m202202 VALUES IN (202202) ENGINE = InnoDB,
PARTITION m202203 VALUES IN (202203) ENGINE = InnoDB,
PARTITION m202204 VALUES IN (202204) ENGINE = InnoDB) */

3. After initializing the cluster, creating an unmanaged tablet for the db containing the above legacy mysql table.
   Run Move tables.

vtgate --version
Version: 10.0.0-SNAPSHOT built on Fri Apr 16 02:15:11 UTC 2021 by vitess@893f76506d2e using go1.15.6 linux/amd64```

Operating system and Environment details

OS, Architecture, and any other information you can provide
about the environment.

• Operating system (output of cat /etc/os-release):

• PRETTY_NAME="Debian GNU/Linux 10 (buster)"
  NAME="Debian GNU/Linux"
  VERSION_ID="10"
  VERSION="10 (buster)"
  VERSION_CODENAME=buster
  ID=debian
  HOME_URL="https://www.debian.org/"
  SUPPORT_URL="https://www.debian.org/support"
  BUG_REPORT_URL="https://bugs.debian.org/"

• Kernel version (output of uname -sr):
  Linux 4.9.27-14.33.amzn1.x86_64

• Architecture (output of uname -m):
  x86_64

Log Fragments

Move Tables Error: rpc error: code = Unknown desc = Table Manager.Apply Schema on zone1- error: /usr/bin/mysql: exit status 1, output: ERROR 1067 (42000) at line 45: Invalid default value for 'eventTimestamp'

[rohit-nayak-ps]

A workaround, until this gets fixed, is to change @@global.sql_mode to remove NO_ZERO_IN_DATE,NO_ZERO_DATE and add ALLOW_INVALID_DATES on the mysql servers which are targets of the vreplication workflows you are running.

[mattlord]

I'm closing this for now as fixed in #9505

Please let me know if I missed or misunderstood something and we can re-open it. Thanks!