[MDEV-7778] impossible create copy of table, if table contain default value for timestamp field - Jira

MariaDB has wonderful feature which allows easy create copy of any table.

create table `new_name` select * from `old_table`

But I found that this is not worked if `old_table` contain default value for timestamp field.

CREATE TABLE `table1` (

  `f1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

0 row(s) affected

CREATE TABLE `table2` (

  `f2` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8

0 row(s) affected

create table `table1copy` select * from `table1` limit 0;

Error Code: 1067

Invalid default value for 'f1'

create table `table2copy` select * from `table2` limit 0;

0 row(s) affected

show create table table2copy

CREATE TABLE `table2copy` (

  `f2` timestamp NULL DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

is duplicated by

MDEV-7842 Create table from select will define timestamp columns with invalid default value of '0000-00-00 00:00:00'

relates to

MDEV-8082 ON UPDATE is not preserved by CREATE TABLE .. SELECT

links to

Bug #34280 - CREATE TABLE fails if in NO_ZERO_DATE SQL mode

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

MariaDB Server