Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7489

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

    XMLWordPrintable

Details

    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','ABC',250,'2014-01-01 12:00'),('2','DE F',200,'2014-05-01 08:00'),('3','GHI',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         | ABC     |      250 | 2014-01-01 12:00:00 |
      | 2         | DE F    |      200 | 2014-05-01 08:00:00 |
      | 3         | GHI     |      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','ABC',250,'2014-01-01 12:00'),('2','DE F',200,'2014-05-01 08:00'),('3','GHI',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         | ABC     |      250 | 2014-01-01 12:00:00 |
      | 2         | DE F    |      200 | 2014-05-01 08:00:00 |
      | 3         | GHI     |      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

      Attachments

        Activity

          People

            bertrandop Olivier Bertrand
            takuya Takuya Aoki (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.