loader doesn't set SQL_MODE before creating schema

[kolbe]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do? If possible, provide a recipe for reproducing the error.

DM fails to load a dump of wordpress, because wordpress uses DEFAULT '0000-00-00 00:00:00' for many datetime columns, and the default SQL mode includes NO_ZERO_DATE.

2. What did you expect to see?

DM should be able to successfully load any schema files that were successfully loaded into and exported from an upstream instance.

This is handled in mysqldump by writing SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' to the beginning of the dump file and then writing SET SQL_MODE=@OLD_SQL_MODE at the end of the dump file (https://github.com/mysql/mysql-server/blob/5.7/client/mysqldump.c#L745).

This is handled in mydumper by having the loader execute SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO' before loading data (https://bugs.launchpad.net/mydumper/+bug/1124106).

The most straightforward fix for this issue would be to add functionality to loader/db.go to execute SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO' before executing any DDL.

3. What did you see instead?

2019/06/18 22:49:36.798 db.go:169: [warning] [exec][sql]CREATE TABLE `wp_comments` (`comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,`comment_post_I[err
or]Error 1067: Invalid default value for 'comment_date'

4. Versions of the cluster

   • DM version (run dmctl -V or dm-worker -V or dm-master -V):

     Release Version: v1.0.0-alpha-94-g1173d26
     Git Commit Hash: 1173d269309c79f45b8d0897247e22f0a714c39d
     Git Branch: master
     UTC Build Time: 2019-06-14 08:37:46
     Go Version: go version go1.12 linux/amd64
     

   • Upstream MySQL/MariaDB server version:

     mysqld  Ver 5.7.26 for Linux on x86_64 (MySQL Community Server (GPL))
     

   • Downstream TiDB cluster version (execute SELECT tidb_version(); in a MySQL client):

     Release Version: v3.0.0-rc.1-201-gb0d6c5b35
     Git Commit Hash: b0d6c5b35bf8faa33ae80ec290ba27c5d243a74e
     Git Branch: master
     UTC Build Time: 2019-06-18 08:20:54
     GoVersion: go version go1.12 linux/amd64
     Race Enabled: false
     TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
     Check Table Before Drop: false
     

[GMHDBJD]

Now we support session in task config to set sql_mode. Close this issure.