CONNECT fails for a table with "default '0000-00-00 00:00:00'"

--------------

SHOW VARIABLES LIKE "%CHAR%"

--------------

+--------------------------+----------------------------+

| Variable_name            | Value                      |

+--------------------------+----------------------------+

| character_set_client     | utf8                       |

| character_set_connection | utf8                       |

| character_set_database   | utf8                       |

| character_set_filesystem | binary                     |

| character_set_results    | utf8                       |

| character_set_server     | utf8                       |

| character_set_system     | utf8                       |

| character_sets_dir       | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.11 sec)

--------------

CREATE DATABASE IF NOT EXISTS test

--------------

Query OK, 1 row affected, 1 warning (0.00 sec)

Note (Code 1007): Can't create database 'test'; database exists

--------------

CREATE OR REPLACE TABLE table_coupon_defaulttime (

  coupon_no varchar(10) NOT NULL DEFAULT '',

  memo varchar(30) DEFAULT NULL,

  discount double DEFAULT NULL,

  KAISHI_BI datetime NOT NULL default '0000-00-00 00:00:00',

  PRIMARY KEY (coupon_no)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

--------------

Query OK, 0 rows affected (0.72 sec)

--------------

INSERT INTO table_coupon_defaulttime VALUES ('1','ＡＢＣ',250,'2014-01-01 12:00'),('2','ＤＥ Ｆ',200,'2014-05-01 08:00'),('3','ＧＨＩ',300,NULL)

--------------

Query OK, 3 rows affected, 1 warning (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 1

Warning (Code 1048): Column 'KAISHI_BI' cannot be null

--------------

SELECT * FROM table_coupon_defaulttime

--------------

+-----------+------------+----------+---------------------+

| coupon_no | memo       | discount | KAISHI_BI           |

+-----------+------------+----------+---------------------+

| 1         | ＡＢＣ     |      250 | 2014-01-01 12:00:00 |

| 2         | ＤＥ Ｆ    |      200 | 2014-05-01 08:00:00 |

| 3         | ＧＨＩ     |      300 | 0000-00-00 00:00:00 |

+-----------+------------+----------+---------------------+

3 rows in set (0.00 sec)

--------------

CREATE OR REPLACE TABLE table_coupon_nodefaulttime (

  coupon_no varchar(10) NOT NULL DEFAULT '',

  memo varchar(30) DEFAULT NULL,

  discount double DEFAULT NULL,

  KAISHI_BI datetime NOT NULL,

  PRIMARY KEY (coupon_no)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

--------------

Query OK, 0 rows affected (0.18 sec)

--------------

INSERT INTO table_coupon_nodefaulttime  VALUES ('1','ＡＢＣ',250,'2014-01-01 12:00'),('2','ＤＥ Ｆ',200,'2014-05-01 08:00'),('3','ＧＨＩ',300,NULL)

--------------

Query OK, 3 rows affected, 1 warning (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 1

Warning (Code 1048): Column 'KAISHI_BI' cannot be null

--------------

SELECT * FROM table_coupon_nodefaulttime

--------------

+-----------+------------+----------+---------------------+

| coupon_no | memo       | discount | KAISHI_BI           |

+-----------+------------+----------+---------------------+

| 1         | ＡＢＣ     |      250 | 2014-01-01 12:00:00 |

| 2         | ＤＥ Ｆ    |      200 | 2014-05-01 08:00:00 |

| 3         | ＧＨＩ     |      300 | 0000-00-00 00:00:00 |

+-----------+------------+----------+---------------------+

3 rows in set (0.00 sec)

--------------

GRANT ALL ON test.* TO 'root'@'10.0.1.33'

--------------

Query OK, 0 rows affected (0.07 sec)

Bye

--------------

SHOW VARIABLES LIKE "%CHAR%"

--------------

+--------------------------+----------------------------+

| Variable_name            | Value                      |

+--------------------------+----------------------------+

| character_set_client     | utf8                       |

| character_set_connection | utf8                       |

| character_set_database   | utf8                       |

| character_set_filesystem | binary                     |

| character_set_results    | utf8                       |

| character_set_server     | utf8                       |

| character_set_system     | utf8                       |

| character_sets_dir       | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.01 sec)

--------------

CREATE DATABASE IF NOT EXISTS test

--------------

Query OK, 1 row affected, 1 warning (0.00 sec)

Note (Code 1007): Can't create database 'test'; database exists

--------------

CREATE OR REPLACE TABLE maria_table_coupon_defaulttime

ENGINE=CONNECT DEFAULT CHARSET=utf8 TABLE_TYPE='MYSQL' TABNAME='table_coupon_defaulttime' DBNAME='test' OPTION_LIST='user=root,host=10.0.1.36'

--------------

Error (Code 1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-00-00 00:00:00) TABLE_TYPE='MYSQL' TABNAME='table_coupon_defaulttime' DBNAME='t' at line 1

Error (Code 1939): Engine CONNECT failed to discover table `test`.`maria_table_coupon_defaulttime` with 'CREATE TABLE whatever (`coupon_no` VARCHAR(10) NOT NULL,`memo` VARCHAR(30),`discount` DOUBLE,`KAISHI_BI` DATETIME NOT NULL DEFAULT 0000-00-00 00:00:00) TABLE_TYPE='MYSQL' TABNAME='table_coupon_defaulttime' DBNAME='test' OPTION_LIST='user=root,host=10.0.1.36' CHARSET=utf8'

Error (Code 1030): Got error 168 "Unknown (generic) error from engine" from storage engine CONNECT

--------------

SELECT * FROM maria_table_coupon_defaulttime

--------------

--------------

CREATE OR REPLACE TABLE maria_table_coupon_nodefaulttime

ENGINE=CONNECT DEFAULT CHARSET=BINARY TABLE_TYPE='MYSQL' TABNAME='table_coupon_nodefaulttime' DBNAME='test' OPTION_LIST='user=root,host=10.0.1.36'

--------------

Query OK, 0 rows affected (0.08 sec)

--------------

SELECT * FROM maria_table_coupon_nodefaulttime

--------------

+-----------+------+----------+---------------------+

| coupon_no | memo | discount | KAISHI_BI           |

+-----------+------+----------+---------------------+

| 1         | ???  |      250 | 2014-01-01 12:00:00 |

| 2         | ?? ? |      200 | 2014-05-01 08:00:00 |

| 3         | ???  |      300 | 1970-01-01 00:00:00 |

+-----------+------+----------+---------------------+

3 rows in set (0.32 sec)

Bye

mysql --user=root -f -v -v -v --show-warnings --execute="SOURCE /root/test3_script_server1.sql;" > /root/test3_script_server1.log

mysql --user=root -f -v -v -v --show-warnings --execute="SOURCE /root/test3_script_server2.sql;" > /root/test3_script_server2.log