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

TIMESTAMP incorrect attributes?

Details

    Description

      I have a table which has an 'updated' timestamp column which on update will update the timestamp, and another timestamp column which may or may not get updated but has no default/on update attributes. I noticed that the other timestamp column was returning the same value as the updated column so looked into it a bit further.

      It seems DEFAULT and ON UPDATE is being set on the first TIMESTAMP column when not defined in the table definition, it seems to be reset for the second column though. 

      CREATE TABLE test ( a TIMESTAMP );
      		 
      DESCRIBE test;
      		 
      | a     | timestamp | NO   |     | current_timestamp() | on update current_timestamp() |
      		 
       
      		 
      CREATE TABLE test2 ( a TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
      		 
      DESCRIBE test2;
      		 
      | a     | timestamp | NO   |     | current_timestamp() |       |
      		 
       
      		 
      CREATE TABLE test3 ( a TIMESTAMP DEFAULT CURRENT_TIMESTAMP, b TIMESTAMP );
      		 
      DESCRIBE test3;
      		 
      | a     | timestamp | NO   |     | current_timestamp() |       |
      		 
      | b     | timestamp | NO   |     | 0000-00-00 00:00:00 |       |
      		 
       
      		 
      CREATE TABLE test4 ( a TIMESTAMP, b TIMESTAMP );
      		 
      DESCRIBE test4;
      		 
      | a     | timestamp | NO   |     | current_timestamp() | on update current_timestamp() |
      		 
      | b     | timestamp | NO   |     | 0000-00-00 00:00:00 |                               |

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          serg Sergei Golubchik added a comment -

          Seems to be in line with https://mariadb.com/kb/en/timestamp/#automatic-values

          MariaDB has special behavior for the first column that uses the TIMESTAMP data type in a specific table. For the first column that uses the TIMESTAMP data type in a specific table, MariaDB automatically assigns the following properties to the column:

          • DEFAULT CURRENT_TIMESTAMP
          • ON UPDATE CURRENT_TIMESTAMP

          and so on

          serg Sergei Golubchik added a comment - Seems to be in line with https://mariadb.com/kb/en/timestamp/#automatic-values MariaDB has special behavior for the first column that uses the TIMESTAMP data type in a specific table. For the first column that uses the TIMESTAMP data type in a specific table, MariaDB automatically assigns the following properties to the column: DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP and so on
          serg Sergei Golubchik added a comment -

          Note also https://mariadb.com/kb/en/server-system-variables/#explicit_defaults_for_timestamp

          serg Sergei Golubchik added a comment - Note also https://mariadb.com/kb/en/server-system-variables/#explicit_defaults_for_timestamp

          People

            serg Sergei Golubchik
            r0t3n Christopher Norris
            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.