Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4716

CREATE TABLE with CURRENT_TIMESTAMP does not support own syntax

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 5.5.2
    • N/A
    • ?, DDLProc
    • None
    • Ubuntu 18.04, n.a.

    Description

      Columnstore does NOT support its own CREATE TABLE syntax (breaks restore of mysqldump):

      SQL> CREATE TABLE `employee` (
      `employee_id` int(11) NOT NULL,
      `full_name` varchar(30) NOT NULL,
      `first_name` varchar(30) NOT NULL,
      `last_name` varchar(30) NOT NULL,
      `position_id` int(11) DEFAULT NULL,
      `position_title` varchar(30) DEFAULT NULL,
      `store_id` int(11) NOT NULL,
      `department_id` int(11) NOT NULL,
      `birth_date` date NOT NULL,
      `hire_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
      `end_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
      `salary` decimal(10,2) NOT NULL,
      `supervisor_id` int(11) DEFAULT NULL,
      `education_level` varchar(30) NOT NULL,
      `marital_status` varchar(30) NOT NULL,
      `gender` varchar(30) NOT NULL,
      `management_role` varchar(255) DEFAULT NULL
      ) ENGINE=ColumnStore DEFAULT CHARSET=utf8
      ;
      ERROR 1178 (42000): The storage engine for the table doesn't support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

      Removing DEFAULT current_timestamp() ON UPDATE current_timestamp()

      SQL> CREATE TABLE `employee` (
      `employee_id` int(11) NOT NULL,
      `full_name` varchar(30) NOT NULL,
      `first_name` varchar(30) NOT NULL,
      `last_name` varchar(30) NOT NULL,
      `position_id` int(11) DEFAULT NULL,
      `position_title` varchar(30) DEFAULT NULL,
      `store_id` int(11) NOT NULL,
      `department_id` int(11) NOT NULL,
      `birth_date` date NOT NULL,
      `hire_date` timestamp NOT NULL,
      `end_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
      `salary` decimal(10,2) NOT NULL,
      `supervisor_id` int(11) DEFAULT NULL,
      `education_level` varchar(30) NOT NULL,
      `marital_status` varchar(30) NOT NULL,
      `gender` varchar(30) NOT NULL,
      `management_role` varchar(255) DEFAULT NULL
      ) ENGINE=ColumnStore DEFAULT CHARSET=utf8
      ;
      Query OK, 0 rows affected (1.015 sec)

      SQL> SHOW CREATE TABLE employee\G
      Table: employee
      Create Table: CREATE TABLE `employee` (
      `employee_id` int(11) NOT NULL,
      `full_name` varchar(30) NOT NULL,
      `first_name` varchar(30) NOT NULL,
      `last_name` varchar(30) NOT NULL,
      `position_id` int(11) DEFAULT NULL,
      `position_title` varchar(30) DEFAULT NULL,
      `store_id` int(11) NOT NULL,
      `department_id` int(11) NOT NULL,
      `birth_date` date NOT NULL,
      `hire_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
      `end_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
      `salary` decimal(10,2) NOT NULL,
      `supervisor_id` int(11) DEFAULT NULL,
      `education_level` varchar(30) NOT NULL,
      `marital_status` varchar(30) NOT NULL,
      `gender` varchar(30) NOT NULL,
      `management_role` varchar(255) DEFAULT NULL
      ) ENGINE=Columnstore DEFAULT CHARSET=utf8
      1 row in set (0.001 sec)

      Using this syntax again for creating a table results in the same error:

      SQL> CREATE TABLE `employee` (
      `employee_id` int(11) NOT NULL,
      `full_name` varchar(30) NOT NULL,
      `first_name` varchar(30) NOT NULL,
      `last_name` varchar(30) NOT NULL,
      `position_id` int(11) DEFAULT NULL,
      `position_title` varchar(30) DEFAULT NULL,
      `store_id` int(11) NOT NULL,
      `department_id` int(11) NOT NULL,
      `birth_date` date NOT NULL,
      `hire_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
      `end_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
      `salary` decimal(10,2) NOT NULL,
      `supervisor_id` int(11) DEFAULT NULL,
      `education_level` varchar(30) NOT NULL,
      `marital_status` varchar(30) NOT NULL,
      `gender` varchar(30) NOT NULL,
      `management_role` varchar(255) DEFAULT NULL
      ) ENGINE=Columnstore DEFAULT CHARSET=utf8;
      ERROR 1178 (42000): The storage engine for the table doesn't support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

      This breaks restore of a mariadbdump.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              oli Oli Sennhauser
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.