Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.15
-
None
-
CentOS release 6.5 (X86_64)
Description
I found that creating a CONNECT table fails to a remote MariaDB table with a datetime column.
It only happens when default '0000-00-00 00:00:00' is specified.
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
server1
--------------
|
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
|
server2
--------------
|
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
|
I made a test case, it can be used with the below command.
server1
mysql --user=root -f -v -v -v --show-warnings --execute="SOURCE /root/test3_script_server1.sql;" > /root/test3_script_server1.log
|
server2
mysql --user=root -f -v -v -v --show-warnings --execute="SOURCE /root/test3_script_server2.sql;" > /root/test3_script_server2.log
|